FinOps Meets Architecture: Tiering ClickHouse from EBS to S3 Without Touching a Query

The Bill That Started It Run ClickHouse on EC2 long enough and you'll hit the same moment everyone does. The queries are fast, the ingestion pipeline is humming, and then someone opens the AWS bill and asks why storage alone costs more than the instance. The math isn't complicated. gp3 EBS costs about $0.08 per GB-month. Keep 3 TB of history and you're paying roughly $245 a month just to store it. That's before headroom, and you pay for provisioned size, not used size. EBS volumes can grow but never shrink, so every "let's give it some buffer" decision stays on the bill forever. We can find one of the obvious fix is S3, at $0.023 per GB-month, about 71% cheaper per gigabyte. The obvious objection is that S3 is object storage. It's slow on first byte, charges per request, and wasn't designed to back a database. So which one is the right architecture? Neither. There's no perfect ClickHouse architecture, only trade-offs you pick on purpose. But there is a design that removes most of the cost while keeping most of the speed, and ClickHouse supports it natively. We built a lab to find out what it actually costs in latency and requests. Repo: clickhouse-tiered-storage-lab The Two Bad Defaults Every design here sits between two extremes. Everything on EBS. It's fast and simple, and you pay premium prices to store data nobody has queried in 18 months(but you can't erase them). In most analytics workloads, dashboards hit the last 30-90 days. The long tail of history sits on the most expensive storage you own, doing nothing. Everything on S3. It's cheap, and every query pays the object-storage tax: network round-trips, per-request charges, and cold-read latency that shows up on interactive dashboards. What you actually want is recent data on local disk and old data on S3, in one table, with no application code stitching them together. The key to that is how ClickHouse stores data in the first place. Dissecting the Machinery: Parts, Disks, Volumes, Policies Parts are the unit of storage. A MergeTree table is a collection of immutable parts. Each insert creates a new part, and background merges combine small parts into bigger ones. ClickHouse tracks where each part lives individually, not per table. That single design decision is what makes tiering possible. Nothing forces a table's parts to live on the same disk. Disks are storage backends. default is your local EBS volume. A disk of type: s3 stores column data as objects in a bucket. A disk of type: cache wraps another disk with a local read-through cache. s3 true /var/lib/clickhouse/disks/s3_raw/ cache s3_raw 2Gi Volumes and policies group disks into ordered tiers: default 0.2 s3_cached A table opts in with one setting, and a TTL rule ages data down automatically: CREATE TABLE events_tiered (...) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (tenant_id, event_type, event_date) SETTINGS storage_policy = 'tiered'; ALTER TABLE events_tiered MODIFY TTL event_date + INTERVAL 12 MONTH TO VOLUME 'cold'; Parts older than 12 months migrate to S3 on their own. move_factor is a second trigger: once the hot volume passes about 80% full, the oldest parts get pushed down regardless of age, so local disk can't fill up. One rule matters more than any config: a part never spans disks. The tier boundary sits at partition granularity, so you have to partition on the column you age by. Partition by month and TTL on the date, and whole months move cleanly. Clients can't tell the difference. The SQL, drivers, and dashboards all stay the same. What Actually Lives on Local Disk When a part moves to S3, the local disk keeps a small metadata stub. In the lab, a part's local data.bin was 52 bytes: 3 1 4113 4113 pat/tmowignbdvqjwafqsosesyyxhfdsb 0 That's a pointer: "this file is 4,113 bytes and lives at this object key." The object in S3 was exactly 4,113 bytes. In the 1M-row run, the entire local footprint for the S3-backed table was 1.3 MB of metadata against 58 MB of data, about 2%. Keep this in mind for later, because it's the most dangerous 2% in the whole setup. The Lab MinIO stands in for S3, so the lab runs offline and costs nothing. Pointing it at real S3 is a one-line .env change. Everything is reproducible with make : make up # minio + clickhouse make load # 1M rows over 24 months, force merges make verify # prove data is on S3 and survives restart make bench # local vs s3-cold vs s3-warm make costs # storage + request extrapolation Proof, not trust. I didn't take the config's word for it. system.parts showed 100% of the S3 table's bytes on the s3_raw disk and zero on local disk. The object store independently agreed (289 objects, within 0.04% of the reported size). A full-column hash confirmed the local and S3 tables held byte-identical data, not just matching row counts. One query, two tiers. With 12 months on S3 and 12 on local disk, a single SELECT spanning a 13-month range across the boundary returned one result set in 168 ms, with 21 S3 GETs. That confirms it read S3 for the older months and local disk for the newer ones in the same query pipeline. Benchmark This is 1M rows in 24 parts, median of 3 runs, with all caches dropped before every cold run. | Query | Local | S3 cold | S3 warm | GETs | |---|---|---|---|---| | Q1 point lookup, 1 tenant, 1 month | 2 ms | 27 ms (13.5×) | 3 ms | 2 | | Q2 aggregate by category, 1 month | 5 ms | 11 ms (2.2×) | 3 ms | 2 | | Q3 aggregate by month, 24 months | 16 ms | 136 ms (8.5×) | 6 ms | 48 | | Q4 full scan | 5 ms | 83 ms (16.6×) | 5 ms | 48 | | Q5 top 20 IDs, 6 months | 27 ms | 62 ms (2.3×) | 22 ms | 12 | Two things stand out. Partition pruning is the lever. Queries that pruned to one partition paid a small cold penalty (2.2× on Q2). Queries that touched every partition paid the most. Cold-read cost follows how much of the table you touch, so the less a query touches, the less S3 hurts. A warm cache erases the penalty. Once the cache held the working set, S3-backed queries ran at parity with local disk. The cache disk is doing the real work here. Size it to your working set, not to your total data. The Cost That Replaces Storage: Requests This is the part most people get wrong. The worry is usually egress, but S3-to-EC2 traffic in the same region is free. The $0.07-0.09/GB figure people quote is for transfer out to the internet. What you pay instead is GET requests, at $0.0004 per 1,000. In this lab, every part cost about 2 GETs regardless of row count: Q1 read 8K rows and Q4 read 1M rows, but that was 2 GETs versus 48. There's an important caveat. The lab's parts were small enough (~2.4 MB) that ClickHouse stored them in compact format, with all columns in a single file. Production-sized parts are stored in wide format, with one file per column. There, GETs scale with parts touched × columns read, and large column files take multiple ranged reads. The lever is the same, but at scale bytes and columns matter too, not just parts. Merge policy matters even more: | Parts scanned | GETs | $/month @ 100k queries | |---|---|---| | 24 (merged) | 48 | $1.92 | | 1,000 | 2,000 | $80.00 | | 5,000 (unmerged) | 10,000 | $400.00 | The same bytes cost 200× more in requests if they're left unmerged. On S3-backed storage, merge policy is a cost control. Watch for NAT Gateway. If your EC2 instance reaches S3 through a NAT Gateway instead of an S3 Gateway VPC Endpoint, you pay $0.045/GB in NAT data processing. That charge looks exactly like the egress you thought you were avoiding. Gateway endpoints are free. This is the single most common avoidable cost in S3-heavy setups. The Savings Here's a worked example: 2 TB of event data over 24 months, us-east-1 list prices, with gp3 provisioned at 1.3× for headroom. Before: 2,000 GB × 1.3 × $0.08 = $208/month | Months kept hot | gp3 GB | S3 GB | After/month | Saving | |---|---|---|---|---| | 12 | 1,000 | 1,000 | $127.00 | 39% | | 6 | 500 | 1,500 | $86.50 | 58% | | 3 | 250 | 1,750 | $66.25 | 68% | | 1 | 83 | 1,917 | $52.75 | 75% | Both tiers are linear in GB, so the percentages hold at any scale. At 10 TB, keeping 3 months hot takes the bill from $1,040 to about $331 a month. The Trade-Offs Nobody Puts in the Blog Post Remember, this is not a perfect architecture. Here's where it hurts. Cold full scans. A cold scan of the whole table was 16.6× slower than local in the lab, and that's against MinIO on loopback (~0.1 ms). Real S3 first-byte latency is 20-50 ms, so a cold scan issuing 48 GETs would add roughly a second. Don't put an interactive dashboard on an unpruned scan of cold data. Point lookups on cold data. Q1 was 13.5× slower and pulled 2.1 MB to answer with 237 KB. Key-value access patterns are the wrong fit for this design. Many small parts. Cost and latency scale with part count. A table with poor insert batching will make this design expensive in exactly the way it was supposed to save money. Late-arriving data. Plenty of real-world data doesn't arrive on time. A mobile app queues analytics events while the phone is offline and syncs them days later. IoT sensors in the field buffer readings until they reconnect. An insert for a date already past the TTL goes straight to the cold volume and then merges there, which means download, rewrite, and re-upload to S3. Either keep the hot window longer than the window in which your data still arrives, or set prefer_not_to_merge on the cold volume and run scheduled OPTIMIZE off-hours. That 2% of metadata. Those 52-byte stubs are the only map from parts to S3 objects. Restarts are fine, since they live on a persistent volume. But lose the EBS volume and your S3 bucket becomes a pile of randomly named, unrecoverable blobs. Back up with BACKUP ... TO S3 or clickhouse-backup, and actually test the restore. EBS snapshots alone aren't enough. Honest lab caveats. This was a single node (zero-copy replication disabled), 1M rows because the 100M run exhausted the dev machine's disk, and MinIO latency rather than real S3. The storage mechanics and cost model tran