Deduplicating inserts on retries

Insert operations can sometimes fail due to errors such as timeouts. When inserts fail, data may or may not have been successfully inserted. This guide covers how deduplication on insert retries works, so that the same data doesn’t get inserted more than once.

When an insert is retried, ClickHouse tries to determine whether the data has already been successfully inserted. If the inserted data is marked as a duplicate, ClickHouse doesn’t insert it into the destination table. However, the user will still receive a successful operation status as if the data had been inserted normally.

Deduplication covers synchronous inserts, asynchronous inserts, and INSERT ... SELECT queries. One setting, deduplicate_insert, controls synchronous and asynchronous inserts. INSERT ... SELECT needs extra care and has a setting of its own. See Settings that control insert deduplication.

Limitations

Uncertain insert status

The user must retry the insert operation until it succeeds. If all retries fail, it is impossible to determine whether the data was inserted or not. When materialized views are involved, it is also unclear in which tables the data may have appeared. The materialized views could be out of sync with the source table.

Deduplication window limit

If more than *_deduplication_window other insert operations occur during the retry sequence, deduplication may not work as intended. In this case, the same data can be inserted multiple times.

Settings that control insert deduplication

ClickHouse deduplicates an insert only when both of the following hold:

1. The destination table keeps a deduplication log. This is a table-level setting.
2. Deduplication is enabled for the query. This is a query-level setting.

Table-level settings

Only *MergeTree engines support deduplication on insertion.

For *ReplicatedMergeTree engines, the deduplication log is enabled by default and is controlled by the replicated_deduplication_window and replicated_deduplication_window_seconds settings. For non-replicated *MergeTree engines, the log is controlled by the non_replicated_deduplication_window setting, which is 0 by default. A plain MergeTree table therefore deduplicates nothing until you set that window to a positive value.

The settings above determine the parameters of the deduplication log for a table. The deduplication log stores a finite number of block_ids, which determine how deduplication works (see below).

Query-level settings

deduplicate_insert accepts three values:

• enable — deduplication is enabled for the INSERT query.
• disable — deduplication is disabled for the INSERT query.
• backward_compatible_choice — the decision is delegated to the legacy settings insert_deduplicate (synchronous inserts) and async_insert_deduplicate (asynchronous inserts).

Note that a query which runs with deduplicate_insert = disable writes no block_ids for its blocks. Such data can’t be deduplicated later, even if you retry the insert with deduplicate_insert = enable. The same holds when the destination table keeps no deduplication log: nothing is recorded, so nothing can be matched on a retry.

Precedence

1. For an INSERT ... SELECT query, deduplicate_insert_select decides. See Deduplication for INSERT … SELECT.
2. For every other INSERT, deduplicate_insert decides.
3. insert_deduplicate and async_insert_deduplicate are read only when deduplicate_insert is backward_compatible_choice.

Legacy and obsolete settings

Version 26.2 also changed the defaults of async_insert and deduplicate_blocks_in_dependent_materialized_views to enabled. The compatibility setting governs all three. If you set compatibility to a version earlier than 26.2, these settings keep their old defaults: deduplicate_insert becomes backward_compatible_choice, which hands the decision to insert_deduplicate and async_insert_deduplicate. A setting you assign explicitly is always honored and is never affected by compatibility.

How insert deduplication works

When data is inserted into ClickHouse, it splits data into blocks based on the number of rows and bytes.

For tables using *MergeTree engines, each block is assigned a unique block_id, which is a hash of the data in that block. This block_id is used as a unique key for the insert operation. If the same block_id is found in the deduplication log, the block is considered a duplicate and isn’t inserted into the table.

This approach works well for cases where inserts contain different data. However, if the same data is inserted multiple times intentionally, you need to use the insert_deduplication_token setting to control the deduplication process. This setting allows you to specify a unique token for each insert, which ClickHouse uses to determine whether the data is a duplicate. insert_deduplication_token has higher priority: ClickHouse doesn’t use the hash sum of the data when the token is provided.

For INSERT ... VALUES queries, splitting the inserted data into blocks is deterministic and is determined by settings. Therefore, you should retry insertions with the same settings values as the initial operation.

Deduplication for INSERT ... SELECT

For INSERT ... SELECT queries, the SELECT part must return the same data in the same order on every attempt. Otherwise the blocks differ, the block_ids differ, and the retry isn’t recognized as a duplicate.

ClickHouse can’t verify that the source data is unchanged, but it can check whether the query itself produces a reproducible result. A SELECT is treated as stable when both of the following hold:

• The query carries an ORDER BY ALL clause. Only the literal ORDER BY ALL is recognized. A plain ORDER BY <expressions> isn’t, and a UNION of two or more SELECTs is never stable.
• The reading pipeline ends in a single stream.

A non-empty insert_deduplication_token is an equivalent substitute for stability, because the token, and not the data, then identifies the insert.

The setting deduplicate_insert_select chooses what to do:

enable_when_possible and enable_even_for_bad_queries also honor deduplicate_insert: if it is disable, the query isn’t deduplicated. force_enable overrides deduplicate_insert.

Keep in mind that the selected table can be updated between retries. The two paths then behave in opposite ways:

• Without insert_deduplication_token, the block_ids are computed from the data. The changed result produces different block_ids, deduplication doesn’t occur, and the retry inserts the new data on top of whatever the first attempt already wrote.
• With insert_deduplication_token, the token alone identifies the insert. The retry is recognized as a duplicate and is dropped, even though it would have inserted different data.

Choose the path that matches what you want a retry to mean. Additionally, when you insert large amounts of data, the number of blocks can overflow the deduplication log window, and ClickHouse won’t know to deduplicate the blocks.

Deduplication for asynchronous inserts

Asynchronous inserts (async_insert, enabled by default since version 26.2) are deduplicated on retries in the same way as synchronous inserts. deduplicate_insert controls both, so no separate switch is needed.

The two insert types also share one deduplication log and compute block_ids the same way. You can therefore switch a client between synchronous and asynchronous inserts without breaking deduplication, and a retry sent in one mode is still recognized as a duplicate of an attempt sent in the other. Moving a workload from synchronous to asynchronous inserts stays safe on a table that relies on deduplication.

Deduplication granularity

The server collects several asynchronous inserts into one batch and writes that batch as one or more parts, at least one per distinct partition key value. Deduplication works per user query, not per batch:

• Each queued query contributes one deduplication token to the batch.
• A token is either the value of insert_deduplication_token, when the query provides one, or a hash of the rows that this query contributed.
• Batching doesn’t influence the tokens, and insert_deduplication_token doesn’t influence how queries are grouped into batches.

This has two consequences:

• When one query in a batch is a duplicate, ClickHouse removes only the rows of that query. The rest of the batch is inserted normally. A part is skipped entirely only when every row in it is removed.
• When two queries in the same batch carry the same token, the second one is dropped before the part is written. This applies per partition: if the two queries write rows to different partitions, both survive.

The DuplicatedAsyncInserts and SelfDuplicatedAsyncInserts events in system.events count these two cases.

Asynchronous inserts and materialized views

Deduplication of asynchronous inserts works together with dependent materialized views. The rule is simple: one block in, one block out. If the inner query of a view turns one input block into one output block, deduplication works. If the view emits a second block, ClickHouse throws a NOT_IMPLEMENTED exception.

A view emits a second block when its output no longer fits in one. max_block_size sets how many rows fit. Column transformations, filtering, and aggregation never add rows, so they always stay in one block. A JOIN can add rows. It works while the result stays under max_block_size, and it fails above that.

To insert through a view that emits more than one block, either set deduplicate_blocks_in_dependent_materialized_views = 0 or use synchronous inserts.

Insert deduplication with materialized views

When a table has one or more materialized views, the inserted data is also inserted into the destination of those views with the defined transformations. The transformed data is also deduplicated on retries. ClickHouse performs deduplications for materialized views in the same way it deduplicates data inserted into the target table.

You can control this process using the following settings for the source table:

• replicated_deduplication_window
• replicated_deduplication_window_seconds
• non_replicated_deduplication_window

Deduplication in the tables under materialized views is additionally governed by the user profile setting deduplicate_blocks_in_dependent_materialized_views, which is enabled by default since version 26.2. Both switches must allow it: deduplicate_insert deduplicates the data inserted into the source table, and deduplicate_blocks_in_dependent_materialized_views additionally deduplicates the data in the dependent tables. Enable both if you want full deduplication.

When inserting blocks into tables under materialized views, ClickHouse calculates the block_id by hashing a string that combines the block_ids from the source table and additional identifiers. This ensures accurate deduplication within materialized views, allowing data to be distinguished based on its original insertion, regardless of any transformations applied before reaching the destination table under the materialized view.

Examples

Identical blocks after materialized view transformations

Identical blocks, which have been generated during transformation inside a materialized view, aren’t deduplicated because they’re based on different inserted data.

Here is an example:

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE MATERIALIZED VIEW mv_dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000
AS SELECT
    0 AS key,
    value AS value
FROM dst;

SET max_block_size=1;
SET min_insert_block_size_rows=0;
SET min_insert_block_size_bytes=0;

The settings above allow us to select from a table with a series of blocks containing only one row. These small blocks aren’t squashed and remain the same until they’re inserted into a table.

We make deduplication in the materialized view explicit, although it is enabled by default:

SET deduplicate_blocks_in_dependent_materialized_views=1;

INSERT INTO dst SELECT
    number + 1 AS key,
    IF(key = 0, 'A', 'B') AS value
FROM numbers(2);

SELECT
    *,
    _part
FROM dst
ORDER BY all;

┌─key─┬─value─┬─_part─────┐
│   1 │ B     │ all_0_0_0 │
│   2 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

Here we see that two parts have been inserted into the dst table. 2 blocks from select – 2 parts on insert. The parts contains different data.

SELECT
    *,
    _part
FROM mv_dst
ORDER BY all;

┌─key─┬─value─┬─_part─────┐
│   0 │ B     │ all_0_0_0 │
│   0 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

Here we see that 2 parts have been inserted into the mv_dst table. That parts contain the same data, however they’re not deduplicated.

INSERT INTO dst SELECT
    number + 1 AS key,
    IF(key = 0, 'A', 'B') AS value
FROM numbers(2);

SELECT
    *,
    _part
FROM dst
ORDER BY all;

┌─key─┬─value─┬─_part─────┐
│   1 │ B     │ all_0_0_0 │
│   2 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

SELECT
    *,
    _part
FROM mv_dst
ORDER by all;

┌─key─┬─value─┬─_part─────┐
│   0 │ B     │ all_0_0_0 │
│   0 │ B     │ all_1_1_0 │
└─────┴───────┴───────────┘

Here we see that when we retry the inserts, all data is deduplicated. Deduplication works for both the dst and mv_dst tables.

Identical blocks on insertion

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

SET max_block_size=1;
SET min_insert_block_size_rows=0;
SET min_insert_block_size_bytes=0;

Insertion:

INSERT INTO dst SELECT
    0 AS key,
    'A' AS value
FROM numbers(2);

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   0 │ A     │ all_0_0_0 │
└────────────┴─────┴───────┴───────────┘

With the settings above, two blocks result from select– as a result, there should be two blocks for insertion into table dst. However, we see that only one block has been inserted into table dst. This occurred because the second block has been deduplicated. It has the same data and the key for deduplication block_id which is calculated as a hash from the inserted data. This behaviour isn’t what was expected. Such cases are a rare occurrence, but theoretically is possible. In order to handle such cases correctly, the user has to provide a insert_deduplication_token. Let’s fix this with the following examples:

Identical blocks in insertion with insert_deduplication_token

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

SET max_block_size=1;
SET min_insert_block_size_rows=0;
SET min_insert_block_size_bytes=0;

Insertion:

INSERT INTO dst SELECT
    0 AS key,
    'A' AS value
FROM numbers(2)
SETTINGS insert_deduplication_token='some_user_token';

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   0 │ A     │ all_2_2_0 │
│ from dst   │   0 │ A     │ all_3_3_0 │
└────────────┴─────┴───────┴───────────┘

Two identical blocks have been inserted as expected.

SELECT 'second attempt';

INSERT INTO dst SELECT
    0 AS key,
    'A' AS value
FROM numbers(2)
SETTINGS insert_deduplication_token='some_user_token';

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   0 │ A     │ all_2_2_0 │
│ from dst   │   0 │ A     │ all_3_3_0 │
└────────────┴─────┴───────┴───────────┘

Retried insertion is deduplicated as expected.

SELECT 'third attempt';

INSERT INTO dst SELECT
    1 AS key,
    'b' AS value
FROM numbers(2)
SETTINGS insert_deduplication_token='some_user_token';

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   0 │ A     │ all_2_2_0 │
│ from dst   │   0 │ A     │ all_3_3_0 │
└────────────┴─────┴───────┴───────────┘

That insertion is also deduplicated even though it contains different inserted data. Note that insert_deduplication_token has higher priority: ClickHouse doesn’t use the hash sum of data when insert_deduplication_token is provided.

Different insert operations generate the same data after transformation in the underlying table of the materialized view

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE MATERIALIZED VIEW mv_dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000
AS SELECT
    0 AS key,
    value AS value
FROM dst;

SET deduplicate_blocks_in_dependent_materialized_views=1;

select 'first attempt';

INSERT INTO dst VALUES (1, 'A');

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER by all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   1 │ A     │ all_0_0_0 │
└────────────┴─────┴───────┴───────────┘

SELECT
    'from mv_dst',
    *,
    _part
FROM mv_dst
ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐
│ from mv_dst   │   0 │ A     │ all_0_0_0 │
└───────────────┴─────┴───────┴───────────┘

select 'second attempt';

INSERT INTO dst VALUES (2, 'A');

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER by all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   1 │ A     │ all_0_0_0 │
│ from dst   │   2 │ A     │ all_1_1_0 │
└────────────┴─────┴───────┴───────────┘

SELECT
    'from mv_dst',
    *,
    _part
FROM mv_dst
ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐
│ from mv_dst   │   0 │ A     │ all_0_0_0 │
│ from mv_dst   │   0 │ A     │ all_1_1_0 │
└───────────────┴─────┴───────┴───────────┘

We insert different data each time. However, the same data is inserted into the mv_dst table. Data isn’t deduplicated because the source data was different.

Different materialized view inserts into one underlying table with equivalent data

CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE TABLE mv_dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE MATERIALIZED VIEW mv_first
TO mv_dst
AS SELECT
    0 AS key,
    value AS value
FROM dst;

CREATE MATERIALIZED VIEW mv_second
TO mv_dst
AS SELECT
    0 AS key,
    value AS value
FROM dst;

SET deduplicate_blocks_in_dependent_materialized_views=1;

select 'first attempt';

INSERT INTO dst VALUES (1, 'A');

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER by all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   1 │ A     │ all_0_0_0 │
└────────────┴─────┴───────┴───────────┘

SELECT
    'from mv_dst',
    *,
    _part
FROM mv_dst
ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐
│ from mv_dst   │   0 │ A     │ all_0_0_0 │
│ from mv_dst   │   0 │ A     │ all_1_1_0 │
└───────────────┴─────┴───────┴───────────┘

Two equal blocks inserted to the table mv_dst (as expected).

SELECT 'second attempt';

INSERT INTO dst VALUES (1, 'A');

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   1 │ A     │ all_0_0_0 │
└────────────┴─────┴───────┴───────────┘

SELECT
    'from mv_dst',
    *,
    _part
FROM mv_dst
ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐
│ from mv_dst   │   0 │ A     │ all_0_0_0 │
│ from mv_dst   │   0 │ A     │ all_1_1_0 │
└───────────────┴─────┴───────┴───────────┘

That retry operation is deduplicated on both tables dst and mv_dst.