ClickHouse Data Types: Complete Reference for Analytics

ClickHouse: Complete Data Type Reference for Betting Analytics (What I Got Burned On)

One Byte That Cost Me 500 GB of Disk Space

When I first started working with ClickHouse, I used String for everything: user_id, event_time, bet amount. A month later, a table with 2 billion rows weighed 4 terabytes. A colleague looked at the schema and said, "Why are you storing a number as a string?" Turns out, String for user_id takes 8 times more space than UInt64. I changed it — and the table shrank to 800 GB.

ClickHouse offers dozens of data types. Using the right type isn't about saving gigabytes — it's about query speed (less data to read from disk) and stability (Decimal instead of Float won't surprise you with rounding errors).

Below is everything I've learned from real projects (betting analytics, fraud detection, LTV). At the end — a ready-made schema for a betting platform.

1. Integer Types: Counting Users and Bets

ClickHouse supports signed (Int) and unsigned (UInt) integers from 8 to 256 bits.

Type	Range	Size	When I Use It
UInt8	0..255	1 byte	Statuses (0/1), error codes
UInt16	0..65535	2 bytes	Port numbers, small counters
UInt32	0..4.2 billion	4 bytes	Country IDs, event types
UInt64	0..18 quintillion	8 bytes	user_id, event_id, amounts in cents
Int128/256	huge	16/32 bytes	Cryptographic hashes, very large counters

Production Practice:

user_id UInt64,          -- 8 billion users is enough for us
age UInt8,               -- nobody lives longer than 255 years
country_code UInt16,     -- 197 countries in the world, but UInt16 is nicer
is_fraud UInt8,          -- 0 or 1, why more?

Common Mistake: using UInt64 for everything. If a field only takes values 0 or 1 (a flag), UInt8 is 8 times more compact. On a billion rows, that's 8 GB vs 1 GB.

What I Got Burned On: I stored timestamp as UInt64 (Unix time). It works, but you lose the ability to use date functions like toDate(), toHour(), etc. Use DateTime.

2. Float32/Float64: Wallet or Hole?

odds Float64,            -- odds can be 2.5, 1.85, 100.0
probability Float32,     -- percentages 0.1..1.0, 32-bit precision is enough

Why Float Is Dangerous for Finances:

SELECT 0.1 + 0.2 AS float_sum;
-- Result: 0.30000000000000004 (classic IEEE 754)

Imagine you have 1 million bets of 0.01 cents each. The rounding error becomes real money. For bet amounts and payouts, use Decimal.

When Float Is Fine: odds (2.15, 1.85), probabilities, percentages, machine learning metrics.

3. Decimal(P, S): Money Loves Precision

bet_amount Decimal(18, 2),   -- up to 10^16 rubles, 2 decimal places
payout Decimal(20, 2),       -- payout can be larger than bet
balance Decimal(32, 2)       -- player's lifetime balance

• P (precision) — total number of digits (up to 38)
• S (scale) — digits after the decimal point

Rule I Derived: for rubles and dollars — Decimal(18,2) is enough with a margin (trillions). For crypto — Decimal(38,8).

Operations with Decimal:

SELECT 
    bet_amount * odds AS potential_payout,  -- Decimal * Float64 → Decimal
    bet_amount + 0.01 AS rounded_up         -- works, but be careful
FROM bets;

What I Got Burned On: ClickHouse doesn't handle Decimal * Decimal with different scales — it promotes to the larger one. We had cents in the 4th decimal place that never rounded. Fix: explicitly cast with toDecimal32().

4. String vs FixedString vs LowCardinality(String)

String — For Everything Long

session_id String,           -- UUID without hyphens, variable length
user_agent String,           -- long strings, unique values
raw_json String              -- JSON logs

FixedString(N) — For Fixed Length (Rarely Needed)

country_code FixedString(2), -- 'RU', 'US', 'DE' exactly 2 bytes
md5_hash FixedString(32)     -- always 32 characters

I Almost Never Use It: if you insert a shorter string, ClickHouse pads it with null bytes, which causes surprises in comparisons.

LowCardinality(String) — Magic for Repeated Values

sport LowCardinality(String),      -- 'football', 'basketball', 'tennis' (repeated)
device LowCardinality(String),     -- 'ios', 'android', 'web' (10-20 unique)
outcome LowCardinality(String)     -- 'win', 'loss', 'void'

How It Works: ClickHouse builds a dictionary of unique values and stores only indexes. For a column with 10 unique values, savings are 100x.

Real-World Moment: in a bets table, the sport field repeated billions of times. After replacing String with LowCardinality(String), the column size dropped from 40 GB to 400 MB.

When Not to Use: if the number of unique values exceeds 10,000 (e.g., user_agent). The dictionary bloats and performance degrades.

5. DateTime vs DateTime64 vs Date: Time Is Money

Type	Precision	Size	When to Use
Date	day	2 bytes	partitioning, daily reports
Date32	day (up to 2106)	4 bytes	if year > 2149 needed
DateTime	second	4 bytes	most events
DateTime64(3)	millisecond	8 bytes	live bets, event ordering
DateTime64(6)	microsecond	8 bytes	logs, metrics

What I Use in Production Projects:

event_time DateTime64(3),      -- milliseconds for live analytics
registration_date Date,        -- daily partitioning
last_update DateTime           -- second precision is enough

Common Mistake: storing time as Unix timestamp (UInt64). You lose all date/time functions:

-- This won't work:
SELECT toHour(event_time_uint) ... -- error

-- You need:
SELECT toHour(toDateTime(event_time_uint)) ... -- extra conversion

What I Got Burned On: I used DateTime for live bets. When it mattered, 10 events in the same second were indistinguishable. Switched to DateTime64(3) — and order was restored.

6. UUID: When Standard Matters More Than Speed

session_id UUID,
bet_uuid UUID DEFAULT generateUUIDv4()

UUID takes 16 bytes (like two UInt64). Comparison is slower than with numbers.

When I Still Use It: need to generate IDs on the client without DB access, integration with external systems, distributed systems without a single generator.

Alternative: UInt128 as two 64-bit numbers, but then you lose functions like toUUID().

7. Array(T): Storing Lists Without Normalization

tags Array(String),                     -- ['football', 'live', 'prematch']
coeff_history Array(Float64),           -- [1.5, 1.8, 2.1] odds changes
bet_bundle Array(UInt64)                -- bet IDs in an accumulator

Where It Actually Helped: we store the history of odds changes for a single event in an array. In a relational DB, you'd need a separate table. ClickHouse works great with arrayMap, arrayFilter, arrayJoin.

Real Query: find events where odds dropped by more than 30%:

SELECT event_id, coeff_history
FROM events
WHERE arrayExists((x, i) -> i > 1 AND x / coeff_history[i-1] < 0.7, coeff_history);

Limitation: nested arrays (Array(Array(String))) are barely supported. Denormalize to a flat structure.

8. Nullable(T): Evil to Avoid

bonus_amount Nullable(Decimal(10,2)),
refund_reason Nullable(String)

Nullable adds one extra flag per value (a bitmask). This means:

• Extra byte per row
• Slower aggregations (SUM, AVG must check for NULL)
• Doesn't work with some engines (e.g., for ORDER BY keys)

My Stance: in ClickHouse, I avoid NULL. Instead:

• Numbers: 0 instead of NULL
• Strings: '' (empty string)
• Dates: '1970-01-01'

Exception: when 0 is a legitimate value. For example, a bonus of 0 rubles is not the same as no bonus awarded. Then use Nullable.

9. Enum8/Enum16: For Finite Value Lists

outcome Enum8('win' = 1, 'loss' = 2, 'void' = 3),
bet_type Enum8('single' = 1, 'express' = 2, 'system' = 3),
event_status Enum8('scheduled' = 1, 'live' = 2, 'finished' = 3, 'cancelled' = 4)

Advantages: stored as 1 byte (Enum8) or 2 bytes (Enum16), fast comparisons, readable output.

Internals: ClickHouse stores numbers, but SELECT outputs strings.

INSERT INTO bets (outcome) VALUES ('win');  -- as string
INSERT INTO bets (outcome) VALUES (1);      -- or as number

Common Mistake: trying to ALTER TABLE ... MODIFY COLUMN to add a new value to an Enum. ClickHouse doesn't allow changing an Enum without recreating the table. All possible values must be planned in advance.

If in doubt, use LowCardinality(String). Sacrifice a byte for flexibility.

10. IPv4/IPv6: Detecting Multi-Accounting

ip_address IPv4,
client_ip IPv6     -- mobile operators use IPv6

Stored as binary (4 or 16 bytes), fast subnet operations.

Real Use Case: find users from the same IP:

SELECT user_id, count() AS bets
FROM bets
WHERE ip_address = IPv4StringToNum('192.168.1.1')
  AND created_at >= today() - 7
GROUP BY user_id
HAVING bets > 50;  -- potential bot

Functions That Save the Day: IPv4NumToString(), IPv4CIDRToRange(), isIPv4String().

Complete Schema for a Betting Platform (Production-Proven)

CREATE TABLE betting.bets_full
(
    -- Identifiers
    bet_id          UInt64 DEFAULT generateUUIDv4() (materialized) ???
    -- no, UUID separately
    bet_uuid        UUID DEFAULT generateUUIDv4(),
    user_id         UInt64,
    event_id        UInt64,
    session_id      String,                          -- not UUID, comes from nginx logs
    
    -- Timestamps
    created_at      DateTime64(3),                   -- milliseconds for live
    updated_at      DateTime,
    bet_date        Date DEFAULT toDate(created_at), -- materialized column
    
    -- Monetary fields (Decimal only!)
    bet_amount      Decimal(18, 2),
    odds            Float64,                         -- odds — Float is fine
    potential_payout Decimal(20, 2) ALIAS bet_amount * odds,
    real_payout     Decimal(20, 2),
    
    -- Categories with repetitions
    sport           LowCardinality(String),
    bet_type        Enum8('single' = 1, 'express' = 2, 'system' = 3),
    outcome         Enum8('win' = 1, 'loss' = 2, 'void' = 3),
    device_type     LowCardinality(String),
    
    -- Lists (change history)
    odds_history    Array(Float64),                  -- odds changes over time
    cashout_attempts Array(DateTime64(3)),           -- cashout attempts
    
    -- Fraud detection
    ip_address      IPv4,
    fingerprint     FixedString(32),                 -- browser hash
    
    -- Nullable only where truly needed
    refund_amount   Nullable(Decimal(18, 2)),       -- NULL if no refund
    cancellation_reason LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY bet_date
ORDER BY (created_at, user_id)
SETTINGS index_granularity = 8192;

Why This Schema Survived in Production:

• bet_date is materialized from created_at — date-based partitioning without extra computation
• LowCardinality for sport and device_type — saves 80% space
• ALIAS for potential_payout — not stored, computed on query
• No Nullable where 0 or empty string suffices

What's Next

Choosing the right types is the foundation. In upcoming articles, we'll cover building aggregations, window functions, and materialized views on this data.

The table from this article has been in our production cluster for a year, with 3 trillion records. It weighs 12 TB (with ZSTD compression). If everything were String, it would be 40 TB. Choose your types wisely.

---

← Previous: ClickHouse Client: How I Befriended the Console and HTTP API in a Gambling Project
→ Next: MergeTree in ClickHouse: How the Engine Slices Analytics into Granules and Merges Parts

— Editorial Team