GoKawiilTinybird is not affiliated with, associated with, or sponsored by ClickHouse, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc.
What I learned operating ClickHouse at scale: the wins, the failures, and the lessons that only come from production experience.
I have been operating a ClickHouse® cluster at Tinybird since version 18.4. That was almost 6 years ago. I wrote my first blog post about how to use ClickHouse® to perform geospatial analysis almost 8 years ago.
In that time, I've dealt with ClickHouse® daily, helped start a company that uses it, sent critical changes to the database project, and managed many petabyte-scale clusters. This ClickHouse review shares the lessons learned. Setting up a cluster is easy, the hard part is keeping it running. Let me go through the good and the bad parts, focusing on the problems you may find (so you can avoid them).
This ClickHouse review is probably useful for people handling ClickHouse® clusters, but not so much for others.
Important note, mainly for ClickHouse®, Inc lawyers: we have nothing to do with ClickHouse®, Inc. They are the sole maintainers of ClickHouse®, and ClickHouse® is a registered trademark owned by ClickHouse®, Inc. I offer my apologies for any errors contained herein and welcome the opportunity to rectify them promptly. We are merely small contributors of new engines , massive performance improvements , distributed joins , and some other things, and, of course, users of the open source version ourselves. I have huge respect for Alexey for starting the project, and for the many people working there.
Architecture
The architecture ClickHouse® proposes for managing large ClickHouse clusters uses replicas and shards. Basically, you split your data into different buckets (e.g., per hash(user_id) ), and every bucket goes to a shard. Then you have different replicas (copies of the data) for each shard.
This is a pretty standard and straightforward architecture, but it's starting to feel like the wrong approach for these kinds of systems. Many people now believe that you have to use cloud storage and separate compute from storage. I don't completely agree, but this approach does offer advantages for cluster management and reduced costs. I'll discuss this in more detail later.
A few years ago, we started with a pretty basic system with no shards, just replicas. We'd scale the replicas vertically to handle larger queries, and we'd add more replicas to handle more traffic. Cloud storage was not an option, so we used local SSD disks (to reduce latency). Adding shards would have been an option if we had to process more data in each query, but we end up not doing it because re-sharding was super hard and none of our customers had that need (well, they did, but if you are smart enough in your data schema design, you can put it off).
... continue reading