Our ClickHouse setup to reduce latency

ObsessionDB reduces ClickHouse® query latency in layers. Our distributed NVMe cache turns the S3 round trip, hundreds of milliseconds at the tail, into a 0.5 ms node-to-node hop and caches data the moment it is written. Projection indexes stay resident in memory and correctly gated at terabyte scale. Under those sit the settings any ClickHouse cluster can tune: the projection index gates, the query condition cache, and parallel replicas.

Most ClickHouse tuning advice starts with the query: sort keys, PREWHERE, skip indexes. That layer matters, and our guide to indexes, projections and materialized views covers when to reach for each. This post is about the layer under it, the server and query settings that decide whether the machinery you built gets used at all. We walk through them in the order you should check them, starting from the one thing no setting removes.

On any S3-backed ClickHouse, ours included, the latency floor is the round trip to object storage, and the tail of that round trip is what sets it. Quickwit's measurements put a typical S3 first byte near 30 ms; AWS's own performance guidance allows 100 to 200 ms. Both are medians, and a fan-out query does not experience the median: it splits into hundreds of GETs and finishes with the slowest one. At the tail S3 runs 300 to 500 ms, so a query touching object storage a few hundred times meets that tail on nearly every run. Adding 30 ms to a query is usually fine. Running every query at the speed of the slowest of its several hundred requests is what kills you.

The filesystem, query condition and distributed caches

To avoid S3 roundtrips, different layers exist, each one solving a different problem.

ClickHouse ships two node-local caches that matter for latency on object storage. The filesystem cache keeps raw data ranges on local disk so a granule read from S3 once is not read from S3 twice. The query condition cache remembers which granules failed a WHERE filter so the next run of that filter skips them without touching data.

ObsessionDB clusters run a third cache underneath both: the distributed one that is shared across every node in the cluster, which increases total disk space and increases the cache hit ratio meaningfully.

The filesystem cache

The filesystem cache is disk configuration, not a query setting. You define a cache-type disk on top of the object storage disk, and a storage policy has to route the table through it:

<storage_configuration>
    <disks>
        <s3_cached>
            <type>cache</type>
            <disk>s3_main</disk>
            <path>/var/lib/clickhouse/cache/</path>
            <max_size>100Gi</max_size>
        </s3_cached>
    </disks>
    <policies>
        <s3>
            <volumes>
                <main><disk>s3_cached</disk></main>
            </volumes>
        </s3>
    </policies>
</storage_configuration>

The query-level setting only permits the cache. If no policy routes through the cache disk, enable_filesystem_cache = 1 permits nothing, with no warning anywhere: a cache disk can sit fully configured in system.disks while every read keeps going to object storage, on the first run and on every run after it.

One query against system.query_log settles whether you are in that state:

SELECT ProfileEvents['CachedReadBufferReadFromCacheBytes'] AS bytes_from_cache
FROM system.query_log
WHERE type = 'QueryFinish' AND query_id = '<your-query-id>';

Zero on a repeat query means the cache is not in your read path. Check system.storage_policies next.

Two more traps live in the same config block. Self-hosted ClickHouse populates the cache on reads only: enable_filesystem_cache_on_write_operations defaults to false in open source (ClickHouse Cloud flips it to true), and the cache disk must declare cache_on_write_operations as well, so a stock self-hosted cluster serves its freshest data cold.

We added this section to show the full cache context within the ecosystem, but we have it disabled by default in ObsessionDB. A node-local cache wastes NVMe in a cluster: on six nodes, a query has one chance in six of landing where the data sits warm, and partial coverage behaves worse than the percentage suggests, because a few cold files per part put S3 back in the critical path of nearly every read. Contributing the same NVMe to the distributed cache serves every node and takes the hit rate to effectively 100%. Our goal is zero cold accesses, and node-local caching cannot get there.

The query condition cache

The query condition cache stores one bit per filter and granule: did this granule survive this WHERE clause. It has been on by default since 25.4, costs 100 MB of memory unless you resize it, and on repeated selective filters it is worth an order of magnitude; we've seen repeated runs dropping from a few seconds to less than 100ms. Leave it on. Filters on append-mostly data hit it constantly, and that describes most analytics workloads.

It has one sharp edge, and it cuts benchmarks. The second run of a test query is served partly from the condition cache, so your before/after comparison measures the cache instead of the change. Set use_query_condition_cache = 0 inside any measurement harness that requires it and nowhere else.

Marks and index files have their own caches with their own failure modes at scale; we covered keeping them resident on a 20 TB table, and the 213 ms p50 that bought, in ClickHouse projections at scale.

The distributed cache

The cache that moves our latency most is the distributed one. Every node contributes its NVMe to one shared mesh, and rendezvous hashing decides which node holds which file, so a part cached anywhere in the cluster is a 0.5 ms hop from any node, where the same read from S3 costs tens of milliseconds at the median and hundreds at the tail. It sits under Alloy, our storage engine developed against the SharedMergeTree API, and it is the reason we can leave the filesystem cache switched off.

Its behavior differs from the native caches in three ways. Data is cached the moment a node writes it, so the freshest partitions serve warm on their first read. Indexes and marks prewarm when a part appears, the same lever that took a 20 TB table to a 213 ms p50 in projections at scale. And work routes to the node that already holds the bytes: around 70% of merges read locally on our two busiest clusters, where natural placement would give a six-node mesh about 17%.

The two native caches save one node a trip to S3. The distributed cache saves the full cluster, survives changing size, and needs no configuration from you. The full design is in the stateless distributed cache post and building on decoupled ClickHouse.

Projections at terabyte scale

The cache decides how fast bytes arrive. Projections decide how few bytes are needed, and they are where our clusters separate most from stock deployments and ClickHouse Cloud. On SharedMergeTree-style setups, projections stop scaling past a few terabytes because part selection overhead starts eating the query. We run one on a 20+ TB, 200-billion-row table at a 213 ms p50; how we made projections 10x faster at 20 terabytes is the engineering underneath, and keeping mark and index files resident in memory is most of it.

The same machinery carries a trap you can fix yourself. 25.11 shipped two gate settings defaulting to 1,000,000 rows, and the official settings reference describes the first as the minimal estimated rows to read from the table. However, the check runs per part (the gate sits in projectionsCommon.cpp, inside the loop over data parts), against each part's selected row ranges, so a table made of parts under a million rows never engages its projection index, at any table size, and background merges keep producing exactly such parts under steady ingest. On a 194-billion-row table that floor was 35 million rows and 1.6 GB read per query; zeroing the gates cut it to 26 thousand rows and 369 KB:

SET min_table_rows_to_use_projection_index = 0;        -- default 1,000,000
SET max_projection_rows_to_use_projection_index = 1e9; -- default 1,000,000

Plain EXPLAIN indexes = 1 shows a full scan either way, because projection-index pruning happens at read time. Check read_rows in system.query_log, then EXPLAIN indexes = 1, projections = 1. Whether a projection index is even the right mechanism is its own decision; indexes vs projections vs materialized views walks the ladder.

The settings worth tweaking

Everything above ships with the platform. These are the levers you hold, on any ClickHouse. Query-side design moves the most latency and has its own guides: sort keys, PREWHERE and skip indexes in ClickHouse query optimization, and the lookup shape in dictionaries and JOINs, where a dictGet beats the equivalent JOIN by 20x. Below the query layer, two settings deserve their own warning labels: one splits a query across machines, the other defers work within one.

Parallel replicas (enable_parallel_replicas = 1) turn every replica into a worker on the same query instead of a failover copy. On a heavy scan or a large aggregation, the fleet shares the read and wall time drops with node count. On a point lookup there is nothing to share, and the coordination becomes pure overhead: small queries can pay more in coordination than they save, and shapes with CTEs, subqueries or JOINs can regress outright. parallel_replicas_min_number_of_rows_per_replica is the guardrail: below the threshold, the query stays on one node. This setting was introduced in 24.10 and requires the new analyzer. There is no single right replica count: a heavy analytical query can and should take every node, high concurrency favors fewer, and ClickHouse picks the actual number from a dozen settings, expected row counts among them. Treat max_parallel_replicas as a per-workload dial.

One more edge: with parallel reading enabled, the planner declines to use projections for that read. If your latency plan depends on a projection, and after the previous section it might, scope parallel replicas to the scan-heavy queries with a per-query SET instead of enabling them globally.

Lazy materialization (query_plan_optimize_lazy_materialization) is the within-node counterpart: for top-N queries, ClickHouse reads the heavy columns only for the rows that survive ORDER BY plus LIMIT. We measured a wide column at 0.45% extra read volume with it on, and 14.5% with it off. The cliff is query_plan_max_limit_for_lazy_materialization, default 10,000: a LIMIT above it turns the optimization off with no message, so a paginated endpoint that grows its page size can fall off a performance cliff.

Which setting for which symptom

Work down the symptoms in order; each row is cheaper to check than the one below it.

Symptom	Check first	The move
Repeat queries as slow as cold ones	CachedReadBufferReadFromCacheBytes in query_log	Route a storage policy through a cache-type disk
Selective query full-scans despite a projection index	read_rows, then EXPLAIN indexes = 1, projections = 1	min_table_rows_to_use_projection_index = 0, raise the max gate
Benchmark beats production	Harness settings	use_query_condition_cache = 0 in tests, on in prod
Fat scans ignore your replica count	enable_parallel_replicas, analyzer on	Enable per query profile, keep the min-rows guard
Point lookups regressed after enabling parallel replicas	Projection usage on those queries	Scope parallel replicas to scans; projections and parallel reads do not mix
Cold queries stuck in seconds with all of the above green	S3 GET count per query	You are at the floor; the fix is cache architecture, not settings

Everything above the last row works on any ClickHouse deployment, self-hosted or managed, ObsessionDB included. The last row is why our clusters hold up on huge datasets: cache on write, prewarmed indexes, locality routing and resident projection indexes sit under every table by default, and no setting can retrofit them onto a stock cluster. The 10-billion-row benchmark against ClickHouse Cloud shows what that stack adds up to, and how we query 18 terabytes in under a second walks one production workload through every layer of it.

If your p99 disagrees with your settings, we will look at it with you. Our performance audit takes your actual query log, applies this checklist and the deeper ones, and hands back the findings whether or not you ever run on ObsessionDB.

FAQ