An Overview of ClickHouse

Each table has an Engine. An Engine determines how and where the data is stored for example:

It could be a table stored locally in native ClickHouse storage:

CREATE TABLE trades(
    time Datetime64(9),
    sym LowCardinality(String)
    price Decimal64(2)
)
ENGINE = MergeTree
PRIMARY KEY (sym)

CREATE TABLE uk_property
ENGINE = S3('https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst')
PRIMARY KEY town

In the case of the S3 engine the data will not be stored anywhere other than the s3 bucket itself, same goes for any of the non-native table engines. Therefore if performance is required it is best to use the native ClickHouse storage.

ClickHouse supports most ANSI SQL queries including GROUP BY, ORDER BY, WITH, JOINS etc, there are a number of other features worth mentioning. Note this is by no means an exhaustive list.

Optimizing Joins

At the time of writing Clickhouse has six different join algorithms as an additional option for JOIN statements. These can be set as part of the SELECT JOIN statement, they are:

• direct
• parallel_hash
• hash
• full_sorting_merge: Normal sort-merge join
• grace_hash
• partial_merge

The choice depends on performance, memory and Join type support. Selecting the correct one is a non-trival matter. A great blog post from ClickHouse goes into detail on how to select the correct algorithm. If you’re not too bothered then you can set the join_algorithm to auto which will allow ClickHouse to try hash joins, switching on the fly to partial merge join if the algorithm’s memory limit is reached.

Materialized Views

In ClickHouse there are two main types of materialized views:

• Refreshable: where the target table SELECT statement is executed periodically.
• Incremental: where the SELECT is run on the inserted rows before adding to the target table.

In the context of Incremental materialized views you might ask “what about aggregations?” In this case the AggregatingMergeTree Engine is a special mergeTree with aggregation state datatypes like AggregateFunction(avg UInt32). These store the intermediate “states” of an aggregation and can be merged at query time in order to compute the final aggregation.

Compression

ClickHouse has a number of nice features to reduce the size of the database:

• DataTypes like LowCardinality(String) which store strings as integers working much the same way as kdb+ enumeration.
• Delta-based Codecs.
• Time-To-Live (TTL) an out of the box way to delete data once it reaches a certain age.
• Column-wise compression.

ASOF Joins

ClickHouse supports ASOF joins:

select * from t
┌────────────────────time─┬─sym─┬─qty─┐
1. │ 2026-03-04 14:45:43.000 │ a │ 100 │
2. │ 2026-03-04 14:45:39.000 │ c │ 150 │
3. │ 2026-03-04 14:45:27.000 │ b │ 200 │
└─────────────────────────┴─────┴─────┘
select * from q
┌────────────────────time─┬─sym─┬─price─┐
1. │ 2026-03-04 14:45:35.000 │ a │ 98  │
2. │ 2026-03-04 14:45:17.000 │ b │ 99  │
3. │ 2026-03-04 14:45:31.000 │ b │ 101 │
4. │ 2026-03-04 14:45:08.000 │ a │ 100 │
└─────────────────────────┴─────┴───────┘

SELECT
time,
sym,
qty,
price
FROM t
ASOF LEFT JOIN q ON (t.sym = q.sym) AND (t.time >= q.time)

┌────────────────────time─┬─sym─┬─qty─┬─price─┐
1. │ 2026-03-04 14:45:27.000 │ b │ 200 │ 99   │
2. │ 2026-03-04 14:45:39.000 │ c │ 150 │ 0    │
3. │ 2026-03-04 14:45:43.000 │ a │ 100 │ 98   │
└────────────────────────────┴───┴─────┴──────┘

Notice that the price here is 0 but it probably should be NULL. In ClickHouse null values are stored as their default values. If you want the data to actually be null then the Nullable datatype will need to be used. In this case a hidden column is created which will be read if required, say for example if you wanted the average price for the above table without nullable you get ~65.67. If it was the nullable datatype you’d get 98.5. Nullable types bloat your dataset so it is best to avoid them if possible.