High Performance, High Reliability Data Loading on ClickHouse®

Recorded: December 9, 2020
Presenter: Alexander Zaitsev – CTO, Altinity

In this webinar, hosted by Altinity’s Robert Hodges, Altinity CTO Alexander Zaitsev examines how to load data into ClickHouse both quickly and reliably. Because ClickHouse is an analytical database, it offers some non-traditional consistency guarantees, and understanding them is essential for building reliable systems. Alexander frames the core risks: an insert that “succeeds” may still sit only in the operating system page cache, materialized views and distributed or replicated tables can end up partially written after a failure, and duplicates can appear on retries.

The first half focuses on performance and the mechanics of an insert, parsing rows into columns, splitting into partitions and parts, sorting, compressing, and writing files. From there Alexander draws practical rules: use bigger blocks, do not insert too often, be careful with aggressive compression and over-partitioning, and keep inserts close to Keeper. He then covers techniques to reduce overhead, including buffer tables and polymorphic parts (compact and in-memory parts backed by a write-ahead log).

The second half turns to reliability. Alexander explains how to make inserts atomic by writing a single part, the differences between the client and HTTP interfaces, and the roles of durability settings, materialized views, distributed inserts, and insert quorum for replicas. He then surveys deduplication, from built-in block-level deduplication and ReplacingMergeTree to application-level logical deduplication and ways to find duplicates efficiently. He closes with a roadmap toward block deduplication for non-replicated tables, transactional materialized views, and transactional multi-table inserts, followed by an extended Q&A.

If you are interested in receiving the PowerPoint presentation, please email us at marketing@altinity.com.

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:13 – Introduction and housekeeping
• 1:45 – Why insert reliability is tricky in ClickHouse
• 5:44 – What happens under the hood during an insert
• 7:50 – Performance considerations for fast inserts
• 12:55 – Buffer tables
• 15:36 – Polymorphic parts: compact and memory parts
• 20:23 – Insert atomicity and writing a single part
• 25:15 – Durability, fsync, and materialized views
• 30:14 – Distributed and replicated table inserts
• 32:59 – Kafka ingestion and best practices
• 35:31 – Deduplication techniques
• 46:24 – Final words, roadmap, and Q&A

Webinar Transcript

0:13 — Introduction and Housekeeping

Robert Hodges: Welcome everyone, my name is Robert Hodges and I’ll be your host today at our webinar on high-reliability data loading on ClickHouse. It’s my pleasure to welcome my friend and colleague Alexander Zaitsev, who is CTO of Altinity and an expert on this topic, so he’ll be running the presentation, which will start in just a second.

Let me tell you a couple of things about the webinar software we’re using that will help you enjoy the presentation. First, we are recording this webinar, and we will send links to both the recording and the slides afterwards. You’ll get that in an email because you’ve signed up. The second thing is that there’s a question and answer box available through the webinar control panel. If you have questions, feel free to just type them into the box. We can answer some of them in the background, and we should have time at the end to answer more questions. The final thing is that at the end of the webinar we will run a very brief poll, which asks three questions and takes about 15 seconds to fill out. It’s just basic information about the level of the webinar, future topics, things like that, that help us make these webinars better the next time. With that, thank you very much for attending. I’d like to turn it over to Alexander Zaitsev, so take it away, Alexander.

Alexander Zaitsev: Thank you, Robert. Today I will talk about ClickHouse inserts and high-reliability data loading. This is a topic that appears pretty regularly in different community channels, in GitHub issues, and in our support experience, because ClickHouse, as an analytical database, has some somewhat non-traditional guarantees on consistency. Understanding those and using the features properly is a must for reliable systems.

1:45 — Why Insert Reliability Is Tricky in ClickHouse

Alexander Zaitsev: Let’s start with a very simple example. Consider that we have an event stream being inserted into your ClickHouse database. You expect that once an insert is completed, your data is in ClickHouse. This is almost right, but there are more moving parts involved. In particular, there is the OS-level page cache, and ClickHouse does not flush everything immediately. So if your data is inserted, it doesn’t mean it has reached storage. Potentially, if your ClickHouse crashes right after your insert has succeeded, you may lose your data.

It gets a little more complicated if you have materialized views. When you insert data, it goes to the table, and then you may have multiple materialized views attached, and you may have cascaded materialized views. They all start writing data to their underlying tables. What happens if your insert is interrupted in the middle? It’s not as easy as it seems. If you have a distributed table, or a distributed system with multiple shards, your single insert typically goes to a single shard, even if you write to a distributed table, and only later, in the background, is the data sent or fetched to other shards. What happens if there is a failure somewhere in the middle? Do you have it consistent or not?

Let’s add replication to the picture. By default, replication is asynchronous, so once you insert the data, even if you have one or multiple replicas, it doesn’t mean the data has been inserted to all replicas. If there is a failure after you inserted the data and your box has died, what happens to your data? These are all questions we’re going to discuss today, along with what we can do in order to get consistent guarantees.

We will discuss performance first, because we definitely want our inserts to go as fast as possible. Our goal for reliability is to make sure that once the data has been inserted, it is in the server, we can access it, and it’s never lost. There is also the topic of deduplication, which often comes up, because you can be retrying if something failed, but how can you be sure your data has been inserted only once and not twice or more? This is not a trivial topic for ClickHouse.

5:44 — What Happens Under the Hood During an Insert

Alexander Zaitsev: Let’s start with performance first. In order to understand what is important for performance and how we tune the system for optimal performance, it’s important to understand what happens under the hood when the data is being inserted to a single ClickHouse server in a single table. ClickHouse does several steps when processing the data. The data itself, when you insert it, for the HTTP interface for example or from files, is organized by rows. This is a natural format from data sources. So ClickHouse parses those rows of data and converts the data to internal structures, which are columns, because ClickHouse, being a columnar database, stores everything in columns.

Then it splits columns into partitions and parts based on the table definition. So potentially a single insert can be split into multiple partitions and parts. Consider that you have the data partitioned by an hour or a day, and you insert data that actually spans multiple hours or multiple days, then you inevitably will have multiple partitions to insert with a single statement. Columns are sorted based on the primary key and the table order, and the primary key is calculated. Then columns are compressed and written to disk. But first they are written into a temporary directory, and every column may require several files in this part. Once all columns are written in this temporary directory, it is renamed to the real one and made available for your queries.

So there are quite a lot of steps here. Some steps involve CPU and other steps involve storage.

7:50 — Performance Considerations for Fast Inserts

Alexander Zaitsev: Based on those steps, we can define general considerations that are important for fast inserts. First and foremost, use bigger blocks, because there is a lot of overhead on a single insert. All this parsing, preparing columns, and writing multiple columns per insert is quite a lot of overhead. If you use bigger blocks, then this overhead, which is pretty much constant for a single statement, doesn’t increase linearly.

The second important principle is do not insert too often. ClickHouse is not an OLTP database, and it’s not designed for single-row inserts generated 100 times per second. You can insert a single record, no problem, but due to overhead you cannot insert too often, because this overhead will keep increasing. Another important consideration is compression. Sometimes, in order to save on disk space, users may want to enable aggressive compression. ClickHouse supports ZSTD up to level 22, and you can certainly do it, but after that you may be quite confused by the decreased insert performance, because ClickHouse will have to apply this compression on insert, and later on in merges and queries, which slows down your performance considerably. In fact, it’s possible in ClickHouse to configure layered compression, with less aggressive compression for small parts and more aggressive for the bigger parts.

Another important consideration is partitioning. As I mentioned, if you have multiple partitions inside one block of inserted data, then it may result in multiple different parts written to disk, and every part has a lot of files because of columns. So it’s not a good idea to partition by second, for example, because it will generate a lot of partitions. It’s also not a good idea to use random generators in your tests that generate a lot of random partitions. In our practice, we recently had a case where the table was designed quite well using daily partitioning, but when the customer generated test data, they generated inserts that contained a random day, and therefore every insert resulted in several hundred partitions. That will never happen in real life, because in real life the data comes for the current day or the day before, but in tests they got very mixed results and were confused with ClickHouse performance.

Two other considerations are related to a cluster setup. Inserts in ClickHouse communicate with the keeper, so they write metadata in Keeper, and therefore if Keeper is not close to the insertion process, then insert latency may increase. So inserts need to be close to Keeper as much as possible. The last consideration here is that ClickHouse has a lot of asynchronous operations internally, by design, because ClickHouse is designed to be as fast as possible, and asynchronous operations allow ClickHouse to be faster. But on the flip side, this is less reliable. There is always a trade-off between performance and reliability, and you can trade one for another. ClickHouse has a lot of fine-tuning settings to configure this trade-off.

12:55 — Buffer Tables

Alexander Zaitsev: Now let’s look into some particular techniques that can reduce overhead during inserts to get better performance. The first is buffer tables. Buffer tables can be used if you have a lot of ingester agents that insert small amounts of data, and you cannot buffer or aggregate it on the application side. You can set up a buffer table in ClickHouse that will collect the data from those multiple inserts and flush it using some pre-configured thresholds.

The use case is where we have multiple small inserts coming into the system. In this case you set up a buffer table connected to a particular destination table. It collects those small inserts, and when a size or time threshold is hit, it flushes the data to your destination table. On the query side, you can query data from the destination table, but this data wouldn’t contain your most recently inserted data through the buffer. On the contrary, if you query from the buffer table, then ClickHouse would automatically merge data both from the buffer and from the destination table, and you will have the full data with the most recent data included.

This is a good approach, and it has existed in ClickHouse for years, but the problem is that the data can be lost on a hard restart. So if the server has been restarted, then everything that is in the buffer can be lost. While it’s good enough, it doesn’t fit some user expectations.

15:36 — Polymorphic Parts: Compact and Memory Parts

Alexander Zaitsev: Another technique, which emerged in ClickHouse not too long ago, is polymorphic parts. This allows it to store small inserts more efficiently, both in terms of storage and in terms of data processing.

Polymorphic parts consist of two new features. One feature, which has existed since ClickHouse 20.3, is compact parts. In order to understand what it is, let’s look at how a part is stored in a file system. Wide parts, which are the default and have existed in ClickHouse for years, are a columnar structure where every part of the data is stored in multiple files. There is one index file, some metadata files, and for every column there are at least two files, files with data and files with the marks. Compact parts, which is quite a new feature, allow you to store the same data but in a smaller number of files. The index is stored as before, but the data and mark files now store all columns altogether. Internally it still maintains the column structure, but at the file system level this is just one or two files.

This is an example from the airline dataset, which contains many columns. The wide part takes 224 files to store, which is quite a big number, while the compact part only takes eight files. So the difference is tremendous, and this plays out pretty well in insert time as well. To summarize the compact part design: this is a single file or a couple of files, but the internal structure is still natural, just a different way to store the data, and the main purpose is to reduce the file system overhead, especially on small inserts. The threshold is controlled by a number of settings, and you can configure the threshold by bytes or by rows. In ClickHouse, this feature was first enabled for system tables in 20.3, which was a kind of testing in the field, because system tables always exist and ClickHouse always writes a lot of data to them, and that was a proof that this feature is stable. Since ClickHouse version 20.8, this is enabled by default, so all parts that are below 10 megabytes in size are stored in the compact form, and you don’t need to configure anything.

The next addition to the gang was memory parts. This is yet another extension to the model, and memory parts allow ClickHouse to store even smaller amounts of data in memory without writing it to disk. In order to guarantee reliability, ClickHouse has a write-ahead log, so with memory parts, even if they are stored in memory, if there is a failure, ClickHouse can restore the data from the write-ahead log after the restart, so the data is secure. The typical pipeline looks like this: a very small source goes to memory, then after inserts get bigger during the merge process, they go to compact parts, and later on compact parts are merged into the bigger wide parts, which are most efficient for queries. With memory and compact parts, queries are also good. Those structures are not as efficient for queries as wide parts, but since we only keep small amounts of data here, it’s not an issue at all.

20:23 — Insert Atomicity and Writing a Single Part

Alexander Zaitsev: So the next big topic is insert atomicity. If we line out user expectations, then a user expects that an insert writes all the data completely or aborts, writes all the data to all dependent objects like materialized views or aborts, and writes to all distributed and replicated tables or aborts. This is what users would expect, but there are no transactions in ClickHouse, and therefore it’s not trivial to guarantee those expectations. So we need to do some extra steps or extra configuration.

Let’s start again with a single table. Consider we have an insert which is committed to a single ClickHouse server. As explained earlier, the data is parsed and written in blocks and parts, and it’s interesting that those blocks are written as soon as they are ready. So if, for example, we have an insert for 10 million rows, then ClickHouse would split it into 9 or 10 parts automatically, and write the first million rows as a part, then the second million, then the third million. If anything happens to the server, you will have a partial insert: some of the data has been written and some has not. The main principle here is that we need to ensure there is a single part per insert, or if we cannot do that, then it’s very hard to guarantee atomicity.

So how can we ensure we write a single part? First, if you insert using the ClickHouse client, then the insert block is controlled by the max_insert_block_size parameter, which defaults to 1 million rows. That means ClickHouse will split data into 1-million-row parts. So in order to insert bigger blocks and guarantee atomicity, you need to increase this block size. You can increase it to 10 million rows or more, but beware of memory, because for atomicity ClickHouse needs to put everything in memory first and then write to the table. So if you’re trying to insert a billion rows and make it a single part, then you probably don’t have enough RAM for that.

Another possible approach is to insert using a temporary table, and from this temporary table run an insert select to a permanent table. In this case, however, the chunks the data can be split into are controlled by several different parameters. The first parameter is max_block_size, which is how ClickHouse reads the data from the table, so this is the select part of the equation. Then there are two parameters, min_insert_block_size_rows and min_insert_block_size_bytes, and ClickHouse will try to merge data into those amounts before writing a chunk of data to storage. So if you want to do an atomic insert here, then you need to adjust those two parameters. The same parameters are also used when inserts are going via HTTP. It’s kind of counter-intuitive that the ClickHouse client and the HTTP interface require different parameters for atomicity. The reason is that the ClickHouse client builds columnar data by itself and then sends it, but when data comes by HTTP, everything is done by the server.

Another possible problem is parallel parsing. For text formats, ClickHouse currently supports parallel parsing, and when data is parsed in parallel, ClickHouse may write it in multiple parts as well, which again might surprise you. And last but not least is the max_insert_threads parameter, which is designed for fast parallel insert selects. If you write an insert select from one table to another, then max_insert_threads can give you much better performance. If you set it to 16, for example, and you have 16 cores, then you get almost linear performance increase. But on the flip side, every thread will be writing a different part, which again may result in some inconsistency if something happens in the middle of your insert select statement.

25:15 — Durability, fsync, and Materialized Views

Alexander Zaitsev: After an insert has completed, it doesn’t mean your data is already on storage, thanks to the OS page cache. In recent ClickHouse versions, the development team added a lot of different settings for managing fsync behavior, so when to do fsync, at what point ClickHouse should call fsync. This is something to look into and probably do some experiments with. The problem with those settings is that, first, you need to understand the Linux behavior very well and know what to expect if you turn something on and off, because on one side it can result in additional latency, and on the other side, sometimes turning those settings on and off doesn’t help.

There are two good articles you can find on the internet, “Files are hard” and one about PostgreSQL fsync, that explain that even if you have fsync, it doesn’t mean your data has been written. So apparently Linux is not very reliable in terms of communication to the storage, and this is something people have to deal with. With ClickHouse, you can always have a replica, and that will give you much better guarantees of reliability than trying to tune this fsync behavior to make sure the data has been written. We will get to replication a bit later.

Before then, let’s consider a couple of other complications. One complication is materialized views. If you write to a table and you have several materialized views, then under the hood the materialized views are executed sequentially in alphabetical order, and there is no atomicity guarantee. So if one materialized view fails for whatever reason, then there is an abort only for part of the data, not for all, so you may easily get some inconsistent behavior where your table and your materialized views are out of sync.

What can we do here? First, there are two partial workarounds that reduce the probability of failure but don’t fix it 100 percent. The first is the parallel_view_processing setting, which runs all views in parallel, so if anything fails there is a bigger chance that no view has been written yet. The second recommendation is not to use cascades. In the picture here, I have materialized view 3 as a source for materialized view 1, and this is even less reliable than having multiple materialized views attached to one table. So instead of this setup, I would probably move materialized view 3 directly to my table and have just three materialized views attached to the table. By the way, ClickHouse doesn’t have any limitation on the number of materialized views attached to a single table. It’s just additional overhead and a possible reliability concern. The good news is that materialized view transactions are coming next year, so we actually have them on the roadmap.

30:14 — Distributed and Replicated Table Inserts

Alexander Zaitsev: The next thing: how can we do a reliable table insert? If we insert to a single shard, then the data is split by the sharding key. If we insert to a distributed table, the data is split by the sharding key and then stored locally in a distribution queue. Once the data is stored locally, the client is acknowledged that everything is good and your insert is successful. But this distribution queue is processed asynchronously, so if there is a hard reset on shard one, then there is possible data loss. Our main recommendation as a workaround is to do inserts locally and not insert to the distributed table. But it’s not always possible. If you have to insert to a distributed table, then there is an insert_distributed_sync parameter you can turn on, and in this case ClickHouse will write to shards in a synchronous manner and acknowledge the client only if all shards have been written successfully. There is also a timeout to control how long ClickHouse will wait until all shards acknowledge the data write.

The replicated table is also not very trivial. When the data is written to the table, the part is written locally and then registered in Keeper. After the part is written locally and registered in Keeper, the client gets an acknowledgement that the insert has been successful, and only then is data fetched asynchronously by the replica or replicas. So if there is a hard reset on shard one, even with replication we may have a data loss. The workaround here is insert quorum. You can set insert_quorum equal to two, and in this case ClickHouse will write to two replicas and make sure the data has been inserted to at least two replicas before acknowledging the client. That gives you a much better consistency guarantee, but at the cost of performance, because there is always a trade-off: writing to two replicas is slower than writing locally to a single one.

32:59 — Kafka Ingestion and Best Practices

Alexander Zaitsev: Ingestion, if you use Kafka, has even more ways to fail. You can fail because Kafka requires one more materialized view to process. There is first a Kafka engine that is subscribed to the topic, with some internal buffer, so you can think of the Kafka engine as a buffer table. Then there is a materialized view that writes to a MergeTree table or replicated MergeTree table if you have a replica, and you may have materialized views attached to this for some other reasons like aggregates. There are many ways to fail here.

The good thing is that Kafka guarantees exactly-once semantics for the consumer, and you can turn it on in the ClickHouse configuration. In this case there is a guarantee that the Kafka engine fetches the data once and only once. The later part of the pipeline is the same as with our ordinary insert, so you need to make sure you have insert quorum for the replica, proper setup of materialized views, distributed tables, and so on. So with OS guarantees, Kafka ingests are reliable in ClickHouse. There are several things that make this a bit more complicated, like having multiple topics, or multiple partitions per topic with partition rebalance, but more or less it is already solved in ClickHouse, so we think Kafka ingest is pretty much reliable.

Just to summarize best practices for reliability: don’t use buffer tables if you don’t have to, because compact and memory parts do the job better. Try to make sure an insert generates a single part if possible, because if you go to multiple parts then there is no easy way to guarantee atomicity. insert_distributed_sync and insert_quorum can help with synchronization for distributed inserts. Don’t use cascading materialized views, and you can apply durability settings if you understand them.

35:31 — Deduplication Techniques

Alexander Zaitsev: The next and last topic for today is deduplication. Let’s start with the question, why are duplicates ever possible at all? The most obvious scenario is when we retry a failed insert. For example, we try to insert and we get an error message, but sometimes we’re not sure if the error is because the insert failed, the network failed, or the client failed, and the client library may want to retry. If the previous insert was successful, then we will get duplicate data. Another possible reason is some collision in the message bus, for example with Kafka rebalances, especially in older versions of ClickHouse, where it was possible that the data has been inserted to a Kafka table twice, because every Kafka table processes independently. And also user errors: if you run your insert twice, you get duplicate data. The problem is that there are no unique keys and unique constraints in ClickHouse, so there is no good way to guarantee, at least at the database level, that there are no duplicates.

But there are some workarounds. First, there is block-level deduplication, which is built for the retry scenario, because ClickHouse keeps a history of block hashes per table. If an insert has a hash that matches a stored block in the history, then this insert is ignored completely and silently, with no error. So if you make an insert to a replicated table and then insert exactly the same data a second time, then the insert is ignored. Sometimes this results in surprising things. For example, if you develop tests and insert data in tests, you cannot insert the same value twice in a replicated table if you insert a single row, because it will be deduplicated automatically. This feature only works for replicated tables so far. There is common development for an unreplicated table, and we expect it to be completed in the next quarter. For non-replicated tables, this history of block hashes will be stored locally, and for replicated tables it is stored in Keeper. There are several settings that control this behavior, like the deduplication window, which is 100 by default, the deduplication window seconds, which is a time window, and deduplicate_blocks_in_dependent_materialized_views, which is useful when there are materialized views attached. It makes sure that if you insert into a table and there is a duplicate at the table level, ClickHouse still continues to apply the insert to all materialized views in the chain.

Another technique ClickHouse has is the ReplacingMergeTree engine. What ReplacingMergeTree does is eventually remove duplicates. You may define the primary key, and this is your kind of unique key, but ClickHouse doesn’t force uniqueness on insert and can only eventually remove duplicates during the merge. Or you can force it with optimize final to do this deduplication on the data, which may be a quite expensive operation, or you can run select final, which will do the deduplication at query time. This is good for cases when the consistency of duplicates is not that important, but you want to make sure that eventually duplicates are removed. Select final is slow for aggregate queries, so consider a table with billions of rows: select final from the table may be quite slow. But if you have a table with a filter by a key column, then it is very fast, because ClickHouse has to do this deduplication only for the data which is subject to the where condition.

In addition to tools on the database level, you can do something on the application level, which we call logical deduplication. This is good for the scenario when you have a natural unique ID in the table, or you can calculate a unique hash. It works like the following: you first insert to some temporary or intermediate table, and then when you insert select from this temporary table to your parent table, you add an extra where condition and check explicitly that the data doesn’t exist already in your destination table. This “where id not in” may be quite heavy, but you can make it fast. First, your data in the temporary table is relatively small, and second, you can apply a deduplication window for one hour, one day, or whatever, which makes it more efficient. You can even build a dedicated materialized view that keeps track of inserted IDs and that way make such a check faster. Another alternative to the temporary table is to use a Null engine. A Null engine is an interesting feature in ClickHouse that allows you to insert data but not store it, while at the same time firing all attached materialized views. So if you insert to a Null table, then you can use a materialized view with a “where id not in” condition that moves the data to your permanent table.

One of our engineers designed a bulletproof deduplication for Kafka. I don’t mean for you to try to understand this excellent diagram now, but you can scan this QR code and navigate to the bigger picture and try to understand this logic. This approach considers that with Kafka, and with rebalancing of partitions, it’s possible that your data may reach different servers due to rebalancing, so for the deduplication you have to do some sort of distributed table. So at this stage it’s not trivial anymore, but you still can make it work, and this diagram explains how.

We’re almost done, a few more topics to discuss. First, how can we find duplicates in a big table? Consider a table with billions or trillions of rows. How can you find if you have duplicates? You can try to group by your hash, where a hash is some unique identifier, and try to find the hashes that have more than one occurrence, and probably some timestamp in order to locate this data better. But if you have billions of unique rows, then it may be very slow and might take a lot of RAM. An interesting approach suggested by one of our clients is to use the neighbor function. When you use the neighbor function, you can compare the value of the column to the value of the previous row, and if those rows match, then there is a duplicate. The beauty of this approach is that it can be done in a single pass and requires much less memory. The caveat is that the neighbor function works inside blocks, and a block is controlled by max_block_size, so it works not on all your table but on a part of your table. On the border of the blocks there may be inconsistency, but it’s easy to locate. So with this approach it’s pretty fast to detect duplicates in a table, especially in a sorted window.

If you have duplicates, ClickHouse has a way to deduplicate. There is a special statement, optimize deduplicate, that fully resorts your table and removes duplicates. It considers duplicates the full row, so if there is an identical row with all values identical, those rows will be removed and only one is left. We have a feature currently in development to implement deduplication on a subset of columns. You may have rows which vary only by a timestamp, for example, but all other columns are the same, and with optimize deduplicate by, you can deduplicate by all columns except your timestamp column, or by some subset of columns. This feature is coming in the next ClickHouse version.

46:24 — Final Words, Roadmap, and Q&A

Alexander Zaitsev: Final words. As we all know, ClickHouse is very fast and reliable, but sometimes bad things happen. So proper schema design is probably the first and most important thing for reliability, so this is the first thing to pay attention to. Default ClickHouse settings are tuned for performance, so ClickHouse is configured for performance’s sake, but not for reliability, so if you want to get more reliability guarantees, you have to change some settings. Atomicity requires careful attention and design, and this is actually a painful problem in ClickHouse, but we’re going to work on that next year. The first thing to arrive is block deduplication for non-replicated tables, which is going to be delivered in Q1. The second thing is transactional materialized view updates, which would guarantee that all materialized views are updated or not updated together with your main table, and we expect something in Q2. After that, we expect transactional multi-inserts will be possible, so you may configure to have multiple inserts where all of them are successful or aborted. Those are some useful links, and you probably know all of them already, the ClickHouse website, the ClickHouse channel, and so on. Thank you, and I’ll be happy to answer questions if Robert hasn’t answered all of them yet.

Robert Hodges: I haven’t, and there are some great ones queued up for you, Alexander. First of all, thank you so much for that talk. By the way, I see a lot of folks who are Altinity customers. I’d like to give you a special welcome, and we welcome everybody else as well. It’s great to see friends on the call and to hear some of the questions you’ve been asking. So let me queue up one which came up twice: are there any performance impacts for compact parts?

Alexander Zaitsev: The performance impact is positive. On insert it works faster. On select, a compact part is slightly less efficient, but since we use it for small amounts of data, you wouldn’t notice it in regular queries.

Robert Hodges: There was another question that came out of the chat. What’s the lifetime of the block hashes? Does it persist across server restart?

Alexander Zaitsev: Yes, it does, because it’s stored in Keeper. By default ClickHouse keeps 100 blocks per table, and you can configure it per table, and it also keeps them for 7 days or 14 days, something like that, so quite a long time.

Robert Hodges: One support question that comes up periodically is people will insert test data into a replicated table, and if you repeat the insert, only the first one goes in and the next one is ignored. So we occasionally get support questions like, hey, why is ClickHouse deleting my data? And it’s just because deduplication is kicking in. Here’s a good one: is it possible to force a buffer to flush to its destination table independent of the threshold set in the buffer table config?

Alexander Zaitsev: Yes, there is a system flush statement that you can run in order to force a buffer flush.

Robert Hodges: Next question. From which version did parallel view processing become available?

Alexander Zaitsev: This is quite an old one. It’s probably from 2019, I don’t remember exactly, but it’s quite old.

Robert Hodges: So it should be in any 20 release and it’s stable?

Alexander Zaitsev: Yes.

Robert Hodges: Great, so if you’re even moderately up to date, you should be able to get that. Here’s a question on S3. This was slightly off topic but related to fast ways to load data. With S3 table functions, is it possible to read from the S3 table function asynchronously, and is it reliable in the sense that it will retry if one of the read operations on a large glob fails?

Alexander Zaitsev: No, it is not. There is no way to fetch data from S3 asynchronously right now, and if a glob fails, I don’t know what to do. But it’s pretty reliable. Recently we were experimenting with a dataset, which is our standard one for experiments, and this dataset takes only four minutes to fetch from S3, and this is something like 70 gigs. What you can do asynchronously is to write to S3. If you configure S3 storage for your MergeTree table, then you can configure ClickHouse to move data to S3 in the background, and this is done asynchronously in the background.

Robert Hodges: Cool. One of the points I think is useful to make is that we’ve told you about some bad things that can happen. Overall, looking at all the ClickHouse installations we know about, and we’ve worked with well over 100 customers, including some really big installations, most of these problems we’re describing are rare. But they do happen, and that’s why we want to make sure you understand how to get around them, and also to be clear that there’s a roadmap to ensure they cannot happen at all. There’s a related question, Alexander: are alter delete operations stable? Sometimes we need to delete rows from our analytic database.

Alexander Zaitsev: Yes, alter delete is stable. This is something that stabilized over 2020 quite a lot. But if you have a huge database, that might be quite a heavy operation, even in the background, especially if you have big partitions, and ClickHouse will have to resort quite a lot of data during this delete. So it’s okay to use, but it’s quite an expensive thing. We actually have a roadmap item to implement lightweight deletes, where ClickHouse would mark data for deletion first, so it’s immediately deleted for the users who query the data, but the delete itself is executed later in the background when time permits.

Robert Hodges: That’s a great point. If anybody on the call has interest in that feature, it comes up a lot in GDPR and data privacy conversations, so please feel free to reach out to us directly if you’re interested in helping us push that one along. It’s a really important feature. Here’s another one, back to buffer tables: what’s the best way to confirm that a buffer table is empty? Querying the buffer and the destination table and confirming the row counts are equal?

Alexander Zaitsev: Yes, maybe that’s the best way here.

Robert Hodges: Here’s one: which solutions, if any, to deduplication do you consider best practice for a large dimensional data warehouse that is inserted continuously but is occasionally updated, like maybe less than 0.1 percent of the workload is updates?

Alexander Zaitsev: It depends on what is updated. If you have a large multi-dimensional data warehouse, you typically have two types of tables. You have your so-called insert-only tables with transactional data, events, and so on, and this data usually is not updated at all. Sometimes, for some data corruption, you might need to reload some rows, but not update. On the other side, you have your dimension tables, which are slowly changing dimensions, and sometimes they may be updated. For those dimensions, the best way is to use ClickHouse dictionaries rather than ClickHouse tables, and in this case updates are handled on the other database side, because ClickHouse just maintains an up-to-date copy in memory of this data. But if you still need to update in ClickHouse, then ReplacingMergeTree is good for such use cases, because those dimensional tables are typically not very huge, and you can run optimize table for them. You can also use any join if you need to join those tables in your queries, because ClickHouse has a join extension which is any join, and in this case it will join a single row even if there are duplicates.

Robert Hodges: I hope that covered it. A related question: is there any way to know when the tables are consistent again after an update? And when I say update in the ClickHouse world, that means reinserting a row with the same unique key as a pre-existing row. So sort of a follow-on, if it’s a question about ReplacingMergeTree.

Alexander Zaitsev: That’s a good one. I don’t know from the top of my head, but you can force it, so you can force optimize table, and if it’s completed, you can be sure the data is updated.

Robert Hodges: I would have said, what’s it worth to you to know? Because it’s like, yes, you can find this out, but are you willing to pay for a scan? And if you’re sharded, finding out the answer may be expensive. That’s really the big trade-off you get with handling duplicates at scale.

Alexander Zaitsev: I would also like to add that if you insert a single row, if you want to update a single row and you insert a single row in ReplacingMergeTree, then if you don’t do anything, it will never be merged to the other rows and it will never be updated, unless you run this optimize. Because consider we have a huge table with a lot of data and you insert a single row, merging everything with a single row is an expensive operation, so ClickHouse wouldn’t do that by itself, but you can always force it.

Robert Hodges: Question: can you run alter delete on a single row, or only a partition?

Alexander Zaitsev: On a single row, sure. Any where condition is possible. And for partitions, actually those are the best things to delete. You can just drop them, and that’s a super fast operation.

Robert Hodges: Okay, let’s see. I think we’ve gotten to the end of the questions. So final question, which is sort of a follow-on, is optimize final reliable if you wait for it to complete?

Alexander Zaitsev: Optimize final, yes, it’s reliable, and this is synchronous. ClickHouse will force you to wait until completion.

Robert Hodges: Great. Some really excellent questions here. I hope we got to all of them. Let me just double-check the chat. We’re right at time, so this is perfect. Oh, there’s one final question, we’ll take one last question and then we have to go. What if we run alter table and have to add columns to MergeTree or replicated MergeTree? New data will have new columns and previous data won’t have anything in the newly added columns. What’s the best way to approach it?

Alexander Zaitsev: Use alter table, yes, just alter table. This is a very fast operation and just changes metadata. Once new data comes, it will be written to these new columns, and all old columns will be considered to have default values.

Robert Hodges: Great. All right, we’re going to call it a day. We have to close up shop, but thank you so much for attending. Alexander, thanks for a really great talk, and thanks for the wonderful questions, we really appreciate them. If you have more questions, don’t hesitate to contact us. This is what we do for a living and we love working with this. So thank you very much once again. The recording and the slide links will be sent to you in an email once we have them ourselves. So have a great day and talk to you soon.

Alexander Zaitsev: Thank you, Robert.

Robert Hodges: Thank you everybody, bye-bye.

Alexander Zaitsev: Bye everybody.

FAQ

Why can an insert into ClickHouse succeed but still lose data? A completed insert may still live only in the operating system page cache rather than on durable storage, so a crash immediately after the insert can lose it. The situation is more complex with materialized views, distributed tables, and replicated tables, where a mid-stream failure can leave data partially written. ClickHouse offers fsync and durability settings, but the most robust guarantee comes from replication, with insert quorum, rather than from tuning fsync alone.

How do you make a ClickHouse insert atomic? An insert is atomic when it writes a single part. With the ClickHouse client, the insert is split by max_insert_block_size, which defaults to one million rows, so you raise that to write larger single parts, while watching memory. With INSERT SELECT and the HTTP interface, the relevant controls are max_block_size and the min_insert_block_size_rows and min_insert_block_size_bytes settings. Parallel parsing and a high max_insert_threads can each produce multiple parts, which breaks atomicity.

What are compact and memory parts, and should I configure them? They are forms of polymorphic parts that store small inserts efficiently. Compact parts pack all columns into one or two files instead of many, dramatically reducing file system overhead on small inserts; the airline-dataset example dropped from 224 files to eight. They are enabled by default since version 20.8 for parts under 10 megabytes, so no configuration is needed. Memory parts keep even smaller data in memory, protected by a write-ahead log so it survives a restart.

How does ClickHouse handle duplicate inserts? Replicated tables use block-level deduplication: ClickHouse stores a history of block hashes (100 by default, kept for several days) in Keeper, and silently ignores an insert whose hash matches a stored block, which makes retries safe. This is why reinserting identical test data into a replicated table appears to do nothing. For removing duplicate rows by key over time, ReplacingMergeTree deduplicates eventually during merges, and you can force it with OPTIMIZE FINAL or resolve duplicates at query time with SELECT FINAL.

What’s the safest way to load data into a distributed or replicated table? For distributed tables, the most reliable option is to insert into the local shard tables directly; if you must write to the distributed table, enable insert_distributed_sync so the client is acknowledged only after all shards are written. For replicated tables, set insert_quorum to two so ClickHouse confirms the data reached at least two replicas before acknowledging the client. Both improve consistency at some cost to performance.

Is Kafka ingestion into ClickHouse reliable? Largely yes. A Kafka engine table feeds a materialized view that writes into a MergeTree or replicated MergeTree table, and there are several points where failure can occur. ClickHouse can enable exactly-once semantics for the Kafka consumer, ensuring the engine fetches each message once. From there, the same reliability practices apply, insert quorum for replicas and careful materialized view and distributed-table setup, so with those guarantees, Kafka ingestion is considered reliable.

© 2026 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.