Events Tables at Scale

Start from the query workload, not the event

The instinct coming from an OLTP background is to design the table first — model the entity, normalize it — and let queries adapt afterward. order-by-is-the-primary-key already showed why that's backwards here: only a left-anchored prefix of ORDER BY narrows a scan, so ORDER BY is not a neutral structural choice, it's a bet on which queries matter most. Designing an events table idiomatically means writing down the query workload first.

A typical multi-tenant product-analytics workload looks like this:

• Nearly every query is scoped to one customer at a time — a dashboard, an API call, a scheduled report, all fetching one tenant's data.
• Within a tenant, queries commonly filter by event_type — "show me all purchase events," "count page_view events."
• Queries very commonly filter by a time range — "in the last 7 days," "this month."
• Queries occasionally need a specific user's or session's full history, without knowing the event type in advance.

That workload directly determines the ORDER BY prefix, using the same reasoning order-by-is-the-primary-key built: put what's filtered on most, and most selectively, first.

CREATE TABLE events
(
    event_id     UUID,
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    user_id      UInt64,
    session_id   UInt64,
    event_time   DateTime64(3),
    properties   Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time)
TTL event_time + INTERVAL 90 DAY DELETE;

Reading the design decisions in this CREATE TABLE, in order:

• ORDER BY (tenant_id, event_type, event_time) — tenant_id leads because "scoped to one tenant" is true of nearly every query; nothing else earns the first slot. event_type comes second because it's the next most common filter, and doing so means a query for one tenant's purchase events narrows on a two-column prefix, not just one. event_time last supports the common "last 7 days" range filter within an already-narrowed (tenant_id, event_type) slice, and keeps physically nearby events close together for compression, exactly as the last module's Primary key lesson described for a trailing column.
• PARTITION BY toYYYYMM(event_time) — a coarse, monthly bucket, for the same operational reason parts-and-partitions gave: cheap bulk retention operations on natural buckets, not a query-speed lever. Partitioning by tenant_id would be the tempting-but-wrong move here — exactly the over-partitioning anti-pattern that lesson warned about, multiplied by however many tenants this table serves.
• LowCardinality(String) on event_type — event_type is a small, bounded vocabulary (page_view, purchase, signup, and a few dozen more, not millions of distinct values), which is exactly the shape LowCardinality dictionary-encodes efficiently.
• properties Map(String, String) — a Map column holds an arbitrary number of key-value string pairs per row, read back with properties['some_key']. It's what lets each event carry whatever properties its event_type needs without a fixed column for every possible one. Covered in depth next.
• TTL event_time + INTERVAL 90 DAY DELETE — covered after that.

Real-world case study: Cloudflare's HTTP analytics pipeline

Cloudflare's HTTP analytics system is one of the most publicly documented ClickHouse events pipelines, and it makes the same trade-offs this lesson is building toward at a much larger scale. Their non-aggregated requests table stores "over 100+ columns, collecting lots of different kinds of metrics about each request passed through Cloudflare," fed by 106 Go consumers reading Cap'n Proto-encoded logs off Kafka. At the time of that writeup, the pipeline handled "on average...6M HTTP requests per second, with peaks of up to 8M requests per second," across a 36-node cluster with 3x replication.

Three of their decisions map directly onto this lesson's concept inventory:

• Flexible, high-cardinality dimensional data: for their aggregated views, they used what they described as "the ClickHouse Nested structure ending in 'Map'...similar to the Postgres hstore data type," specifically because it let them attach varying key/value dimensions to aggregated rows without a fixed, wide column list. That's their own Nested-based naming convention for the pattern, not literally today's native Map(K, V) type this lesson uses — but it's solving the exact same problem this lesson's properties Map(String, String) column solves, one layer down at the raw-event level.
• Retention as an explicit, costed decision, not an afterthought: storing a full year of raw, non-aggregated data was priced out at "18.52 PiB (RF x3)" — expensive enough that keeping it indefinitely, unaggregated, was never the plan. That's the same reasoning behind this lesson's TTL ... DELETE clause, just at a scale where the dollar cost of skipping that decision is visible in the blog post itself.
• index_granularity as a real, workload-tuned setting: for their aggregated tables specifically, they set index_granularity to 32 instead of the default 8,192, and reported query latency dropping 50% with throughput roughly tripling as a result. That's the sparse-primary-index lesson's over-read/index-size trade-off, chosen deliberately in the direction of a much smaller granule for a workload where that trade paid off — not evidence that 32 is a good default, just proof the knob is real and worth tuning for the right workload.

Idiom vs. anti-pattern: Map column vs. an EAV property table

Event properties are the part of this schema most likely to get redesigned badly, because the instinct that produces the bad version comes from good OLTP training: "properties vary by event type and I don't want a schema migration every time someone adds one, so I'll normalize them into their own table." That instinct produces the Entity-Attribute-Value (EAV) pattern — and it's a real thing engineers reach for, not a straw target, because in a row-oriented OLTP database it's a perfectly reasonable way to model sparse, varying attributes.

CREATE TABLE events
(
    event_id    UUID,
    tenant_id   UInt32,
    event_type  LowCardinality(String),
    event_time  DateTime64(3)
)
ENGINE = MergeTree
ORDER BY (tenant_id, event_type, event_time);

CREATE TABLE event_properties
(
    event_id        UUID,
    property_key    String,
    property_value  String
)
ENGINE = MergeTree
ORDER BY (event_id, property_key);

-- Reconstructing one event's full properties requires a JOIN:
SELECT e.event_id, e.event_type, groupArray((p.property_key, p.property_value)) AS props
FROM events e
INNER JOIN event_properties p ON e.event_id = p.event_id
WHERE e.event_id = '3fa85f64-5717-4562-b3fc-2c963f66afa6'
GROUP BY e.event_id, e.event_type;

-- properties Map(String, String) already lives on the events row itself.
SELECT event_id, event_type, properties
FROM events
WHERE event_id = '3fa85f64-5717-4562-b3fc-2c963f66afa6';

-- Filtering or reading a single property doesn't need the whole map:
SELECT event_id, properties['sku'] AS sku
FROM events
WHERE tenant_id = 42 AND event_type = 'purchase' AND properties['sku'] = 'WIDGET-9';

The EAV version doesn't just cost you a JOIN — the memory-bound cost a later lesson in this module (denormalization-tradeoffs) covers in depth. It multiplies row count: an event with 10 properties becomes 10 rows in event_properties instead of one, and every one of those rows repeats the event_id and pays the per-row storage overhead again. Columnar compression, the mechanism this whole course started from, works by finding similar values sitting next to each other in the same column; a property_key/property_value table interleaves unrelated keys and value types in the same two columns, which is close to the opposite of that. A Map(String, String) column keeps one row per event — matching the actual entity — and keeps property access to a single-column lookup instead of a join across two tables.

This isn't a universal rule against ever normalizing anything — denormalization-tradeoffs, later in this module, covers when a JOIN is the right call. It's specifically that per-event, sparse, varying attributes are the shape Map (or Nested, for cases needing more structure than a flat string-to-string mapping) was built for, and EAV is the shape a transactional mental model reaches for out of habit.

TTL: retention as a decision, not an accident

TTL event_time + INTERVAL 90 DAY DELETE tells ClickHouse to drop rows once event_time is more than 90 days old — but "drop" doesn't mean "immediately," and that timing detail matters operationally. Expired rows aren't deleted the instant they cross the threshold; they're removed the next time ClickHouse merges the part they live in. A row that turned 91 days old five minutes ago is very likely still physically on disk, and a query with no other filter can still return it, until the background merge process gets around to that part.

Row's event_time crosses the 90-day threshold
  → row is now "expired," but still physically present
  → still returned by queries that don't filter it out some other way
  → removed only when a background merge processes that row's part
  → merge timing follows the same schedule as any other background merge —
    not a fixed, predictable deadline

For a raw events table this is almost always fine — 90 days is a policy, not a compliance deadline measured in minutes. If it isn't fine — actual regulatory deletion requirements, for instance — TTL alone isn't the tool; that needs a harder guarantee than "eventually, on the next merge."

Design exercise

A mobile game studio runs several independently published game titles on one shared analytics pipeline. Every player action is logged as an event: level_start, level_complete, purchase, session_end. The studio's actual query patterns, gathered from their dashboard and support tooling:

• Almost every query is scoped to one game title.
• The most frequent aggregate query is "count of level_complete events per game per day," run on every game's dashboard.
• Support engineers occasionally need "this specific player's full event history across all event types," without knowing the event type in advance.
• Each event type has different properties: level_complete has level_id and duration_ms; purchase has sku and amount_cents; session_end has just duration_ms.
• Raw events only need to be queryable directly for 30 days; older data is rolled up elsewhere.