Why the ClickHouse filesystem cache decides object storage query speed
A team that has moved MergeTree data onto S3, GCS or Azure Blob Storage often sees query times that vary widely from one run to the next. The cause is usually the local filesystem cache that sits in front of the remote disk. It decides how often a query has to wait on object storage round trips and how often it reads data already on local disk. When the cache holds the parts a query needs, latency looks close to local storage. When it does not, the query fetches those parts remotely, and the same query can look very different on a cold node.
The cache holds copies of the data blocks that ClickHouse has read from the remote disk, so repeated reads of hot ranges can be served locally. Teams need to think about it once queries touch more data than the node can keep, or when a mix of dashboards and ad hoc scans compete for the same local space. The settings that control the cache, including its path and maximum size, are defined in the ClickHouse documentation for the storage configuration, and the exact names and defaults can change between releases. Confirm them against the version you run in a pre-production environment, using the version guidance in the ClickHouse production FAQ before you copy any example configuration.
The practical first step is to measure rather than assume. Record query durations for representative dashboards and investigations after a restart, then again once the cache has warmed, and note which queries stay slow. A gap that persists points to sizing, placement or access patterns, not to the object store itself. Ask any partner or internal owner to show the cache hit behavior for your own workload, because the same configuration can behave very differently for a log search workload and a long-range aggregation.
Cache settings and when each one fits
This table lists the main settings that shape a ClickHouse filesystem cache in front of object storage, with the workload each one suits. Read each row as a question to answer for your own tables and query pattern. Key names and behavior change between releases, so confirm every name and default in the documentation for the version you run before you write it into configuration.
| Setting | Situation it suits | What to confirm in the version docs |
|---|---|---|
type set to cache | Wraps an existing S3, GCS or Azure disk so repeat reads are served from local storage | Exact disk type name and how the wrapped disk is referenced in the same block |
path | A dedicated directory on fast local storage, kept apart from data and log volumes | Ownership and permissions required for the ClickHouse user, and whether the directory is created at startup |
max_size | Caps local cache use so it cannot exhaust the host disk during heavy scans | Accepted units, how eviction runs when the limit is reached, and headroom the docs recommend |
max_elements | Limits the number of cached file segments when many small files churn through the cache | Whether the key exists in your release and how it interacts with max_size |
max_file_segment_size | Sets the size of cached chunks; smaller chunks can suit random reads on wide tables | The default value and whether changing it requires a restart or a new cache directory |
Write-through caching (cache_on_write_operations or the version equivalent) | Keeps recent inserts and merged parts warm for dashboards that read fresh data | Whether it covers merges as well as inserts, and the local space and write cost it adds |
Per-query cache switch (enable_filesystem_cache) | Turns caching off for one-off full scans so they do not evict hot data | Whether the setting is session or query scoped, and its default in your release |
How to size the cache and where to put it
Size the cache from the hot working set, not from the size of the bucket. Start by listing which tables and partitions your dashboards and scheduled queries read most often, then estimate how many bytes those partitions occupy after compression. Recent partitions usually dominate the working set for event, log and telemetry data, but the true figure depends on your query patterns, so measure it. Use the query log and the cache system tables after a representative period to see which files are read repeatedly and how much cache space they consume. A cache sized to the bucket wastes host disk, while a cache sized to the working set of last week's reports may thrash when a new investigation touches older data.
Place the cache path on fast local NVMe, and keep it on a volume separate from the ClickHouse data and log volumes. If the cache shares a filesystem with parts being merged or with logs, cache churn competes with merges and log writes, and a full disk can stall both. Treat the maximum cache size as a ceiling that must leave headroom for merges, temporary files for large queries and the operating system. Every gigabyte given to the cache is a gigabyte taken from that headroom, so a larger cache trades lower query latency for higher disk pressure on the host. Confirm the exact cache settings, path semantics and eviction behavior for your ClickHouse version in the documentation before you finalize the values.
Validate the sizing in a pre-production environment with representative data and queries, as the ClickHouse production guidance recommends. Replay a realistic mix of dashboard, ad hoc and batch queries against a cold cache and then a warm one, and record latency, cache hit behavior and disk usage at peak. Repeat the test after a restart, because a cold cache after restart is the case that users notice first. Document the final size, the reasoning behind it and the disk alerts that will fire before the cache begins to crowd out other workloads.
Setting up the cache step by step
This sequence takes a self-managed ClickHouse node from an object storage disk with no local cache to a configured filesystem cache in front of it, and ends with a test query that proves the cache is being read and written.
Confirm the version and object storage disk type
Start by checking which ClickHouse version is running and which object storage disk type your tables will use, such as an S3 disk, a GCS disk configured through the S3 disk type, or an Azure Blob Storage disk. Confirm the version against the ClickHouse security policy and the release notes for your line, and confirm in the release notes that your chosen disk type supports the cache setting you plan to use. Run the version query on each node and record the disk type from the storage configuration. The step is done when every node reports the same version and you have written down the disk type and bucket or container that the cache will front.
Create the cache directory with correct ownership
On each node, create a cache directory on a local fast disk, separate from the object storage bucket and from the data directory, so cache reads do not compete with other I/O on the same volume. Set ownership to the user that runs the ClickHouse server process, typically the clickhouse user on Linux packages, and restrict permissions so other local users cannot read cached data. Keep the path on a volume with enough free space for the maximum size you plan to set in the next step. The step is done when a write test as the ClickHouse service user succeeds in the directory and an ls of the directory shows the expected owner and mode.
Add the cache disk to the storage configuration
Edit the storage configuration file in the ClickHouse config directory and add a disk of type cache that references the existing object storage disk as its underlying disk. Set the path to the cache directory from the previous step and set the maximum size, using a value you have sized against the working set your queries touch. Keep the object storage disk definition unchanged so that data written before the change remains readable. The step is done when the configuration file parses without errors on a single test node and the new disk name appears in the disk listing after startup.
Reference the cache disk in a storage policy
Create or update a storage policy that places the cache disk in a volume, and assign that policy to the tables that should read through the cache. Keep the policy name explicit so that engineers can see which tables use it when they review the schema later. Avoid moving existing parts into the cached policy without a plan, because the first reads after a move will be cold. The step is done when a table created with the new policy reports that policy in the system tables that list storage policies and volumes, and the disk it uses is the cache disk.
Restart or reload according to the documentation
Apply the configuration using the method the ClickHouse documentation describes for your version, which is a server restart for most storage changes unless your version supports a reload for this section. Restart one replica at a time when the table is replicated, and wait for the node to rejoin and sync before moving to the next. Do this in a pre-production environment first with representative data. The step is done when the server is running on each node, the replication queue is empty or draining normally, and the disk listing still shows the cache disk.
Run a test query on a cold cache
Pick a table on the new policy with a known row count and run a query that reads a defined time range, such as a count with a filter on the ordering key. Record the duration of this first run, which reads from object storage and fills the cache, and note that the figure depends on network and data volume in your environment. Confirm the query returns the same result as the same query against the object storage disk without the cache. The step is done when the result matches the baseline and cache files appear in the cache directory on disk.
Check the cache system tables and commands
Run the same query a second time and compare its duration with the first run. Then query the system tables that expose cache state and the cache-related events in the profile or query log, and confirm that the cache size grows and that hit counts increase on the repeat run. Use the cache management commands described in the documentation to inspect or clear the cache during testing, and record the commands you used so operators can repeat them. The step is done when the repeat run shows cache hits, the cache size reported by the system tables matches the directory size on disk, and the results are consistent across replicas.
Document the cache settings and the test evidence
Write down the final cache path, maximum size, owner, storage policy name, ClickHouse version, and the cold and warm timings from the test, and store them with the configuration in version control. Include the commands that inspect and clear the cache so the on-call team can use them during an incident. Ask your partner or internal team to confirm that the settings match the sizing decision and the recovery notes. The step is done when a second engineer can read the record, rebuild the configuration on a spare node, and reproduce the cache hit behavior from the same test query.
What query latency looks like when the cache is cold and warm
A restarted or newly added node begins with an empty or partly filled local cache, so the first queries that touch object storage wait on a network round trip for each block they read. Replication restores the data from healthy peers, but the cache is local state and does not return with the data. Expect the slowest queries right after the event, lasting until frequently read ranges are fetched again.
- Cache fill at start: Check how much of the configured cache is populated after startup before you route user traffic to the node.
- Object storage round trips: Each miss reads from the bucket, so latency follows the object store and network path more than local disk.
- Replica catch-up: Missing data syncs automatically after recovery, but query latency on that replica can stay elevated while its cache refills.
- Observation: Compare bytes read from cache and from source in the query log for the minutes after the event, and inspect the cache system tables for fill level. Confirm the table and column names in your ClickHouse version.
- Traffic timing: Decide whether a new node should join the load balancer pool before its cache reaches a level your latency target assumes.
Check the warm-up period after each restart in your own environment, because it depends on your data volume, query mix and cache size.
A backfill, a regulatory extract or a dashboard opened on an old date range reads far more distinct data than the working set holds. If the scan is larger than the cache, earlier blocks can be evicted before later queries reuse them, so the scan may not benefit from the cache it fills. Latency then tracks the volume read from the bucket, the degree of parallelism and the limits of the object store.
- Eviction during the scan: When the scan exceeds cache capacity, the first partitions read may be gone by the time the job finishes.
- Cache pollution: Large one-off reads can displace hot data that interactive users depend on, so isolate heavy jobs where your design allows it.
- Shared throughput: Parallel scans compete for bandwidth and request limits with live traffic on the same network path.
- Observation: Track bytes read from source versus cache per query, and run the same historical query twice to see the gap between the cold and second pass.
- Scheduling: Run backfills outside peak dashboard hours, and confirm with your vendor documentation whether your version offers settings to limit cache use for specific queries.
Run one representative historical query against production-sized data in a pre-production environment before you publish any expectation for ad hoc historical reports.
When the frequently queried recent partitions fit within the cache, most reads are served locally and latency moves toward what a local disk tier would show. The object store is then paid mainly on misses, on new writes and on background work. This is the state that most dashboards and objectives should describe, but only after you have measured the hit ratio for your own query mix.
- Hit ratio as the core signal: Track the share of bytes served from cache during business hours rather than a daily average, which can hide peak-time misses.
- Recent data first: Recent partitions usually dominate interactive queries, so size the cache to hold them with headroom for growth.
- Merges and new parts: Background merges and fresh inserts change which files are hot, so expect small shifts in latency after heavy ingestion.
- Latency by query class: Plot p95 or p99 latency per query class beside the hit ratio, so a drop in hits explains a rise in latency on the same chart.
- Capacity review: Revisit cache size when data volume, retention or the number of dashboards changes.
Record the hit ratio and data volume under which each latency figure was measured, so the numbers can be reproduced after the next capacity change.
Service levels for query latency are only meaningful when they name the cache state they assume. A single latency target that ignores restarts and historical scans will be missed during predictable events, and the miss will look like a platform fault. Writing objectives per state, and alerting on the conditions that move a system between states, gives application owners and auditors a clearer record of what the platform promises.
- Objectives per state: Define separate targets for steady state and for documented recovery or backfill windows, and state the assumed hit ratio in each.
- Alerts on the driver: Alert on falling hit ratio or rising source reads, not only on slow queries, so the cause is visible early.
- Restart runbook: Include cache warm-up checks and traffic gating in the node restart and replica recovery procedure.
- Cost of misses: Ask your provider to show how object storage request and transfer charges change with the miss rate in your environment.
- Quote scope: When requesting a quote, ask for cache monitoring dashboards, runbook updates and a load test that covers a cold start.
Review each objective after major upgrades, because cache behavior and query plans can change between versions.
Security and operational risks of the cache layer
The filesystem cache holds copies of object storage data on local disk, so it inherits risks from both sides: the bucket credentials that reach it and the local volume that stores it. Each card below names one risk, what it means for this cache, and what to verify in your environment before go-live.
Bucket credentials and rotation
The disk configuration holds the access key or identity that reads and writes the bucket, so anyone who can read the ClickHouse configuration can usually reach the data. Use a dedicated bucket principal scoped to the one prefix ClickHouse uses, and confirm with your platform team how credentials are injected and rotated without a server restart. Check that rotation has been rehearsed on a non-production node, because a rotated key that the cache still holds will cause read failures under load.
File permissions on the cache directory
Cached parts are plaintext copies of table data, written to the path set in the disk configuration. Confirm that the cache directory is owned by the ClickHouse service account, is not world-readable, and is excluded from shared host mounts and general-purpose backup jobs that run as other users. Verify the permissions after each package upgrade or container image change, since a new default can silently widen access.
Disk exhaustion on local volumes
The cache fills the local volume it lives on, and a full volume also blocks merges, temporary files and logs for the whole server. Put the cache on a dedicated volume or set a maximum size well below the free space you need for merges and sorting. Alert on volume usage separately from cache size, and agree with the team who operates the host what happens when the volume reaches its threshold.
Encryption and access controls for cached copies
A cache file is the same data as the bucket object, so it needs the same protection. If the bucket uses server-side encryption with customer-managed keys, confirm the host volume is encrypted at rest and that the key policy covers the server. Apply the same classification to host access, snapshots and disk disposal, and remember that deleting a table does not necessarily remove cached copies immediately.
Monitoring cache hit behavior
A falling hit rate after a deploy or restart is an early sign of a cold cache, and a rising miss rate can point to a sizing problem or a query pattern that scans more history than the cache holds. Track hits, misses, cache size and evictions from the cache system tables and expose them to your monitoring stack. Set a baseline during a representative load period so that a sudden change is visible to operators.
Interaction with backups and replication
Replication copies parts between replicas, and the backup documentation notes that replication alone does not protect against human error or corruption. Cache contents are not a backup and are not the source of truth, so back up the table data through BACKUP and restore-test it on a spare cluster, and do not treat the cache directory as part of that backup. Confirm that a restored node rebuilds its cache from the bucket rather than expecting local files to survive.
Questions teams ask before configuring the ClickHouse cache
How much effort does configuring the ClickHouse filesystem cache take?
The configuration itself is a small change to the storage policy and server settings, but the effort lies in choosing the right size and placement and proving the result under your own workload. The main drivers are how many buckets and disks are involved, how many nodes sit behind each cache path, how much of the working set you expect to keep hot, and whether the cache must be tuned separately for different tables. Teams usually spend more time measuring cold and warm query behavior than editing the configuration. Ask any partner to break the estimate into design, configuration, testing and handover so you can see where the hours go.
Can our internal team configure the cache using the ClickHouse documentation alone?
A team with working ClickHouse operations experience can usually configure the cache from the official documentation, especially on a single cluster with one object storage bucket. The harder parts are judging cache size against your query patterns, deciding how cache paths map to local disks, and knowing which system tables and commands show whether the cache is doing its job. Check that the engineers who will own the change have run ClickHouse in production, have access to a non-production cluster with representative data, and can review the changelog before each upgrade. If any of those are missing, a scoped engagement can cover the gap while your team keeps ownership.
How long does validation take, and what changes it?
Validation length depends on data volume, the number of queries you need to test, and how closely the test environment matches production. A cache test is only meaningful if it runs representative queries against data of similar size, measures cold and warm runs separately, and repeats after restarts and merges. Larger datasets take longer to load and warm, and wider query coverage adds test cases. Ask for the validation plan in writing, including which queries are included, which metrics are recorded, and what result would lead to a change in cache size or placement. Avoid any timeline that is not tied to those inputs.
What does the work need from us before it starts?
The work needs access to the object storage buckets the cache will front, read access to a representative set of queries from your logs or application code, and a way to reach the ClickHouse hosts where the cache paths will be created. Expect to provide the current storage policy, server configuration, cluster topology and the table definitions that use object storage. You will also need to name an owner who can approve changes on the production cluster and a window for restarts if they are required. Credentials should be scoped to the buckets and hosts in the engagement, and any access granted for testing should be removed when the work ends.
What changes after a ClickHouse upgrade that affects the cache?
Upgrades can change cache behavior, settings names, default values and system table columns, so the cache configuration should be rechecked against the release notes for each version you move to. The ClickHouse production guidance recommends a pre-production environment with representative data and queries, and reading the changelog before upgrading because minor backward-incompatible changes can occur. Confirm the supported version lines in the security policy and the release notes for your target version. After an upgrade, rerun the cold and warm query tests and compare cache hit behavior with the baseline you recorded before the change.
How is the cost shaped by our infrastructure and the written scope?
Cost has two sides: the infrastructure you pay your cloud or data center provider for, and the services fee for planning, configuring, testing and handing over the cache. Infrastructure cost depends on local disk capacity for the cache, the number of nodes that each keep a cache, object storage request and egress charges, and any test environment you need to run validation. The services cost depends on the number of buckets, tables, nodes and query patterns in scope, and on whether you want ongoing support or upgrade testing. DeployOpen does not publish a price list. Ask for a quote built from a written scope that lists the environments, workloads and deliverables included, so you can compare it with the internal effort you would otherwise need.
What should we check first if cold queries are much slower than expected?
Start by confirming whether the slow queries are actually cold, meaning the data they read was not in the local cache, and then check whether the cache has enough capacity for the data those queries touch. Look at the cache-related system tables and metrics on each node, and compare the same query on a warm and a cold run. If the cache is too small for the working set, the fix is usually sizing or placement, not a configuration change alone. If the cache is sized correctly and queries are still slow, review the object storage region, network path and the parts layout of the affected tables before changing settings.
Running the cache after go-live
The cache needs a standing review after go-live, because its size and hit pattern change as data grows and workloads shift. Check usage against the configured maximum size and watch the ratio of cache hits to misses for queries that read from the object storage disk. Sustained eviction is the signal to act: a cache that keeps discarding recently read data will send more reads to the bucket and make latency less predictable. Before go-live, record a baseline of query latency for your dashboard and investigation queries with the cache warm, and keep it with the configuration version it describes. Without that baseline, a later slowdown is hard to attribute to the cache rather than to the query mix.
Three events should trigger a review: an upgrade, a node replacement and a visible change in partition volume. Read the changelog before each upgrade, because minor backward-incompatible changes can occur, and rehearse the upgrade in a pre-production environment with representative data so you can compare cache behavior and latency against the baseline. A replaced node starts with an empty cache at its cache path, so a temporary latency increase is expected. Decide in advance how long a warm-up period is acceptable and when the node should be treated as a problem. The ClickHouse production FAQ gives the upgrade guidance to plan around.
Revisit the maximum size as retained partitions grow, since the working set for recent queries changes with the data. Confirm the free space on the cache volume still leaves headroom for other processes before raising the limit. Write a runbook covering two procedures: clearing the cache, which is the right step after a disk fault or suspected corruption, and warming it, which means choosing the queries to run, their order and who approves the run inside a change window. Name an owner for each step, record the rollback, and confirm the system table and cache-clearing statement names for your release in the official documentation. Ask any partner to hand over this runbook as a deliverable.
How DeployOpen scopes and delivers cache work
The sequence below turns a cache question into a written scope, a reviewed storage design, a configured cache in your own infrastructure, and evidence that queries behave as planned after the handover. Each step ends with a named output you can check.
Agree a written scope before work starts
DeployOpen and your team write down the object storage provider, the number of ClickHouse nodes, the tables and query patterns the cache must serve, and the acceptance tests. Most engagements are fixed-scope, so the deliverables and timeline are agreed in writing before any change is made to a production cluster. The output is a signed scope document that names what is in and out, including whether tiering, backups or upgrades are handled separately.
Inventory the current storage policy and disks
DeployOpen reads the existing storage configuration, including the disks, the object storage endpoints, the storage policies that reference them, and the volumes where parts currently live. The review records which cache paths and size limits are already set, and which tables use the object storage disk. The output is a short inventory that shows where the cache sits in the policy and which workloads it will affect.
Review the access model and network path
The team checks which ClickHouse users and service accounts read and write the object storage bucket, how credentials are supplied, and how the nodes reach the bucket over the network. Bucket rules, lifecycle settings and encryption are reviewed for conflicts with ClickHouse's own expectations. The output is a list of access changes your security team must approve before configuration begins.
Size the cache from representative workloads
Working from the inventory and the agreed query set, DeployOpen proposes a cache path on fast local storage and a maximum size per node, with the reasoning shown. Sizing is checked against the data that queries touch most often, not the total table size. The output is a sizing note that your team can challenge before any setting is changed.
Configure the cache in your environment
DeployOpen applies the cache settings through the configuration files your team manages, in a pre-production environment with representative data first. Changes are made one node at a time where the cluster allows it, and the existing configuration is saved so it can be restored. The output is a configuration change record and a node-by-node log showing the cache is active.
Verify with representative queries
The agreed query set runs against a cold cache and then a warm cache, and results are compared with the cache system tables and the query log. Latency, read volume from object storage and cache hit behavior are recorded for each query type. The output is a verification report that states what was measured, under which conditions, and any queries that did not meet the acceptance tests.
Hand over the documentation and runbook
DeployOpen writes the final configuration, the cache sizing rationale, the monitoring queries and the procedure for clearing or resizing the cache. Your operators are walked through the system tables and commands they will use after handover. The output is a handover pack and a recorded session that your team can use without DeployOpen present.
Confirm operations after handover
If you choose a retainer, DeployOpen reviews cache behavior at the agreed intervals and reflects changes in data volume or upgrades in the configuration. Otherwise, your team owns monitoring and the agreed review points stay in the runbook. The output is a named owner for cache health and a schedule for re-running the verification queries.
