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
Signal
Target
Why this number
Service availability
99.95% / month
About 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 s
Dominated by the async flush timeout plus one S3 PUT and one Keeper commit.
Insert to visible
p99 < 5 s
Other replicas learn the new part set from Keeper asynchronously.
Dashboard query, warm cache
p95 < 300 ms
Data on local NVMe or in RAM. Sets the cache-sizing requirement.
First query after cold start
p95 < 10 s
Paid in S3 GETs. Prewarming the hottest parts is the lever.
Resume from idle
p95 < 30 s
Warm pool of nodes; metadata loads from Keeper and object storage, no data copy.
Data loss for acked writes
RPO 0
Ack 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
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.
02 · Write path
Ingestion: batching, async inserts, dedup
Every INSERT into a MergeTree table becomes at least one immutable part. In the cloud a part is a set of S3 objects plus a Keeper commit. So the whole write-path design is about turning millions of small events into few, large parts without losing or duplicating any.
The path of one batch
Accept. Proxy authenticates, routes to a replica in the read-write group. Producers are Kafka consumers (managed pipes or the Kafka engine), HTTP clients, or INSERT … SELECT FROM s3().
Buffer. Either the client batches (10k–100k rows, or about one insert per second per table), or the server does it with async inserts: rows wait in an in-memory buffer keyed by table, settings and format.
Form the part. Sort by the table's ORDER BY, split by partition, compress each column, build the sparse primary index (one mark per 8,192-row granule).
Write objects. Upload part files to object storage under fresh, unique keys. Nothing is overwritten, so a half-finished upload is garbage, never corruption.
Commit. In one Keeper transaction, add the part to the table's part set and record its dedup hash. This is the linearisation point.
Ack. Only now does the client get success. Other replicas pick the new part set up asynchronously.
Merge later. Background merges combine small parts into large ones. Keeper assigns each merge to one replica so the work is done once.
Why batching matters twice
Small inserts cost S3 PUTs (about 4 objects per part) and Keeper writes (one commit per part). They also create merge debt. When a partition accumulates too many active parts, MergeTree first delays inserts and then rejects them with "too many parts". That is the built-in backpressure signal.
Async inserts
The server buffers small inserts and flushes on size or time. Since 24.2 the flush timeout is adaptive, starting at 50 ms. With wait_for_async_insert=1 the client is acked only after the flush is durable. With =0 it is fire-and-forget: faster, but a crash loses the buffer.
-- Many small producers, server-side batching, durable ack
INSERT INTO events
SETTINGS async_insert = 1, wait_for_async_insert = 1
FORMAT JSONEachRow
{"ts":"2026-10-28 10:00:00","tenant":"acme","event":"click"}
-- Kafka consumer retrying a batch: same token on every retry
INSERT INTO events
SETTINGS insert_deduplication_token = 'events/topic-a/p7/off-1834000-1835999'
FORMAT RowBinary …
Exactly-once-ish
Kafka gives at-least-once delivery. We get effectively-once by making the insert idempotent and committing offsets only after the insert is acked.
Block dedup. Replicated and shared MergeTree keep a window of recent block hashes in Keeper per table. A retried identical block is acknowledged but not written again.
Deterministic token. Derive insert_deduplication_token from (topic, partition, first offset, last offset). A retry after a timeout then dedups even if the batch was re-serialised differently.
Async inserts dedup per source insert inside the flushed batch. Recent releases unify the switches under deduplicate_insert.
Materialized views need their own dedup on retry, or a retried insert double-counts in the target table.
Business-key dedup (the producer sends the same event twice) is a different problem. Use ReplacingMergeTree with a version column and accept eventual dedup, or FINAL at query time.
Say it out loud"The ack point is the Keeper commit. Before it, a retry is safe because the dedup hash isn't there yet. After it, a retry is safe because it is. The window is bounded, so tokens must be deterministic and retries must happen within the window."
03 · Shared storage
Storage on object storage
Data lives once, in object storage. Compute nodes keep only caches. Metadata that must be consistent (which parts exist) lives in Keeper. This is what lets you add a replica in seconds and turn compute off at night.
Concern
ReplicatedMergeTree on local disks
SharedMergeTree on object storage
Data copies
One full copy per replica
One copy in S3; durability delegated to S3
Replication
Replicas fetch parts from each other; replication log in Keeper
No replica-to-replica traffic; leaderless, replicas read the part set from Keeper
Metadata
Part metadata on every server's disk
Part set, dedup hashes and merge assignments in Keeper
Adding a replica
Copy the full dataset
Load metadata, start serving; caches warm on demand
Scale-out unit
Shards, with resharding pain
Hundreds of replicas per table, no shards
Insert quorum
Optional insert_quorum
Every insert is a quorum insert by construction
Weak spot
Disk cost ×3, rebalancing
S3 latency and request cost, Keeper as shared dependency
What goes in Keeper
The active part set per table (names, checksums, ranges)
Recent insert block hashes for dedup
Merge and mutation assignments so each runs once
Table schema versions and DDL coordination
Workload and resource definitions (workload_zookeeper_path)
Keeper never holds column data. Its load scales with the number of parts and commits, which is the second reason to batch.
Cache hierarchy on each node
Query
└ mark cache · uncompressed cache · query cache RAM
└ filesystem cache local NVMe
└ (distributed cache tier, optional) shared
└ object storage source of truth
The filesystem cache stores compressed byte ranges of S3 objects in chunks of a few MB, read-through by default (write-through with cache_on_write_operations). LRU, or SLRU so one big scan cannot flush the hot set. A hit saves a GET, its latency, and a step toward S3 throttling.
S3 request economics
Item
List price
Design consequence
Storage, S3 Standard
$0.023 / GB·mo
About 3–4× cheaper than one gp3 SSD copy, and SSD needs 3 copies.
PUT, COPY, POST, LIST
$0.005 / 1k
1,000 tiny inserts/s with 4 objects each ≈ 10 billion PUTs a month ≈ $52k. Batch.
GET
$0.0004 / 1k
Cheap per request, but range reads add up. Cache, and read large ranges.
Per-prefix request rate
≈3,500 PUT/s · 5,500 GET/s
Spread object keys across random prefixes; a 503 SlowDown is a signal, not an outage.
Same-region S3 to EC2 transfer
free
Cross-AZ replica traffic is what costs money, and SharedMergeTree removes most of it.
Layout details that drive pruning
Parts are sorted by ORDER BY; the sparse primary index has one entry per granule, so it fits in RAM even for trillions of rows.
Partitioning is for lifecycle (drop a month, TTL to cold storage), not for speed. Coarse partitions keep part counts low.
Codecs per column: Delta/DoubleDelta for timestamps, Gorilla for floats, then LZ4 or ZSTD.
Small inserts produce compact parts (few files); big parts are wide (one file per column), which keeps PUT counts down for small writes.
04 · Read path
Query layer
Any replica can be the initiator. Because every replica sees the same data, the question is not where the data is but how to split the reading across nodes and keep caches useful.
Planning a distributed query
Parse and analyse. The analyzer resolves names, applies row policies, picks projections or materialized-view targets if they cover the query.
Prune. Partition pruning, then primary-key ranges over marks, then skip indexes. The output is a list of mark ranges per part, with a byte estimate.
Decide the fan-out. Small read: run locally with max_threads. Large read: use parallel replicas. automatic_parallel_replicas_mode can make this decision from collected statistics, with a bytes-per-replica threshold.
Execute. Vectorised pipeline per thread. Aggregations produce partial states; the initiator merges them (two-level aggregation for high cardinality).
Return. Stream blocks to the client; apply LIMIT, ORDER BY merge at the initiator.
Parallel replicas
With no shards, scale-out for one query comes from splitting mark ranges across replicas. The initiator acts as coordinator: replicas announce which parts they have, the coordinator hands out ranges, assigning a part to the same replica each time (consistent hashing) so its cache stays warm, and lets idle replicas steal unclaimed ranges.
Costs: coordination round trips (bad for 20 ms queries), and JOINs need the right side broadcast or a supported mode. That's why the switch is automatic and threshold-based.
Classic shards (contrast)
A Distributed table scatters the query to one replica per shard and gathers partial results. It is still the answer when the dataset is bigger than what one node's cache can usefully hold per query, or for multi-region layouts. On shared storage you mostly do not need it.
Caches above the data
Cache
Key
Invalidation
Use it for
Query result cache
Normalised query AST + relevant settings + user
TTL only (query_cache_ttl); inserts do not invalidate
Dashboards that tolerate seconds of staleness. Opt in with use_query_cache = 1.
Mark / primary index cache
Part + column
Part removed by merge
Always on; keeps pruning in RAM.
Uncompressed cache
Compressed block
LRU
Many short point-ish queries on the same blocks.
Filesystem cache
S3 object byte range
LRU / SLRU; objects are immutable
Everything that would otherwise be a GET.
Pre-aggregation
Materialized view or projection
Maintained on insert
Moving query CPU to ingest time.
Say it out loud"Immutability makes caching easy: an object never changes, so cache entries never go stale. Only the part set changes, and that comes from Keeper. The result cache is the one place staleness exists, and it is opt-in with a TTL."
05 · Isolation
Multi-tenancy and workload scheduling
Isolation comes in layers. Between customers it is hard: separate pods, networks and storage prefixes. Inside one service it is soft: a scheduler that decides who gets the next CPU slot, IO request or byte of memory. The interviewer wrote that scheduler, so this is where to spend the most time.
Layer
Mechanism
Protects against
Between tenants
Separate Kubernetes pods per service, network isolation, own bucket or prefix, optional customer-managed keys
Everything: CPU, memory, page cache, data access
Between workloads of a tenant
Compute-compute separation: read-write and read-only groups over the same storage
Ingest and merges hurting dashboards
Inside one node
Workload scheduler: CPU slots, IO, query slots, memory reservations
Big scans, memory hogs, merge storms
Per user / key
Quotas, settings profiles with constraints, max_concurrent_queries_for_user
Runaway clients, abuse, cost blowouts
Shared dependencies
Rate limits on Keeper and S3 per service; IO limits per workload
One service exhausting Keeper or an S3 prefix
The scheduling hierarchy
Resources are declared once; a single WORKLOAD tree is shared by all of them. Leaves hold queries; inner nodes apply policies and limits. Two kinds of resource behave differently:
Time-shared · CPU, IO, query slots
Requests queue at leaves and are dequeued by policy. priority is strict and drives preemption; weight gives max-min fair shares between siblings. Constraints: max_concurrent_threads, max_cpus/max_cpu_share (token bucket), max_io_requests, max_bytes_inflight, max_bytes_per_second with max_burst_bytes, max_concurrent_queries, max_queries_per_second.
Space-shared · memory
Allocations are admitted, grow and shrink, and can be evicted (killed). A query with reserve_memory > 0 starts pending until the reservation fits under max_memory. precedence decides admission and who gets evicted; weight shares by normalised usage. A soft limit (max_memory_before_spill) asks operators to spill first. Too many waiters returns SERVER_OVERLOADED.
-- infrastructure limits come from server config; tenant workloads go under them
CREATE RESOURCE cpu (MASTER THREAD, WORKER THREAD);
CREATE RESOURCE memory (MEMORY RESERVATION);
CREATE RESOURCE s3_read (READ DISK s3);
CREATE RESOURCE s3_write (WRITE DISK s3);
CREATE WORKLOAD all SETTINGS max_concurrent_threads_ratio_to_cores = 2, max_memory_ratio = 0.8;
CREATE WORKLOAD system IN all SETTINGS weight = 1; -- merges, mutations
CREATE WORKLOAD user IN all SETTINGS weight = 9;
CREATE WORKLOAD dashboards IN user SETTINGS priority = -1, max_cpu_share = 0.6;
CREATE WORKLOAD ingest IN user SETTINGS weight = 2;
CREATE WORKLOAD adhoc IN user SETTINGS weight = 1, precedence = 1,
max_cpu_share = 0.5, max_bytes_per_second = '500Mi' FOR s3_read;
SELECT … SETTINGS workload = 'dashboards';
-- pin it: SETTINGS PROFILE with workload = 'adhoc' CONST; server: merge_workload = 'system'
Slots, not threads. A thread must hold a CPU slot to run. In overload, each released slot goes to the workload the policy picks; inside one workload, round-robin by default, or scheduler = 'fair' | 'las' | 'priority'.
Preemption. With cpu_slot_preemption, threads renew their slot every cpu_slot_quantum_ns. A thread that cannot renew for cpu_slot_preemption_timeout_ms makes the query downscale. Without preemption, fairness is by number of slot grants, not CPU-seconds.
Slot parking. A thread waiting on remote IO can release its slot so the CPU does useful work for someone else.
Weights are floors, caps are ceilings. Weight gives a best-effort guarantee from below; max_cpu_share limits even when the node is idle. Caps waste capacity; use them for cost control or a hostile class, not by default.
Per host. Every host schedules independently, so every max_* is per host. Definitions are replicated through Keeper so all hosts agree on the tree.
Strict mode.throw_on_unknown_workload forces every query into a known workload; a default leaf catches unmarked ones.
Per-host versus cluster-wide limits
Approach
How
Good
Bad
Local only
Each node enforces its limits
No coordination on the hot path; survives partitions
A tenant pinned to one node by cache affinity gets 1/N of its budget
Static split
Cluster budget ÷ N per node
Simple
Wrong under skew; must change on every scale event
Leased tokens
Central allocator grants tokens every 100 ms–1 s; nodes spend locally and report demand
Close to global accuracy, local latency
Allocator is a dependency; choose fail-open or fail-closed
Edge enforcement
Rate-limit per tenant at the proxy
Cheap, catches QPS abuse
Knows nothing about cost per query
A parallel-replicas query touches many hosts; each enforces the same workload name locally, so the query is throttled consistently without global state.
Noisy-neighbour simulator
One node, three workloads. Dashboards send many short queries. Ad-hoc scans arrive rarely but each wants many threads for seconds. Ingestion is steady. Each tick is 10 ms of one CPU slot. Switch the policy and watch dashboard p95.
DashboardsAd-hoc scansIngestion
Model: dashboard query 30–80 ms of CPU, up to 4 threads; scan 6–12 CPU-seconds, up to max_threads; ingest batch 0.2–0.4 CPU-seconds, 2 threads, 20 per second, weight 2. Weights and cap apply in the workloads mode. Fractional slots stand in for time-slicing within a quantum.
06 · Elasticity
Autoscaling and idling
Shared storage turns scaling into a compute-only problem: no data to move, only caches to warm. The hard parts are choosing the signal, avoiding flapping, and hiding cold starts.
Horizontal
Add replicas to a group. With SharedMergeTree a new replica loads the part set and serves at once. Good for concurrency and for parallel replicas. Cache affinity uses consistent hashing so adding the Nth node moves only about 1/N of the cached parts.
Vertical
Bigger pods. Needed when single queries run out of memory, since a GROUP BY that does not fit on one node does not get better with more nodes. Requires restart, so do it make-before-break: start the bigger replica, drain, then stop the old one.
Signals and policy
Scheduler wait time per workload is the best overload signal: CPU at 90% with no queueing is fine, CPU at 60% with queries waiting for slots is not.
Memory: pending reservations and evictions push vertical scaling; OOM kills are the most user-visible failure.
CPU smoothed over minutes, as a backstop.
Scale up fast (one or two windows), scale down slowly (tens of minutes) with hysteresis and a cooldown.
Scheduled scaling for known peaks; tenant-set min and max replicas as the cost guardrail.
The scheduler absorbs bursts of seconds; the autoscaler handles minutes; capacity planning handles weeks.
Scale to zero, then wake
No queries for the idle timeout and no background work pending, so the service idles. Merges from another read-write service sharing the storage can keep it awake.
Pods are released. Data stays in S3, metadata in Keeper. Billing for compute stops.
A client connects. The proxy holds the connection and asks the control plane to resume.
Nodes come from a pre-warmed pool with the image already pulled. Replicas load the part set and primary indexes lazily.
Prewarm: fetch the most recent partitions and the most queried columns in the background, rate-limited so it does not hurt the first query.
What idling saves
Billed hours = active hours + wake-ups × idle timeout, capped at 24. Each wake-up is also one cold cache, paid in latency and GETs. A longer timeout trades money for fewer cold starts.
07 · Things that break
Failure modes and mitigations
For each failure, give the blast radius first, then detection, then mitigation. Open any row.
08 · Money
Cost tradeoffs
In the calculator's default scenario compute is about three quarters of the bill, storage about a fifth, and S3 requests under a tenth. The levers below move one line by spending on another.
Lever
You save
You pay
When it wins
Object storage instead of replicated SSD
~10× on storage ($0.023 vs ≈$0.08 × 3 copies)
Cold-read latency, request fees, cache tier
Almost always for analytics
Bigger NVMe cache
GETs, tail latency, throttling risk
Instance cost (NVMe-backed nodes)
Hot set fits; dashboards repeat
Bigger batches / async inserts
PUTs, Keeper writes, merge CPU
Freshness (hundreds of ms to seconds)
Many small producers
Stronger codecs (ZSTD level up)
Storage, GET bytes, cache footprint
Insert and merge CPU
Long retention, read-mostly
Materialized views, projections
Query CPU, latency
Storage, insert CPU, write amplification
Same aggregations run all day
Parallel replicas
Latency of big queries
Coordination, more node-seconds per query
Interactive scans over large ranges
Idling and scale to zero
Compute while unused
Cold starts, warm-pool cost (provider side)
Dev, staging, office-hours dashboards
Separate read-only group
Over-provisioning the write group for peaks
Second set of caches
Ingest and dashboard peaks don't overlap
Result cache
Repeated query CPU
Staleness up to TTL, RAM
Dashboards auto-refreshing
Hard caps (max_cpu_share)
Predictable spend per class
Idle capacity left unused
Chargeback, untrusted ad-hoc users
Say it out loud"The provider's margin lives in multiplexing: idle tenants release nodes into a shared warm pool, and busy tenants pick them up. Every isolation mechanism that reserves capacity, like caps or dedicated groups, eats into that, so reserve only where the SLO demands it."
09 · The whole picture
Architecture
Draw this by minute 15. Teal arrows are the write path, grey arrows the read path.
Inserts and SELECTs land on different compute groups over the same storage. The write path ends with a Keeper commit after the S3 PUT; the read path learns the part set from Keeper and fetches bytes through local and shared caches. No arrow connects replicas for replication, because there is none.
Narrate in this order
Storage first: S3 is the source of truth, Keeper is the source of truth about S3.
Read path: proxy, read-only group, prune, caches, GET on miss.
Control plane last: it is off the data path.
Point at the dependencies
Keeper is on the write path, not the read path for data.
The proxy is on every path; it must be stateless and horizontally scaled.
The control plane failing must not stop queries, only scaling.
10 · 60 minutes
Driving the interview
You lead; the interviewer steers. Budget the hour so the isolation and scheduling discussion gets a full twelve minutes, because that is where this interviewer will push.
Habits that score
State assumptions with numbers, then move on.
Name the alternative you rejected and why.
Say what you would measure to know you were wrong.
Use ClickHouse vocabulary precisely: part, granule, mark, workload, resource, slot.
When challenged, separate the guarantee (what holds) from the mechanism (how).
Traps
Treating weights as quotas. They are floors under contention.
Claiming cluster-wide fairness from per-host scheduling.
Forgetting that merges are a workload too.
Saying "Kafka gives exactly-once" without the idempotent sink.
Solving isolation only with more hardware.
Likely follow-ups
11 · Reading list
Further reading
Read in this order. For each, the line under the title is what to take into the interview.
The template for multi-cluster, shared-data warehouses: virtual warehouses over S3, a transactional metadata service, files assigned to workers by consistent hashing for cache locality, and file stealing for stragglers. Compare its metadata store with Keeper's role in SharedMergeTree.
MergeTree parts and merges, pruning (primary key, skip indexes, partitions), vectorised and multi-node execution, and the cloud deployment mode. Learn its vocabulary; the interviewer will use it.
Multi-level serving trees for aggregation and columnar storage for nested data. Note the query dispatcher: it schedules work in slots across a shared cluster and handles stragglers, the same problem as parallel replicas and CPU slots.
Compute-storage separation, isolation model, and the per-replica concurrency limit. Read with the SharedMergeTree page (leaderless replication through Keeper), warehouses (compute-compute separation, idling caveats) and the filesystem cache explainer.
The most important page for this interview. Know time-shared versus space-shared resources, priority versus precedence, CPU slot preemption and parking, threads versus CPU throttling, memory reservations and eviction rules, query slots, and why limits are per host.