Skip to content
Toolforge

8. Types and the SQL dialect

ClickHouse speaks SQL, and it has opinions. This chapter is the type system — where a wrong choice costs real storage and speed — and the parts of the dialect that are genuinely different from PostgreSQL.

Size your integers

Section titled “Size your integers”

Unlike a row store, where a column’s width is lost in row overhead, the width of an integer column here is the width of the whole file.

   Int8 / UInt8     1 byte     -128…127   /  0…255
   Int16 / UInt16   2 bytes
   Int32 / UInt32   4 bytes               /  0…4.29 billion
   Int64 / UInt64   8 bytes
   Int128 / Int256  16 / 32 bytes

An HTTP status code in a UInt64 is 8 bytes; in a UInt16 it is 2. On 40 billion rows that is 320 GB versus 80 GB before compression — and compression does not fully erase the difference, because there is real information in those wasted high bytes only if you use them.

Use the smallest type that fits, with headroom. UInt8 for a status flag, UInt16 for an HTTP code, UInt32 for a duration in milliseconds, UInt32 for a Unix timestamp until 2106.

Dates and times

Section titled “Dates and times”
   Date          2 bytes    days since 1970,  up to 2149
   Date32        4 bytes    a wider range including pre-1970
   DateTime      4 bytes    seconds since epoch, up to 2106
   DateTime64(n) 8 bytes    sub-second precision; n = digits (3 = ms, 6 = µs, 9 = ns)

DateTime is 4 bytes and enough for almost everything. DateTime64(3) doubles that; use it when you genuinely need milliseconds, not by default.

Both DateTime and DateTime64 can carry a time zone in the type:

ts DateTime('UTC')
local_ts DateTime('Europe/London')

The stored value is always a UTC instant; the time zone affects display and the behaviour of functions like toStartOfDay. Store UTC and convert at the edge — the same advice as everywhere, and here the type system will help you if you are explicit.

Pair a DateTime with CODEC(Delta, ZSTD) or CODEC(DoubleDelta, ZSTD) (chapter 2). It is nearly free and often 20–75× compression.

Nullable costs more than you think

Section titled “Nullable costs more than you think”

In PostgreSQL, nullability is a bit in the row header and is essentially free. In ClickHouse it is not.

A Nullable(UInt32) column is stored as two columns: the values, and a separate UInt8 mask marking which are null.

   Nullable(UInt32)
   ├── col.bin        the values
   └── col.null.bin   1 byte per row, the null mask

The consequences:

  • An extra file and extra I/O for every read of that column.
  • Some optimisations do not apply, including certain index and aggregation fast paths.
  • A Nullable column cannot be in the primary key in older versions, and its behaviour in sort keys is awkward regardless.
  • Nulls propagate, so aggregate functions must check the mask per value.

The idiomatic ClickHouse answer:

-- instead of this
duration Nullable(UInt32)


-- do this
duration UInt32 DEFAULT 0        -- and treat 0 as "unknown" by convention
country  LowCardinality(String) DEFAULT ''

Use Nullable when the distinction between “zero” and “unknown” genuinely matters analytically — a temperature reading, a price, a score where zero is a real value. Do not use it reflexively for every column that might be absent.

LowCardinality — again, because it matters

Section titled “LowCardinality — again, because it matters”

Chapter 2 covered the mechanism. The rule restated:

country     LowCardinality(String)     -- ~200 values      ✅
status      LowCardinality(String)     -- ~10 values       ✅
host        LowCardinality(String)     -- ~5,000 values    ✅
url         String                     -- millions         ❌ plain String
user_email  String                     -- millions         ❌ plain String

Under ~10,000 distinct values: use it. It saves storage and makes GROUP BY and equality filters significantly faster, because the engine compares and hashes integers instead of strings.

You can combine it with Nullable — LowCardinality(Nullable(String)) — and the null becomes just another dictionary entry, which is one of the few places Nullable is cheap.

Enum — when the values are truly fixed

Section titled “Enum — when the values are truly fixed”
status Enum8('pending' = 1, 'paid' = 2, 'shipped' = 3, 'cancelled' = 4)

Stored as one byte. Compared as integers. Displayed as strings.

The catch is that adding a value requires an ALTER TABLE, which on a large table is a metadata change but still an operation you must plan. If the set of values might grow — event types, error codes, feature names — use LowCardinality(String) instead. It is nearly as efficient and infinitely more flexible.

Use Enum for genuinely closed sets: a boolean-like tri-state, a fixed order status, a log level.

Arrays

Section titled “Arrays”

First-class, and used far more in ClickHouse than in most SQL databases.

CREATE TABLE events (
    ts     DateTime,
    tags   Array(LowCardinality(String)),
    values Array(Float64)
) ENGINE = MergeTree ORDER BY ts;
SELECT * FROM events WHERE has(tags, 'error');
SELECT * FROM events WHERE hasAll(tags, ['error', 'payment']);
SELECT length(tags), arraySum(values), arrayMap(x -> x * 2, values) FROM events;

ARRAY JOIN expands an array into rows — MongoDB’s $unwind, or SQL’s UNNEST:

SELECT tag, count()
FROM events
ARRAY JOIN tags AS tag
GROUP BY tag ORDER BY count() DESC;

LEFT ARRAY JOIN keeps rows with empty arrays.

Arrays are stored efficiently — an offsets column plus a flat values column — so an Array(LowCardinality(String)) of tags is genuinely compact. This makes the “tags on an event” pattern practical at a scale where a join table would not be.

The higher-order functions are a large part of ClickHouse’s expressive power: arrayMap, arrayFilter, arraySum, arrayReduce, arraySort, arrayEnumerate, arrayJoin, arrayZip. They let you do per-row computation over nested data without leaving SQL.

Map, Tuple, Nested, and JSON

Section titled “Map, Tuple, Nested, and JSON”

Map(K, V) for key-value attributes:

attrs Map(LowCardinality(String), String)


SELECT attrs['browser'], attrs['os'] FROM events;
SELECT * FROM events WHERE mapContains(attrs, 'utm_source');

Convenient, and be aware that reading attrs['browser'] reads the whole map for those rows — keys and values — not just that key. For a handful of attributes you query constantly, promote them to real columns; keep the map for the long tail.

Tuple groups values: Tuple(Float64, Float64) for a coordinate. Accessed by position or by name if declared with names.

Nested is sugar for parallel arrays:

items Nested (sku String, qty UInt32, price Decimal(10,2))

Stored as items.sku, items.qty, items.price — three arrays that must stay the same length. ARRAY JOIN items.sku, items.qty expands them together.

JSON. ClickHouse has had several approaches here — String plus JSONExtract* functions, the experimental Object('json') type, and a newer native JSON type. This area has changed significantly across recent versions, so check what your version supports rather than trusting any guide.

The advice that has stayed true regardless: extract the fields you query into real typed columns at insert time, and keep the raw JSON in a String column with CODEC(ZSTD(3)) for the rest. Materialised columns make this automatic:

raw     String CODEC(ZSTD(3)),
user_id UInt64 MATERIALIZED JSONExtractUInt(raw, 'user_id'),
event   LowCardinality(String) MATERIALIZED JSONExtractString(raw, 'event')

Typed columns get the index, the compression, and the vectorised execution; the raw column is there when you need something you did not anticipate.

Other types worth knowing

Section titled “Other types worth knowing”
   Decimal(P, S)      exact decimal — use for money, like everywhere else
   UUID               16 bytes; effectively incompressible
   IPv4 / IPv6        4 / 16 bytes, with proper functions and display
   FixedString(N)     exactly N bytes; good for hashes and country codes
   Point, Ring,       geo types with functions
   Polygon
   AggregateFunction  a partial aggregate state (chapter 9)
   SimpleAggregate-   a lighter state for functions where the state is
   Function           just the value (sum, min, max, any)

FixedString(2) for a country code beats String slightly and beats LowCardinality(String) rarely — measure. IPv4 is worth using over UInt32 purely for the functions and readability.

Dialect differences worth knowing

Section titled “Dialect differences worth knowing”

Coming from PostgreSQL, these will surprise you.

Column aliases are usable in the same SELECT, and in WHERE and GROUP BY:

SELECT toStartOfHour(ts) AS hour, count() AS c
FROM events
WHERE hour >= now() - INTERVAL 1 DAY      -- the alias, in WHERE
GROUP BY hour
HAVING c > 100;

This is convenient and it is not standard SQL.

GROUP BY can use positional references — GROUP BY 1, 2 — and WITH ROLLUP, WITH CUBE, WITH TOTALS.

LIMIT n BY column — top n per group, in one clause. This is genuinely useful and has no plain-SQL equivalent:

SELECT country, url, count() AS c
FROM page_views GROUP BY country, url
ORDER BY country, c DESC
LIMIT 3 BY country;                        -- top 3 URLs per country

SAMPLE — approximate answers over a deterministic subset, if the table declares a SAMPLE BY key:

SELECT count() * 10 FROM page_views SAMPLE 0.1;

Fast estimates over enormous tables. It requires the sampling key to be part of the primary key, and it gives you speed in exchange for accuracy — a legitimate trade for a dashboard.

FINAL — chapter 4.

SETTINGS on a statement:

SELECT … FROM … SETTINGS max_threads = 4, max_memory_usage = '4G';

Table functions let you query things that are not tables:

SELECT * FROM s3('https://bucket/data/*.parquet', 'Parquet');
SELECT * FROM url('https://api.example.com/data', 'JSONEachRow', 'x UInt32');
SELECT * FROM file('/var/data/*.csv', 'CSV', 'a UInt32, b String');
SELECT * FROM postgresql('host:5432', 'db', 'table', 'user', 'pass');
SELECT * FROM mysql(…), mongodb(…), remote(…), numbers(1000000);

numbers() is the fastest way to generate test data. s3() reading Parquet directly, with no import step, is a genuinely useful capability — you can query a data lake in place and INSERT INTO … SELECT from it when you decide to keep the data. It works because of ranged reads: the engine fetches a Parquet footer, learns where the row groups are, and then pulls only the columns it needs, so a 2 GB file can answer a query with a few megabytes of transfer.

Hundreds of functions beyond standard SQL: toStartOfInterval, dateDiff, formatReadableSize, formatReadableQuantity, uniqCombined, quantileTDigest, topK, sequenceMatch, windowFunnel, retention, arrayJoin, cityHash64, geoDistance. The funnel and retention functions in particular exist because ClickHouse came from a web analytics product, and they do in one call what would otherwise be a page of self-joins.

What goes wrong

Section titled “What goes wrong”

Nullable on everything. Extra files, extra I/O, lost optimisations. Use defaults.

UInt64 for small values. Multiply the waste by your row count.

String where LowCardinality(String) belongs. The most common missed win.

Enum for a set that grows. Every new value is an ALTER.

Reading a whole Map for one key in a hot query. Promote it to a column.

DateTime64 everywhere when DateTime would do — double the storage for precision nobody uses.

Assuming PostgreSQL semantics. Integer division, NULL comparison behaviour, and implicit casts differ in places. Test rather than assume.

What to take from this chapter

Section titled “What to take from this chapter”
  • Size integers to the data. In a column store the type width is the file width.
  • DateTime is 4 bytes and usually enough; add Delta/DoubleDelta codecs.
  • Nullable costs a second file and disables optimisations. Prefer a default value unless “unknown” is analytically distinct from zero.
  • LowCardinality(String) under ~10,000 distinct values, always.
  • Enum only for genuinely fixed sets; otherwise LowCardinality.
  • Arrays are first-class, with ARRAY JOIN and a rich set of higher-order functions — the tags-on-an-event pattern works at scale.
  • For JSON, extract the fields you query into typed MATERIALIZED columns and keep the raw text compressed alongside.
  • The dialect gives you aliases in WHERE, LIMIT n BY, SAMPLE, statement-level SETTINGS, and table functions over S3, URLs, files and other databases.

Next: aggregation — the thing you actually came for.

Written by Sepuri Sai Krishna

© 2026CC BY 4.0Source