Clickhouse primary key

How Clickhouse primary key works and how to choose it

Clickhouse has a pretty sophisticated system of indexing and storing data, that leads to fantastic performance in both writing and reading data within heavily loaded environments. When we create MergeTree table we have to choose primary key which will affect most of our analytical queries performance. In order to make the best choice here, let’s figure out how Clickhouse primary keys work and how to choose them.

Setting primary key

Each MergeTree table can have single primary key, which must be specified on table creation:

CREATE TABLE test
(
    `dt` DateTime,
    `event` String,
    `user_id` UInt64,
    `context` String
)
ENGINE = MergeTree
PRIMARY KEY (event, user_id, dt)
ORDER BY (event, user_id, dt)

Here we have created primary key on 3 columns in the following exact order: event, user_id, dt. Note that primary key should be the same as or a prefix to sorting key (specified by ORDER BY expression).

Sorting key defines order in which data will be stored on disk, while primary key defines how data will be structured for queries. Usually those are the same (and in this case you can omit PRIMARY KEY expression, Clickhouse will take that info from ORDER BY expression).

Granuled data storage

Clickhouse divides all table records into groups, called granules:

Clickhouse granules

Number of granules is chosen automatically based on table settings (can be set on table creation). Default granule size is 8192 records, so number of granules for a table will equal to:

A granule is basically a virtual “minitable” with low number of records (8192 by default) that are subset of all records from main table. Each granule stores rows in a sorted order (defined by ORDER BY expression on table creation):

Clickhouse granule sorted data

Primary key marks and index storage

Primary key stores only first value from each granule instead of saving each row value (as other databases usually do):

Clickhouse Primary Key storage details

This is something that makes Clickhouse so fast. Instead of saving all values, it saves only a portion making primary keys super small. And instead of finding individual rows, Clickhouse finds granules first and then executes full scan on found granules only (which is super efficient due to small size of each granule):

Clickhouse Primary Key filtering process

Query performance

Let’s populate our table with 50 million random data records:

INSERT INTO test
SELECT * FROM generateRandom(
  'dt datetime, event Text, user_id UInt64, context Text'
) LIMIT 50000000;

As set above, our table primary key consist of 3 columns:

Clickhouse will be able to use primary key for finding data if we use column(s) from it in the query:

As we can see searching by a specific event column value resulted in processing only a single granule which can be confirmed by using EXPLAIN:

That’s because, instead of scanning full table, Clickouse was able to use primary key index to first locate only relevant granules, and then filter only those granules. We can also use multiple columns in queries from primary key:

On the contrary, if we use columns that are not in primary key, Clickhouse will have to scan full table to find necessary data:

At the same time, Clickhouse will not be able to fully utilize primary key index if we use column(s) from primary key, but skip start column(s):

When primary key index is used

Clickhouse will utilize primary key index for best performance when:

1. Query WHERE or ORDER clause contains first column from primary key.
2. QueryWHERE clause contains first X columns from primary key. And if any, ORDER clause contains following columns from primary key.
3. Query WHERE clause contains all columns from primary key.

In other cases Clickhouse will need to scan all data to find requested data.

Primary key usage in Clickhouse

Summary on choosing primary key

Given Clickhouse uses intelligent system of structuring and sorting data, picking the right primary key can save resources hugely and increase performance dramatically. When choosing primary key columns, follow several simple rules:

1. Pick only columns that you plan to use in most of your queries.
2. Pick the order that will cover most of partial primary key usage use cases (e.g. 1 or 2 columns are used in query, while primary key contains 3).
3. If not sure, put columns with low cardinality first and then columns with high cardinality. This will lead to better data compression and better disk usage.