Why ClickHouse workload scheduling keeps dashboards and ingestion apart
Workload scheduling lets one ClickHouse cluster divide its CPU, disk and memory between competing kinds of work, so a heavy analyst query or a backfill cannot stall inserts or dashboards. Teams usually need it once a single shared cluster serves continuous inserts from agents or pipelines, dashboards that refresh on a timer, and ad hoc analyst queries that can scan billions of rows without warning. Before that point, the default behavior is often acceptable. Once one class of query regularly delays another, the question shifts from capacity to how the server allocates what it already has.
Resource isolation and access control solve different problems, and teams often confuse them. Access control, handled through users, roles, row policies, settings profiles and quotas in the access control model, decides who may run which queries and which rows they can see. Workload scheduling decides how much of the server a class of work receives once it runs. A user can be fully authorized and still exhaust memory for everyone else. Plan both: restrict the default user as the access control documentation recommends, then decide which workloads must be protected from each other.
The exact behavior of workload scheduling depends on the ClickHouse version in use, including which resource types can be scheduled, how workloads and resources are declared, and how weights and concurrency limits are applied. Confirm the syntax, defaults and limitations against the documentation for your release before designing hierarchies, and test them on a copy of your cluster. The ClickHouse production FAQ also asks for a pre-production environment with representative data and queries, which is the right place to check whether a scheduling setup protects inserts under real load.
Which workload split fits your cluster
Ingestion-heavy clusters spend most of their day absorbing writes from agents, collectors or Kafka consumers, with dashboards and reports as a secondary load. Illustrative example: a telemetry pipeline streams events around the clock while a small set of morning reports runs once. The usual fit is a dedicated ingestion lane with a weighted share of CPU and IO, separated from interactive traffic, so background merges keep pace with arrivals.
- Dedicated lane: insert users get their own workload, so a long report cannot starve the merges that keep parts under control.
- Batch-friendly writers: collectors send larger batches or use asynchronous inserts, which reduces the number of small parts the merge process must absorb.
- Merge backlog: the count of parts per partition climbs during business hours and does not return to its baseline overnight.
- Insert errors: writers see timeouts or errors about too many parts, and Kafka consumer lag grows during query peaks.
- Headroom test: insert latency rises only when a specific dashboard or export runs, which points to a missing lane.
Check whether insert latency and part counts change when a known heavy query runs, and confirm the exact scheduling settings for your server version in its documentation before you rely on them.
Dashboard-heavy clusters serve many users who refresh the same panels on short intervals. Each query is usually small or medium in size, but the concurrency is high and bursts often line up with the start of a shift or a scheduled review. The usual fit is a dashboard workload with a bounded number of simultaneous queries, reading from summary tables that keep each refresh cheap.
- Concurrency cap: dashboards get a fixed number of simultaneous slots, so a refresh storm queues instead of consuming every core.
- Service identities: each dashboard tool connects as a dedicated user, so usage can be measured, limited and traced in the query log.
- Pre-aggregated sources: panels read tables produced by materialized views, which shrinks the work each refresh needs.
- Synchronized timeouts: panels fail at the same time each day, or one heavy panel slows unrelated ones on the same page.
- Cache misses: repeated identical queries bypass caching, so the same expensive scan runs for every viewer.
Review the panels that fail most often, then confirm with the query log whether the failures cluster at peak refresh times or follow a single heavy panel.
Mixed analyst access means people write their own queries against production tables. These queries are hard to predict, may scan wide time ranges and sometimes run without a limit. Illustrative example: an analyst exports a full month of raw events on a Friday afternoon while the weekly review dashboards are loading. The usual fit is a separate analyst workload with lower priority than dashboards and ingestion, combined with per-query limits and scoped access.
- Per-query limits: memory and runtime ceilings stop a single exploratory scan from exhausting the server.
- Lower priority: analyst work yields to dashboards and ingestion during business hours and can use spare capacity overnight.
- Scoped access: roles and row policies limit which tables and rows each group can read, so the lane also acts as an access boundary.
- Surprise failures: analysts report queries stopped by limits they did not expect, which suggests the limits were set without a sample of real query shapes.
- Export pressure: dashboards slow down whenever a team runs bulk exports, a sign that exports need their own lane.
Review the query log for the users with the largest reads and confirm which limits and priorities apply to each group in your server version's documentation.
A shared multi-team cluster hosts several products or departments on one set of servers, usually with cost shared across them. Illustrative example: three product teams send events to the same cluster, and a backfill from one team slows the dashboards of the other two. The usual fit is one workload per team with agreed shares, a platform-owned ingestion lane, and settings profiles and quotas that define each group's defaults.
- Team boundaries: each team gets a workload with an agreed share of resources and a quota on query volume.
- Settings profiles: each group gets defaults that its members cannot override, which keeps limits consistent across teams. See the ClickHouse access control documentation for how profiles and quotas are defined.
- Shared ingestion ownership: a platform owner manages shared pipelines so one team's backfill cannot block the others.
- Peak disputes: teams argue over the same hours, or one team's workload consumes the capacity agreed for another.
- Quota surprises: a team reaches its daily quota with no advance warning or visibility into who used it.
Agree the split in writing, assign an owner for each lane, and review it after each major change in data volume or team count.
Scheduling options and when each fits
This table compares the main ways to keep ingestion, dashboards and ad hoc queries from competing for resources in self-managed ClickHouse. Read each row as a control with its own job, then match the workload mix you have to the option that protects it. Most deployments combine two or three of these controls rather than relying on one.
| Option | What it controls | When it fits | Trade-off to plan for |
|---|---|---|---|
| Workload hierarchy | Groups queries under parent and child workloads so a share of CPU, IO and memory can be assigned to each group | Several teams or pipelines share one cluster and each needs a guaranteed share of capacity | Hierarchy design takes planning, and every new workload needs a place in the tree before it can be governed |
| Weights | Sets the relative share of CPU or IO between sibling workloads when they compete for resources | Ingestion and dashboards are both busy and you want proportional sharing without hard caps | Weights only matter under contention, so an idle cluster hides their effect and load tests must simulate peaks |
| Priorities | Decides which pending work is served first when resources are scarce | Latency-sensitive dashboards must win over batch exports or backfills | Low-priority work can be starved for long periods if high-priority demand never drops |
| Concurrency limits | Caps how many queries of a given kind run at once | Ad hoc users can launch heavy queries and you need a hard ceiling on parallel load | Queries beyond the limit wait or fail, so users may see timeouts unless limits and client retry behavior are agreed |
| Query-level memory limits | Caps memory a single query can allocate and governs spilling to disk for large sorts and aggregations | A few expensive queries could exhaust server memory and crash or slow unrelated work | Tight limits make legitimate large queries fail, so limits need per-user or per-profile exceptions and monitoring |
| Settings profiles and quotas | Apply consistent query limits and usage ceilings to users and roles through access control | Many analysts share the cluster and you need the same limits everywhere without per-query tuning | Profiles are only as good as user assignment, and the default user starts with broad permissions unless restricted |
| Server-level resource settings | Bounds total threads and memory the server process uses for all queries together | A single node hosts mixed traffic and must stay responsive under peak load | Global settings affect every workload at once, so changes need a pre-production test with representative queries |
How to set up workload scheduling on a self-managed cluster
This sequence moves from measuring real query traffic to a tested scheduling policy, so ingestion, dashboards and ad hoc queries share one cluster without starving each other. Each step ends with a check you can repeat before changes reach production.
Inventory current query types from query log
The platform team pulls a representative window from system.query_log and groups statements by user, client application, query shape and time of day. Separate ingestion inserts, scheduled dashboard refreshes, interactive dashboard reads, ad hoc analyst queries and background maintenance such as merges and mutations. For each group, record peak concurrency, duration, memory and rows read. The step is done when every significant source maps to a named group. Check that the query counts across all groups add up to the total in the log for the same window.
Define the workload hierarchy
The platform team designs a parent workload with child workloads that mirror the groups from the inventory, such as ingestion, dashboards, ad hoc and maintenance. Each child needs a clear owner and a stated purpose, so later disputes about priority have a reference point. Confirm the CPU and IO scheduling resources your ClickHouse version supports, and check the workload and resource syntax against the documentation for that release. The step is done when the hierarchy is written as SQL and reviewed. Verify it by querying the system tables for workloads and resources and confirming every planned name exists exactly once.
Set weights and priorities for each workload
The platform team assigns weights to sibling workloads, which set each group's share when resources are contended, and uses priorities where one group should be served first. Keep the first version simple, with a small number of distinct weights, because each extra rule is harder to explain during an incident. Ask the business owners of dashboards and ingestion which latency and freshness they need during peak hours. The step is done when the values are documented next to the owners. Verify on a test cluster by running a heavy ad hoc workload alongside a dashboard workload and confirming the dashboard latency stays within the agreed expectation.
Assign workloads to users and queries
The security or platform engineer maps each service account and analyst role to a default workload, usually through a settings profile, and allows individual queries to override it with a workload setting where a team needs it. Restrict the default user first, because the access control documentation notes that it starts with all permissions. Limit who can change workload settings so that analysts cannot move their own queries into a higher priority group. The step is done when each account resolves to exactly one intended workload. Verify by running a test query as each account and checking the workload it actually used in the query log.
Apply concurrency and memory limits
The platform team sets per-query memory limits, concurrency caps and quotas for the groups most likely to overload the cluster, typically ad hoc analysis and bulk exports. Limits belong in settings profiles and quotas so they follow the account, not the client code. Agree with the owners what happens when a query is rejected, because a clear error message is easier for analysts to act on than a slow cluster. The step is done when each limit has a documented owner and reason. Verify by running a deliberately oversized test query and confirming it fails with the expected error while dashboard and ingestion checks still pass.
Test under realistic concurrency on pre-production
The platform team replays representative data and the mixed query profile from the inventory on a pre-production cluster, at peak-like concurrency and with ingestion running. The ClickHouse production guidance recommends a pre-production environment with representative data and queries, so use the same ClickHouse version as production, and check the changelog for that version before upgrading. Run the test once without scheduling and once with it, and compare dashboard latency, insert lag, error rates and memory use. The step is done when the results are reviewed by the platform, data and security owners. Verify that the production rollout plan repeats only the tested settings.
Failure modes that starve or mislead teams
These five risks share one pattern: a scheduling rule that looks correct on paper starves a workload, misleads a dashboard, or breaks quietly after a change. Each card names the symptom to watch for and the check that catches it before users do.
Misclassified queries
A dashboard query placed in the ingestion class, or a broad ad hoc scan placed in the dashboard class, takes resources from the workloads the rules were meant to protect. The symptom is dashboards that slow down only when one analyst runs a large query, even though total load looks normal. Check the workload recorded for each query in system.query_log against the rules you intended, and sample a representative period of traffic from each application user before you set any weights.
Ingestion falling behind
Low-priority ingestion is the most common casualty when interactive use takes most of the CPU. Inserts queue, parts accumulate and the gap between event time and visible data grows without any error being raised. Check consumer lag or insert latency per source, the count of active parts per partition, and whether lag recovers when interactive load drops. Alert on lag as well as on failed inserts.
Memory limits conflicting with scheduling
Scheduling decides which query gets CPU and IO first, but memory limits still apply per query and per server. Two queries that each fit their own limit can fail together under peak concurrency, and the failures often land in the protected workload. Check for memory exceptions in query logs, compare peak memory against the server limit at your busiest hour, and load test the workload settings on a copy of production-scale data.
Unclear ownership across teams
Weights, quotas and concurrency caps change the experience of every team sharing the cluster, yet often no one owns them. A platform engineer may tighten a limit to resolve one incident, and a data team may later raise it without knowing who depends on the change. Assign each workload a named owner, record every change with its reason, and review definitions alongside the roles and settings profiles that already control access.
Changes that break after upgrades
Scheduling settings and the behavior around them can change between releases, so a configuration that worked on one version may be ignored or rejected on the next. Teams that move across releases without reading the changelog often find the difference under live load. Check the release notes for every version on the upgrade path, rerun your workload tests on a pre-production cluster, and confirm your release line still receives security updates.
Questions teams ask before scheduling ClickHouse workloads
How much effort does ClickHouse workload scheduling take to set up?
The effort depends mostly on how many distinct workload types share the cluster, how large the cluster is, and how much test coverage the team needs before changing production settings. A cluster with two or three workload types and a small number of query patterns is a much smaller exercise than a shared cluster serving ingestion, scheduled reports, dashboards and analyst queries with different latency targets. Ask any partner to break the effort into design, configuration, testing and handover, and to state which of those steps is fixed in the written scope. No duration is given here because none has been researched for this topic, so confirm the estimate against your own cluster and query logs.
What drives the cost of isolating workloads in ClickHouse up or down?
Cost moves with four factors: the number of workload classes that need separate resource rules, the number of nodes or shards that must be configured consistently, the amount of representative load testing, and the number of teams who must agree on priorities. Each factor adds design review and verification time. One-time work covers the workload design, configuration and testing. Recurring work covers reviewing limits when query patterns change and when you upgrade ClickHouse. When you request a quote, ask for these items as separate lines so you can see which ones your team could take on.
Can our in-house team isolate ClickHouse workloads without a partner?
Yes, if you have engineers who can read ClickHouse system tables, change server and user configuration in version control, and test changes against realistic load. The harder part is usually not the syntax but agreeing on which workload deserves priority when resources run short, and who answers when a dashboard slows down during an ingestion peak. Teams with an operations group and a pre-production cluster often do the design in-house and bring in a partner for review or for a fixed-scope test cycle. A partner is most useful when the team lacks time to build and run the load tests.
What do you need from our team before the work starts?
Plan to provide the following before design begins:
- Query logs from a representative period, so the workload classes match real traffic rather than assumptions.
- Access to a pre-production ClickHouse cluster with data close to production in volume and shape.
- A named owner for each workload, such as ingestion, dashboards and ad hoc analysis, who can approve limits and service expectations.
- Read access to the current access control setup, including users, roles, settings profiles and quotas.
The ClickHouse production FAQ recommends a pre-production environment with representative data and queries, so this requirement comes from the vendor's own guidance.
How long does testing a workload scheduling change usually need?
The ClickHouse production FAQ recommends a pre-production environment with representative data and queries and reading the changelog before upgrading, but it does not give a testing duration, and none has been researched for this topic. The length depends on how many workload classes you test, how long a representative load pattern takes to replay, and whether you need to observe a full business cycle such as a month-end reporting run. Ask for the test plan, the load scenarios it covers and the exit criteria, then confirm the schedule against those items in your own environment.
What should a fixed-scope quote for workload scheduling include?
A fixed-scope quote should name the clusters and environments in scope, the workload classes and their owners, the resource and concurrency controls to be configured, and the test scenarios used to verify them. It should also list the deliverables, such as configuration files, a runbook for changing limits, and a handover session, plus the assumptions the schedule depends on, including access to query logs and a pre-production cluster. Ask what is excluded, how changes requested mid-project are priced, and whether ongoing review after the first release is a separate retainer. DeployOpen agrees scope, deliverables and timelines in writing before work starts.
How do ClickHouse upgrades affect workload scheduling settings?
Scheduling settings can change behavior between releases, so each upgrade needs a regression check against your workload classes. The ClickHouse security policy lists versions 26.9, 26.8, 26.7 and 26.3 as currently supported with security updates, and the production FAQ asks teams to read the changelog because minor backward-incompatible changes can occur. Confirm which release line your cluster runs, re-run the load scenarios after each upgrade in pre-production, and keep the scheduling configuration under version control so a rollback is quick.
What changes for workload settings across ClickHouse upgrades
Workload scheduling settings can behave differently after an upgrade, so every ClickHouse upgrade should include a rerun of your scheduling tests before the change reaches production. The ClickHouse production FAQ says stable releases ship roughly monthly, while LTS releases ship twice a year and are supported for a year after release. That cadence means your cluster will change several times a year, and each change can alter how resource limits, workload hierarchies, concurrency limits and priorities are applied. The FAQ also notes that minor backward-incompatible changes can occur, so a patch-level or minor move is not automatically safe for the controls that protect your dashboards from ingestion spikes.
The production FAQ recommends running a pre-production environment with representative data and queries, and reading the changelog before every upgrade. For this topic, the changelog review should be specific: check release notes for changes to scheduling settings, workload and resource definitions, query admission behavior, and the system tables you use to confirm that a workload is being throttled as intended. Ask whether any setting names, defaults or precedence rules have changed. Record the result in the upgrade plan so the person approving the change can see which scheduling controls were checked and which were not.
Rerun the scheduling tests as a fixed step after each upgrade, not only after major releases. A useful test set reproduces a heavy ingestion batch, a dashboard refresh burst and a long ad hoc query at the same time, then compares latency and throughput with the baseline from the previous version. If a test that passed before now shows one workload taking more than its share, treat it as a blocking finding. Keep the test scripts under version control so they can be run against a copy of the cluster, and confirm with your deployment team how a failed test blocks rollout across replicas.
How DeployOpen scopes and delivers ClickHouse workload scheduling
DeployOpen starts with the workloads that compete on your cluster and the service levels each one needs, then writes a fixed scope for the scheduling design, its configuration and its verification. The work runs inside your own infrastructure, and the handover leaves your team able to change weights, priorities and limits without us.
How we scope
- We inventory ingestion, dashboard and ad hoc query patterns, with their peak times and the users who depend on each.
- The written scope names the CPU, IO and memory controls to configure, and the workload hierarchy each one sits in.
- We agree which failure modes the scope must prevent, such as ingestion lag during a dashboard spike, and how each will be tested.
How we build
- We set up workload and resource definitions on a copy of your cluster using representative data and realistic query mixes.
- We configure concurrency limits, weights and priorities, then test them under simulated contention before anything reaches production.
- Changes to settings profiles and users go through your change process, with each configuration kept in version control.
How we hand over
- You receive the workload hierarchy, configuration files and a runbook explaining how each control behaves under load.
- We document the system tables and metrics your operators should watch to confirm each workload gets its intended share.
- Your team runs a handover session where they change a weight or limit in a test environment and verify the result.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after the fixed-scope delivery ends.
- Retainers can cover re-checking scheduling settings after ClickHouse version upgrades, since behavior may change between releases.
- Ask any quote to list who performs each task, what recurs after handover, and what your own team must provide.
