alt Hacker NewsI've operated petabyte-scale ClickHouse clusters for 5 years
Comments
As an aside, I was stuck when turning on the Fulham v Crystal Palace game last week to find that Fulham have ClickHouse on their shirts this year, and Palace have Temporal AI. Talk about my worlds colliding.
That's a lot of ®, curious how ClickHouse® Inc. is treating the use of its name by others ... Hopes it's not like Oracle with JavaScript
Complex systems have complex operations and problems. I think isolating the read and write workloads and separating the read queries by departments or even users is a distinct possibility especially with technologies like DuckDB.
I’m operating a terabyte-scale Clickhouse - but only because I left Langfuse running for a few months on a MacBook :)
But seriously Clickhouse does love disk space.
I just wish Amazon would offer it as an RDS DB; it'd make my life so much easier.
My dude, you only need to put the registered trademark symbol after the first occurrence of the mark. You® don't® need® to® put® it® after® every® mention® of® Click®House®™™®™®™®™®™.
A couple weeks ago I was scanning Github for repos with frequent commits to identify so-called "software factories" and was surprised to see clickhouse. I wouldn't touch it with a 10ft pole considering how quickly they're merging code into main. I'm talking 50+ commits per day and thousands of AI generated issues and triages. Check it out for yourself https://github.com/clickhouse/clickhouse
> "Every single company handling ClickHouse® struggles with ingestion."
Very true. Reading about "too many parts" gave me flashbacks.
(previously owned ingestion into CH at PostHog, no longer)
For those interested, there's also a second part [2025]: https://www.tinybird.co/blog/what-i-learned-operating-clickh...
(note: the first part was originally published April 2025 according to the date tooltip)
I cocked an eyebrow more than once reading this.
> A quick note about HTTP: ClickHouse® offers a TCP connector with a native protocol, but we don't use it. It does not offer many advantages for the type of application we build
This needs more elaboration. One of the major goals of running a ClickHouse cluster is to provide low-latency queries; a persistent TCP connection removes the need to re-establish a new connection for each query and thus reduces overall latency in line with CH goals. So I really didn't understand this.
> ClickHouse® open source faces a significant challenge: limited support for cloud storage. Modern OLAP databases and data systems should leverage cloud storage for cost efficiency and independent scaling of compute and storage resources. Snowflake established this standard over a decade ago, and ClickHouse® (open source) lags behind
ClickHouse writing to NVMEs is exactly how they provide their latency and performance advtanges. Writing and reading to object buckets is fundamentally slower with multiple network hops to reach what is, in this architectural context, a storage server for your storage server. If you really need far more storage, and are willing to sacrifice query latency to get it... why not architect for one of the OLAP databases, like Snowflake, where that was part of their architecture from day one?
> Because you are testing your analytics queries, right?
No? Half the point of an OLAP database is to let users write their own queries. If we knew the queries ahead of time, we probably wouldn't need an OLAP database, and instead use a less-flexible streaming architecture storing intermediate calculations so as not to need to pay for petabyte-scale storage. The expected value from paying for all of that storage is to support not knowing which queries will be written by users.
> Every single company handling ClickHouse® struggles with ingestion... Backpressure mechanism: Some people put Kafka before ClickHouse®. This does the job
The whole trade-off that you make with column-store databases like ClickHouse (instead of row-store databases like Postgres) is that inserts are slow for column stores (whereas they are fast for row stores). Inserts happen slowly, asynchronously, in the background. It is the price you pay for fast analytics queries. This is why OLAP databases have a latency lag and do not show real-time results. This is why stores like Kafka are usually a good fit, you let Kafka hold onto new data until batch insertions can catch up. If you do need real-time queries, you don't write to an OLAP directly; you write to a stateful frontend that answers the query itself, then streams out historical data from the OLAP that was successfully written there. And the first thing you do in a "I want to have my cake, and eat it too, and yes I'm willing to pay for the privilege" architecture like that is... to keep the persistent TCP connections, because that's really low-hanging fruit.
Where does ClickHouse fit between ElasticSearch, Pinot, TrinoDB, or just plain Spark? These are very different tools but I’m curious to know if any one has already compared them and can share some thoughts regarding their maintainability, QPS, latency, etc...
When replaying customer queries against the next version, how do you compare results for queries using now() or approximate aggregates? Curious how you separate expected differences from actual regressions.
At first when I read this, I was like "Why does The Onion's ClickHole site need so many servers?"
[dead]
[dead]
> For loads with over 20k rows/s and people pushing changes, you may need a full-time person to handle the cluster and take a look at the crazy queries people are going to write.
I think this was a benefit of DBA culture in previous eras. Not that the DBAs were specifically necessary to write good queries (often they'd need to work with application teams to guide them towards schemas/behavior that worked well) or to maintain the database (managed DB offerings obsolete a lot of this work), but because they functioned as gatekeepers and rate-limiters of what queries and schemas could exist.
In that mode, DBAs functioned a bit like a human/process version of a thin microservice wrapping database access functionality. A big benefit was that the rate of change of queries/schema changes/access patterns was controlled and had a higher probability of being reviewed and thought about by humans before it went live. This also resulted in an increased end-database-user culture of trying to make existing schemas/query patterns work before jumping straight to bespoke access patterns. That culture's not what you want as e.g. a startup or pro-rapid-big-refactors shop, but it is what you want when your DB reliability needs or query rate/dataset size are high.
I don't think it's a given that a gatekeeper team is worth the overhead and cost; that's situational. I do think that the code version of that team (aforementioned microservice that wraps DB accesses/schema changes and nothing else) is usually not worth the cost. In my experience, that pretty much always reduces reliability and free performance gains that come from using direct DB clients from user code.