OPTIMIZE TABLE with a WHERE clause

Maintenance on a partitioned table is rarely “do this to partition 202401”; it is “do it to every partition older than three months” or “every partition with more than one part”. OPTIMIZE TABLE and the partition commands only take literal partitions, so you query system.parts to generate statements, paste them into a script and run that, once per node. KILL QUERY already has a WHERE. I asked for the same on OPTIMIZE, for example OPTIMIZE TABLE metrics WHERE partition_id < '202404', and said I did not care about the spelling.

The only comment, from a community member, generalised it better than I had: the same condition could drive DROP PARTITION and ATTACH PARTITION FROM. His caveat was atomicity, since a command that picks its own targets can act on a different set than the one it looked at. For idempotent OPTIMIZE that does not matter; for DROP it is a real design question. There has been no maintainer reply and no PR. Labels are feature and comp-mergetree; the issue is open.

On 26.4 a WHERE after the table name is a syntax error at parse time (Expected one of: ... ON, PARTITION, DRY RUN, FINAL, FORCE, DEDUPLICATE, CLEANUP ...). The workaround is still the generator:

SELECT format('OPTIMIZE TABLE {}.{} PARTITION ID \'{}\' FINAL;', database, table, partition_id)
FROM system.parts
WHERE database = currentDatabase() AND table = 'metrics' AND active AND partition_id < '202404'
GROUP BY database, table, partition_id
HAVING count() > 1
ORDER BY partition_id;

For a single partition see the optimize one partition snippet.