Blog· Tested on ClickHouse 26.4

PRIMARY KEY that is not a key, ORDER BY that is not a query: two naming requests

In 2022 I asked to rename MergeTree's PRIMARY KEY (it identifies nothing) and ORDER BY (it is not a query clause). One is open, the other was closed in a day. Neither word changed.

Source: github.com

I filed two naming requests the same day. #34426 asked to rename MergeTree’s PRIMARY KEY (to PRIMARY INDEX, or SORT INDEX), because it is not a key: it identifies nothing and enforces nothing, it is a sparse index over the sorted data. #34427 asked to rename or alias the table-level ORDER BY to SORT BY, because it controls on-disk layout and people read it as a promise about query result order.

#34427 got an answer within hours: a maintainer asked whether it was too late to change, noted Vertica uses ORDER BY the same way, and closed it the next morning with no change. #34426 never got a maintainer reply; it is open, labelled usability, comp-sql-syntax and external, with one comment, mine.

The point is easy to show:

CREATE TABLE a (k UInt32, j UInt32, v String) ENGINE = MergeTree PRIMARY KEY k ORDER BY (k, j);
INSERT INTO a VALUES (1, 1, 'x'), (1, 1, 'y');
SELECT count() FROM a WHERE k = 1;   -- 2

Neither SORT BY nor PRIMARY INDEX parses on 26.4. The one-sentence version: the primary key is the prefix of the sorting key that gets indexed, and neither is unique. See ORDER BY, PARTITION BY and how queries skip data.

feature-requestmergetreenaming

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.