v.24.12Experimental Feature

New MergeTree Setting for Descending Sort Order Support in ClickHouse

Official CH changelog text
A new MergeTree setting allow_experimental_reverse_key that enables support for descending sort order in MergeTree sorting keys. This is useful for time series analysis, especially TopN queries. Example usage: ENGINE = MergeTree ORDER BY (time DESC, key)- descending order for the time field. #71095 (Amos Bird).
Introduces a new MergeTree setting allow_experimental_reverse_key that enables the use of descending sort order in MergeTree table sorting keys.

Why it matters

This feature addresses the need to efficiently support descending order in sorting keys, which is particularly valuable for time series analysis and improving the performance of TopN queries by allowing columns like timestamps to be sorted in descending order.

How to use it

To use this feature, enable the setting allow_experimental_reverse_key in your MergeTree table configuration and define the sorting key with descending order as part of the ORDER BY clause. For example:

ENGINE = MergeTree
ORDER BY (time DESC, key)

This will sort the time field in descending order.

Related resources

PreviousNext