Build1 publisher3 min readPublished
One TTL clause pushes year-old ClickHouse parts onto storage costing 71% less per gigabyte
ClickHouse records a disk for every part, so a TTL rule can age old months onto S3 with the queries untouched. The lab behind the write-up substitutes MinIO for S3, so the latency half of the trade is still unmeasured.
The Engineer · Build desk
What happened
- gp3 EBS runs about $0.08 per GB-month, so 3 TB of ClickHouse history costs roughly $245 a month, and because EBS bills provisioned size and volumes grow but never shrink, that line does not fall.
- ClickHouse tracks the disk of every MergeTree part individually rather than per table, so one table can hold recent parts on a local volume and older ones as objects in an S3 bucket.
- A table joins the tiered policy with SETTINGS storage_policy = 'tiered', and an ALTER TABLE MODIFY TTL rule then sends partitions older than 12 months down to the cold volume on its own.
- The hot volume carries move_factor 0.2, a second trigger that pushes the oldest parts to S3 once local disk passes roughly 80% full, regardless of how old those parts are.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint Because a part never spans disks, the tier boundary lands at partition granularity, and an existing table partitioned on anything other than the column it ages by has to be rebuilt before it can tier at all.
- cost Whoever picks the 12-month TTL is picking what share of history the cheaper per-gigabyte rate applies to, and everything inside that window keeps paying EBS prices on provisioned capacity.
- exposure The local metadata directory holds the map from part to object key, so a snapshot policy that versions the bucket and skips the ClickHouse data directory keeps bytes nobody can address.
- capability The saving is reachable through server config and DDL alone, so a team can take it without asking application owners to change drivers, SQL or dashboards.
Where the TTL line sits decides how much of the per-gigabyte gap reaches the invoice. The gap itself checks out: $0.08 to $0.023 per GB-month is 71.25% [1]. Three terabytes on gp3 is $245.76 a month [2]. The same 3,072 GB on S3 would be $70.66 [3]. A 12-month TTL moves only what is older than 12 months, so the real case is a blend. Keep a terabyte hot and push two down, and storage is $81.92 plus $47.10, or $129.02 a month, 47% below the all-EBS figure [4]. The write-up notes that in most analytics workloads dashboards hit the last 30 to 90 days while the long tail of history sits on the most expensive storage available [23].
The cold volume in the lab does not point at the S3 disk. It points at s3_cached, a cache disk wrapping s3_raw with max_size 2Gi [9] [10]. A disk of type cache is a local read-through layer in front of another disk [8]. Two gibibytes ahead of two terabytes of aged parts holds about 0.1% of them [5], which is fine for a lab on one machine. Sizing max_size for real data means measuring which old partitions get queried, and how often.
Age is one trigger and fullness is the other. At move_factor 0.2 the oldest parts move once the hot volume passes roughly 80% full, whatever their age [13]. An ingest spike can therefore put last week's partitions on S3, and a dashboard reading them pays a cold read.
"We built a lab to find out what it actually costs in latency and requests," the write-up says [22]. That lab runs MinIO in place of S3 so it works offline and costs nothing, with a one-line .env change to point it at a real bucket [19]. Request charges in it are zero, and first-byte time is whatever a container on the same host returns. For a latency number from that harness to say anything about S3, the endpoint would have to sit in the same region as the node, the cache would have to be in the same state, and the query would have to touch the same number of parts [5]. The source text ends with the make targets, before the measurements [24].
The stub left behind after a move is 52 bytes: a length and an object key, 4,113 bytes at pat/tmowignbdvqjwafqsosesyyxhfdsb, and the object in the bucket measured exactly 4,113 bytes [16]. Stubs sit under the metadata_path declared on the disk, /var/lib/clickhouse/disks/s3_raw/ in this config [20]. In the one-million-row run they totalled 1.3 MB against 58 MB of data [17]. The write-up calls that "the most dangerous 2% in the whole setup" [18].
Before trusting this on real data I would rehearse the restore: replace the EBS volume, reattach the bucket, then run a query against a cold partition.
What to watch
- Results from the same lab after the one-line .env switch to real S3, with the bucket in the same region as the ClickHouse node.
- A run with max_size set to a production-scale cold working set instead of 2Gi, showing how much of the per-request bill the cache removes.
- Whether the promised request-count figures report GETs per query, the number that sets the S3 request line on the invoice.