ClickHouse distributed ReplacingMergeTree FINAL LIMIT and keyset pagination cursor stability

Answer to the core question

In current ClickHouse releases, a keyset cursor that relies on FINAL + LIMIT on a Distributed table is not guaranteed to be gap‑free or duplicate‑free unless two conditions are met:

• The sharding key used by the Distributed table is identical to the ORDER BY key of the underlying ReplacingMergeTree.
• No per‑shard LIMIT is applied before the final deduplication step.

When the sharding key differs from the sorting key, duplicates can exist on multiple shards. A LIMIT pushed to each shard (the default behaviour) may truncate the set of rows that reach the initiator. FINAL then deduplicates only the rows that survived that truncation, which can vary from page to page. As a result, a cursor such as WHERE key > last_key can skip or repeat keys across pages.

Effect of distributed_push_down_limit

Setting distributed_push_down_limit=1 forces the LIMIT to be applied on the initiator after all shards have returned their rows. This changes which physical rows are examined by FINAL and can alter the set of surviving row versions between pages. The behaviour is version‑sensitive and is not deterministic across releases.

Recommended safe pattern

1. Align sharding and sorting keys. Create the Distributed table with a sharding key that matches the ORDER BY of the ReplacingMergeTree. Then FINAL deduplication is globally sufficient, and a keyset cursor will be stable.
2. Disable push‑down for pagination queries. If you cannot align the keys, set distributed_push_down_limit=0 (or use a SET statement before the query). This ensures all rows are collected by the initiator, and you can safely apply FINAL or a GROUP BY … argMax(version) at the top level.
3. Validate with EXPLAIN. Inspect query_log or system.query_log to confirm that the LIMIT is not being pushed to shards and that FINAL is applied at the initiator.

Diagnostic question

To decide the safest approach for your workload, we need to know whether the sharding key of your Distributed table matches the ORDER BY key of the underlying ReplacingMergeTree. If they differ, the recommendation is to disable push‑down and deduplicate at the initiator.