Data types

LowCardinality — use it for repeated strings#

LowCardinality(T) applies dictionary encoding, so the column stores small integer references instead of repeated values. Filtering, GROUP BY, and storage all get cheaper.

The threshold is measured: if a dictionary contains fewer than 10,000 distinct values, ClickHouse mostly reads and stores the column more efficiently. Above 100,000 distinct values, it can perform worse than an ordinary type.

town       LowCardinality(String),
event_type LowCardinality(String),
status     LowCardinality(String)

Country codes, event names, status values, and log levels are good candidates. User IDs and URLs are not.

It applies to String, FixedString, Date, DateTime, and numeric types other than Decimal. Consider it instead of Enum when working with strings — it gives more flexibility with the same or better efficiency, and adding a value needs no ALTER.

Count distinct values before deciding:

SELECT uniq(ratecode_id), uniq(pickup_location_id), uniq(vendor_id)
FROM trips
FORMAT VERTICAL;

Avoid Nullable#

A Nullable column stores a separate UInt8 mask file alongside the column data. That extra column is processed every time the data is read, costing storage and performance. A Nullable field also cannot be part of a table index.

Use a sentinel default instead:

-- Prefer this
count UInt32 DEFAULT 0,
note  String DEFAULT ''
 
-- Over this
count Nullable(UInt32),
note  Nullable(String)

Check whether the nulls are even there before keeping the type:

SELECT countIf(vendor_id IS NULL) AS vendor_id_nulls
FROM trips
FORMAT VERTICAL;

Reserve Nullable for cases where "absent" and "zero" genuinely differ and your queries distinguish them.

Numbers#

Select the minimal bit width that fits, and prefer unsigned when negatives are impossible: UInt16 over Int32 costs half the bytes to read and compresses better. Check the real range with SELECT min(col), max(col) before choosing.

For money, use Decimal(P, S), which does exact arithmetic. Precision P ranges 1 to 76 and maps onto Decimal32/64/128/256(S). Never store currency in a float — summing floats produces results like 499693.60500000004 where Decimal gives 499693.605.

Decimal128 and Decimal256 are significantly slower than Decimal32 and Decimal64 because their arithmetic is emulated, and overflow checking is not implemented for them.

Dates and times#

Prefer DateTime over DateTime64 unless millisecond or finer precision is genuinely needed. Date is faster than DateTime under most conditions and takes half the storage.

At precision 9 the supported range narrows to 1677-09-21 through 2262-04-11 in UTC.

In a primary key, toDate(created_at) produces a smaller and faster index than a full datetime when day granularity suffices — a date fits in 16 bits. See choosing a primary key.

Strings#

String holds arbitrary-length text with no declared limit and replaces VARCHAR, TEXT, BLOB, and CLOB. There is no performance benefit to declaring a maximum length, because none exists. ClickHouse stores and returns the bytes as-is, with no encoding conversion.

FixedString(N) stores exactly N bytes and is efficient only when the data really is N bytes long. Shorter values are padded with null bytes; longer values raise Too large value for FixedString(N).

Enums, UUIDs, and IP addresses#

Enum8 holds up to 256 values, Enum16 up to 65,536. Labels are stored as integers and validated on insert:

type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)

Adding a label requires an ALTER, so prefer LowCardinality(String) where the value set changes.

IPv4 stores 4 bytes and IPv6 stores 16, far less than the string forms, with address-specific functions available. UUID stores 16 bytes instead of a 36-character string — but note that UUIDs sort by their second half for historical reasons, which degrades performance for UUIDv7 columns used in a primary key.

Composite types#

Array(T) is 1-indexed and T may itself be an array. Tuple(...) groups columns temporarily. Nested(...) behaves like a table inside a cell, exposed as parallel arrays.

Map(K, V) is implemented internally as Array(Tuple(K, V)), which has two consequences: keys are not unique, and m[k] scans the map, so lookup time is linear in map size. Promote frequently queried keys to real columns.

The JSON type#

JSON stores each distinct JSON path as its own subcolumn, so querying one path reads only that path with full columnar performance:

CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;

max_dynamic_paths defaults to 1024, and max_dynamic_types to 32. Keep the number of dynamic paths below roughly 10,000.

Use the JSON type when structure is genuinely dynamic or unpredictable and you need to query individual paths. Use explicit columns when the schema is known and consistent — they remain faster. Use String when the document is an opaque blob you never query into. The trade-offs of the JSON type are slower inserts, slower reads of entire objects, and storage overhead.

The JSON type is production ready as of version 25.3.

Aggregate state types#

AggregateFunction(func, types...) and SimpleAggregateFunction(func, type) store intermediate aggregation states rather than final values. They exist for materialized view targets on AggregatingMergeTree — see table engines.

Checklist#

• Use strict types; avoid Nullable unless absence is meaningful and queried.
• Choose the minimal numeric width, unsigned where negatives are impossible.
• Prefer Date over DateTime, and DateTime over DateTime64.
• Apply LowCardinality below ~10,000 distinct values, not above ~100,000.
• Use Decimal for money, never a float.

Related#

• Choose a primary key — type width directly affects index size
• Compression and codecs — what to do after types are right
• Table engines — where aggregate state types are used