Per-principal query memory limits

QuestDB Enterprise can limit the native memory tracked for a single query, overriding the server-wide query memory limit for a specific user, group, or service account. Use it to help prevent one tenant's runaway query from exhausting shared memory, or to grant a trusted principal more headroom than the default. Per-principal limits are available since QuestDB Enterprise 4.0.2; the server-wide workload limits they override are available since QuestDB 10.0.0.

Set a limit with ALTER USER, ALTER GROUP, or ALTER SERVICE ACCOUNT:

ALTER USER john SET MEMORY LIMIT 512M;
ALTER GROUP analysts SET MEMORY LIMIT 2G;
ALTER SERVICE ACCOUNT ingest_app SET MEMORY LIMIT 1G;
ALTER USER john SET MEMORY LIMIT UNLIMITED;  -- clear the user's own limit

The value is a byte count or a size with a K, M, or G suffix. Each suffix multiplies by 1024, so 512M is 536870912 bytes. Setting a limit requires the SET MEMORY LIMIT permission, which is included in GRANT ALL and held implicitly by database admins.

warning

Treat SET MEMORY LIMIT as an administrative permission, not a self-service one. It takes no entity name, so its holder can set the limit of any principal, including its own. Because a per-principal limit overrides the workload limit rather than tightening it (see How limits resolve), a non-admin who holds it can raise its own ceiling above cairo.query.memory.limit.bytes. The built-in admin is exempt only as a target: its limit cannot be set at all.

GRANT ALL expands to individual permissions at the moment it is granted. A principal granted ALL before upgrading to a version with this feature does not acquire SET MEMORY LIMIT; only grants issued after the upgrade include it, and no migration backfills it. Grant it explicitly to existing administrators: GRANT SET MEMORY LIMIT TO admins;.

How limits resolve

QuestDB resolves the limit for a query by strict precedence. It takes the first level that is set, not the smallest across levels:

1. The principal's own limit. For a user this is the user's own limit; for a query that assumes a service account it is the service account's limit.
2. A group limit (users only). When a user has no limit of its own, it inherits the most restrictive (smallest positive) limit among the groups it belongs to. Service accounts never inherit group limits.
3. The workload limit. Otherwise the server-wide cairo.query.memory.limit.bytes applies.

A value of 0 (or UNLIMITED) means "not set" at that level, so resolution falls through to the next one. A more specific level, when set, fully overrides the broader one and binds even when it is larger, so a per-user or per-group override can raise a principal's ceiling above the workload limit, not only lower it. A user who assumes a service account takes on the service account's limit. The built-in admin cannot be given a limit, so ALTER USER admin SET MEMORY LIMIT is rejected with Cannot set memory limit for built-in admin, and the admin runs under the workload limit. Size that limit with the admin's diagnostic queries in mind.

The cap applies to the principal's queries on both the primary and replicas. The statement itself runs on the primary only: a replica rejects it with replica cannot set memory limit and receives the new value through the replicated ACL tables. A changed limit applies to the principal's next query, including on connections that are already open, while a query already running keeps the limit it started with.

A query that crosses its limit fails with the same query memory limit exceeded [workload=QUERY, ...] error as a breach of the workload limit. See memory limits for the message format.

note

A limit bounds a single query. Two concurrent queries by the same principal each run under the full limit, so the principal's aggregate usage can exceed it. The cap guards against one runaway workload, not total concurrent usage.

What a per-principal limit covers

Per-principal limits have the same coverage as workload limits. They apply to tracked native allocations for:

• The principal's queries.
• Its background COPY ... TO exports, which use the issuing principal's limit. Some memory used to produce the export file is not yet covered, and exports do not appear in query_activity, so their usage cannot be observed while they run.
• UPDATE on a non-WAL table, which is applied on the caller's own thread and acquires its own query-workload tracker.

It does not reach work that runs under an internal context rather than a principal. That work stays bounded only by its own workload limit:

• UPDATE on a WAL table, the default table type, because the statement is applied by the WAL apply job and draws on that job's cairo.wal.apply.memory.limit.bytes budget instead. Whether a large UPDATE is capped by a SET MEMORY LIMIT override therefore depends on the table type. A WAL-table UPDATE that breaches the WAL apply limit suspends the table for every principal until ALTER TABLE RESUME WAL, so the failure is not isolated to the tenant that issued it.
• Materialized view refresh, live view refresh, and WAL apply itself. A WAL apply batches many principals' transactions into one tracker, so it could not attribute usage to a single principal in any case.
• COPY ... FROM imports, which acquire no memory tracker at all and are unaffected by either kind of limit.

note

An external (SSO/OIDC) user can only receive a limit by inheriting one from a mapped group: ALTER USER ... SET MEMORY LIMIT on an external user is rejected with Cannot set memory limit for external user. The user receives its inherited group limit at login. On the primary, a later ALTER GROUP ... SET MEMORY LIMIT also reaches the user's open sessions at their next query. On a replica, and on the primary after a restart or promotion, an existing external session keeps the limit it logged in with until it reconnects. See Mapping groups and permissions for the external alias flow.

Inspecting limits

• SHOW USERS, SHOW GROUPS, and SHOW SERVICE ACCOUNTS report per-principal limits in a memory_limit column, in bytes. For users, it is the user's own limit or, when it has none, the most restrictive of its groups'. For groups and service accounts, it is the entity's own limit. The column excludes the server-wide workload limit. null means the entity has no limit of its own and, for a user, no inherited one either, so the workload limit applies to its queries.
• The filtered forms SHOW GROUPS userName and SHOW SERVICE ACCOUNTS { userName | groupName } carry the column too, reporting each listed group's or service account's own limit. This shows which inherited limit binds for a user with no limit of its own.
• query_activity exposes the effective limit and live usage of each running query through its memory_limit and memory_used columns.
• SHOW USER does not carry the column. The unfiltered SHOW USERS, SHOW GROUPS, and SHOW SERVICE ACCOUNTS require LIST USERS; the filtered forms require USER DETAILS unless the caller names itself or one of its own groups. A user without LIST USERS can still read its own effective cap from the memory_limit column of query_activity, which always lists the caller's own queries.
• The stored value is persisted on the sys.acl_entities system table. That table is protected: only the built-in admin can read it, and an ACL principal holding DATABASE ADMIN is still denied.

Upgrading

Breaking change

memory_limit is appended as the last column of SHOW USERS, SHOW GROUPS, and SHOW SERVICE ACCOUNTS, including their filtered forms, whether or not any limit is set. SELECT * on sys.acl_entities returns one more column as well. Clients that read these results by position must be updated; clients that read by column name are unaffected.

The SHOW results carry the column from the first start of the upgraded binary, reading null until limits are set. The sys.acl_entities column that stores the value is added by an automatic migration when an upgraded node first starts as a primary or is promoted from replica to primary. Persisted principal limits are not enforced until the migration has been applied. The migration passes through two windows, and each refuses a different set of statements:

• Before the column exists, a SET MEMORY LIMIT with a non-zero size is refused with:

  Cannot modify ACL entities: the memory_limit column has not been migrated in yet; retry shortly, or restart the node if it persists

  SET MEMORY LIMIT UNLIMITED and ALTER ... ENABLE or DISABLE still succeed.

• Once the column has been added but WAL apply has not yet reached it, SET MEMORY LIMIT UNLIMITED and ALTER ... ENABLE or DISABLE are refused as well, so that a stale in-memory value cannot overwrite a stored limit. A non-zero SET MEMORY LIMIT keeps the first message; the others read:

  Cannot set memory limit while the ACL memory_limit column migration is still being applied, retry [name=john]

  or Cannot enable while ... and Cannot disable while ... for a status change.

The window normally closes on its own once WAL apply catches up, so retry the statement first. If the error persists, restart the node: the migration runs again at startup.