ClickHouse DB::Exception: Cannot assign OPTIMIZE

The "DB::Exception: Cannot assign OPTIMIZE" error in ClickHouse occurs when an OPTIMIZE TABLE statement cannot be executed. This can happen because the table is in readonly mode, there are no parts eligible for merging, or the operation is blocked by an ongoing process. The error code is CANNOT_ASSIGN_OPTIMIZE.

Impact

The OPTIMIZE TABLE operation does not execute, meaning parts are not merged as requested. This can lead to a growing number of data parts over time, potentially degrading query performance. However, ClickHouse performs background merges automatically, so failing to run OPTIMIZE manually is usually not critical unless you are trying to force a specific merge for deduplication or data compaction.

Common Causes

1. Table is in readonly mode -- replicated tables enter readonly mode when they lose their ZooKeeper connection. In this state, OPTIMIZE is blocked.
2. No parts to merge -- the table has only one part or all parts are already optimally merged.
3. Another OPTIMIZE or merge is already running -- concurrent OPTIMIZE requests on the same table can conflict.
4. Table engine does not support OPTIMIZE -- not all engines support this operation (e.g., Log family engines).
5. Replica is not the leader -- in replicated tables, only the leader replica can assign merges. Non-leader replicas will reject OPTIMIZE.

Troubleshooting and Resolution Steps

1. Check if the table is in readonly mode:

   SELECT database, table, is_readonly, is_leader
   FROM system.replicas
   WHERE table = 'your_table';
   

   If is_readonly = 1, resolve the ZooKeeper connection issue first.

2. Check current part count and merge activity:

   -- Count parts
   SELECT count() FROM system.parts
   WHERE database = 'default' AND table = 'your_table' AND active;
   
   -- Check ongoing merges
   SELECT * FROM system.merges
   WHERE database = 'default' AND table = 'your_table';
   

3. Wait for ongoing merges to complete before retrying:

   -- Monitor merge progress
   SELECT table, progress, elapsed, num_parts
   FROM system.merges
   WHERE table = 'your_table';
   

4. Verify the replica is the leader for replicated tables:

   SELECT is_leader FROM system.replicas WHERE table = 'your_table';
   

   If not the leader, run OPTIMIZE on the leader replica instead.

5. Use OPTIMIZE with FINAL for forced merging:

   -- Force merge into a single part (resource intensive)
   OPTIMIZE TABLE your_table FINAL;
   
   -- For a specific partition
   OPTIMIZE TABLE your_table PARTITION '2024-01' FINAL;
   

6. Check if the table engine supports OPTIMIZE:

   SELECT engine FROM system.tables WHERE name = 'your_table';
   

   Only MergeTree family engines support OPTIMIZE.

Best Practices

• Do not rely on manual OPTIMIZE for regular operation. ClickHouse's background merge process handles part merging automatically.
• Use OPTIMIZE TABLE ... FINAL sparingly, as it forces all parts to merge into one per partition and can be very resource-intensive on large tables.
• If you need deduplication, design your queries to handle duplicates with FINAL keyword in SELECT or use ReplacingMergeTree with periodic OPTIMIZE for cleanup.
• Monitor system.replicas for readonly status to catch ZooKeeper connectivity issues early.
• Schedule OPTIMIZE operations during low-traffic periods to avoid resource contention.

Frequently Asked Questions

Q: Is it necessary to run OPTIMIZE TABLE regularly?
A: No. ClickHouse automatically merges parts in the background. Manual OPTIMIZE is only needed in specific scenarios like forcing deduplication in ReplacingMergeTree or compacting data for a specific partition.

Q: What does OPTIMIZE TABLE FINAL do differently?
A: OPTIMIZE TABLE FINAL forces ClickHouse to merge all parts in each partition into a single part, even if the background merger would not normally do so. This is resource-intensive and should be used judiciously.

Q: Why does OPTIMIZE succeed but no merge happens?
A: If the table has only one active part per partition, there is nothing to merge. OPTIMIZE may return success without performing any action. Use OPTIMIZE ... FINAL if you need to force a rewrite.

Q: Can I run OPTIMIZE on a non-leader replica?
A: No. In replicated tables, only the leader assigns merges. Running OPTIMIZE on a non-leader will result in this error. Connect to the leader replica to run the operation.