ClickHouse Data Types: Overview

Tip: Integer Types

UInt8, UInt16, UInt32, UInt64 — choose the smallest that fits.

Gotcha: Float Precision

Float32 vs Float64. Float32 saves space but loses precision.

Tip: String vs FixedString

String is variable length. FixedString(N) is fixed length — faster for uniform data.

Gotcha: Nullable Overhead

Nullable columns use extra storage. Avoid unless necessary.

Tip: DateTime64

DateTime64(3) -- millisecond precision

Gotcha: Enum Types

Enum8 (256 values) vs Enum16 (65536 values). Use the smallest that fits.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

ClickHouse's data types are more nuanced than traditional databases. I've learned to use specific types for specific purposes: LowCardinality(String) for low-cardinality strings (status, country, category), FixedString for fixed-length codes (ISO currency codes, hashes), DateTime64 for high-precision timestamps, and Decimal for financial calculations. Using the right type reduces storage by 2-10x and improves query performance proportionally.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)