A practical introduction to primary indexes in ClickHouse

`UserID.bin`, `URL.bin`, and `EventTime.bin` are the data files on disk where the values of the `UserID`, `URL`, and `EventTime` columns are stored.

Note

- As the primary key defines the lexicographical order of the rows on disk, a table can only have one primary key.
- We're numbering rows starting with 0 in order to be aligned with the ClickHouse internal row numbering scheme that is also used for logging messages.

### Data is organized into granules for parallel data processing[​](#data-is-organized-into-granules-for-parallel-data-processing "Direct link to Data is organized into granules for parallel data processing")

For data processing purposes, a table's column values are logically divided into granules.
A granule is the smallest indivisible data set that is streamed into ClickHouse for data processing.
This means that instead of reading individual rows, ClickHouse is always reading (in a streaming fashion and in parallel) a whole group (granule) of rows.

Note

Column values aren't physically stored inside granules: granules are just a logical organization of the column values for query processing.

The following diagram shows how the (column values of) 8.87 million rows of our table
are organized into 1083 granules, as a result of the table's DDL statement containing the setting `index_granularity` (set to its default value of 8192).

The first (based on physical order on disk) 8192 rows (their column values) logically belong to granule 0, then the next 8192 rows (their column values) belong to granule 1 and so on.

Note

- The last granule (granule 1082) "contains" less than 8192 rows.
- We mentioned in the beginning of this guide in the "DDL Statement Details", that we disabled [adaptive index granularity](/docs/whats-new/changelog/2019#experimental-features-1) (in order to simplify the discussions in this guide, as well as make the diagrams and results reproducible).

  Therefore all granules (except the last one) of our example table have the same size.
- For tables with adaptive index granularity (index granularity is adaptive by [default](/docs/operations/settings/merge-tree-settings#index_granularity_bytes) the size of some granules can be less than 8192 rows depending on the row data sizes.
- We marked some column values from our primary key columns (`UserID`, `URL`) in orange.
  These orange-marked column values are the primary key column values of each first row of each granule.
  As we will see below, these orange-marked column values will be the entries in the table's primary index.
- We're numbering granules starting with 0 in order to be aligned with the ClickHouse internal numbering scheme that is also used for logging messages.

### The primary index has one entry per granule[​](#the-primary-index-has-one-entry-per-granule "Direct link to The primary index has one entry per granule")

The primary index is created based on the granules shown in the diagram above. This index is an uncompressed flat array file (primary.idx), containing so-called numerical index marks starting at 0.

The diagram below shows that the index stores the primary key column values (the values marked in orange in the diagram above) for each first row for each granule.
Or in other words: the primary index stores the primary key column values from each 8192nd row of the table (based on the physical row order defined by the primary key columns).
For example

- the first index entry ('mark 0' in the diagram below) is storing the key column values of the first row of granule 0 from the diagram above,
- the second index entry ('mark 1' in the diagram below) is storing the key column values of the first row of granule 1 from the diagram above, and so on.

In total the index has 1083 entries for our table with 8.87 million rows and 1083 granules:

Note

- For tables with [adaptive index granularity](/docs/whats-new/changelog/2019#experimental-features-1), there is also one "final" additional mark stored in the primary index that records the values of the primary key columns of the last table row, but because we disabled adaptive index granularity (in order to simplify the discussions in this guide, as well as make the diagrams and results reproducible), the index of our example table doesn't include this final mark.
- The primary index file is completely loaded into the main memory. If the file is larger than the available free memory space then ClickHouse will raise an error.

Inspecting the content of the primary index

On a self-managed ClickHouse cluster we can use the [file table function](https://clickhouse.com/docs/sql-reference/table-functions/file/) for inspecting the content of the primary index of our example table.

For that we first need to copy the primary index file into the [user\_files\_path](https://clickhouse.com/docs/operations/server-configuration-parameters/settings/#server_configuration_parameters-user_files_path) of a node from the running cluster:

- Step 1: Get part-path that contains the primary index file
`SELECT path FROM system.parts WHERE table = 'hits_UserID_URL' AND active = 1`

returns `/Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4` on the test machine.- Step 2: Get user\_files\_path
The [default user\_files\_path](https://github.com/ClickHouse/ClickHouse/blob/22.12/programs/server/config.xml#L505) on Linux is
`/var/lib/clickhouse/user_files/`

and on Linux you can check if it got changed: `$ grep user_files_path /etc/clickhouse-server/config.xml`

On the test machine the path is `/Users/tomschreiber/Clickhouse/user_files/`

- Step 3: Copy the primary index file into the user\_files\_path

`cp /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4/primary.idx /Users/tomschreiber/Clickhouse/user_files/primary-hits_UserID_URL.idx`


  

Now we can inspect the content of the primary index via SQL:

- Get amount of entries
`SELECT count( )<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String');`
returns `1083`

- Get first two index marks
`SELECT UserID, URL<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 0, 2;`

returns

`240923, http://showtopics.html%3...<br/> 4073710, http://mk.ru&pos=3_0`- Get last index mark
`SELECT UserID, URL FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 1082, 1;`
returns
`4292714039 │ http://sosyal-mansetleri...`


  

This matches exactly our diagram of the primary index content for our example table:

The primary key entries are called index marks because each index entry is marking the start of a specific data range. Specifically for the example table:

- UserID index marks:

  The stored `UserID` values in the primary index are sorted in ascending order.  
  'mark 1' in the diagram above thus indicates that the `UserID` values of all table rows in granule 1, and in all following granules, are guaranteed to be greater than or equal to 4.073.710.

[As we will see later](#the-primary-index-is-used-for-selecting-granules), this global order enables ClickHouse to [use a binary search algorithm](https://github.com/ClickHouse/ClickHouse/blob/22.3/src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp#L1452) over the index marks for the first key column when a query is filtering on the first column of the primary key.

- URL index marks: