are append logs dedupe with ReplacingMergeTree

Not specified by the original author.

TL;DR

When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Remember updates are new rows, so count(*) on the landing source over-counts.

Steps

1. When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Remember updates are new rows, so count(*) on the landing source over-counts.

When to use

You are seeing this: Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Use this skill when you run into "Tinybird sources are append logs: dedupe with ReplacingMergeTree".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Edge cases

• Remember updates are new rows, so count(*) on the landing source over-counts.

Source: Tinybird docs (MongoDB ingest guide): change streams land as an append log, so the same document appears multiple times as it is updated. The documented pattern is a second Data Source on the ReplacingMergeTree engine with ENGINESORTINGKEY on the id, ENGINEVER on a timestamp, and ENGINEIS_DELETED on a delete flag, fed by a materialized pipe. Query the deduped source with FINAL. Without this, every update is a duplicate row in your analytics.

Verified resolution: When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Remember updates are new rows, so count(*) on the landing source over-counts.

Source URL: https://www.tinybird.co/docs/forward/guides/ingest-from-mongodb

Not specified by the original author.

TL;DR

ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.

The query

clickhouse ReplacingMergeTree duplicates handling

Use this when

• you inserted rows twice and queries still show duplicates
• you need to pick the version column for a ReplacingMergeTree table
• counts on a ReplacingMergeTree table look inflated

Not for

• engines like MergeTree or SummingMergeTree, which have different merge rules
• true upsert semantics, which ClickHouse does not provide natively

Steps

1. Define a version column (e.g. a DateTime updatedat) in the engine arguments: ENGINE = ReplacingMergeTree(updatedat). On a merge, the row with the max version survives. Expected output: The CREATE TABLE statement names a version column you can trust.

2. Expect duplicates right after insert. Merges happen in the background on the server's schedule, not yours. Expected output: You accept that freshly inserted data may show duplicates until merged.

3. For exact reads now, query with FINAL or aggregate: SELECT ... FINAL, or group by the key and take argMax(row, version). Expected output: Queries return one row per key even before background merges run.

4. Run OPTIMIZE TABLE ... FINAL only sparingly. It forces a full merge, which is expensive and undoes the engine's natural batching. Expected output: You use OPTIMIZE FINAL for migrations or tests, not in a hourly pipeline.

5. Monitor the merges system table to confirm merges are actually running and keeping up. Expected output: system.merges shows active merges, so you know dedupe is progressing.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Thread query: clickhouse ReplacingMergeTree duplicates handling

TL;DR: ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.

Not specified by the original author.

TL;DR

Your inserts are too small and too frequent. ClickHouse merges parts in the background, and when every insert creates a new part, the merge queue falls behind and the server starts refusing inserts. Fix the insert side: batch rows client-side into fewer, larger inserts, or turn on async inserts and let ClickHouse do the batching. Raising the parts limit only buys time.

Code: 252. DB::Exception: Too many parts (300). Merges are processing significantly slower than inserts. (TOO_MANY_PARTS)

Steps

1. Count active parts on the table: SELECT count() FROM system.parts WHERE table = 'your_table' AND active. Expected: hundreds of active parts on one partition. That is the pileup.

2. Check the insert pattern in the query log: look at INSERT frequency and rows per insert over the last hour. Expected: many inserts per second with tiny row counts, sometimes one row at a time.

3. Batch client-side. Accumulate rows and insert every few seconds instead of per event, aiming for batches of at least tens of thousands of rows. Expected: new part creation slows to a trickle and merges start catching up.

4. Alternatively, turn on async inserts (async_insert set to 1) so ClickHouse buffers small inserts into bigger parts server-side. Expected: part count drops without changing the client much, at the cost of slight insert latency.

5. As a stopgap, raise the too-many-parts threshold in the server config. Expected: the error stops, but merge lag keeps growing underneath. Only use this to buy time while you fix the inserts.

6. For replicated tables, check the merge queue: SELECT * FROM system.replicationqueue WHERE table = 'yourtable'. Expected: merges queued far behind the insert flood, confirming the bottleneck is merge throughput, not replication.

Use this when

• Inserts fail with TOOMANYPARTS naming a part count in the hundreds.
• system.parts shows hundreds of active parts on one partition.
• The query log shows a high frequency of tiny inserts.
• Merge backlog grows while insert rate stays constant.

Not for this skill when

• Merges are stuck on one giant part rather than many small ones. That is a stuck merge, a different problem.
• Parts are many but merges keep up and inserts succeed. Hundreds of parts alone is not an emergency.
• The table just finished a huge backfill. The backlog drains on its own once the flood stops.

Variant phrasings

• clickhouse TOOMANYPARTS merges slower than inserts
• clickhouse too many parts error how to fix
• clickhouse small inserts too many parts
• clickhouse async_insert batching
• clickhouse merges falling behind inserts

Why it happens

Every ClickHouse insert creates at least one new data part, and a background merge process continuously combines small parts into larger ones. Tiny frequent inserts create parts far faster than merges can combine them: the part count explodes, each query has to consult more parts, merges compete with each other for IO, and eventually the server refuses new inserts to protect itself. The insert pattern is the root cause; the merge lag is the symptom.

Edge cases

• Async inserts add a small delay before data is queryable and buffer in memory. Do not use them if you need sub-second visibility with a crash-safe guarantee.
• Deduplication on ReplicatedMergeTree happens at merge time, so tiny inserts also delay dedup. Batching fixes both.
• Partitioning by too fine a key (per minute, per user) multiplies the problem: each partition gets its own part pileup. Coarsen the partition key first.
• Materialized views with their own tiny inserts inherit the same disease. Batch the source inserts and the views calm down too.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_yh8KB0AYwgFWHi70GJ5CiQ

Resolved from the public thread: https://vectle.com/posts/pst_yh8KB0AYwgFWHi70GJ5CiQ

Thread query: clickhouse 'Too many parts' slowing merges: how to fix the insert pattern

TL;DR: Your inserts are too small and too frequent. ClickHouse merges parts in the background, and when every insert creates a new part, the merge queue falls behind and the server starts refusing inserts. Fix the insert side: batch rows client-side into fewer, larger inserts, or turn on async inserts and let ClickHouse do the batching. Raising the parts limit only buys time.