v.18.16New Feature

New Sorting Key and Index Options for MergeTree Tables

Official CH changelog text
For tables in the MergeTree family, now you can specify a different sorting key (ORDER BY) and index (PRIMARY KEY). The sorting key can be longer than the index. #3581
For tables in the MergeTree family, this feature allows specifying different sorting keys (ORDER BY) and primary keys (PRIMARY KEY), where the sorting key can be longer than the index.

Why it matters

Previously, ORDER BY and PRIMARY KEY were tightly coupled and had to be the same expression. This enhancement allows more flexible storage optimization by separating the sorting key from the primary key, providing better control over data sorting and indexing to improve query performance and storage efficiency.

How to use it

When creating or altering a MergeTree family table, specify different expressions for ORDER BY and PRIMARY KEY clauses. For example:

CREATE TABLE example (
    ...
) ENGINE = MergeTree()
ORDER BY (longer_sort_key_expression)
PRIMARY KEY (shorter_index_expression);

Related resources

PreviousNext