Table partitioning in Clickhouse

Using partitions in Clickhouse

Clickhouse supports table partitioning which can be useful in cases we deal with serial data and need to work only with a moving window of it (e.g. we only work with last 3 months of data). Since it’s better to avoid mutations (happen when we delete data), partitions can become an efficient way to get rid of unused (outdated) data.

How partitions work

Clickhouse allows us to split tables in multiple blocks. This can later be used to manipulate (say, drop or move) those blocks in an efficient manner. To enable partitions we have to define partition key using PARTITION BY expression, which Clickhouse will use to split table data:

CREATE TABLE test (a,b,c) PARTITION BY (a) ORDER BY (b)

When partition key is defined, Clickhouse will automatically route incoming data to be saved into separate blocks (partitions):

We will still have single table and everything will work like we expect it to work. But internally, table data will be stored within multiple partitions. Why use that? Let’s see on examples.

Test tables

Let’s say we have a situation where we need to store timeseries data. We work with last 7 days of data only and we need to clear old data on a daily basis. We have a simple table:

CREATE TABLE unpartitioned
( `t` Date, `label` UInt8, `value` UInt32 )
ENGINE = MergeTree ORDER BY (label, t)

We usually filter by t and label or by label only:

SELECT AVG(value) FROM unpartitioned WHERE t = today() AND label = 10

We have populated our table with 100 million random rows:

INSERT INTO unpartitioned
SELECT today() - rand32() % 10, rand32() % 10000, rand32()
FROM numbers(100000000);

Creating paritioned table

Let’s create partitioned table and copy source data:

CREATE TABLE partitioned
(`t` Date, `label` UInt8, `value` UInt32)
ENGINE = MergeTree PARTITION BY t ORDER BY label

INSERT INTO partitioned SELECT * FROM unpartitioned

Since we have chosen t column (date) to partition our table based on, we have removed it from order key. We can check which partitions our table have by using system.parts table:

SELECT partition, formatReadableSize(sum(bytes))
FROM system.parts
WHERE table = 'partitioned'
GROUP BY partition

Which gives us the following:

We can see that 10 partitions were created for our table (which is exactly what we’ve expected). Different partition was created for each t column value (each day).

Query performance

When working with partition tables, Clickhouse will check if all partitions should be used for a query or it can skip some partitions. This is a tricky situation that can result in performance either improved or degraded depending on how many partitions out query hits.

Single partition hit

Assume we have the following query:

SELECT avg(value) FROM unpartitioned WHERE t = today() AND label = 10

Let’s see how that works for the original table:

Now the partitioned one:

Partitioned table required Clickhouse to scan less rows to give an answer to our query. Why? Clickhouse tells that it has to read more parts for unpartitioned table:

This happens because in case of partitioned table, we have a single partition hit — Clickhouse reads data from a single partition and ignores others. Our partitioned table has simple sort key built on top of label column. Which means Clickhouse finds target partition first and then filters query based on a label sort key:

In case of unpartitioned table, Clickhouse loads all possible parts based on a combination of label + t (note columns order) values, since they are both being a part of the sorting key:

Multiple partitions hit

Now let’s assume we have the following query:

SELECT t, avg(value) FROM unpartitioned WHERE label = 10 GROUP BY t

Here we want to get data accross all available period and filter it by label. Let’s see how unpartitioned table performs:

EXPLAIN ESTIMATE SELECT t, avg(value) FROM unpartitioned WHERE label = 10 GROUP BY t

Which shows us that Clickhouse is going to read 450k rows from 6 parts:

While partitioned table will result int reading almost 700k rows from 35 parts:

This happened, because unpartitioned table still uses its sorting key to find relevant parts to load and then filter. While partitioned table has to find relevant partitions first and then find relevant parts to further filter in each partition:

This gives us an important insight into how partition can improve or make things worse:

• If we have most of our queries hit single partition, we will generally see query performance improvement,
• On the other hand, if queries usually hit multiple partitions, it can result in degraded performance.

Read performance gains can be an effect of partitioning, but shouldn’t be used for that, as sorting key is something to tune for performance.

Ingest performance

Using partitions will also affect how data is ingested. When data arrives into partitioned table, Clickhouse has to basically find relevant partitions for data and then (create and then) populate them with new data. This is one additional step if we compare to unpartitioned table:

We can spot the difference if we try to insert into partitioned and unpartitioned table (with the same sorting key by label column):

Thus we have a limit of how many partitions can be inserted within a single query, which is 100 by default. Trying to insert too many partitions will result in the following error:

If we still need to insert to a lot of partitions, we have to split data into multiple batches grouped into smaller packets of partitions.

Given this, it’s a good case to have a partitioned table when we insert serial data (e.g. timeseries) and our insert batches usually hit single or little partitions.

Choosing partitioning key

Good approach is to try to keep number of partitions within a couple of tens or so. As a popular case, for dates, it’s a good idea to use higher aggregating functions to lower the number of partitons:

PARTITION BY toYYYYMM(date_column)

Remember, that in most cases partitioning is not needed at all as this is not the tool to improve query performance. The only case when performance is improved — when queries hit single partition.

Good reason for using partitions is that we can manage table partitions as a separate objects, e.g. drop or move it.

Removing data by dropping partitions

Let’s return to our original unpartitioned table. Since we only need last 7 days of data, we have to delete old data each day:

ALTER TABLE unpartitioned DELETE WHERE t < (today() - 7)

This will result in a mutation, because rows will not be deleted instantly, but only marked as deleted by Clickhouse. Then, in the background, Clickhouse will eventually remove that data.

For partitioned table we can instantly drop entire partition with outdated data:

ALTER TABLE partitioned DROP PARTITION '2022-11-08'

Partition name (like 2022–11–08) can be found in the system.parts table. And since this is a lightweight operation (basically a folder being removed from disk) server performance is not affected. We can also instantly see table size reduction.

Summary

Using partitions in Clickhouse allows transparently splitting table into multiple blocks and have a way to manage those blocks independantly (e.g. drop them). Partitioning key should always be a low cardinality expression (with a couple of tens of values). Do not consider partitioning for query performance improvement and mind that data ingest performance can degrade for partitioned tables.