Clickhouse table per tenant in production · Anantha Kumaran
Random thoughts of a programmer.
© 2026. All rights reserved.
Anantha Kumaran<br>random thoughts
Clickhouse table per tenant in production
02 Jun 2026
At work, we made a decision to go with a table per tenant approach and<br>we have been running that setup for a couple of years now. I thought this<br>would be the right time to share what we have learned and what works<br>well.
Why?
The first question is why do you want to create a table per tenant? In<br>our case, we allow users to define their own event attributes with<br>their own types. There are 2 main ways you can handle this: keep all<br>the attributes as a JSON field, or create a table per tenant.<br>Clickhouse json support has been getting better and better. When<br>we did the benchmark 2 years ago, creating a table per tenant was way<br>better from multiple perspectives, like less storage due to better<br>compression, way less query latency. Back then JSON was mostly stored<br>as a string, so you had to load and parse the whole blob rather than<br>just the columns you needed. I will not spend more time on why we made<br>this decision here. This post is more about what you need to be aware<br>of and how to handle things if you decide to go down this route.
Table vs Partition vs Part
+-----------------------------------------+<br>| table |<br>| +---------------------------------+ |<br>| | partition1 | |<br>| | +--------+ +-------+ +-------+ | |<br>| | | part1 | | part2 | | part3 | | |<br>| | +--------+ +-------+ +-------+ | |<br>| +---------------------------------+ |<br>| +---------------------------------+ |<br>| | partition2 | |<br>| | +--------+ +-------+ +-------+ | |<br>| | | part1 | | part2 | | part3 | | |<br>| | +--------+ +-------+ +-------+ | |<br>| +---------------------------------+ |<br>+-----------------------------------------+
A clickhouse table is made of multiple partitions and each partition<br>is made of multiple parts. Clickhouse creates a new part per insert<br>and there are usually multiple parts per partition and multiple<br>partitions per table. Most of the complexity comes from having too many<br>parts. Clickhouse recommends no more than 5k tables, 50k<br>partitions and 100k parts.
The number of tables doesn’t really matter, what you need to watch is<br>the total part count. You can have just 10 tables but still hit the<br>too many parts problem if your partitions are not set up<br>carefully.
Parts are immutable
In Clickhouse, parts are immutable. Every insert creates a new<br>part. In the background, Clickhouse merges parts together and 5 to 20<br>parts per partition is considered normal. If the merge process can’t<br>keep up with the rate of inserts, things will start to go out of<br>control.
Clickhouse gives you two ways to insert data. The first is batch<br>insert, where you handle the batching yourself. This is atomic and<br>durable. The second is asynchronous insert, where Clickhouse<br>buffers rows in memory at the partition level and flushes them every n<br>seconds. There is a risk of data loss if the server crashes and you<br>don’t wait for the flush.
If you go with asynchronous insert, you will likely need to tune the<br>following settings
async_insert_busy_timeout_ms = "30000" # 30 seconds<br>async_insert_max_data_size = "104857600" # 100 mb<br>async_insert_max_query_number = "1000"
The value you want to set depends on many factors. This affects how<br>long it takes for new data to show up in a read query and if you wait<br>for the flush, this also determines how long it takes to get an ack for<br>an insert. Set up proper monitoring for async operations.
Number of parts
Your major goal should be to keep the number of parts under control. The<br>total number of parts per server is one of the main things you need to monitor<br>closely, and it’s surprisingly easy to let it get out of hand. In most cases,<br>you should avoid creating partitions per table. Since you already have a lot of<br>tables, adding partitions on top will multiply the part count and make things<br>worse much faster.
Server startup time
Clickhouse loads all tables during startup. If you have a lot of tables,<br>like 10k+, it can take multiple minutes. At peak, our servers were taking<br>more than 5 minutes to start. There is a flag called<br>async_load_databases to control whether you want to load tables<br>asynchronously. At first glance, async load might look like a great<br>idea, but it usually doesn’t work very well. It’s likely that you will<br>be running more than 1 clickhouse server and you would be rolling<br>restart the servers. If you use async_load_databases, the server<br>will immediately announce to the world that it’s up and ready to serve<br>requests. But if you send any requests and that specific table is not<br>yet loaded, it will take quite a lot of time to finish. If you have<br>any reasonable system load, this will usually end up causing a mini incident.
It is best to load the tables synchronously and let the other servers<br>handle the requests while it’s restarting. If you run it on<br>Kubernetes, it also makes sense to have a...