ClickHouse quotas and settings profiles

Why ClickHouse quotas and settings profiles matter on a shared cluster

Quotas limit how much work a user can trigger inside a time window, and settings profiles with constraints fix the execution settings a user is not allowed to change. On a shared self-managed ClickHouse cluster, these two controls decide whether one heavy dashboard, an ad hoc analyst query or a backfilling ingestion job can starve everyone else of memory and CPU. Without them, every account can run as large a query as the hardware allows, and the first runaway report becomes an operations incident rather than a planning question.

Both controls sit inside ClickHouse access control, alongside users, roles and row policies. Quotas count queries, rows read and execution time per user over intervals you define, while settings profiles set values such as memory limits and timeouts and can mark them as read-only or bound them with minimum and maximum constraints. The access control documentation is the reference to confirm exact object names, quota keys and syntax for the version you run, because the available settings and interval options can change between releases. Check them in a pre-production environment before you apply them to production accounts.

Start by listing your workload classes: dashboards, interactive analysts, scheduled ingestion and administrators. Decide which of them can exceed a limit and who approves that exception, and confirm that the default user, which starts with broad permissions, is no longer used by these workloads. Then test each profile with a query that should fail and one that should pass, and record which accounts are exempt. A quota that nobody tests is usually discovered during an outage, so plan the test as part of the rollout rather than as an afterthought.

Quota, profile and constraint options and when each fits

This table compares quotas, settings profiles, constraints and profile inheritance by the limit each one places on a user, role or query. Read each row against your workload: quotas bound totals over an interval, while profiles and constraints shape individual queries and settings. Confirm exact quota keys and setting names against the access control documentation for the ClickHouse release you deploy.

Control	What it limits	Workload it fits	Trade-off to weigh
Quota per user or role	Query count, rows read and execution time summed over a defined interval	Shared analyst or BI accounts where total consumption matters more than any single query	Limits reset at interval boundaries, so a burst can still hit heavy load within one window and a long job may stop mid-interval
Settings profile defaults	Default values for memory, timeouts and other settings applied when a user connects	Separating interactive dashboards from batch ingestion or reporting roles	Defaults can be overridden by the user unless a constraint also applies, so they guide behavior but do not enforce it
Constraint on a setting	Minimum, maximum or fixed value a user may set for a named setting	Locking memory limits or timeouts so a single query cannot exceed an approved ceiling	Each constraint must be checked per setting; an overly tight value can break legitimate queries, so test with representative workloads
Profile inheritance across roles	Settings and constraints a role inherits from one or more parent profiles	Building a small set of tiers, such as analyst, engineer and admin, without repeating values	Inheritance order and conflicts can be hard to trace; review effective settings with SHOW ACCESS and test each role
Quota with constraints combined	Interval totals plus per-query ceilings	Public-facing or multi-tenant query access where both total load and single runaway queries matter	More controls mean more tuning; document the reason for each value so reviews can judge whether it still fits
Rely on server-wide settings only	Limits applied to all users equally	Small environments with one trusted user group	Cannot differentiate between users, so one heavy workload can affect everyone; usually insufficient for regulated multi-team access

How quotas, profiles and constraints combine at query time

A user's effective limits come from the roles granted to that user, and the profiles and quotas attached to those roles. When a query arrives, ClickHouse resolves the authenticated identity to its granted profiles, which supply the settings in effect for that session, such as memory caps and execution time limits. A constraint attached to a profile then governs any value the user tries to set: if the user attempts a value outside the allowed range or a read-only setting, the statement is rejected rather than silently adjusted. Quotas run alongside this. Each quota counts usage across its interval, and once a limit is reached, further work is blocked until the interval resets. Confirm on your version exactly how each setting and quota is inherited when a user holds several roles, because overlapping grants are where unexpected limits usually come from.

Queries already running when a quota is exhausted are a separate question from new ones. Limits are checked as work is admitted and accounted, so the practical effect on in-flight queries depends on which limit was hit and on the version in use. Test this directly: start a long query under a low limit, then submit a second query after the threshold is crossed, and record the exact error code, the message text and whether the first query is interrupted. Do the same for the reset behavior, because the time at which a blocked user can query again is what your runbooks and alerts will depend on.

The interval length of a quota decides how a burst is treated. A short interval allows a heavy user to consume a large share of capacity quickly and then be cut off, while a long interval smooths usage but lets one expensive ingestion or analyst session drain the budget for the rest of the period. Choose the interval to match how your teams actually query, and document the choice with the owners of each workload. Confirm the interval semantics, the counted resources and the reset timing in the access control documentation for your release before you publish limits to users.

Choosing limits for analysts, dashboards, ingestion and admins

Analysts write ad hoc queries, often exploring unfamiliar tables and sometimes running a broad scan by accident. The limits that matter most here are execution time, rows read and memory per query, because a single unbounded SELECT over a large event table can consume a node's resources and slow everyone else. Query counts matter less for this group than for dashboards, since a human rarely issues hundreds of queries an hour.

Execution time: cap max_execution_time in a profile so a runaway scan stops instead of holding resources for hours.
Rows and bytes read: set max_rows_to_read or max_bytes_to_read to catch queries that lack a filter on the ordering key.
Memory per query: cap max_memory_usage so one aggregation cannot exhaust server memory.
Trade-off: tight caps block legitimate exploratory work, so give senior analysts a separate profile with higher ceilings rather than loosening the default.
Feedback: return a clear error message to users and document how to narrow a query or request a higher limit.

Before you choose numbers, sample the query log for a representative week and compare the slowest and largest queries with the limits you plan to set.",

Ingestion and pipeline service accounts write data continuously, and their workload is predictable in shape but can spike during backfills or replays. Limits on these accounts protect query users from write storms and prevent a broken pipeline from consuming memory or exhausting the connection pool. Read limits are usually less relevant, but a pipeline that reads back large ranges for validation may still need a ceiling.

Memory caps: set max_memory_usage high enough for batch inserts and merges triggered by the pipeline, but low enough to protect the node.
Query counts: quotas on statements per interval help catch loops that retry failing inserts too quickly.
Execution time: allow longer timeouts for backfills, or give them a separate profile that operators enable for a window.
Scope: grant insert rights only to the tables the pipeline writes, so a limit breach does not reach other data.
Trade-off: generous limits on ingestion can hide a bad batch size, so monitor insert throughput and failures alongside the caps.

Ask your pipeline owners for their expected peak batch sizes and retry behavior, then test the limits against a replay of a real backfill.",

Administrators need broad access to diagnose incidents, run maintenance and manage users, so they should not share the limits applied to analysts. The risk here is the reverse: an admin account with no limits can run a heavy diagnostic query during an incident and worsen the outage. Separating admin duties from routine query work, and keeping the admin profile tightly controlled, is usually more important than the numeric values.

Separate accounts: use a named admin account for maintenance, distinct from any account used for daily analysis.
Lighter caps, not none: give admins a higher but still bounded profile so a mistaken SELECT on a huge table is interrupted.
Constraint locking: use settings constraints so admins cannot silently raise the limits applied to other roles.
Break-glass access: document how an admin temporarily lifts a limit during an incident and how the change is reverted and reviewed.
Trade-off: the more exceptions you allow for admins, the harder it is to audit who bypassed a limit, so log settings changes.

Review the admin role membership and profile assignments on a fixed schedule, and confirm the break-glass procedure with the team that will use it during an incident.

Setting up ClickHouse quotas and settings profiles step by step

This sequence moves a self-managed ClickHouse cluster from open access to named profiles and quotas that cap what each workload can consume, with a check at each stage that proves the limits hold.

Inventory users, roles and their workloads
Before creating anything, list every account on the cluster and the work it does: dashboards, ingestion services, analysts, scheduled jobs and administrators. Record which accounts are shared by several teams, which run long exploratory queries and which must never be throttled. Use SHOW USERS and SHOW ROLES to capture the current state, and note which grants each account holds. The output is a written table that maps each workload to a proposed role, so limits are set per workload rather than guessed per person. Ask your team to confirm the list with each application owner before you continue.
Restrict the default user first
Confirm the default user is locked down before you add limits, because it starts with all permissions and any unthrottled session can bypass the profiles you are about to build. Follow the access control guidance to set a password or disable the account, and create a named administrator through SQL. Then run SHOW GRANTS FOR default and SHOW CREATE USER default to verify the change. The check passes when an unauthenticated or default login is rejected and the named administrator can still manage access.
Create named settings profiles with memory and timeout values
Create one settings profile per workload class using CREATE SETTINGS PROFILE in SQL, setting values such as max_memory_usage, max_execution_time and max_rows_to_read that match the inventory. Keep dashboard, ingestion and analyst profiles separate so each can be tuned without affecting the others. Choose the numbers from measured query behavior on a pre-production cluster with representative data, not from defaults. The check is SHOW CREATE SETTINGS PROFILE for each profile, confirming the values you intended are stored exactly as written.
Add constraints that block users from raising limits
Attach constraints to each profile so a session cannot override the values with its own SET statement. Use MIN, MAX, READONLY or CHANGEABLE_IN_READONLY constraints on memory and timeout settings, and test the boundaries you choose. The check is to open a session as a test analyst, attempt SET max_memory_usage to a value above the ceiling, and confirm the server returns a constraint violation error instead of accepting the change.
Define quotas per role with interval limits
Create quotas with CREATE QUOTA that limit queries, rows read, result rows and execution time over defined intervals such as an hour or a day. Keep the quota scope aligned with the roles from step one, so an analyst role has its own budget separate from ingestion. Review the quota's keys and intervals with your team, because a shared quota across services can throttle one application because of another. The check is SHOW CREATE QUOTA and a comparison of the stored limits against the table you wrote in step one.
Assign profiles and quotas to roles, not individuals
Grant each settings profile and quota to a role, then assign that role to users, so a new analyst inherits the correct limits without manual changes. Use SQL GRANT and SETTINGS PROFILE clauses on the role, and avoid attaching per-user overrides unless a documented exception requires one. The check is SHOW GRANTS FOR role and SHOW ACCESS to confirm each user lists the expected role, and then SHOW CURRENT QUOTA as that user to see the active limits and consumption.
Test a deliberately large query under a test account
Create a test account in the analyst role and run a query designed to exceed the memory, row or time limit, such as a full scan of a large table with a low max_rows_to_read value. Confirm the server stops the query and returns the expected error, and that the message names the setting that triggered it. Record the error text in your runbook so operators can recognize it later. This is the step that proves the profiles and constraints work end to end, so do not mark the rollout complete until it passes.
Review limits against the query log and document exceptions
After workloads run for a representative period, review the query log and quota consumption to see which roles hit their limits and whether legitimate jobs are being cut off. Adjust values through versioned SQL changes, and record each exception with its owner and reason. Store the final CREATE statements in source control so a rebuild or upgrade restores the same profiles, quotas and roles. The check is a clean diff between the stored statements and SHOW CREATE output on the live cluster.

Testing limits before they reach production users

Validate every quota and settings profile on a pre-production cluster before any production user meets it. ClickHouse's production guidance recommends a pre-production environment with representative data and queries, and limits are where that recommendation pays off, because a cap that looks reasonable on paper can block a dashboard refresh that nobody modeled. Start by inventorying the workloads each user group actually runs: scheduled ingestion, analyst ad hoc queries, dashboard panels from tools such as Grafana or Metabase, and any batch exports. For each workload, record typical and peak execution time, rows read and result sizes from the system.query_log table on the test cluster, using a copy of production-shaped data rather than a small sample. Peak values matter more than averages, because the month-end report or the first query after a deployment is the one that trips a limit.

Set limits with headroom above the measured peaks, and treat the headroom figure as a decision for your team, not a number this page supplies. Confirm the exact quota and constraint semantics against the access control documentation for your release before you write them, since the behavior of each field is version-specific. Then rehearse a breach deliberately. Run a query designed to exceed the execution time or rows read limit for a test user, record the exact error code and message that the client library returns, and check that your application, notebook and alerting handle it sensibly. A team that has seen the error in rehearsal will not mistake a capped query for a database outage at 2 a.m.

Limits are not one-time work. Re-review them whenever a schema changes, a materialized view or projection is added, a dashboard is rebuilt, or a new data source is onboarded, because each of these can shift rows read or memory use without anyone touching the quota. Make limit changes go through the same change record as schema changes, and rerun the workload measurements on the pre-production cluster before raising a cap. Also confirm that the pre-production cluster's release matches production within your upgrade policy, since a version difference can change how queries are costed. Ask your implementation partner to include these measurement and rehearsal steps in the written scope, so the work is priced and delivered rather than left to the first incident.

Security gaps that defeat quotas and profiles

Quotas and settings profiles only limit what they are attached to. The gaps below usually come from accounts that sit outside those limits, from overrides nobody reviewed, or from access paths that skip the SQL session entirely. Each card names the mitigation and the audit step to run against your own cluster.

Default user starts with full permissions

The default user begins with all permissions, so any client that connects as it can run queries outside the limits you assigned to named accounts. Disable it or give it a password and restrict its network access, then move workloads to named users. Audit by listing the default user's grants with SHOW GRANTS FOR default and confirming that no application connects with it.

Users created without a profile or quota

A user created without a settings profile or quota inherits no caps on memory, execution time or rows read, so one runaway dashboard can starve the cluster. Make a profile and a quota part of the standard user template, and reject creation requests that skip them. Audit by listing users and their assigned profiles and quotas through the access system tables and confirming each one has both.

Users can override constraints

A profile sets a value, but if the user is not also bound by a constraint, they can change that value in their session with a SET statement. Pair every locked setting, such as max_memory_usage or max_execution_time, with a constraint that sets its allowed range or read-only status. Audit by testing the constraint from a restricted account and confirming that an out-of-range SET is rejected.

Shared service accounts hide the workload

When several pipelines, BI tools and jobs connect with one shared account, quota usage cannot tell you which workload consumed the budget. Give each application or team its own user with a dedicated profile and quota. Audit by checking whether any account serves more than one workload, and by reviewing the query log to confirm each query is attributable to a single user.

Admin accounts exempted without review

Administrators often get broad or unlimited profiles so that maintenance is not blocked, and those exemptions tend to stay after the work is done. Keep admin accounts separate from daily use, document each exemption with an owner and a review date, and remove it when the task ends. Audit by listing every account whose profile or quota lacks caps and confirming that each one appears on the approved exemption list.

Limits skip other access paths

Quotas and profiles apply to the sessions they govern, so access through other interfaces the cluster exposes may not be bound by the same limits. Confirm which protocols and ports are open, including HTTP, the native protocol and any emulation ports, and check that each path authenticates a user with the expected profile. Audit by running the same heavy query over every enabled interface as a restricted user and comparing the results to the limits you expect.

How much effort and cost does it take to set quotas and profiles?

What does the effort for quotas and settings profiles depend on?

The effort depends mostly on how many users, roles and workloads the cluster serves, and on how much profiling already exists. A cluster with a few service accounts and one dashboard tool is a small exercise. A cluster with analysts, ingestion jobs, embedded application queries and ad hoc exports needs each group measured before any limit is written. Teams that already have query logs grouped by user and by application can move faster, because the first task is reading data rather than collecting it. Ask any provider to state how many principals are in scope, which workloads they belong to, and whether the profiling work is included.

Can an internal team set up quotas and profiles without outside help?

Yes, a team with ClickHouse operations experience can do it, and the access control model is driven by SQL statements, so the work is familiar to database engineers. The harder part is judgment: deciding what a normal query looks like for each workload, and testing that a limit does not break a legitimate dashboard. Teams without a pre-production cluster or without a named owner for each workload usually find this slow. An outside partner adds value mainly through a written test plan, a review of the limits, and a second pair of eyes on the settings that lock memory and timeouts.

What recurring work follows after limits are in place?

Limits need review whenever the data or the consumers change. A new table, a larger dashboard, a new ingestion pipeline or a schema change can make a previously safe row limit too tight or too loose. Recurring work usually includes reviewing the quota usage and the query log for rejected or near-limit queries, checking that settings profiles still match the workloads they serve, and confirming that new users are assigned to the correct profile. Ask the provider whether these reviews are part of a retainer or a one-time handover, and how often they are scheduled.

What does the client need to provide before the work starts?

The work needs query logs covering a representative period, a list of users, roles and service accounts with their owners, and a named contact for each workload that will be affected. A pre-production ClickHouse cluster with representative data and queries is strongly recommended, because limits tested only in production can cause outages for analysts. The client should also confirm the current state of the default user and any existing users.xml configuration, since those affect how profiles and quotas are applied. Missing query logs or missing owners are the most common reasons a scope grows.

How is a partner quote built from a written scope?

A quote should follow from a written scope that lists the principals in scope, the workloads they belong to, the profiling work, the settings to be locked, the quota windows to be defined, the test cycles on the pre-production cluster, the rollout steps and the handover documentation. Each item should be priced or estimated separately so the client can see which parts are one-time and which recur. DeployOpen does not publish a price list. A quote is produced from the agreed scope, and the scope and deliverables are normally confirmed in writing before work begins.

What should a quote itemize for quotas and profiles?

A useful quote separates the discovery work, the design of profiles and quotas, the testing on a non-production cluster, the production rollout, knowledge transfer and any recurring review. For each part it should state who performs the work, what artifacts are delivered, such as SQL scripts or a runbook, and what assumptions are made about the cluster. Ask whether the quote covers changes after go-live, how new users are onboarded, and what happens if a workload owner requests a higher limit. Any figure should be traceable to the scope, not to a generic package.

Does a quota or profile change need a maintenance window?

Access control changes are normally made with SQL statements and do not require a full cluster restart, but the effect on running workloads must still be checked. A tighter limit can interrupt a job that worked yesterday, so changes should be rolled out first to a pre-production cluster and then to production with the affected owners informed. Ask the provider how changes will be scheduled, who approves them, and how a rollback will be performed if a limit blocks a critical query.

Limits to confirm in your environment before rollout

Quotas and settings profiles only protect you if they behave as expected on your release, your topology and your client tools. The cards below list the checks to run in a pre-production environment with representative queries before any limit reaches a production user.

Exact setting names on your release

Quota fields and profile constraints are named per release, and a name that works on one line may be absent or behave differently on another. Check the names and their semantics against the reference for the exact version you run, and test each limit with a query that should breach it. Record the version alongside the configuration so a later upgrade can be compared against it. Check the documentation for access control before you write the first quota.

Limits across replicas and distributed queries

A query that fans out through a Distributed table or runs against a replicated shard can count differently at the node that receives it than at the nodes that do the work. Confirm which node enforces each limit, whether the counters are per node or cluster-wide, and what happens when a client connects to a different replica after failover. Run a distributed test query on a multi-node pre-production cluster and compare the counters on each node.

Error surfacing through the client stack

When a quota or constraint blocks a query, the error text reaches the user only if every layer passes it through. Load balancers, proxies, JDBC and ODBC drivers, and BI tools may retry, wrap or hide the original code. Test each client your users run, including the HTTP and native paths, and confirm that the error reaches logs and the user with enough detail to act on.

Session settings that override profiles

Dashboards, ETL jobs and BI connectors often send their own session settings on connect, such as a higher max_execution_time or a changed memory limit. Inventory every service account and connector, capture the settings each one sends, and check whether a profile constraint blocks or silently overrides them. Ask the owners of those tools whether a failed session setting will stop their jobs or fall back to defaults.

Access control changes across upgrades

Each upgrade can change how access control, quotas and profiles are evaluated, so a configuration that passed in pre-production may behave differently after a version jump. Read the release changelog for every version between your current and target release before you schedule the change, and rerun your limit tests against the new build. Keep a copy of your access definitions with SHOW ACCESS output so you can compare before and after.

Security update coverage for your line

Limits are only one layer, and an unsupported build leaves known defects in place. The security policy lists the versions that currently receive security updates, and all other versions are treated as unsupported. Confirm that your running line appears on that list, and set a review date that matches the support window of the LTS or stable line you choose. Check the policy again before each planned upgrade.

How DeployOpen scopes and delivers ClickHouse access limits

DeployOpen approaches quotas and settings profiles as part of the wider access model for your ClickHouse cluster, not as a standalone configuration task. We agree what each user group may consume and what must stay locked before any change reaches your servers, inside infrastructure you control.

How we scope

Inventory every user, service account and application that queries the cluster, with its expected query patterns
Agree written limits for query counts, rows read and execution time per user or role
Define which memory and timeout settings are locked by profiles and which constraints users may still adjust

How we build

Create quotas and settings profiles through SQL-driven access control, with changes tracked in version control
Test each limit on a pre-production cluster with representative data and queries before any production change
Confirm that the default user and any legacy users.xml entries cannot bypass the new quotas and profiles

How we hand over

Document each quota, profile, constraint and the user groups they apply to, with the reason for each value
Provide review queries for checking current quota usage and assigned profiles during access reviews
Run a walkthrough with your platform and security teams covering how to request and approve limit changes

How we support

Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts
Ongoing support, upgrades and continuous testing are available as a retainer for limit changes after handover
Revisit quotas when workloads, user groups or ClickHouse release lines change, following the upgrade notes

Limiting ClickHouse users with quotas and settings profiles