ClickHouse 26.2 Broke insert_deduplicate = 0: Why Your Re-Inserts Silently Vanish and How to Fix It

You upgrade a ClickHouse cluster from 25.8 to 26.x. Everything looks fine. Then a re-sync job, a reindex, or a backfill runs, reports "rows written" with no error — and the data isn't there. No exception, no failed query, nothing in your application logs. Just missing rows.

If that sounds familiar, you've hit a genuine breaking change introduced in ClickHouse 26.2 around insert deduplication. This post explains precisely what changed, why your data disappears, how to confirm you're affected, and — most importantly — the right way to fix it (spoiler: it's usually not turning deduplication off).

TL;DR

• Since 26.2, a new setting deduplicate_insert defaults to enable and takes precedence over the old insert_deduplicate. Setting insert_deduplicate = 0 no longer disables deduplication on its own.
• On Replicated* tables, ClickHouse identifies each inserted block by a hash and remembers recent block hashes in Keeper. Re-inserting byte-identical data whose hash is still remembered is dropped silently — the query still reports success.
• This bites re-syncs, reindexes, backfills, idempotent replays, and the classic ALTER ... DELETE followed by re-INSERT, because a delete does not clear the remembered block hashes.
• 26.2 also turned async_insert on by default, which can make the behavior inconsistent across nodes and harder to diagnose.
• The right fix is almost always a stable, per-batch insert_deduplication_token — not deduplicate_insert = 'disable'.

What actually changed in 26.2

Before 26.2, insert deduplication for synchronous inserts was governed by insert_deduplicate (and async_insert_deduplicate for async inserts). Setting insert_deduplicate = 0 was the well-known escape hatch when you wanted to insert the same data again.

26.2 restructured this. There is now a single main switch, deduplicate_insert, which accepts three values:

• enable — deduplicate this insert (the new default).
• disable — don't deduplicate, and write no block IDs at all for this insert.
• backward_compatible_choice — defer to the legacy insert_deduplicate / async_insert_deduplicate settings.

The critical line, straight from the official documentation: since version 26.2, deduplicate_insert defaults to enable, so setting insert_deduplicate = 0 no longer turns deduplication off on its own. The legacy setting is only consulted when deduplicate_insert is backward_compatible_choice.

In other words: your existing insert_deduplicate = 0 configuration compiles, applies, shows up correctly in system.query_log — and does nothing. That's what makes this so easy to miss.

Two other 26.2 default flips ride along with the same compatibility gate and matter here:

• async_insert now defaults to on.
• deduplicate_blocks_in_dependent_materialized_views now defaults to on.

Why your data disappears: the block-hash mechanism

To fix this confidently you need the mental model.

When you insert into a *MergeTree table, ClickHouse splits the data into blocks. For each block it computes a block_id — a hash of the block's contents. On Replicated* tables, these block IDs are recorded in Keeper and kept for a sliding window (controlled by replicated_deduplication_window and replicated_deduplication_window_seconds).

On every insert, ClickHouse checks the incoming block's hash against that window. If the hash is already there, the block is considered a duplicate and skipped — silently, and reported as success. This is by design: it's what makes insert retries safe. If a client times out and retries, you don't get double-written data.

The problem is that "identical bytes = duplicate" is only correct when the duplicate is an accidental retry. It's wrong when you intend to insert the same data again. And several completely normal operations do exactly that:

• Backfills and reindexes that re-emit rows that haven't changed.
• Idempotent replays from a queue or CDC pipeline.
• Re-syncs that push a table's contents again.
• ALTER TABLE ... DELETE followed by re-INSERT of the same rows.

That last one is the sharpest trap, and it's worth spelling out.

The DELETE + re-INSERT trap

A real, reproducible scenario (reported against 26.3 on a replicated cluster) goes like this:

-- 1) Insert a row — its block hash is now recorded in Keeper
INSERT INTO events VALUES ('2026-03-11', 1);
 
-- 2) Delete it — the on-disk data is gone,
--    but the block hash in Keeper is NOT cleared
ALTER TABLE events DELETE WHERE date = '2026-03-11';
 
-- 3) Re-insert the exact same row — silently dropped, 0 rows written
INSERT INTO events VALUES ('2026-03-11', 1);
 
SELECT count() FROM events WHERE date = '2026-03-11';
-- Expected: 1
-- Actual:   0

The delete removes the rows but leaves the block hash sitting in Keeper's deduplication window. The re-insert produces the same hash, matches the remembered one, and is discarded. Pre-26.2 you could have worked around it with insert_deduplicate = 0; post-26.2 that switch is ignored by default, so the workaround silently stops working — which is exactly why an upgrade surfaces it.

If you look in the server log for an affected insert, you'll see lines like:

Found duplicate block IDs: ..., retry times 1
Block with ID ... already exists locally as part ...; ignoring it.

Those are the smoking gun.

Why insert_deduplicate = 0 doesn't save you (even before you consider 26.2)

There's a second, subtler reason to stop relying on insert_deduplicate = 0. When you insert through a Distributed table into ReplicatedMergeTree locals, insert_deduplicate only affects the Distributed layer. The block-hash check still happens at the ReplicatedMergeTree layer via Keeper. So even with insert_deduplicate = 0 correctly propagated to every node, the replicated layer can still drop the block. 26.2 just removed the last illusion that the old flag was in control.

The async_insert complication

Because 26.2 also enabled async_insert by default, the same logical data can travel two different code paths (sync vs async) and, in some cluster configurations, end up with different block hashes on different nodes. The result is maddening to debug: the row survives on some replicas and is dropped on others, so counts differ depending on which node answers your query. If you're chasing "data present on node A, missing on node B" after an upgrade, this is a prime suspect.

How to confirm you're affected

Don't guess — verify. A few reliable checks:

1. Count before and after a deliberate re-insert. Insert a known batch twice and confirm the row count doesn't move on the second attempt when you expected it to.

2. Watch the parts. Inspect part creation around the insert:

   SELECT name, rows, min_block_number, max_block_number, modification_time
   FROM system.parts
   WHERE table = 'events' AND active
   ORDER BY modification_time DESC
   LIMIT 10;

   If a repeated insert adds no new part (and max_block_number doesn't advance), the block was deduplicated.

3. Compare reported rows to reality. system.query_log will show your insert with insert_deduplicate = 0 applied and a nonzero written_rows, while the table count stays flat. That mismatch — success reported, nothing landed — is the signature of this bug.

4. Grep the server log for Found duplicate block IDs / already exists locally ... ignoring it during the window of the insert.

How to fix it

Here are the options, ordered roughly by how I'd recommend them. All are consistent with the official 26.2 behavior.

1. Use a stable, per-batch insert_deduplication_token (recommended)

insert_deduplication_token overrides the content hash entirely — when a token is set, ClickHouse identifies the insert by the token, not by the data. Give each logically-distinct batch its own token:

INSERT INTO events
SETTINGS insert_deduplication_token = 'reindex/2026-09-21/studies/chunk-0007'
VALUES (...);

Or via a client, set it per batch:

insert_deduplication_token = "<stable batch identity>"

This is the correct fix because it preserves the good half of deduplication (a retry of the same batch, with the same token, is still deduplicated) while allowing genuinely intended re-inserts to go through (a different batch gets a different token).

The one rule that matters: the token must be stable across retries of the same batch, and distinct across different batches. Derive it from a natural batch identity — file name, job run ID, offset range, chunk number, partition + date. Do not derive it from anything freshly minted per attempt (a new UUID, a per-attempt query ID); if you do, every retry gets a new identity and you'll double-write on retries — reintroducing the exact problem deduplication exists to prevent.

For pure streaming ingestion where every block already carries new data, you often need no token at all — the hashes never collide. Reserve tokens for the paths that re-emit identical data.

2. deduplicate_insert = 'backward_compatible_choice' (bridge to unblock the upgrade)

If you have many insert sites and need the upgrade to proceed now, this restores the pre-26.2 semantics exactly: it's the only mode that still reads insert_deduplicate / async_insert_deduplicate. Your existing insert_deduplicate = 0 configs start working again with no code change.

SET deduplicate_insert = 'backward_compatible_choice';

Apply it per query, per user profile, or server-wide. Treat it as a bridge, not a destination — it's a compatibility knob that will eventually go away. Use it to buy time while you migrate the affected paths to tokens.

3. compatibility = '25.8' (make the whole upgrade behavior-neutral)

Setting compatibility to a version earlier than 26.2 reverts all three 26.2 default changes at once — deduplicate_insert becomes backward_compatible_choice, and async_insert and deduplicate_blocks_in_dependent_materialized_views return to their old defaults.

SET compatibility = '25.8';

This is useful when you want the upgrade to change nothing about behavior while you audit — especially if you rely on materialized views and want the MV dedup default reverted too. The catch: it also reverts flips you might actually want, and it hides the problem rather than resolving it. If you use it, pin it deliberately and track it as tech debt.

4. deduplicate_insert = 'disable' (usually the wrong answer)

It's tempting to just turn it off:

SET deduplicate_insert = 'disable';   -- think twice

The decisive objection, per the docs: an insert run with disable writes no block IDs at all, so that data can never be deduplicated later either. A subsequent genuine retry after a network timeout will silently double-write. Only reach for this if you have end-to-end idempotency elsewhere — for example a ReplacingMergeTree with dedup-on-read via FINAL.

5. Make the data genuinely non-repeating (data-model fix)

If the source data legitimately differs between runs, no setting is needed — the hashes won't collide. The classic failure is a "one row per entity" table whose row hasn't changed in months, so a reindex produces a byte-identical block. Adding a real per-run column (an ingest timestamp, a run ID) makes every insert unique by construction. That's reasonable for new tables; it's rarely worth a migration on existing ones, where a token is cheaper.

Which fix should you pick?

The healthy end state for most teams is: tokens on the paths that intentionally re-insert identical data, everything else left on the default. The compatibility knobs are how you get there without a rushed migration, not where you stop.

An upgrade checklist

Before you move a cluster to 26.2+:

1. Inventory intentional re-inserts. Find every job that re-emits identical data: backfills, reindexes, replays, re-syncs, and any DELETE-then-INSERT flow.
2. Give each a stable per-batch token, derived from a natural batch identity — never from a per-attempt UUID.
3. Unblock with a bridge if needed (backward_compatible_choice on the affected profiles, or compatibility = '25.8' cluster-wide) so the upgrade isn't gated on the full migration.
4. Verify with the count / system.parts / system.query_log checks above on a staging cluster before production.
5. If you use materialized views, validate MV behavior too — deduplicate_blocks_in_dependent_materialized_views also flipped on in 26.2.

Wrapping up

This isn't a bug so much as a default that finally aligned deduplication under one switch — but the migration is sharp because the old escape hatch (insert_deduplicate = 0) goes quiet without complaint, and the failure mode is silent data loss on success, the worst kind to debug. Understand the block-hash model, find the paths where you intentionally re-insert the same data, and give them stable per-batch tokens. Use the compatibility settings as a bridge to keep your upgrade moving, and treat "just disable dedup" as the last resort it is.

Get this right once and your re-syncs, reindexes, and backfills become boring again — which, for data infrastructure, is exactly what you want.

References: https://clickhouse.com/docs/concepts/features/operations/insert/deduplicating-inserts-on-retries