Cloud OLAP Design - Systems - Jordi Villar
ClickHouse · system design interview prep

Cloud OLAP System Design

A worked design of a multi-tenant cloud OLAP service in the shape of ClickHouse Cloud: shared object storage, stateless-ish compute, Keeper for metadata, and a workload scheduler that keeps one tenant's scan from eating another tenant's dashboard.

01 · Scope

Requirements, SLOs and back-of-envelope

Pin the scope in the first four minutes: a regional, multi-tenant managed service where each tenant gets one or more services (compute groups) over a shared copy of their data in object storage.

Functional

  • Create, scale, pause and delete services through an API and console.
  • Ingest by streaming (Kafka via managed pipes or Kafka engine) and by HTTP/native INSERT, plus bulk loads from S3.
  • Full ClickHouse SQL over HTTPS, native TCP and MySQL wire protocol.
  • Materialized views, projections, TTL moves and deletes, lightweight updates.
  • Several compute groups over the same tables: one read-write for ingest, read-only groups for dashboards.
  • Users, roles, quotas, settings profiles, and workload classes per tenant.
  • Backups, restore, and point-in-time recovery to another region.

Non-functional

  • Isolation: one tenant's load must not move another tenant's p99. Inside a tenant, dashboards must survive ad-hoc scans.
  • Elasticity: storage and compute scale independently; compute can go to zero.
  • Durability: an acknowledged insert is never lost (object storage plus Keeper quorum).
  • Freshness: rows visible to queries within seconds.
  • Cost: storage priced near object storage, not near replicated SSD.
  • Operability: rolling upgrades, AZ failure with no data loss, no manual rebalancing.

SLOs to propose

SignalTargetWhy this number
Service availability99.95% / monthAbout 22 minutes of downtime. Needs replicas in 3 AZs and a Keeper quorum that survives one AZ.
Insert ack (sync or async with wait)p99 < 1 sDominated by the async flush timeout plus one S3 PUT and one Keeper commit.
Insert to visiblep99 < 5 sOther replicas learn the new part set from Keeper asynchronously.
Dashboard query, warm cachep95 < 300 msData on local NVMe or in RAM. Sets the cache-sizing requirement.
First query after cold startp95 < 10 sPaid in S3 GETs. Prewarming the hottest parts is the lever.
Resume from idlep95 < 30 sWarm pool of nodes; metadata loads from Keeper and object storage, no data copy.
Data loss for acked writesRPO 0Ack only after the part is in object storage and committed in Keeper.

Back-of-envelope calculator

Change any input. Hardware and behaviour numbers are assumptions you should state out loud; S3 prices are S3 Standard list prices in us-east-1 ($0.023/GB-month for the first 50 TB, $0.005 per 1,000 PUTs, $0.0004 per 1,000 GETs).

Presets
Workload
rows/s
bytes
×
days
QPS
GB
Storage behaviour
%
days
tables
ms
Hardware assumptions
vCPU
$/vCPU·h
MB/s raw
GB/s
%
Say it out loud

Fixed modelling constants: a new part writes about 4 objects; merges rewrite each byte about 5 times over its life and upload in 16 MiB chunks; reads fetch 4 MiB ranges; merges cost about half the CPU of the original insert; each compute group keeps at least 2 replicas.