Running ClickHouse MergeTree tables on S3 storage: what it does and when to use it
If your team holds large event or telemetry volumes on local disks and the cost of keeping years of data on servers is climbing, S3-backed MergeTree tables are one option to evaluate. S3-backed MergeTree tables move data parts to object storage while compute stays on servers you run. The ClickHouse server itself is open source under the Apache License 2.0 (Apache-2.0), so using object storage is a design choice you make and operate inside your own infrastructure, not a feature you buy from a managed service. Your team still owns the query nodes, the local cache, the bucket policies and the upgrade schedule.
This design fits best where data is written in bulk, read mostly by time range, and where some extra latency on older queries is acceptable. Typical candidates are log and telemetry archives that analysts query occasionally, compliance datasets kept for long retention windows, and historical partitions that rarely change. It fits poorly for latency-sensitive dashboards over recent data, for workloads with heavy small inserts and frequent merges, and for teams without anyone who can read ClickHouse system tables and object storage metrics. Before committing, measure your own query mix on a pre-production copy with representative data, because cache behavior depends on how often the same parts are read.
Confirm three things early: which S3-compatible service your organization operates or approves, whether your network and identity model allow the ClickHouse nodes to reach the bucket with scoped credentials, and who owns the decision to accept slower reads on cold data. Those answers determine whether a storage policy on object storage is a sound fit or whether a tiered design on local volumes is the safer first step. Check the ClickHouse documentation for the disk configuration your server version expects, since object storage behavior is version-sensitive.
How a storage policy moves MergeTree parts onto S3
A storage policy decides where ClickHouse writes MergeTree data parts and where they may move later. The policy groups one or more *volumes*, each volume holds one or more disks, and each disk points at a storage backend such as a local path or an S3 or S3-compatible bucket. When a part is inserted, ClickHouse places it on the first volume in the policy that has room, so the order of volumes in the configuration is a design decision, not a formatting detail. For object storage, the usual pattern is a small local volume for fresh parts and an S3 volume behind it, with the choice of which volume receives inserts checked against your own ingestion and query patterns. Confirm the exact configuration keys and disk types for your ClickHouse version in the storage configuration documentation before you write them into production files.
Parts do not stay where they are first written. Background merges combine small parts into larger ones, and TTL MOVE rules can push parts to another volume once they reach an age or condition you define. The two processes interact: a merge produces a new part, which is then subject to the move rules again, so a poorly chosen move interval can cause many small transfers to the bucket. Because object storage charges per request as well as per byte, merge and move frequency is a cost driver as well as a performance one. Ask your team to measure part counts and move activity over a representative week before settling the rules, and use the sibling topics on hot, warm and cold storage tiers in ClickHouse and TTL retention for the full rule design rather than setting them ad hoc.
The local cache sits between queries and the bucket because reading a part from S3 costs far more time than reading it from local disk. When a query touches data that lives on the object store, ClickHouse can serve those byte ranges from a filesystem cache on local disk, so repeated reads of hot ranges avoid the network round trip. A cold cache means the first queries after a restart, a new node or a large merge pay full object storage latency, and that is the first thing to size and test. The cache has its own maximum size, its own placement on fast local storage, and its own system tables for inspection. Plan a cache test that starts cold, runs your dashboard and investigation queries, and records latency, because the cache settings are only validated by the queries your users actually run.
Which S3 disk configuration fits which workload
This table compares four ways to place MergeTree data on S3 or S3-compatible storage, from a plain object disk to a mixed local and object policy. Read each row as a question: what the workload needs, what the configuration gives up, and when it is the wrong choice. The main takeaway is that object-only storage suits data that is written once and read occasionally, while latency-sensitive dashboards usually need local caching or local hot storage.
| Configuration | Best-fit workload | Main trade-off | Check before choosing |
|---|---|---|---|
| Plain S3 disk, no cache | Cold archives and rarely queried history where slow reads are acceptable | Every read of uncached parts goes to the bucket, so query latency varies widely | Confirm expected query frequency and the latency your users will tolerate |
| S3 disk wrapped in the filesystem cache | Historical data queried repeatedly over a stable set of recent parts | Cache sizing and placement decide performance, and a cold cache slows the first queries | Size the cache against the working set and monitor cache hit behavior |
| Mixed policy: local hot volume plus S3 cold volume | Recent, frequently queried data on local disks with older data moved to object storage | Needs TTL MOVE rules and a clear view of when parts change tiers | Confirm TTL rules, volume order and how moves are observed in system tables |
| Fully object-backed policy with replicated tables | Shared storage designs where compute nodes are added or replaced often | Replication and merge behavior on object storage need careful validation, and zero-copy replication is not recommended for production | Confirm the supported replication approach for your ClickHouse version with the vendor documentation |
| Interactive dashboards on recent data | Frequent short queries that users expect to return quickly | Object storage reads add variable delay, so most hot data should stay on local disks | Test representative queries against each candidate policy before committing |
Which workloads tolerate object storage latency
Object storage adds latency to every part that is read from the bucket instead of local disk, so the question is how often each query touches cold parts and how much a slow read costs the user. The cards below cover common workload types and what to check for each before moving tables to S3.
Cold historical queries
Ad hoc investigations and compliance lookups that scan months of old data run infrequently and often tolerate slower reads. Parts fetched from the bucket on a cache miss add latency per part, so the total time depends on how many parts a query touches. Confirm with the team that owns these queries that a longer response is acceptable and that concurrent cold scans will not starve other users.
Dashboards over recent data
Dashboards that refresh every few seconds and read the last few hours are the workload most sensitive to object storage latency. If recent parts are not held in the local cache or on a fast local volume, each refresh can wait on network round trips. Keep the hot partitions on local disk and measure refresh time against your panel timeouts before deciding to place recent data on S3.
Frequent small inserts
Streaming pipelines that write many small batches create many small parts that must be merged over time. Each insert and merge may read or write objects in the bucket, which multiplies request latency and request charges. Measure insert latency and merge backlog under your real ingestion rate, and batch writes where the source allows it.
Large batch scans and aggregations
Long analytical scans that read many columns across large date ranges are throughput-bound more than latency-bound. Object storage can sustain high parallel reads, so these queries often tolerate S3 well when the cache is warm or the scan is planned. Check how much of each scan is served from the cache and whether concurrency limits in your storage policy hold up under peak load.
Point lookups and high-concurrency queries
Queries that return a few rows for many concurrent users depend on fast access to index marks and small column reads. Each uncached read adds latency, and the effect compounds under concurrency. This workload usually belongs on local disk, so test it against a representative concurrency level before accepting object storage for these tables.
Replicated tables on shared objects
Replicated tables on object storage need careful design because replicas and merges depend on the same bucket. Zero-copy replication on S3 is off by default and not recommended for production, so each replica typically keeps its own copy. Confirm the replication design and the cost of duplicate storage with your platform team before going further.
Setting up an S3-backed MergeTree table step by step
This sequence takes a self-managed ClickHouse cluster from a supported version to a tested S3-backed MergeTree table, with a check at each stage so you know the table is storing, caching and restoring data as designed. Run it first in a pre-production environment with representative data.
Confirm the ClickHouse version is supported
Before touching storage, check the ClickHouse security policy for the versions that currently receive security updates, and choose a stable or LTS release from the GitHub releases page. The production FAQ recommends stable releases for most production use and reading the changelog before upgrading, because minor backward-incompatible changes can occur. Record the exact version string on every node. Done means each node reports the same version, and that version appears in the supported list. If the version is not supported, stop here and plan an upgrade first.
Create the bucket and least-privilege credentials
Your storage or platform team creates a dedicated bucket for ClickHouse data, with access limited to that bucket and the operations ClickHouse needs. Keep the bucket private, with no public access, and confirm your object storage supports the S3 API features your ClickHouse version expects. Store the access key in your secrets manager, not in plain configuration files. Check the result by writing and reading a test object with the ClickHouse service identity and confirming that a request outside the bucket is denied. Confirm bucket lifecycle and versioning rules with your storage vendor before any data arrives, since expiry rules applied outside ClickHouse can delete data it still references.
Define the disk and storage policy configuration
Add an S3 disk definition and a storage policy in the server configuration, usually under a file in the config directory, so that tables can reference a named policy. The disk block sets the endpoint, bucket path and credentials, and the policy names the volumes that tables will use. Keep a local disk in the policy for metadata and any data that must stay on local storage. Roll the configuration out to each node with your normal change process. Verify by querying the system tables for disks and storage policies and confirming the S3 disk and policy appear on every node with the expected names.
Configure the filesystem cache
Place a local filesystem cache in front of the S3 disk so that frequently read parts are served from fast local storage. Decide the cache path, maximum size and the cache settings for your query pattern with your platform team, and make sure the path has enough free space and is not shared with other data. Confirm the cache directory exists and is writable by the ClickHouse service user on every node. The check is to run a query over a table, then inspect the cache system tables to confirm entries were created and the size stays under the configured limit. Expect the first queries after a restart to be slower because the cache starts cold.
Create tables on the storage policy
Create a ReplicatedMergeTree table, or a MergeTree table on a single node for a test, with the SETTINGS clause that names your S3-backed storage policy. Choose the ordering key and partitioning for your query and retention needs before creating the table, because changing them later means rebuilding data. If you run replicas, confirm that the replication coordination layer is healthy first, since the replication guide recommends at least three Keeper nodes on dedicated hosts in production. Verify the table by checking the system tables for the table's storage policy and confirming new parts are written to the S3 disk after an insert.
Load representative data and measure
Load a representative sample of your event, log or telemetry data, including the largest partitions and the queries your analysts and dashboards run most often. Run those queries cold after a restart and then warm, and record the latency for each. Compare results with the same workload on local disk so you can judge which queries tolerate object storage latency and which need a different tier. Also check that merges complete and that part counts stay within a range your team has agreed. Done means you have a written record of cold and warm latency per query class, and the team agrees whether the design fits each workload.
Run a restore test on a spare cluster
Take a backup of the S3-backed table using BACKUP to a location separate from the live bucket, then restore it into a spare cluster or a separate database. The backup documentation notes that replication alone does not protect against human error or corruption, and warns that a backup never test-restored will probably not restore properly when needed. Compare row counts and checksums or sample queries between the source and the restored table. Record the commands, the restore time and any errors. Repeat this test on a schedule your operations team sets, and treat a failed restore as a blocking issue for production use.
Bucket rules and settings to avoid
Most problems with ClickHouse on object storage come from the bucket and the credentials around it, not from ClickHouse itself. The cards below cover the mistakes we see most often, each with a check you can run before go-live. Read them alongside the backup and replication sections of this pillar, because object storage holding data parts is not a backup.
Sharing the bucket with other applications
When ClickHouse parts sit in a bucket that other systems also write to or clean up, a lifecycle rule or bulk delete from another team can remove table data. ClickHouse expects to own the prefix it writes under. Give the ClickHouse disk its own bucket or a dedicated prefix, and confirm in writing which other processes have write or delete access to it. Keep the ClickHouse path free of general-purpose sync or cleanup jobs.
Lifecycle rules that expire data
Object lifecycle rules that transition or expire objects by age do not understand ClickHouse part merges, moves and TTL deletes. An expiration rule can delete objects ClickHouse still references, which shows up later as missing parts on read. Review every lifecycle policy on the bucket, remove expiration rules, and let ClickHouse TTL settings control retention. Test the policy on a non-production bucket first.
Versioning and delete protection left unreviewed
Bucket versioning changes what a delete means, and retained noncurrent versions add storage cost that is easy to overlook as parts are merged and replaced. Decide deliberately whether versioning is on, how long noncurrent versions are kept, and who can disable object lock or purge versions. Ask your storage team to show the current settings and the cost model before the first load of production data.
Broad credentials for the ClickHouse disk
A single access key with full administrative rights on the account is a common shortcut. If that key leaks from a config file or log, an attacker can read, overwrite or delete every table on the disk. Create a dedicated principal limited to the ClickHouse bucket or prefix, with the object actions it needs and nothing more, and rotate its keys on a schedule. Keep the keys out of plain-text configuration where your secrets process allows.
Relying on replication as a backup
Replicas of a ReplicatedMergeTree table, and a shared object store, both faithfully copy a bad DROP, a mistaken DELETE or a corrupted part. Replication keeps the cluster available. It does not give you a point in time to return to. Schedule BACKUP and RESTORE to a separate location, keep a copy outside the ClickHouse bucket, and run restore tests on a spare cluster, as described in the backup and replication sections of this pillar.
Assuming object storage latency is the same as local disk
Object storage is reached over the network, so cold reads, merges and large scans can be slower than on local NVMe, and throughput depends on the cache and the provider's limits. Workloads with strict query latency targets need a measured trial, not an assumption. Confirm the bucket region, network path and request limits with your provider, and test representative queries with the local cache both warm and cold.
Questions teams ask before running ClickHouse on S3
Does replication protect ClickHouse data stored on S3?
No. Replication keeps replicas in sync, so a deleted table, a bad migration or a corrupted part can be copied to every replica within moments. ClickHouse's replication documentation says replication alone does not protect against human error or corruption. Object storage changes where the bytes live, not that point. You still need BACKUP and RESTORE to a separate location, with retention set by your recovery requirements. A recovered replica does resync missing data from a healthy replica automatically, which covers node loss but not logical mistakes. Confirm with your storage team whether the bucket itself has versioning or object lock, and treat that as an extra control, not a backup.
Is ClickHouse on S3 safe for production use?
It can be, for the right workloads, but only after a tested design. ClickHouse's production guidance recommends stable releases for most production use and a pre-production environment with representative data and queries. Choose a supported version: the security policy currently lists 26.9, 26.8, 26.7 and 26.3 as supported, and the project's LTS lines are 26.8 and 26.3. Object storage adds latency on cache misses, so test cold-cache query times against your real SLOs before committing. Run a restore test on a spare cluster as well, because the backup documentation warns that an untested backup will probably not restore when needed.
What drives the cost of ClickHouse on S3?
Four components carry most of the cost, and each is paid to a different party. Object storage is billed by your cloud or storage provider per stored gigabyte and per request. Compute is the ClickHouse nodes and Keeper hosts you run, whether in your data center or a cloud account. The local cache uses disk on those nodes, so it is sized against your hot working set. Testing, design and runbooks are the labor. The storage and compute charges recur monthly; design, migration and the first restore test are one-time. Cost rises with request volume, cache misses, cross-region traffic and replica count. Ask any partner for a quote that separates these lines and states the assumptions behind the data volume and query mix.
What must our team provide before an engagement starts?
Your team provides access, data and decisions. Typically that includes a non-production environment with representative data and sample queries, the bucket and network design approved by your cloud or infrastructure team, credentials and key management policy, retention rules from data owners, and the recovery objectives your business needs. You also name a technical owner who can approve changes and attend restore tests. Ask the partner to list these inputs in the written scope, with the owner of each, so delays are visible early.
Can our in-house team build and run this without a partner?
Yes, if you have engineers who already operate ClickHouse, Keeper and object storage. The hard parts are not the storage policy syntax. They are cache sizing, bucket rules, backup scheduling, restore testing, upgrades and monitoring of moves and merges. Assess your team against those areas first. An external partner is useful when the team lacks that experience, when a deadline is fixed, or when an independent review is needed before go-live. Either way, the same written checks apply, and the runbook should be owned by your staff after handover.
How long does a scoped ClickHouse on S3 engagement take?
Duration depends on scope, not on a fixed package. No timeline is published here, and any figure a vendor quotes should be checked against scope. The main variables are the number of tables and tiers, whether you are migrating existing data, how many environments you need, the number of restore tests, and how quickly your teams approve bucket and network changes. Ask for milestones with named deliverables: design, pre-production build, query and cache testing, backup and restore proof, and handover. Most DeployOpen engagements are fixed-scope, so the agreed timeline is written before work starts.
Which bucket and storage settings should we avoid?
Engineers should confirm these against the current ClickHouse documentation for their version before deciding. In general, avoid sharing one bucket between unrelated systems or environments, avoid lifecycle rules that expire or transition objects ClickHouse still expects to read, and avoid object-level policies that block the credentials ClickHouse uses to list, read, write and delete. Keep the backup destination in a separate bucket with its own credentials and retention. Review storage class transitions with the vendor, because cold-tier access patterns can add latency and cost. Document every rule in the runbook so later changes are reviewed.
How DeployOpen scopes and delivers S3-backed ClickHouse
DeployOpen plans, builds, tests and hands over self-managed ClickHouse with MergeTree tables on S3 storage inside your own cloud accounts or data centers. Each engagement starts from a written scope that names your workloads, latency expectations, bucket setup and the restore tests you need before go-live.
How we scope
- Inventory which tables, query patterns and retention periods would move to object storage and which must stay on local disks
- Record latency expectations for dashboards, ad hoc queries and batch jobs so each workload is matched to a storage tier
- Agree the bucket, region, credentials and network path to S3 or an S3-compatible service in your infrastructure
- Confirm the written scope, deliverables and timeline before any work starts, since most engagements are fixed-scope
How we build
- Design storage policies with object storage disks, local volumes and the filesystem cache sized for your query mix
- Configure bucket lifecycle and access rules so ClickHouse is the only writer and deletions follow ClickHouse retention
- Test cold-cache and warm-cache query times against representative data and queries in a pre-production environment
- Validate replicated table behavior on object storage before production, using the ClickHouse replication documentation as the reference
How we hand over
- Document the storage policy, cache settings, bucket configuration and the reasons behind each choice for your operators
- Provide runbooks for checking disk usage, cache hit behavior, moves between volumes and recovery after a failed node
- Run a BACKUP and RESTORE test to a spare cluster and record the results, since untested backups often fail when needed
- Walk your team through the handover so they can operate and change the setup without DeployOpen
How we support
- Plan upgrades against the ClickHouse release lines and changelog, testing storage behavior in pre-production first
- Review bucket policies, credentials and cache capacity as data volumes and query patterns change over time
- Offer ongoing support, upgrades and continuous testing as a retainer, with scope agreed in writing
- Re-run restore tests after significant configuration or version changes to confirm recovery still works
