I85PREP
⌘ K
ClickHouse Certified study materials
ClickHouse Certified study resources

Tenant dashboards scan 800 GB: fixing the sorting key on a 14-billion-row table

ClickHouse Certified · Put your knowledge to work in context.

All case studies

Tenant dashboards scan 800 GB: fixing the sorting key on a 14-billion-row table

Your team runs a self-managed 6-node ClickHouse cluster holding an events table with 14 billion rows (~90 TB compressed). Ingest is ~1.5 TB/day, appended by a Kafka pipeline. The table is MergeTree with PARTITION BY toYYYYMM(event_time), ORDER BY (event_time) and index_granularity = 8192. About 50,000 tenants share it, each holding between 100 and 40 million rows per month. Dashboards filter WHERE tenant_id = ? AND event_time >= now() - INTERVAL 1 HOUR. Those queries currently scan 300-800 GB each and take 40-90 seconds; EXPLAIN indexes = 1 shows every part in the month is selected and the primary key index does nothing for tenant_id. A minmax skip index on tenant_id with GRANULARITY 4 was added last quarter and changed nothing. Retention is 24 months. Analysts occasionally run time-only ad-hoc queries with no tenant filter, so those cannot be made to regress badly.

1. Explain precisely why the tenant predicate cannot prune data today, and state the `ORDER BY` change you would make. What must you check before rolling it out?

Read the worked answer

Why nothing is pruned. ClickHouse reads in granules of index_granularity rows (8192 here) and the sparse primary key stores one mark per granule, taken from the ORDER BY prefix. With ORDER BY (event_time) the marks are ordered by time only, so a tenant_id predicate gives the engine no way to jump over granules: every mark inside the queried time range is read. Monthly partitioning does prune, but a one-hour dashboard window still lands inside the current month, which is the largest part.

Why the skip index is dead weight. minmax stores one min and one max per granule group; with 50,000 tenants interleaved across a part, those bounds span nearly the whole domain, so every granule is selected. A bloom_filter would not rescue this either: each 8192-row granule contains many distinct tenant_id values, so false positives stay close to 100%. Bloom filters pay off on high-cardinality columns where a granule holds few distinct values, e.g. request_id for point lookups.

The change. ORDER BY (tenant_id, event_time). Each tenant's rows become contiguous, marks are ordered by tenant then time, and a per-tenant one-hour query can binary-search the marks and read a handful of granules. Keep PARTITION BY toYYYYMM(event_time) so analysts' time-only queries still prune by partition.

Before rollout, verify: EXPLAIN indexes = 1 on a tenant query and on a time-only query against a backfilled copy; read_bytes deltas in system.query_log; and that very small tenants do not create a long tail of tiny granules that hurts compression.

2. How do you physically migrate 90 TB to the new sort order without an outage or a 3-day stall, and what is the alternative if a full rewrite is politically impossible?

Read the worked answer

A sort-order change is a rewrite: MergeTree does not let you ALTER ... MODIFY ORDER BY in place, so you build a new table and swap.

  1. Create events_v2 with the new ORDER BY, the same partition key, codecs and TTL.
  2. Dual-write: point the Kafka pipeline (or a Distributed table fronting both) at events_v2 as well, so no new rows are missed during the migration.
  3. Backfill one partition at a time: INSERT INTO events_v2 SELECT * FROM events WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01'. Per-partition inserts keep memory bounded and avoid producing tens of thousands of parts in one shot. Throttle with max_insert_threads and watch system.merges and system.mutations.
  4. Budget disk: you need roughly the table size again, plus merge headroom (about 2x compressed for the affected partitions).
  5. Validate: per-partition count() plus a content checksum such as sum(cityHash64(...)); row counts alone will not catch a truncated backfill.
  6. Swap atomically with EXCHANGE TABLES events AND events_v2 (requires the Atomic database engine), keep the old table for a grace period, then drop it.

Cheaper alternative: add a projection ORDER BY (tenant_id, event_time) and let the engine maintain the second sort order. No dual table, but every insert now writes two layouts, and the projection is only used when the query's filters match its prefix. Refreshable materialized views are the other option for serving the dashboard shape directly.