A practical introduction to primary indexes in ClickHouse

2 rows in set. Elapsed: 0.006 sec.
```

We can see that the compression ratio for the `UserID` column is significantly higher for the table where we ordered the key columns `(IsRobot, UserID, URL)` by cardinality in ascending order.

Although in both tables exactly the same data is stored (we inserted the same 8.87 million rows into both tables), the order of the key columns in the compound primary key has a significant influence on how much disk space the [compressed](https://clickhouse.com/docs/introduction/distinctive-features/#data-compression) data in the table's [column data files](#data-is-stored-on-disk-ordered-by-primary-key-columns) requires:

- in the table `hits_URL_UserID_IsRobot` with the compound primary key `(URL, UserID, IsRobot)` where we order the key columns by cardinality in descending order, the `UserID.bin` data file takes **11.24 MiB** of disk space
- in the table `hits_IsRobot_UserID_URL` with the compound primary key `(IsRobot, UserID, URL)` where we order the key columns by cardinality in ascending order, the `UserID.bin` data file takes only **877.47 KiB** of disk space

Having a good compression ratio for the data of a table's column on disk not only saves space on disk, but also makes queries (especially analytical ones) that require the reading of data from that column faster, as less i/o is required for moving the column's data from disk to the main memory (the operating system's file cache).

In the following we illustrate why it's beneficial for the compression ratio of a table's columns to order the primary key columns by cardinality in ascending order.

The diagram below sketches the on-disk order of rows for a primary key where the key columns are ordered by cardinality in ascending order:

We discussed that [the table's row data is stored on disk ordered by primary key columns](#data-is-stored-on-disk-ordered-by-primary-key-columns).

In the diagram above, the table's rows (their column values on disk) are first ordered by their `cl` value, and rows that have the same `cl` value are ordered by their `ch` value. And because the first key column `cl` has low cardinality, it is likely that there are rows with the same `cl` value. And because of that it is also likely that `ch` values are ordered (locally - for rows with the same `cl` value).

If in a column, similar data is placed close to each other, for example via sorting, then that data will be compressed better.
In general, a compression algorithm benefits from the run length of data (the more data it sees the better for compression)
and locality (the more similar the data is, the better the compression ratio is).

In contrast to the diagram above, the diagram below sketches the on-disk order of rows for a primary key where the key columns are ordered by cardinality in descending order:

Now the table's rows are first ordered by their `ch` value, and rows that have the same `ch` value are ordered by their `cl` value.
But because the first key column `ch` has high cardinality, it is unlikely that there are rows with the same `ch` value. And because of that is is also unlikely that `cl` values are ordered (locally - for rows with the same `ch` value).

Therefore the `cl` values are most likely in random order and therefore have a bad locality and compression ration, respectively.

### Summary[​](#summary-1 "Direct link to Summary")

For both the efficient filtering on secondary key columns in queries and the compression ratio of a table's column data files it is beneficial to order the columns in a primary key by their cardinality in ascending order.

## Identifying single rows efficiently[​](#identifying-single-rows-efficiently "Direct link to Identifying single rows efficiently")

Although in general it is [not](/docs/knowledgebase/key-value) the best use case for ClickHouse,
sometimes applications built on top of ClickHouse require to identify single rows of a ClickHouse table.

An intuitive solution for that might be to use a [UUID](https://en.wikipedia.org/wiki/Universally_unique_identifier) column with a unique value per row and for fast retrieval of rows to use that column as a primary key column.

For the fastest retrieval, the UUID column [would need to be the first key column](#the-primary-index-is-used-for-selecting-granules).

We discussed that because [a ClickHouse table's row data is stored on disk ordered by primary key columns](#data-is-stored-on-disk-ordered-by-primary-key-columns), having a very high cardinality column (like a UUID column) in a primary key or in a compound primary key before columns with lower cardinality [is detrimental for the compression ratio of other table columns](#optimal-compression-ratio-of-data-files).

A compromise between fastest retrieval and optimal data compression is to use a compound primary key where the UUID is the last key column, after low(er) cardinality key columns that are used to ensure a good compression ratio for some of the table's columns.

### A concrete example[​](#a-concrete-example "Direct link to A concrete example")

One concrete example is a the plaintext paste service <https://pastila.nl> that Alexey Milovidov developed and [blogged about](https://clickhouse.com/blog/building-a-paste-service-with-clickhouse/).

On every change to the text-area, the data is saved automatically into a ClickHouse table row (one row per change).

And one way to identify and retrieve (a specific version of) the pasted content is to use a hash of the content as the UUID for the table row that contains the content.

The following diagram shows

- the insert order of rows when the content changes (for example because of keystrokes typing the text into the text-area) and
- the on-disk order of the data from the inserted rows when the `PRIMARY KEY (hash)` is used:

Because the `hash` column is used as the primary key column

- specific rows can be retrieved [very quickly](#the-primary-index-is-used-for-selecting-granules), but
- the table's rows (their column data) are stored on disk ordered ascending by (the unique and random) hash values. Therefore also the content column's values are stored in random order with no data locality resulting in a **suboptimal compression ratio for the content column data file**.

In order to significantly improve the compression ratio for the content column while still achieving fast retrieval of specific rows, pastila.nl is using two hashes (and a compound primary key) for identifying a specific row:

- a hash of the content, as discussed above, that is distinct for distinct data, and
- a [locality-sensitive hash (fingerprint)](https://en.wikipedia.org/wiki/Locality-sensitive_hashing) that does **not** change on small changes of data.

The following diagram shows

- the insert order of rows when the content changes (for example because of keystrokes typing the text into the text-area) and
- the on-disk order of the data from the inserted rows when the compound `PRIMARY KEY (fingerprint, hash)` is used:

Now the rows on disk are first ordered by `fingerprint`, and for rows with the same fingerprint value, their `hash` value determines the final order.

Because data that differs only in small changes is getting the same fingerprint value, similar data is now stored on disk close to each other in the content column. And that is very good for the compression ratio of the content column, as a compression algorithm in general benefits from data locality (the more similar the data is the better the compression ratio is).

The compromise is that two fields (`fingerprint` and `hash`) are required for the retrieval of a specific row in order to optimally utilise the primary index that results from the compound `PRIMARY KEY (fingerprint, hash)`.