Why ClickHouse hot, warm and cold tiering needs a storage policy plan
A team that keeps months of event or log data on NVMe disks eventually faces a storage bill that grows faster than the data is queried. Tiering answers that problem directly: in self-managed ClickHouse, a storage policy defines an ordered list of volumes, each backed by one or more disks, and TTL ... TO VOLUME or TO DISK rules move data parts from fast storage such as NVMe to HDD or object storage as they age. The result is that recent data stays on fast disks while older data sits on cheaper capacity, and queries still run against the same table without application changes.
Tiering moves data, it does not delete it. Rows only leave the cluster when a separate TTL delete rule removes them, which is covered in the retention section of this page. Tiering also does not replace replication or backups. A moved part is still one part of the same table, so a bad DELETE, a corrupted disk or a faulty schema change affects the moved data just as it affects local data. Keep BACKUP and RESTORE on its own schedule and test restores on a spare cluster.
Plan the policy before the first table is created. Decide which volumes exist, which disk types back them, how much data each tier must hold, and which age or size thresholds trigger a move. Confirm with your storage team how the cold tier is provisioned and how its latency compares with local disks, and confirm the ClickHouse version you run supports the disk types you intend to use. Teams usually need tiering when retention is measured in months or years, when hot storage is nearing capacity, or when a cheaper tier can absorb historical queries that tolerate slower reads.
Which tier layout fits which workload
Clusters running on servers you own usually pair a small pool of NVMe for recent, frequently queried partitions with larger HDD volumes for older data. Queries against recent data stay fast, while historical scans read from slower disks and tolerate higher latency. Both tiers are local, so every tier follows the same replication and backup model and no network hop is added to reads from the cold volume.
- Latency tolerance: Dashboards and alerting queries should hit the NVMe volume. Long-range reports that scan HDD volumes will run slower, so confirm with application owners which queries touch old partitions.
- Cost drivers: NVMe capacity is the most expensive component per terabyte. Drive count per node, RAID or redundancy choices, and the number of hot days you keep all change the bill.
- One-time versus recurring: Hardware purchase and rack space are one-time. Power, support contracts, drive replacement and capacity growth recur.
- Vendor confirmations: Ask the server or storage vendor for sustained throughput of each drive class, failure rates for your intended drive models, and supported controller configurations for mixed media.
- Sizing checks: Confirm that the NVMe volume holds the expected hot window with headroom for merges, which temporarily need extra free space.
Before committing hardware, ask the vendor to confirm the drive models, controller and warranty terms in writing.
Separated storage places older data in an S3-compatible bucket and keeps only the hot window on local NVMe. Object storage typically offers the lowest cost per terabyte for long retention, but every read from the cold tier pays network and object request latency. A local filesystem cache in front of the bucket reduces repeated reads, and its size and placement become part of the design. Bucket hosting may be a cloud provider, or a self-hosted system such as Ceph in your own data center.
- Latency tolerance: Interactive queries should stay on the NVMe tier. Cold reads are acceptable for audits, quarterly analysis and backfills, but not for alerting.
- Cost drivers: Stored capacity, request counts, egress if the bucket is in another network or region, and the size of the local cache all drive cost. Frequent merges and small parts increase request volume.
- Recurring versus one-time: Storage and requests recur monthly. Migration of existing parts into the bucket is a one-time effort.
- Vendor confirmations: Confirm with the storage provider or Ceph team the durability model, request pricing, lifecycle rules, and whether the endpoint supports the access patterns ClickHouse uses.
- Replication caution: Replicated tables on object storage have their own considerations, so review the sibling topic on zero-copy replication before choosing this layout for replicated clusters.
Ask the storage team to confirm bucket location, versioning and deletion rules in writing before any TTL move is enabled.
Some organizations do not allow object storage for regulated or sovereignty reasons, so all tiers must live on local disks. The design still separates recent and historical data, but both tiers are fixed hardware. Capacity planning becomes the main risk, because adding a tier later usually means adding servers or drives. The hot tier should be sized for the query workload, and the cold tier for the retention period that auditors or business owners require.
- Latency tolerance: Historical queries run at HDD speed for their whole duration, so set expectations with report consumers early.
- Cost drivers: Server count, drive count and the cost of expanding storage in place. Power and cooling costs grow with every added shelf.
- Recurring versus one-time: Hardware and initial configuration are one-time. Drive replacement, support and capacity growth recur.
- Vendor confirmations: Confirm with the hardware vendor the expansion path, mixing of drive types in one chassis, and the expected rebuild time after a drive failure.
- Retention alignment: Check that retention rules in the TTL configuration match the capacity you purchase, so the cold volume does not fill unexpectedly.
Ask the vendor for expansion lead times and support terms before the first rack is ordered.
The right layout depends on three facts: whether object storage is permitted, how long data must be kept, and how much historical query latency the business accepts. A short retention window with strict latency needs often favors local NVMe alone, while long retention with infrequent historical access favors a bucket behind a cache. Mixed environments can start with two local tiers and add object storage later, but migrating parts between disk types is an operational project that should be tested first.
- Decision inputs: Write down the permitted storage locations, retention length, and the queries that touch old data.
- Test before deciding: Load a representative sample of data into each candidate layout and measure query times for the top ten historical queries.
- Cost comparison: Compare total cost over the retention period, including hardware refresh, power and bucket requests, not only purchase price.
- Exit path: Confirm how data moves back if a layout must change, and who approves that move.
- Documentation: Record the chosen layout and its assumptions so that future capacity reviews start from the same facts.
Review the layout with your ClickHouse operators and your storage vendor together before the storage policy is written.
Storage policy and TTL options and when each fits
This table compares the storage choices a team makes when tiering ClickHouse data: local disk volumes, object storage volumes, move versus delete TTL, age-based versus fill-based moves, and a filesystem cache in front of object storage. Read each row as a decision with its trade-off, and use the sibling topics on S3 storage and the filesystem cache for configuration detail.
| Option | What it does | Trade-off | When it fits |
|---|---|---|---|
| Local disk volume (NVMe or HDD) | Keeps parts on disks attached to the ClickHouse hosts in a named volume | Lowest query latency, but capacity is bounded by each host and grows with hardware purchases | Recent data that is queried often and needs predictable latency |
| Object storage disk volume | Keeps parts in S3-compatible or other object storage through a disk type | Cheap, elastic capacity, but query latency depends on object store speed and cache state | Older, rarely queried data, or workloads that tolerate higher latency |
| Move TTL | Relocates expired-by-age parts or rows to another volume in the policy | Data stays queryable on the slower tier, so retention is preserved at lower cost | Data that must remain queryable for a longer period |
| Delete TTL | Removes rows or columns once their age condition is met | Frees space permanently and cannot be recovered from ClickHouse itself | Data whose retention ends and that has no long-term value on any tier |
| Move by age | Moves parts once their data reaches a set age, based on the TTL expression | Predictable placement that matches business time windows; needs a correct time column | Event, log and telemetry data where recency drives access patterns |
| Move by disk fill | Moves parts when a volume reaches a configured fill level | Protects hot disks from filling, but placement follows capacity rather than data age | Capacity-constrained hosts where a safety valve matters more than age rules |
| Filesystem cache in front of object storage | Keeps recently read object data on local disk to reduce repeated remote reads | Speeds repeat queries, but a cold cache returns to remote latency and needs local disk sizing | Object-backed tables with a working set that fits within the cache |
| Keep everything on local disks | No tier movement; retention is handled only by delete TTL or manual steps | Simplest to operate, but storage cost scales with retained history | Short retention windows or small datasets where tiering adds more work than it saves |
How to configure tiers and TTL MOVE rules step by step
This sequence turns a flat ClickHouse disk layout into a tiered design where recent data sits on fast storage and older data moves automatically to cheaper capacity. Each step ends with a check so the team knows placement is correct before production tables depend on it.
Inventory disks and capacity on every node
Your team lists every node in each replica set, the disks and mount points available to ClickHouse, their media type (NVMe, HDD or object storage endpoint), usable capacity, and the expected daily ingest volume per table. Note which nodes will share a storage policy, because replicas should use identical layouts. The step is done when you have a written table of node, device, media, capacity and mount path, reviewed by the platform owner. Confirm the exact sizing method with your DeployOpen or internal architect against your own ingest rates, since no general sizing figure applies to every workload.
Define disks and volumes in server configuration
Add a storage configuration file under the ClickHouse server config directory that declares each disk by its path and type, then groups disks into named volumes in the order they should be used, such as a hot volume on NVMe and a cold volume on HDD or object storage. Keep the file identical across replicas and deploy it through your normal configuration management. The check is that the server starts without errors, and that the system.disks table lists every disk with the expected name, path and free space on each node.
Create a storage policy with ordered volumes
Create a storage policy that references the volumes in tier order, with the hot volume first, so that new parts are written to fast storage by default. Set the move factor so ClickHouse knows when to move parts between volumes based on free space, and review the value against your capacity headroom. The policy is ready when system.storage_policies shows the expected volume order and the policy name on each node. Verify the result on each replica, since a mismatch between nodes is a common source of unexpected placement.
Apply the policy to tables
Attach the storage policy to new tables with the storage_policy setting in the CREATE TABLE statement, so that the table is placed on the tiered layout from its first insert. For existing tables, plan a migration that creates a new table with the policy and copies data in a controlled window, because the policy cannot be changed on an existing table in place. Confirm the setting with SHOW CREATE TABLE or by querying system.tables for the storage_policy column. A new insert should then create parts on the hot disk, which you can confirm in system.parts.
Add TTL MOVE rules to the table
Define a TTL expression on the timestamp column with a TO VOLUME clause that names the colder volume, so rows older than your chosen threshold are moved after the merge process picks them up. Pair the rule with a delete TTL only if your retention policy requires expiry, and keep the MOVE rule and the retention rule in one reviewed definition. Agree the threshold with the data owners and compliance team before writing it. The check is that the rule appears in system.tables under the table's create statement, and that a test table with synthetic old timestamps produces moves after a forced merge in a non-production environment.
Verify placement with parts and disks tables
Query system.parts grouped by table, partition, disk_name and active status to see which disks hold which parts, and compare row counts and bytes per disk against your expectations. Also check system.part_moves_between_shards or the server log for move activity, and watch for parts stuck on the hot disk after the TTL threshold has passed. The step is complete when a sample of old partitions shows the cold disk name and recent partitions show the hot disk name. Record these queries as a runbook so operators can repeat the check after every change.
Roll the change out on a pre-production cluster first
Repeat the full sequence on a pre-production cluster loaded with representative data volumes, query patterns and retention settings, then run realistic queries against both tiers and measure latency and disk usage. Include a failure test in which one replica is restarted during moves to confirm that replication stays healthy. Only after the pre-production results match your targets should the change move to production, one replica set at a time, with the same checks repeated. Ask your team to confirm the test window and rollback procedure in writing before the production change is scheduled.
Monitor moves and capacity after go-live
Set alerts on free space for each volume, on the count of parts remaining on the hot disk past the TTL threshold, and on failed or slow moves in the server logs and metrics. Review the placement queries from the verification step on a regular schedule, and record any change to disks, policies or TTL rules in your change log. The step is done when the alerts are wired into your monitoring system and the first review confirms that cold partitions are landing where the policy says. Your monitoring team should confirm metric names against the ClickHouse version you run.
How ClickHouse decides when parts move between tiers
A TTL ... TO VOLUME rule does not move data on the clock alone. ClickHouse evaluates TTL MOVE rules during background merges and moves, so a part that has crossed its age threshold may stay on the hot volume until the server gets to it. Moves operate on whole parts, not individual rows, which means a partition that mixes recent and old data can be partly relocated, and a large merged part that spans the boundary may wait until its entire contents qualify. Plan for moves to lag behind the calendar, and do not promise stakeholders that a row will sit on NVMe for an exact number of days.
The timing depends on merge activity, background pool capacity, and the merge and move settings configured on your version. Heavy ingestion that creates many small parts, or a busy merge queue, can delay the evaluation further, while a quiet cluster may move data sooner. Confirm the trigger behavior and the relevant settings for your exact ClickHouse release in the ClickHouse docs and the version-specific documentation, then measure it. A pre-production test with representative insert rates and part sizes will tell you more than any general schedule, and it should be repeated after each upgrade.
Before go-live, decide how much lag your hot volume can absorb. Size the NVMe volume for the data that can accumulate between the TTL threshold and the actual move, and watch free space on each disk through system tables and your monitoring so a slow move does not fill the fast tier. Record the observed lag in your test results so the retention and capacity assumptions you give auditors or capacity planners match what the cluster actually does.
Security and operational risks of tiered storage
Tiered storage moves data across volumes and buckets that each have their own access, encryption and capacity profile. The risks below sit at those boundaries, where a policy change or a credential can expose or strand data without anyone noticing.
Object storage credentials and bucket policies
Each cold volume usually depends on a bucket and an access key that ClickHouse holds in its configuration. Anyone who can read that configuration can often read every part stored in the bucket. Check who can read the server configuration files, scope the key to a single bucket and prefix, and confirm that bucket policies deny public access and unexpected principals. Rotate keys on a schedule your team can actually run.
Encryption and key handling per tier
A part moved to a cold disk is only as protected as that disk's encryption setup. Confirm whether each volume uses server-side encryption at the bucket, client-side encryption in ClickHouse, or neither, and who holds the keys. Keys that live on the same host as the data give little separation. Decide how a moved part is decrypted after a key rotation, and test that path before you need it.
Replication and backups do not follow tiers
Replication keeps replicas in sync, and a recovered replica syncs missing data from a healthy replica. It does not protect against human error or corruption, because a bad delete or a bad move is replicated too. Backups must be planned for each tier, including the object storage holding cold parts. The ClickHouse backup documentation also warns that a backup never test-restored will probably not restore properly, so schedule restore tests on a spare cluster.
Capacity alerts on each volume
A full hot volume stops inserts, and a full cold bucket or disk can stall moves and merges. Alert on free space for every volume in every storage policy, not only on the host's root filesystem. Watch how fast the hot volume fills between moves, since a TTL MOVE rule that runs late leaves recent data on the most expensive tier longer than planned. Confirm which storage system metrics your Prometheus setup already exposes and where gaps remain.
Change control for storage policies
A storage policy controls which volumes receive data and when parts move, so a small edit can change where every new part lands. Keep policy definitions in version control, review changes the same way you review schema changes, and test them on a pre-production cluster with representative data. Record which tables use each policy, because altering a policy does not re-place existing parts automatically. Confirm that the change window and rollback steps are written down before the change.
How to monitor moves between tiers
Which system tables show where ClickHouse parts live and how full each disk is?
The system.parts table shows each active part, its table, partition, size and the disk it sits on through the disk_name column, so you can see which parts have already moved. The system.disks table reports each disk's total and free space, and system.storage_policies lists the volumes and disks that make up each policy. Query system.parts filtered on active = 1 to avoid counting merged-away parts. Compare results across replicas, because each replica keeps its own local view of placement.
How do I alert when a ClickHouse volume is filling up?
Alert on free space per disk from system.disks, and set the threshold with enough margin for merges and moves, which need temporary room. Export these values to your monitoring system through the built-in Prometheus endpoint or a collector your team already runs, then page on sustained low free space rather than a single sample. Also alert on move activity stalling, because a full destination volume stops TTL moves silently in many setups. Confirm the exact metric names in your ClickHouse version before writing alert rules.
How do I detect parts stuck on the wrong tier after a TTL MOVE rule?
Compare each part's age and disk in system.parts against your TTL rule. A part older than the MOVE threshold that still sits on the hot disk is a candidate, but first check whether it is merging, since moves run as background work after the TTL time is reached. Look at system.parts columns such as delete_ttl_info_min and move_ttl_info for the rule metadata that applies to each part. Review system.part_log for recent move events and failures before assuming a rule is broken.
What errors appear when a tier disk is unavailable?
When a disk in a volume is unreachable or read-only, inserts and merges that target it fail, and queries that read parts from it return errors until the disk recovers. Typical signals include I/O errors and disk-related messages in the server log, and entries in system.errors and system.part_log for failed operations. For object storage, the symptom is usually request timeouts or access denials against the bucket. Test these failure modes on a staging cluster, because the exact messages depend on the disk type and ClickHouse version.
How do I test a storage policy and TTL move before it reaches production?
Build a staging cluster with the same storage policy, volume order and disk types, then load a representative dataset with timestamps spread across the retention windows you plan to use. Use shortened TTL intervals in the test environment to trigger moves quickly, then confirm placement in system.parts and check that queries return identical results before and after. Simulate a full destination volume and a disconnected disk, and record how the server behaves. Repeat the test after each ClickHouse upgrade, as recommended in the production version guidance.
Who should own tier monitoring, and what does the work cost?
Your platform or database team usually owns tier monitoring, since it depends on disk capacity planning, monitoring stack configuration and access to the servers. If you outsource it, the cost depends on the number of clusters, the monitoring tools you already run and how many alert paths and runbooks you need. DeployOpen plans and builds this work as a fixed-scope engagement with a written scope before work starts, and quotes follow from that scope. Ask any provider which alerts, dashboards and test cases are included and who maintains them after handover.
Should I verify that moved data still restores correctly?
Yes. Moving parts between disks does not replace a backup, and replication does not protect against deletion or corruption. Before relying on a tiered layout, confirm that your backup and restore process covers the tables on every tier, including data on object storage, and run a test restore on a spare cluster. Record which parts moved and when, so you can explain placement during an audit or incident review.
Limits to confirm before you design the tiers
Tiered storage depends on what the target ClickHouse version supports, how it behaves under pressure, and how it interacts with replication and restores. Confirm each limit against the exact version you plan to run before you fix the volume layout and retention rules.
Disk types on the chosen version
Confirm which disk types the target release supports for each volume, including local NVMe, HDD and object storage disks. Support for object storage disks can differ by release and by the disk configuration you choose, so check the exact combination in the vendor documentation and in a test cluster before you commit the storage policy. Record the result against the version number you intend to deploy.
Moves when the target volume is full
Ask how a TTL MOVE rule behaves when the destination volume has no free space. Find out whether the move is retried, skipped or blocked, whether parts stay on the source tier, and what alert fires. A full cold volume can quietly leave hot disks filling up, so confirm the behavior and the monitoring signal before you rely on it for retention.
Replicated tables on object storage
Confirm how ReplicatedMergeTree tables behave when a volume sits on object storage. Check whether replicas share the same objects, whether zero-copy replication is enabled, and what the vendor recommends for replicated data on that tier. Replication guarantees for local disks do not automatically carry over, so ask for the supported design in writing.
Restore behavior for cold data
Confirm whether BACKUP and RESTORE handle parts that live on cold volumes or object storage, how long a restore takes when data must be fetched back, and whether incremental backups remain valid after parts move between tiers. A backup that works on hot data may behave differently for cold parts, so test a restore that includes them.
Upgrade path for storage changes
Ask how changes to storage policies, volume definitions and disk configuration are applied on a running cluster, and whether any change needs a restart or a rolling procedure. Check how the configuration carries across the stable and LTS release lines, and read the changelog for each target version before the change is scheduled.
What tiered ClickHouse storage costs and what it needs from your team
What drives the cost of a tiered ClickHouse storage build?
The main drivers are data volume, retention length, how fast queries must return on older data, the number of nodes and disks, and the object storage you choose. Each tier you add (NVMe, HDD, object storage) adds hardware or service to size, configure, monitor and test. Retention length sets how much data sits in each tier, which sets capacity. Latency needs decide whether cold data can live on object storage or must stay on local disks. Ask for each of these to be written into the scope so the quote follows from them.
What is one-time work and what recurs after tiered storage goes live?
One-time work covers designing the storage policy, defining volumes and TTL MOVE rules, building the test data path, running the first moves and documenting the result. Recurring work covers watching disk usage per tier, reviewing move activity and merge backlogs, checking that object storage credentials and caches still work, and re-running moves after schema or retention changes. Your team pays for the ongoing capacity and cloud or hardware costs, and a support retainer covers the recurring operational work if you choose one.
Can our own team build tiered ClickHouse storage without a partner?
Yes, a team with Linux, ClickHouse and storage experience can build it, and the ClickHouse documentation describes the storage policy and TTL mechanics. The effort is in the decisions: which data moves, when, to which disk, and how you prove it. Teams often find the hardest parts are sizing NVMe for hot data, choosing object storage behavior for queries, and building monitoring for moves that fail silently. Check whether your engineers have time for a pre-production environment with representative data and queries, since that step is where most surprises appear.
How long does a fixed-scope tiering build take?
Duration depends on scope: the number of tiers, the number of tables and clusters, whether object storage is new to your environment, how many replicas exist, and how much testing you require. DeployOpen does not publish durations. Most engagements are fixed-scope, so the written scope fixes deliverables and the timeline is agreed before work starts. Ask for the timeline as milestones (design, pre-production build, cutover, monitoring handover) so you can see which steps depend on your infrastructure team or on access approvals.
What should we ask for in a written quote for tiered storage?
Ask the provider to state the following in writing:
Does tiering ClickHouse data replace backups?
No. Moving data between disks or to object storage changes where data lives, not whether it is protected. A TTL MOVE rule that deletes or corrupts data on one tier is still a data loss event, and replication copies mistakes to every replica. The ClickHouse backup documentation notes that replication alone does not protect against human error or corruption. Keep BACKUP and RESTORE jobs running to storage separate from the tiers you are managing, and test restores on a spare cluster, because a backup never test-restored will probably not restore properly.
Who is responsible for data in object storage, and what should we confirm first?
Your organization owns the data and the bucket or storage account, and the team operating ClickHouse owns the disk configuration and credentials. Confirm who controls bucket policies, encryption keys, access logs and lifecycle rules before tiering starts, because a lifecycle rule outside ClickHouse can remove parts it still expects. Confirm also where the object storage runs and whether it meets your data residency rules. DeployOpen plans and implements inside infrastructure you control, so the storage platform's owner should sign off on the design.
What evidence should we expect that tiering works and that data moves on schedule?
Expect a written test plan and the results from it. Useful evidence includes the storage policy definition, the TTL MOVE rules, a record of parts per disk before and after moves, monitoring dashboards for disk usage and move activity, and restore test results. For compliance readiness, the evidence should show that retention and movement are configured, run and monitored, but an independent auditor decides whether controls meet a standard. DeployOpen prepares systems, controls and evidence and does not issue audit reports or certifications.
Where to read the official ClickHouse documentation on storage
Start with the official ClickHouse documentation and repository before you commit to a storage policy, because the exact settings for volumes, disks and TTL moves depend on the release you run. The ClickHouse docs explain how to choose a production version, and they recommend testing on a pre-production environment with representative data and queries. Read the storage pages for your target version, not a copy from an older cluster, and treat the release notes as part of the design input rather than an afterthought.
Before you build, confirm three things against your own version: the disk and storage policy syntax your server accepts, how TTL MOVE rules are evaluated during background merges, and which object storage disk types are supported with the replication setup you plan. The ClickHouse repository and its releases page show the changes that landed in each build, which is where behavior changes around merges and moves usually appear first. The security policy lists which versions still receive security updates, so a tiering design on an unsupported line carries risk that no storage setting can fix.
Ask your platform team or DeployOpen to record the exact server version in the design document, along with the storage policy definition and a test plan that moves real parts between volumes. Re-check the documentation at each upgrade, because a setting that worked on one release may be deprecated or changed on the next. Any cost or capacity figure you derive from the docs should be validated against your own hardware and object storage pricing, since the documentation describes behavior, not your bill.
How DeployOpen scopes and delivers ClickHouse storage tiering
DeployOpen plans, builds, tests and documents tiered storage for self-managed ClickHouse inside your own infrastructure. Each engagement starts from a written scope that sets out which tables, volumes and retention rules are in play, and the handover leaves your team able to operate the tiers without us.
How we scope
- Inventory the tables, partitions, query patterns and retention periods that a hot, warm and cold design must serve.
- Define the storage policy, volumes and TTL MOVE rules in a written scope before any build work begins.
- Agree which storage media, object storage endpoints and cache settings are in scope, and which your team provides.
How we build
- Configure NVMe, HDD or object storage volumes and the storage policy in a staging cluster with representative data.
- Test TTL MOVE rules against part sizes and merge behavior so moves run on schedule without blocking inserts or queries.
- Verify replica consistency across tiers and check that data reaches each volume as the scoped rules intend.
How we hand over
- Deliver the storage policy files, TTL definitions and runbooks as versioned artifacts your team can review.
- Document the monitoring queries and alert thresholds for disk usage, move backlog and failed moves on each volume.
- Walk your operators through a tier move, a rollback and a restore test so the procedures are rehearsed before go-live.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer alongside the fixed-scope build.
- Review tier behavior after ClickHouse upgrades, since the changelog should be read before each version change.
- Revisit retention and volume sizing with your team when data growth or query patterns change.
