Senior

What is the difference between ORDER BY and PRIMARY KEY in ClickHouse, and why are they separated?.

sobes.tech AI

Answer from AI

In ClickHouse, PRIMARY KEY and ORDER BY are different concepts, although they are closely related.

ORDER BY defines the physical order of data storage in the table. ClickHouse stores data sorted by the sort key, which allows efficient execution of queries with filters and aggregations on these fields.
PRIMARY KEY is a logical key used for searching and merging data during inserts and updates. In ClickHouse, PRIMARY KEY does not impose uniqueness constraints but serves to optimize access.

Separating these concepts allows flexible configuration of storage and indexing. For example, you can set ORDER BY to optimize reading, and PRIMARY KEY to manage data merging.

Example:

CREATE TABLE example (
    id UInt64,
    date Date,
    value Float64
) ENGINE = MergeTree()
ORDER BY (date, id)
PRIMARY KEY (id);

Here, data is physically ordered by date and id, which speeds up queries by date, and PRIMARY KEY by id helps efficiently update or search records by identifier.

Previous Next