Why ClickHouse Sharded Table Queries Return Inconsistent Row Counts—and How to Fix It

0. Problem Analysis

In an online ClickHouse (CK) cluster, several dashboard metrics displayed erratic data volumes: the same sharded table sometimes returned 4 k+ rows, sometimes only 1 k+, or 3 k+. The issue was reproducible across multiple tables and did not affect local (non‑sharded) tables.

Example: three rows were written to a sharded table that maps to three CK instances, each holding a local table with one row. Querying the sharded table sometimes returned 1, sometimes 2, and sometimes all 3 rows.

1. Inspect Configuration File

The cluster consists of six CK instances, grouped in pairs as backup replicas, forming three replica groups. This configuration enables both sharding and a replication factor of two.

The DDL for the sharded table and its corresponding local tables is shown below.

2. Why It Happens

When writing to a sharded table, CK distributes rows to the three replica groups according to the sharding key (cityHash64). However, the table was created with a non‑replicated engine, so each write is sent to only one instance within a replica group.

During a query, CK follows the same configuration and selects a random instance from each replica group. If the chosen instance did not receive the write (because the write went to its sibling), the query misses that shard’s data, resulting in a lower row count. This explains the observed pattern where missing rows belong to specific local tables.

3. How to Resolve

The fix is to align the table engine with the cluster’s replication intent. Convert each local table from a non‑replicated engine to a replicated engine (e.g., ReplicatedMergeTree) so that writes are duplicated across the two instances in a replica group.

After altering the DDL, synchronize existing data from the old tables to the new replicated ones, then resume writes. Re‑run the queries multiple times; the row count stabilizes and matches the actual data volume.

Conclusion

The inconsistent query results are not a ClickHouse bug per se; they stem from a misconfiguration where the sharded table’s underlying local tables were non‑replicated despite the cluster being set up for redundancy. Correcting the table engine eliminates the issue.