Using the ClickHouse query cache

How the ClickHouse query cache speeds up repeated dashboard queries

When many users open the same dashboards against a self-managed ClickHouse cluster, most of the load is identical SELECT statements running again and again. The ClickHouse query cache stores the results of SELECT queries that opt in with the use_query_cache setting, so an identical repeat can return the stored result instead of scanning the data again. The gain is lower latency and less CPU and IO on the cluster, paid for with memory held by the cache and with results that may be a little behind the underlying data.

The cache is a per-query, opt-in feature, not a global speed-up. Nothing is cached unless the query, the client or the dashboard tool sends the setting, so a dashboard layer such as Grafana, Superset or Metabase only benefits if its generated SQL or its data source configuration passes that setting through. Before you plan around it, check which setting names and cache-related options your exact ClickHouse version supports in the ClickHouse documentation, and test the behavior on a copy of your dashboards, because a query that is not cacheable will simply run as it always did.

Treat the cache as a decision about which dashboards may show slightly old numbers. Ask the dashboard owners how fresh each panel must be, measure how often identical queries actually repeat in system.query_log, and confirm the cache size and entry lifetime you intend to use on your version before rollout. Queries that return user-specific or time-sensitive results need a different plan, which the rest of this page covers.

Which ClickHouse query cache settings fit dashboard traffic

This table compares the main query cache settings for repeated dashboard queries, with when each fits and what it trades off. Confirm exact setting names, defaults and scope against the ClickHouse documentation for your version before you change anything, because those details were not verified for this page. Read each row as a decision point: the main takeaway is that lifetime, staleness and sharing settings should be set together, not one at a time.

Setting	When it fits	What it trades off
use_query_cache	Dashboard queries that run many times with identical text and settings, such as panels refreshed on a timer	Only queries that opt in are cached, so each dashboard or client must enable it explicitly; confirm whether it is set per query, per session or per profile
Entry lifetime setting (query_cache_ttl)	Dashboards where data changes on a known schedule, so a fixed maximum age matches how fresh users expect results to be	A long lifetime serves older results for longer; a short lifetime lowers hit rates and increases load on the cluster
Staleness setting (query_cache_min_query_runs or similar)	Queries that should only be cached after they have proven to repeat, which keeps one-off exploration out of the cache	Caching only after several runs delays benefit for new dashboards; confirm the exact name and behavior in your version
Cache size limit (max_size_in_bytes)	Servers with spare memory where the dashboard working set fits and cache churn stays low	A large cache takes memory from queries and merges on the same host; size it against your memory limits and monitoring
Cache entry count limit (max_entries)	Many small, distinct dashboard queries where a count cap prevents a long tail of rarely reused results from filling the cache	A low count limit evicts useful results early; a high one can hold many entries that are never read again
Cross-user sharing setting (query_cache_share_between_users)	Shared company dashboards where all viewers should see the same results for the same query	Sharing means one user's cached result can be served to another; only enable it where row policies and access rules give every user the same visibility
Queries that should never be cached	Queries returning per-user or per-tenant results without row policy isolation, queries with volatile functions such as now(), and ad hoc exploration	Excluding them keeps results correct but leaves that load on the cluster; confirm how your version treats non-deterministic functions

How the ClickHouse query cache handles stale results

A cached result can be older than the data beneath it, and the acceptable age depends on who reads the dashboard. With use_query_cache enabled, ClickHouse may return a stored result for a repeated query instead of recomputing it, so a chart can lag behind rows that were inserted a moment ago. The entry lifetime, set through settings such as query_cache_ttl, bounds how long a result can be served, and the staleness tolerance is the age your business will accept. Confirm both values against your own ingestion pattern before you choose them, because a short lifetime on a table that changes every few seconds delivers little benefit.

The interaction between lifetimes and writes is where most surprises happen. A write that lands during an open cache window does not invalidate the cached result on its own, so a dashboard can keep showing the earlier figures until the entry expires. Check the current behavior for your release in the ClickHouse query cache documentation and test it with a controlled insert, reading the same query before and after the write. Also note that cache entries are per query text and settings, so two dashboards that differ by a single filter will not share a result.

Operational dashboards and finance or compliance reports need different tolerances. An on-call view of error rates may be useful at a few seconds of lag, while a month-end revenue or regulatory report usually needs figures that match the source at the moment of the run, so those queries are often better left uncached. Before enabling the cache, agree in writing with each dashboard owner what lag is acceptable, who can see a figure marked as cached, and how they will report a number that looks wrong. That conversation costs little and prevents a support ticket that begins with a figure that does not match the warehouse.

Queries that should never be cached in ClickHouse

The query cache speeds up repeated reads, but it returns stored results without re-running the query. Some query classes depend on fresh data, per-user visibility or live system state, so caching them produces wrong or unsafe answers. Apply exclusions by application, user or settings profile before enabling the cache broadly.

Queries that need fresh writes

Reports that must reflect the latest inserts, such as reconciliation checks run right after a batch load or a dashboard showing live incident counts. A cached result stays valid until its entry expires, so a query that runs within the lifetime of an earlier result can show rows that no longer match the table. Check whether a user expects data from the last few minutes, then set use_query_cache to 0 for that query or its application.

User-specific and sensitive results

Queries whose output depends on who asked, such as row-policy filtered views, tenant dashboards or personal activity lists. The cache keys entries on the query text and settings, so a result computed for one user must never be served to another. Keep these queries out of the cache unless you have confirmed that the cache key and access rules separate every audience. Review the access model with the access-control documentation before enabling caching for any shared role.

Non-deterministic functions

Queries that call functions returning a different value on each run, such as now(), rand() or generateUUIDv4(). Caching them either freezes a value that should move or hides the variation the query was written to show. Check the function list in each query before caching, and move time-dependent filters into the application where the result can be recomputed on a schedule the team controls.

Ingestion checks and system table reads

Health checks, insert verification and monitoring queries that read system tables such as the query log or parts information. Their purpose is to observe the current state of the server, so a cached answer hides the failure they exist to catch. Exclude them by application name or a dedicated settings profile, and confirm the monitoring user never inherits a cache-enabled profile.

Very large result sets

Exports and wide scans that return millions of rows or large string columns. Storing them consumes cache memory that could hold many smaller dashboard results, and a single large entry can crowd out the rest. Set a result size limit for the cache and route bulk exports to a non-cached path, so that dashboards keep their hit rate.

Enabling the ClickHouse query cache in a pre-production environment first

This sequence turns query cache use from a guess into a tested setting: you identify the dashboard queries worth caching, prove the cache behaves correctly on representative data, and roll it out only where the staleness trade-off has been agreed.

Inventory dashboard queries in query log
Pull the dashboard queries from system.query_log, grouping them by normalized query text, user or service account, and frequency. Note which ones repeat often, which return the same result for every viewer, and which depend on the current time or per-user filters. Record the tables each query reads, since the cache's value depends on how often identical queries arrive. Done when you have a ranked list of candidate dashboards with their query patterns and owners named in the list.
Build a pre-production cluster with representative data
Stand up a non-production ClickHouse environment that runs the same server version as production, loaded with a representative copy of the tables the candidate dashboards read. The production FAQ recommends a pre-production environment with representative data and queries, so the test is only meaningful if data volume, table layout and query shapes resemble production. Confirm the version and table definitions match before you continue, and note any gaps that could change cache behavior.
Set a conservative cache size and lifetime
Configure the query cache size and the entry lifetime for the pre-production server with deliberately modest values, so cached results cannot consume large amounts of memory while you learn the workload. Confirm the exact setting names and defaults for your version in the documentation before editing any configuration. Done when the settings are applied, the server has restarted or reloaded as required, and the active values are visible when you query the settings table.
Enable the cache for one dashboard
Choose the single dashboard whose staleness the owner has accepted or is willing to test. Add use_query_cache to the queries that dashboard sends, either per query or through a settings profile assigned only to its service account. Keep all other dashboards uncached at this point. Done when the dashboard's queries run with the setting and no other workload has inherited it.
Verify cache hits and misses
Run the dashboard several times and check the query log for cache-related events, comparing the first execution with repeat executions and confirming that repeat runs are served from the cache. Also run a query with different parameters or a different user to confirm that results are not shared where they should not be. Done when you can show, from logged evidence, the expected hits, the expected misses, and no cross-user leakage.
Confirm staleness behavior with the dashboard owner
Walk the dashboard owner through the observed lifetime of cached results, using a concrete example of data that arrives after a cached result was stored. Ask whether a delay of that length is acceptable for each chart, and flag any figure that must always be current, such as a live operational count. Record the agreed staleness per dashboard in writing. Done when the owner signs off on the lifetime in writing and any charts that must bypass the cache are listed.
Roll out per dashboard or settings profile
Move the approved configuration to production in small steps, enabling the cache for one agreed dashboard at a time through its own settings profile or query setting. After each step, check the query log for hit rates and memory use, and confirm that other workloads behave as before. Keep a documented rollback that removes the setting. Done when each approved dashboard has its own recorded lifetime, production memory use stays within the size you set, and the rollback has been tested in pre-production.

Cache policy by workload: dashboards, ad hoc analysis and pipelines

Shared dashboards are the strongest fit for the query cache because many viewers send identical SQL on a timer. A cached result can serve the second and later viewers without scanning the table again. The trade-off is staleness: viewers see the last cached result until the entry expires, so the lifetime must match how often the underlying data changes and how often the panel is meant to refresh.

Lifetime matched to refresh cadence: set query_cache_ttl no longer than the interval at which the panel should show new data, so a panel that promises near-live numbers does not serve older results.
Access model checked before sharing: entries are reused only for identical queries, but confirm that row policies and user roles produce the same result before enabling reuse across audiences.
Settings defined once: apply use_query_cache in the dashboard user's settings profile so the policy lives in one place and is not repeated in each panel.
Cache-friendly SQL: identical text and literals produce hits, while panels that embed the current time or per-viewer values usually miss, so review those panels first.
Cache size watched at peak: track cache memory and hit behavior during the busiest refresh window, not only on average.

Before publishing a dashboard, check that its freshness promise, lifetime and access model all agree.

Analyst queries are exploratory. They are often rerun with small edits and frequently answer questions about data that is still changing, such as late-arriving events or a recent correction. Caching can help when a team repeatedly runs the same expensive exploration, but it can also hide new data from people who expect current numbers. For this group, the usual approach is to leave the cache off by default and enable it deliberately for known heavy queries.

Off by default: leave use_query_cache unset in analyst profiles so each exploration reads current data unless someone opts in.
Opt-in for known heavy reports: enable the setting on a named query or saved report that many people run, and document its lifetime beside it.
Bypass after loads or corrections: a backfill or manual fix can be masked by a cached result, so analysts should disable the cache for the session after a known data change.
Verification sessions: when a number is being reconciled with finance or another source, run it with the cache disabled and record the setting used.
Shared personal queries: an identical query from one analyst can reuse another's entry if access is the same, so confirm the sharing rules before enabling the cache for ad hoc work.

Ask analysts which figures they reconcile against other systems, and exclude those queries from caching.

Pipeline queries usually should not use the query cache. They often read the latest loaded timestamp, offset or key set to decide what to insert next, and they need current state to avoid gaps and duplicates. A cached answer can cause a pipeline to reload data or skip it. Cache entries also consume memory on the same server that absorbs inserts, so the cache can compete with ingestion during busy periods.

Watermark and checkpoint reads: queries that read the latest loaded timestamp or position must return current state, or the job will reprocess or miss rows.
Deduplication and existence checks: lookups that decide whether a row has already arrived can miss rows loaded moments earlier if their result is cached.
Explicit disable in job settings: set use_query_cache to 0 in the pipeline's user profile or on each statement, so the decision survives later changes to dashboard defaults.
Memory shared with inserts: monitor cache memory together with insert latency and merge backlog, since both draw on the same server resources.
Expensive repeats handled upstream: if a pipeline query is repeated and costly, address the schema or a materialized view first, then revisit caching only if the result is still safe to reuse.

After each pipeline deployment, confirm in the query logs that its statements are not being served from the cache.

Who can change the query cache and what cached results expose

The query cache is only as safe as the settings and roles around it. Who can switch caching on, how large the cache may grow, and which users can read a shared result all determine what the cache exposes. Review these controls together before enabling the cache for dashboards.

Restrict use_query_cache by profile

The use_query_cache setting decides whether a query reads from or writes to the cache, so it should not be left to each user. Set it in a settings profile assigned to dashboard accounts, and check which profiles ad hoc analysts and service accounts inherit. Confirm that users who can override the setting in their own session cannot bypass the intended profile. Test this with a non-admin account before rollout.

Control who can raise cache limits

Cache size and entry limits are server-level and profile-level settings that determine how much memory the cache can hold. A user who can raise these limits can push other workloads toward memory pressure. Limit write access to profiles and roles that manage the server, and document who approved any increase. Ask your platform team which accounts hold grant rights on these settings.

Shared results and row policies

A cached result is served to any user who sends the same query text, so the cache can show rows that a user's row policy would normally hide if the policy was not part of the cache decision. Before enabling sharing for a dashboard, confirm that every table it reads has row policies covering every role that can view the result. Treat any table without full policy coverage as a reason to exclude that query.

Review row policies before sharing

Row policies restrict which rows a role sees, and they must match the access model the business expects. Review policies for each role that reads cached dashboards, including policies that apply to joined tables and to users who belong to multiple roles. Document the review and repeat it when roles or tables change. Ask whether an auditor can trace which policy applied to a given result.

Remove the default user's full permissions

ClickHouse's default user starts with all permissions, so it can change cache settings and read any table unless you restrict it. Disable or constrain the default account, then create named users with roles that grant only the dashboard reads they need. Verify that the default account cannot be used over your exposed interfaces before you enable the cache.

Monitor memory used by the cache

Cached results occupy server memory, so a growing cache can affect ingestion and query latency on the same nodes. Watch memory use over time, compare it with the configured limits, and alert before the cache competes with merges and other queries. Include cache growth in capacity reviews, since dashboards added later can increase it without any change to the cache settings.

What does it cost and take to run the ClickHouse query cache well

How much effort does setting up the ClickHouse query cache take in-house?

Enabling the cache is a small change, but making it safe takes more work: deciding which dashboards may tolerate stale results, setting per-user or per-profile cache limits, and checking that cached results never cross row policy boundaries. An internal engineer familiar with ClickHouse settings profiles can usually handle the configuration itself. The effort grows when many teams query the same cluster with different access rules, because each rule set needs its own verification. Confirm the scope of that verification with your own data team before estimating internal time, since the work depends on your dashboards and access model rather than on the feature itself.

What drives the effort of a query cache project?

Three factors move the effort most. The first is the number of dashboards and distinct queries that repeat often enough to benefit from caching. The second is row policy complexity, since a cached result must be keyed correctly for each user or role that sees different rows. The third is freshness: a dashboard that must show data within seconds needs a short entry lifetime or no cache at all, while a monthly report can tolerate a long one. Each factor adds testing, documentation and review cycles. A written scope should list the dashboards in scope, the access model and the freshness rule for each group.

Can our team run the ClickHouse query cache without outside help?

A team with ClickHouse operations experience and access to its query logs can usually run the cache alone once the initial settings are agreed. Outside help tends to add value in three places: measuring whether the cache actually reduces load, testing cache behavior under your row policies and roles, and writing the runbook that dashboard owners will follow. If your team has no one who reads system tables or reviews access control in ClickHouse, a fixed-scope engagement that ends with documented settings and a handover is the more predictable route. DeployOpen plans and implements these changes inside your own infrastructure.

What does the cache project need from our team?

The work depends on access to real usage, not on assumptions. Expect to provide:

Query log access, so the most repeated dashboard queries can be identified.
Named owners for each dashboard, who can state how stale their figures may be.
A pre-production ClickHouse cluster with representative data and the same access rules as production.
A list of users, roles and row policies that affect what each dashboard shows.

Without a pre-production cluster, cache behavior cannot be tested safely, because a wrong cache key can expose one user's rows to another. Ask the partner to confirm each of these items in the written scope.

How long does it take to measure whether the query cache helps?

Measurement depends on how often your dashboards refresh and how much traffic they generate, so no fixed duration applies. A useful measurement window covers a full business cycle, such as a week that includes month-end reporting, so peaks and quiet periods both appear in the data. Compare query counts, rows read and response times before and after enabling the cache for each dashboard group, using system tables that record cache hits. Ask any partner to state the measurement window and the metrics in the written scope, and to say what result would lead to keeping, adjusting or removing the cache.

Does the ClickHouse query cache change after an upgrade?

It can. Cache behavior is controlled by settings, and ClickHouse stable and LTS releases may change defaults, setting names or query analysis, so cached results and their hit rates should be retested after each upgrade. The ClickHouse production FAQ recommends reading the changelog before upgrading because minor backward-incompatible changes can occur, and running upgrades in a pre-production environment first. Add a cache test to your upgrade checklist: confirm the settings still take effect, rerun the dashboard comparison, and check that row-level isolation still holds. Confirm the supported version line you run in the ClickHouse security policy.

How is the cost of a ClickHouse query cache project quoted?

DeployOpen does not publish a price list. A quote follows from a written scope that names the dashboards, access model, freshness rules, pre-production environment, measurement plan and handover materials. Most engagements are fixed-scope, so the scope, deliverables and timeline are agreed in writing before work starts. Ongoing support, upgrade testing and continuous checks are available as retainers, which recur, while setup and documentation are one-time. When you request a quote, ask which items are included, which depend on your environment, and what changes would reopen the price.

How DeployOpen scopes and supports ClickHouse query caching

DeployOpen treats query caching as a scoped change to a self-managed ClickHouse cluster, not a settings tweak. We agree which dashboards are cached, how stale results are handled and which queries stay uncached, then build, test and document the configuration in your infrastructure.

How we scope

Inventory the dashboard and API queries that repeat, with their owners, refresh needs and freshness tolerance.
Agree which query classes may use the query cache and which must always read live data.
Set written scope, deliverables and timelines before work starts, since most engagements are fixed-scope.

How we build

Configure use_query_cache, cache size and entry lifetime in a pre-production cluster with representative queries.
Test cache hits, misses and stale-result behavior against the agreed freshness rules for each dashboard.
Confirm that user-scoped and sensitive queries are excluded so cached results cannot cross access boundaries.

How we hand over

Document each cached query class, its lifetime, its size limit and the reason it is or is not cached.
Provide a runbook for changing cache settings and for clearing cached results after a data correction.
Walk your engineers through the test results so they can extend the cache policy with confidence.

How we support

Review cache settings again when ClickHouse upgrades change query behavior, after checking the release changelog.
Offer ongoing support, upgrades and continuous testing as a retainer if your team wants them.
Confirm with your own team how cache metrics feed into the monitoring you already run.