Data Types & Schema Design

Choosing the right data type can make your queries 10x faster and your storage 5x smaller. Every byte matters at billion-row scale.

🔢 Numeric Types

🧒 ELI5: Choosing the Right Container

If you're carrying water, you don't use a swimming pool when a glass will do. Similarly, don't use Int64 (8 bytes) when UInt8 (1 byte) is enough. At 1 billion rows, that's 7 GB wasted!

Type	Size	Range	Use Case
`UInt8`	1 byte	0 to 255	Status codes, booleans, small enums
`UInt16`	2 bytes	0 to 65,535	Port numbers, small counters
`UInt32`	4 bytes	0 to 4.2 billion	User IDs, timestamps (epoch)
`UInt64`	8 bytes	0 to 18 quintillion	Large IDs, big counters
`Int8/16/32/64`	1-8 bytes	Signed (negative too)	When values can be negative
`Float32`	4 bytes	~7 decimal digits	Approximate decimals (lat/lng)
`Float64`	8 bytes	~15 decimal digits	Scientific calculations
`Decimal(P,S)`	4-16 bytes	Exact precision	Money, financial data

⚠️ Never Use Float for Money!

Float64 can produce rounding errors: 0.1 + 0.2 = 0.30000000000000004. Use Decimal(18,2) for financial data.

📝 String Types

Type	Description	When to Use
`String`	Variable-length, no limit	General text, URLs, JSON
`FixedString(N)`	Exactly N bytes	Country codes (2), UUIDs (36)
`LowCardinality(String)`	Dictionary-encoded	Columns with few unique values (<10K)
`Enum8/Enum16`	Named integer values	Status, category with fixed options

💡 LowCardinality is a Game-Changer

If a column has fewer than ~10,000 unique values (like country, browser, OS), wrapping it in LowCardinality(String) can make queries 2-10x faster and use 5-10x less storage. It's basically free performance!

-- Instead of this:
country String

-- Use this (much faster!):
country LowCardinality(String)

📅 Date & Time Types

Type	Size	Resolution	Range
`Date`	2 bytes	Day	1970 to 2149
`Date32`	4 bytes	Day	1900 to 2299
`DateTime`	4 bytes	Second	1970 to 2106
`DateTime64(3)`	8 bytes	Millisecond	1900 to 2299

🧒 ELI5

Use Date when you only care about the day (like birthdays). Use DateTime when you need hours/minutes/seconds (like log timestamps). Use DateTime64(3) when you need milliseconds (like high-frequency trading).

📐 Schema Design Best Practices

1. Denormalize Your Data

Unlike PostgreSQL where you normalize into many tables and JOIN, ClickHouse prefers wide, flat tables. JOINs are expensive in columnar databases.

-- ❌ DON'T: Normalized (slow JOINs)
SELECT o.total, c.name, p.title
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;

-- ✅ DO: Denormalized (fast scan)
SELECT total, customer_name, product_title
FROM orders_denormalized;

2. Choose ORDER BY Wisely

-- Put most-filtered columns FIRST
-- If you always filter by date and user_id:
ORDER BY (event_date, user_id, event_type)

-- NOT this (date buried at the end):
ORDER BY (event_type, user_id, event_date)  -- ❌ slow!

3. Use the Smallest Possible Types

-- ❌ Wasteful
status String          -- "active", "inactive" = ~8 bytes each
country String         -- "US", "UK" = variable bytes
age Int64              -- 8 bytes for a number 0-150?!

-- ✅ Efficient
status Enum8('active'=1, 'inactive'=2)  -- 1 byte
country LowCardinality(String)              -- dictionary encoded
age UInt8                                     -- 1 byte (0-255)

Table Engines SQL Queries

October 2026 practice

Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.

Use the narrowest numeric type that fits. DateTime64 when you need fractions. LowCardinality(String) for short repeated strings. The JSON type is the production type for documents with changing keys, since version 25.3. You can hint a path, skip a path, and cap dynamic paths with max_dynamic_paths. Paths over the cap go into shared data. Do not use the older experimental Object('json') form for new tables.

payload JSON(
    max_dynamic_paths = 256,
    user_id UInt64,
    SKIP secret
)