Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 35 ยท Performance and Indexing

Projections, Codecs and LowCardinality

Speed up and shrink ClickHouse tables: projections with another sort order, per-column codecs such as ZSTD, Delta and DoubleDelta, and LowCardinality strings.

Beyond choosing the right sorting key, ClickHouse gives you three levers that change how data is stored or laid out: projections (extra copies sorted or pre-aggregated differently), compression codecs (how each column is encoded on disk) and the LowCardinality type (dictionary encoding for repetitive strings). All three reduce the bytes read, and ClickHouse speed is largely about bytes read.

All ClickHouse snippets are read-only, as the server cannot run here. The runnable stand-ins illustrate the ideas with SQL.

Projections: a second layout inside the table

A table has one sorting key. If dashboards filter by site but an alerting job filters by status, one of them scans too much. A projection is a hidden, automatically maintained copy of some columns stored inside each part, either with a different ORDER BY or pre-aggregated.

SQL
ALTER TABLE hits ADD PROJECTION by_status
(
    SELECT * ORDER BY status, ts
);

ALTER TABLE hits MATERIALIZE PROJECTION by_status;

ALTER TABLE hits ADD PROJECTION daily_totals
(
    SELECT site, toDate(ts) AS day, count() AS hits, sum(bytes) AS bytes
    GROUP BY site, day
);

You never mention the projection in a query. The optimizer picks it when it would read fewer rows than the main table:

SQL
SELECT count() FROM hits WHERE status = 503;           -- can use by_status
SELECT site, sum(bytes) FROM hits GROUP BY site;       -- can use daily_totals

Verify with EXPLAIN (it shows the projection name in the read step) or with system.query_log, whose projections column lists used ones. Points to know:

  • Projections live in the same part as the data, so they are consistent and cannot lag, unlike a materialized view with its separate target table.
  • They cost storage and insert time. A full-sort projection roughly doubles the data.
  • ADD PROJECTION affects only new parts; MATERIALIZE PROJECTION builds it for old ones.
  • Pre-aggregating projections are limited compared with materialized views (no joins, and behaviour with ReplacingMergeTree and lightweight deletes needs care).

Rule of thumb: use a projection for a second access path on the same table; use a materialized view when you need transformation, joins or a separately queryable target.

Compression codecs

Every column is compressed in blocks. The default is LZ4, fast with a modest ratio. You can choose a codec per column, and you can chain a specialized codec (which rearranges data to be more compressible) with a general-purpose codec:

SQL
CREATE TABLE sensor_readings
(
    ts       DateTime  CODEC(Delta, ZSTD(1)),
    device   UInt32    CODEC(ZSTD(1)),
    temp     Float32   CODEC(Gorilla, ZSTD(1)),
    counter  UInt64    CODEC(DoubleDelta, ZSTD(1)),
    note     String    CODEC(ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (device, ts);
Codec What it does Best for
LZ4 fast general compression (default) anything where speed matters
ZSTD(level) better ratio, a little slower; levels 1-22 cold data, strings, large columns
Delta stores differences between neighbours slowly changing integers, timestamps
DoubleDelta differences of differences evenly spaced timestamps, monotonic counters
Gorilla XOR of consecutive floats slowly changing float metrics
T64 crops unused high bits of integers integers with small ranges
NONE no compression already compressed data

Sorted data compresses better, which is why the sorting key also affects disk size. To compare, look at system.columns (data_compressed_bytes, data_uncompressed_bytes). Codecs are a trade: stronger compression saves disk and I/O but costs CPU on reads. Benchmark with your own data and queries before changing defaults.

LowCardinality: dictionary encoding

Columns like country, device, browser or status_text contain millions of rows but only a few dozen distinct values. Storing the full string every time wastes space and makes comparisons slow. LowCardinality(String) stores each distinct value once in a dictionary and the rows hold small integer references:

SQL
CREATE TABLE visits
(
    ts       DateTime,
    country  LowCardinality(String),
    browser  LowCardinality(String),
    url      String
)
ENGINE = MergeTree
ORDER BY (country, ts);

Benefits are smaller storage and faster GROUP BY/filters on those columns. It works best up to roughly 10,000 distinct values per column in a part; beyond that, or when almost every value is unique (urls, ids), it loses its edge and a plain String is better. It is transparent in queries: you write WHERE country = 'DE' as usual. LowCardinality can wrap String, FixedString, numbers and dates, and Nullable types as LowCardinality(Nullable(String)).

Related type tips: use the smallest integer that fits (UInt8 for a status flag), DateTime instead of strings, Enum8 for a small closed set of names, and avoid Nullable unless NULL means something (it adds a hidden mask column).

Stand-in: delta encoding and dictionaries

The first stand-in shows why Delta helps: differences between neighbouring timestamps are small and repetitive. The second builds a dictionary the way LowCardinality does.

SQL Stand-in: Delta encoding of timestamps

Press Run (or Ctrl + Enter) to see the result here.

The original column holds ten-digit numbers; the deltas are tiny numbers (58 or 61) with high repetition, which is why a general-purpose codec can squeeze them far better. DoubleDelta would encode the differences of these deltas, which are near zero.

SQL Stand-in: a dictionary like LowCardinality

Press Run (or Ctrl + Enter) to see the result here.

Roughly 5,000 rows of text shrink to a tiny dictionary plus one byte per row. Real LowCardinality also compresses the code column further.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a ClickHouse projection?

    Projections are maintained with the part and chosen automatically by the optimizer when they allow less data to be read.
  2. 2. Which codec chain suits a monotonically growing counter sampled at regular intervals?

    DoubleDelta stores differences of differences, which are almost constant for regular series, and ZSTD then compresses them.
  3. 3. When should you avoid LowCardinality(String)?

    With very many distinct values the dictionary becomes large and the benefit disappears; use a plain String.
  4. 4. After ALTER TABLE ... ADD PROJECTION, how do you build it for existing parts?

    New parts get the projection automatically; ALTER TABLE ... MATERIALIZE PROJECTION backfills older parts.

Summary

  • Projections add an extra sort order or aggregation inside the table, picked automatically.
  • Codecs encode each column; combine a specialized codec (Delta, DoubleDelta, Gorilla) with ZSTD or LZ4.
  • LowCardinality dictionary-encodes repetitive strings, saving space and time.
  • Fewer bytes read is the main route to speed; measure before and after.

Next: Query Profiling and Optimization