After insert, select count(1) from ReplicatedMergeTree engine table at once, get difference result for many times

Question

The situation is that: I insert some data into ReplicatedMergeTree engine table, and I do this query select count(1) from table at once, and I get different results. As I know, this is caused by the Replicated mechanism, It will spend some time for Replicated shard copying data, so if the query routes to Replicated shard and will respond different result.

How can I avoid this problem if I want to use the data I insert at once?

Answer 1

For reading from Distributed table you can play with the next settings:

• insert_quorum setting for INSERT queries. For example, for 3 replicas you specify insert_quorum = 3, so client will wait until data is replicates across all 3 replicas. https://clickhouse.com/docs/en/operations/settings/settings/#settings-insert_quorum
• select_sequential_consistency. Setting for SELECT queries. Select will include the data written with insert_quorum. https://clickhouse.com/docs/en/operations/settings/settings/#settings-select_sequential_consistency