v.18.16New Feature

ALTER TABLE now supports MODIFY ORDER BY for sorting key changes in MergeTree tables

Official CH changelog text
The ALTER TABLE query now has the MODIFY ORDER BY action for changing the sorting key when adding or removing a table column. This is useful for tables in the MergeTree family that perform additional tasks when merging based on this sorting key, such as SummingMergeTree, AggregatingMergeTree, and so on. #3581 #3755
The ALTER TABLE query now supports the MODIFY ORDER BY action to change the sorting key when adding or removing columns in tables of the MergeTree family.

Why it matters

This feature allows users to modify the sorting key of MergeTree-based tables during schema changes such as adding or removing columns. It solves the problem of not being able to update the sorting key without recreating the table, which is essential for tables performing merges with special logic like SummingMergeTree and AggregatingMergeTree.

How to use it

Use the ALTER TABLE query with the MODIFY ORDER BY clause to change the sorting key. For example:

ALTER TABLE table_name MODIFY ORDER BY (new_sorting_key)

This can be combined with adding or removing columns in the same query.

Related resources

PreviousNext