Data Types

ClickHouse's type system looks superficially like any SQL database's, but several choices here have direct, measurable effects on storage size and query speed — because every type decision is also a column-storage decision.

Numeric types: pick the narrowest one that fits

ClickHouse gives you explicit-width integers — UInt8, UInt16, UInt32, UInt64, up to UInt256, plus signed Int8..Int256 — and floating point Float32/Float64. Unlike Postgres, where INTEGER is the path of least resistance regardless of actual range, picking the narrowest type that fits your data here directly shrinks every part on disk and the amount of memory a scan has to move through.

For money or anything needing exact arithmetic, use Decimal(P, S) rather than Float64 — floats accumulate rounding error under aggregation, which is exactly the operation ClickHouse spends most of its time doing.

String vs FixedString

String is a variable-length byte string with no encoding assumption (not necessarily UTF-8-validated). FixedString(N) stores exactly N bytes, padding with zero bytes — cheaper only when every value is genuinely the same length (country codes, fixed-width hashes); using it for anything else wastes space padding shorter values.

LowCardinality: dictionary-encode repetitive strings

LowCardinality(String) stores a small dictionary of distinct values plus an integer index per row, instead of repeating the string itself. It's the right call for columns like status, country, event type, or plan tier — anything with a bounded, modestly sized set of distinct values relative to row count. The compression mechanics of the underlying dictionary are covered in Compression; the thing to internalize here is when to reach for it: low distinct-value count relative to total rows, used often in GROUP BY, WHERE, or joins.

Nullable(T): correct, but not free

Wrapping a type in Nullable(...) doesn't just add a flag to the existing column — it adds an entirely separate hidden column: a bitmap recording, per row, whether the value is null. Every read of that column now involves reading two columns instead of one, and several optimizations (certain codecs, some vectorized operations) can't apply as cleanly through the extra indirection.

Date and time precision

• Date — 2 bytes, day resolution, range up to 2149.
• Date32 — 4 bytes, day resolution, wider range (including pre-1970 dates).
• DateTime — 4 bytes, second resolution, timezone-aware.
• DateTime64(precision) — 8 bytes, configurable sub-second precision (milli/micro/nanosecond) for event timestamps that need it.

Using DateTime64(9) everywhere "just in case" doubles storage over plain DateTime for no benefit if you never actually need nanosecond precision — match the type to the real precision of your source data.

Enum8 / Enum16

CREATE TABLE requests
(
    method Enum8('GET' = 1, 'POST' = 2, 'PUT' = 3, 'DELETE' = 4)
)
ENGINE = MergeTree ORDER BY method;

An Enum stores a single byte (Enum8) or two bytes (Enum16) on disk while still comparing, sorting, and displaying as the named string — similar end result to LowCardinality(String) for a fixed, known-in-advance set of values, with the schema itself documenting the valid values.