ClickHouse Primary Key vs Index: How They Differ (2026)

The primary key in ClickHouse isn’t a constraint like in traditional relational databases; it’s the sorting key that dictates how data is physically ordered on disk.

Let’s see this in action. Imagine you have a table events that logs user actions:

CREATE TABLE events (
    event_timestamp DateTime,
    user_id UInt64,
    event_type String,
    details String
) ENGINE = MergeTree()
ORDER BY (event_timestamp, user_id);

Here, (event_timestamp, user_id) is your ORDER BY clause, which is effectively your primary key. When you insert data, ClickHouse physically sorts rows based on this tuple. Rows with earlier event_timestamp come first. If timestamps are the same, then rows with smaller user_id come first.

INSERT INTO events VALUES
('2023-10-26 10:00:00', 101, 'login', '{"ip": "192.168.1.1"}'),
('2023-10-26 10:05:00', 102, 'click', '{"button": "buy"}'),
('2023-10-26 10:00:00', 100, 'view', '{"page": "/home"}');

When ClickHouse writes this data, it might look something like this on disk (conceptually):

Now, if you query for events within a specific time range:

SELECT * FROM events WHERE event_timestamp BETWEEN '2023-10-26 09:59:00' AND '2023-10-26 10:01:00';

ClickHouse can efficiently locate the relevant data blocks because the event_timestamp is the first column in the ORDER BY key. It doesn’t need to scan every single row. It uses a primary index (often a sparse index built on the ORDER BY key) to jump directly to the data blocks that might contain the desired timestamps.

The ORDER BY clause defines the physical sorting and is the primary index. Any other INDEX you define is a secondary index, which is an additional data structure built on specific columns to speed up queries that filter on those columns.

Consider adding a secondary index:

ALTER TABLE events ADD INDEX user_id_idx user_id TYPE set(1024);

This user_id_idx is a secondary index. It creates a separate data structure that maps user_id values to the physical location of rows containing those user_ids. This index is useful if you frequently query by user_id alone:

SELECT * FROM events WHERE user_id = 101;

Without this index, ClickHouse would have to scan the entire table or rely heavily on the primary index, which might not be optimal for filtering solely on user_id. With the secondary index, ClickHouse can quickly find all rows associated with user_id = 101 without scanning irrelevant data.

The key difference lies in their function: the ORDER BY key (primary key) dictates the physical layout of data, enabling efficient range scans and sorting. Secondary INDEXes are supplementary structures that accelerate lookups on columns not covered by the primary key’s prefix.

It’s crucial to understand that secondary indexes have overhead. They consume disk space and add latency to inserts and updates because these index structures also need to be maintained. Therefore, you should only add secondary indexes for columns that are frequently used in WHERE clauses and where the performance gain justifies the overhead.

ClickHouse’s primary index (derived from ORDER BY) is built on the leading columns of the ORDER BY key. For a key like (event_timestamp, user_id), the primary index covers event_timestamp and the combination of event_timestamp and user_id. Queries that filter on event_timestamp or event_timestamp and user_id will benefit directly from the primary index. Queries filtering only on user_id will not benefit from the primary index unless user_id is the first column in the ORDER BY key.

The ORDER BY key in MergeTree tables is not just about data ordering; it’s the foundation for ClickHouse’s data skipping capabilities. When a query has a WHERE clause, ClickHouse consults the primary index (and any active secondary indexes) to determine which data parts (files on disk) could contain the matching rows. It uses the min/max values stored for each column within a data part to make this decision. If a data part’s range for a queried column falls entirely outside the WHERE clause’s filter, that entire data part is skipped, drastically reducing I/O.

The TYPE parameter for secondary indexes, like set(1024) or minmax, determines how the index is structured and what kind of information it stores. set(N) creates a set of the N most frequent values, useful for equality checks on high-cardinality columns. minmax stores the minimum and maximum value for a column within a data block, excellent for range queries.

The next concept you’ll encounter is how to choose the optimal ORDER BY key for your specific workload, balancing query performance with insertion speed and disk usage.