ClickHouse DB::Exception: Sizes of columns doesn't match

The "DB::Exception: Sizes of columns doesn't match" error in ClickHouse signals that the internal arrays backing different columns in a data block contain different numbers of rows. Under normal circumstances every column in a block should have exactly the same row count, so this error typically points to data corruption on disk, a bug in a table engine or merge operation, or a faulty custom input format.

Impact

This error is serious. It usually surfaces during SELECT queries, merges, or mutations and can prevent you from reading affected parts entirely. If the corrupted part participates in a merge, the merge will fail repeatedly and may stall background operations across the table. In replicated setups a single bad part can block the replication queue.

Common Causes

Data corruption on disk — bit-rot, interrupted writes, or faulty storage can leave part files in an inconsistent state where column files disagree on the number of rows.
Incomplete or interrupted mutations — a killed mutation may leave partially-rewritten column files behind.
Engine-level bugs — certain edge cases in MergeTree variants or projection maintenance have historically triggered this mismatch.
External tooling modifying part directories — manually copying, renaming, or editing files inside a part directory can break internal consistency.
Faulty custom input formats — a user-defined format or an external program piping data via INSERT ... FORMAT may produce blocks where columns have unequal lengths.

Troubleshooting and Resolution Steps

Identify the affected part. Check the full error message — it normally includes the part name. You can also query the system log:
```
SELECT * FROM system.part_log
WHERE table = 'your_table' AND event_type = 'MergeParts'
ORDER BY event_time DESC
LIMIT 20;
```
Validate the part with CHECK TABLE. This reads through part data and reports corruption:
```
CHECK TABLE your_database.your_table;
```
Detach and re-attach the bad part. If the part is small or can be regenerated, detaching it removes it from the active set:
```
ALTER TABLE your_table DETACH PART 'all_1_1_0';
```
On replicated tables the replica will re-fetch a healthy copy from another replica automatically once the bad part is detached.
Drop the corrupted part (replicated tables). When a good copy exists on another replica:
```
ALTER TABLE your_table DROP DETACHED PART 'all_1_1_0';
```
The replication queue will schedule a fetch for the missing range.
Restore from a backup. If all replicas carry the same corruption, restore the table or the specific partition from your most recent backup.
Investigate merge and mutation logs. Look for killed or stuck mutations that may have caused the inconsistency:
```
SELECT * FROM system.mutations
WHERE table = 'your_table' AND is_done = 0;
```
Upgrade ClickHouse. If the root cause is a known engine bug, check the ClickHouse changelog and upgrade to a version that includes the fix.

Best Practices

Use replicated tables so that a corrupt part on one node can be automatically replaced from another replica.
Enable checksums (the default) and periodically run CHECK TABLE as part of your maintenance routine.
Avoid manual manipulation of part directories on disk — use SQL commands for attach, detach, and move operations.
Keep ClickHouse up to date to benefit from bug fixes in merge and mutation logic.
Maintain regular backups so you have a recovery path when all replicas are affected.

Frequently Asked Questions

Q: Is the SIZES_OF_COLUMNS_DOESNT_MATCH error always caused by disk corruption?
A: Not always. While disk corruption is a frequent cause, the error can also stem from engine bugs, interrupted mutations, or external tools modifying part files. The key indicator is that column arrays inside a single block have different lengths.

Q: Can I continue reading other parts of the table while one part is corrupt?
A: Yes. If you know the affected partition, you can exclude it with a WHERE clause or detach the bad part to unblock queries on the rest of the data.

Q: Will replication automatically heal this on a ReplicatedMergeTree table?
A: If you detach or drop the corrupt part, ClickHouse will re-fetch it from a healthy replica. However, if every replica has the same corruption, you will need to restore from a backup.

Q: How do I prevent this error from recurring?
A: Use reliable storage, keep checksums enabled, avoid manually editing part files, and stay on a current ClickHouse release. Monitoring system.part_log for merge errors can help you catch issues early.