NULLs in ClickHouse can hurt performance

When coming from relational databases, NULLs are the go-to for optional fields. Using them in ClickHouse can lead to unexpected and often unnoticeable performance degradation. This article explain why.

PostgreSQL

When using null values in PostgreSQL, you rarely notice any difference. In PG, columns are nullable by default and you can index them.

Internally, each row in PostgreSQL has a bitmap that indicates which columns are NULL. It's only present when there are null values in a particular row using a bit flag.

PostgreSQL is a row-oriented database, so when you read a row, you read all the columns together.

ClickHouse

Unlike PostgreSQL, ClickHouse is a columnar database. Instead of storing data by rows, it organizes them by columns. Each column is stored separately as a contiguous block of data.

Let's suppose we have a table that stores HTTP logs. We want to store the visitor's user ID, which can be empty for anonymous users.

If you are coming from OLTP databases, the first idea is to create a table with a nullable user_id column:

CREATE TABLE http_logs
(
    timestamp DateTime,
    path LowCardinality(String),
    user_id Nullable(UInt32) CODEC(Delta, ZSTD)
)
ENGINE = MergeTree
ORDER BY (path, timestamp);

The user_id column is defined as nullable and uses a combination of Delta and ZSTD compression codecs. Since columns are stored as chunks (partitions), compression is a very efficient way to reduce storage cost and improve performance. Decoding compressed data on the fly is usually faster than reading 10x more data.

Since the user_id column is typed, it's stored as a contiguous block of memory of the same type (UInt32 in this case). To avoid extra data manipulations, ClickHouse stores the data in the same format it would be loaded into RAM. You can view this as creating a plain C array (uint32_t user_id[]) and storing it on a disk.

Because of that, we can't store NULL values directly. It's not possible to assign null values to integers.

There are two ways to handle NULL values:

• Add metadata to each value to indicate whether it's NULL or not.
• Use a separate null map to indicate which values are NULL.

The first approach requires additional storage for each value, breaks CPU vectorization and adds unnecessary padding for data alignment.

The second approach, which is used by ClickHouse, is more efficient, but it still requires additional space and processing to manage the map. Every time a column is filtered, ClickHouse reads the map and checks which values are NULL. Since typical ClickHouse deployments store millions of rows, this leads to significant overhead.

Despite a similar approach to PostgreSQL, this does not work well in ClickHouse. Columnar databases are designed to scan billions of values without any indexes. This is a pretty rare use case for row-oriented databases. That's one of the reasons why columns are not nullable by default, and you can't use NULLs in indexes in ClickHouse.

To avoid null values, the typical solution is to replace them with a special value, such as 0 or -1. In our case, zero works well, since user IDs usually start with 1.

Internals

Internally, null values are stored as a byte map (array of UInt8). The map itself is just a contiguous block of values that acts as a mask. If a column contains 1 million of values, the null map will also contain 1 million of elements (bytes). Such a map can be compressed very well, because it contains only two distinct values (0 and 1) where most of the data is usually zero.

On the disk and in memory, it looks as follows:

user_id
├── values:   [42, 17, 0, 3, 0]
│
└── null_map: [ 0,  0, 1, 0,  1]

In the values array, the null variables are set to type's default value (0 for integers). In the null map, a value of 1 indicates that the corresponding value in NULL. In our example, the third and fifth values are NULL.

As you can see, our user_id already contains zeros internally, even when the column is nullable. So using zeros instead of NULLs makes sense. Although, ClickHouse does not guarantee that in the values array, the null values will always be set to a default value. They can contain any number.

In theory, the null map could be stored as a bitmap, where each bit represents the nullability of a value. But ClickHouse developers decided to use a byte map instead.

/// Class that specifies nullable columns. A nullable column represents
/// a column, which may have any type, provided with the possibility of
/// storing NULL values. For this purpose, a ColumNullable object stores
/// an ordinary column along with a special column, namely a byte map,
/// whose type is ColumnUInt8. The latter column indicates whether the
/// value of a given row is a NULL or not. Such a design is preferred
/// over a bitmap because columns are usually stored on disk as compressed
/// files. In this regard, using a bitmap instead of a byte map would
/// greatly complicate the implementation with little to no benefits.

Using bitmaps requires additional processing and breaks vectorized operations on CPU.

Sparse columns

One thing to be aware of is that sometimes ClickHouse uses sparse serialization. When a column contains mostly default values, there is no point in storing all values. It's controlled by ratio_of_defaults_for_sparse_serialization setting. If 93.75% of values are default, ClickHouse will switch to sparse serialization.

Instead of storing all values, only non-default values and their positions are stored.

This is another reason to prefer default values instead of NULLs. By default, the sparse storage is not used for columns with NULL values. It was introduced recently, and it is disabled by default. The nullable_serialization_version settings must be set to allow_sparse to enable it. In this case, there is no need for a null map. The sparse table contains a default value that is set to NULL.

Testing

We can test the performance of regular columns by generating 1 billion of random rows:

Here is how to do it with nulls:

INSERT INTO http_logs
SELECT
    now() - rand() % 86400 AS event_time,
    arrayElement(['/', '/about', '/search', '/login'], rand() % 4 + 1) AS path,
    if(randUniform(0, 1) < 0.5, toUInt32(rand() % 1_000_000), NULL) AS user_id
FROM numbers(1_000_000_000);

Half of the data will contain NULL values for user_id. After repartitioning, all our data fits in a single partition.

To confirm that the null map is present, we can check the partition directory:

root@bbfd10cdf772:/var/lib/clickhouse/data/default/http_logs/all_1_900_4# ls -haltr
total 2.3G
drwxr-x--- 2 clickhouse clickhouse 4.0K Jun  2 16:32 .
-rw-r----- 1 clickhouse clickhouse 4.5M Jun  2 16:32 path.bin
-rw-r----- 1 clickhouse clickhouse  18M Jun  2 16:32 timestamp.bin
-rw-r----- 1 clickhouse clickhouse 2.4K Jun  2 16:32 path.dict.cmrk2
-rw-r----- 1 clickhouse clickhouse   62 Jun  2 16:32 path.dict.bin
-rw-r----- 1 clickhouse clickhouse  82K Jun  2 16:32 path.cmrk2
-rw-r----- 1 clickhouse clickhouse 2.1G Jun  2 16:32 user_id.bin
-rw-r----- 1 clickhouse clickhouse 251K Jun  2 16:32 timestamp.cmrk2
-rw-r----- 1 clickhouse clickhouse 300K Jun  2 16:32 user_id.cmrk2
-rw-r----- 1 clickhouse clickhouse 148K Jun  2 16:32 user_id.null.cmrk2
-rw-r----- 1 clickhouse clickhouse 187M Jun  2 16:32 user_id.null.bin
-rw-r----- 1 clickhouse clickhouse  208 Jun  2 16:32 serialization.json
-rw-r----- 1 clickhouse clickhouse  83K Jun  2 16:32 primary.cidx
-rw-r----- 1 clickhouse clickhouse   10 Jun  2 16:32 count.txt
-rw-r----- 1 clickhouse clickhouse 416K Jun  2 16:32 statistics.packed
-rw-r----- 1 clickhouse clickhouse    1 Jun  2 16:32 metadata_version.txt
-rw-r----- 1 clickhouse clickhouse   10 Jun  2 16:32 default_compression_codec.txt
-rw-r----- 1 clickhouse clickhouse  115 Jun  2 16:32 columns.txt
-rw-r----- 1 clickhouse clickhouse  196 Jun  2 16:32 columns_substreams.txt
-rw-r----- 1 clickhouse clickhouse  710 Jun  2 16:32 checksums.txt
drwxr-x--- 4 clickhouse clickhouse  36K Jun  2 16:42 ..

The null map occupies 187 MB, while the user id data occupies 2.1 GB. Thanks to compression, the whole table is only 2.3G on disk.

For test, I have created two tables. The http_logs_zero does not use NULLs, but zeros instead.

SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE table IN ('http_logs', 'http_logs_zero')
GROUP BY table

Query id: 1d39890b-1e4b-4805-9a01-6cb438f6011b

   ┌─table──────────┬─size─────┐
1. │ http_logs_zero │ 2.06 GiB │
2. │ http_logs      │ 2.24 GiB │
   └────────────────┴──────────┘

2 rows in set. Elapsed: 0.005 sec.

The nullable table is a bit larger, but the difference is not significant, given that it contains one billion rows.

Now, let's run a simple group by query:

SELECT
    path,
    user_id,
    count() AS cnt
FROM http_logs
WHERE timestamp >= (now() - toIntervalDay(1))
GROUP BY
    path,
    user_id
ORDER BY cnt DESC
LIMIT 3

Query id: fb043551-6534-4c74-a765-615527f20014

   ┌─path────┬─user_id─┬───────cnt─┐
1. │ /       │    ᴺᵁᴸᴸ │ 119626823 │ -- 119.63 million
2. │ /search │    ᴺᵁᴸᴸ │ 119615525 │ -- 119.62 million
3. │ /about  │    ᴺᵁᴸᴸ │ 119608140 │ -- 119.61 million
   └─────────┴─────────┴───────────┘

3 rows in set. Elapsed: 13.696 sec. Processed 956.93 million rows, 9.57 GB (81.82 million rows/s., 818.15 MB/s.)
Peak memory usage: 553.02 MiB.

SELECT
    path,
    user_id,
    count() AS cnt
FROM http_logs_zero
WHERE timestamp >= (now() - toIntervalDay(1))
GROUP BY
    path,
    user_id
ORDER BY cnt DESC
LIMIT 3

Query id: abbbf047-a0fb-44f0-a172-78011faa7921

   ┌─path────┬─user_id─┬───────cnt─┐
1. │ /search │       0 │ 120145358 │ -- 120.15 million
2. │ /login  │       0 │ 120144600 │ -- 120.14 million
3. │ /       │       0 │ 120129451 │ -- 120.13 million
   └─────────┴─────────┴───────────┘

3 rows in set. Elapsed: 10.153 sec. Processed 961.13 million rows, 8.65 GB (94.66 million rows/s., 851.96 MB/s.)
Peak memory usage: 397.45 MiB.

Even on a $5 VPS, the query takes less than 15 seconds. This is without secondary indexes! The table that uses zeros instead is faster by 25%.

This is not always the case. When your column consists of mostly nulls, and you do aggregations, it can be faster because ClickHouse skips NULL values during aggregation. But in general, based on my experience, using NULLs in ClickHouse can slow things down a lot in a production environment. As a rule of thumb, they should not be used, unless there is a strong reason to do so.

When you have multiple nullable columns, the performance can degrade even more. I have seen cases where nulls lead to 2-3x slowdowns.

A lot of patterns from row-oriented databases don't translate well to ClickHouse. I've experienced that multiple times myself. When in doubt, I always benchmark different approaches.