# Cairo engine

> **For AI agents:** the complete documentation index is at [llms.txt](https://questdb.com/docs/llms.txt). Every page is available as markdown by appending `.md` to its URL, or by sending an `Accept: text/markdown` request header.

Configuration settings for the Cairo SQL engine in QuestDB, including the query, materialized view refresh, and WAL apply memory limits.

The Cairo engine is the core storage and query engine in QuestDB. These settings
control how data is written, read, indexed, and queried. Most defaults work well
for typical workloads, but tuning may be needed for high-throughput ingestion,
large analytical queries, or specific storage configurations.

To cap the native memory a single query, view refresh, or WAL apply batch may
allocate, see [Memory limits](#memory-limits).

## General

### cairo.date.locale

- **Default**: `en`
- **Reloadable**: no

The locale used to handle date types.

### cairo.root

- **Default**: `db`
- **Reloadable**: no

Directory for storing database tables and metadata. This directory is relative
to the server root directory provided at startup.

If you set it to an absolute path, QuestDB no longer derives its other
directories from the server root directory. The `conf`, `import`, `export`,
`tmp` and `.checkpoint` directories are then created as siblings of the
directory you specify, rather than as children of the server root directory.
Under Docker this places them outside the mounted volume, so leave `cairo.root`
at its default and change the volume mapping instead.

### cairo.system.table.prefix

- **Default**: `sys.`
- **Reloadable**: no

Prefix for QuestDB internal data storage tables. These tables are hidden from
the web console.

### cairo.timestamp.locale

- **Default**: `en`
- **Reloadable**: no

The locale used to handle timestamp types.

### config.reload.enabled

- **Default**: `true`
- **Reloadable**: no

When `false`, disables the `reload_config()` SQL function.

### query.timeout

- **Default**: `60s`
- **Reloadable**: no

A global timeout for long-running queries, given as a duration: `500ms`, `120s`,
`2m` and `1h` are all valid, and a plain number is read as milliseconds.

This key replaces `query.timeout.sec`. When both are set, `query.timeout` takes
precedence; when neither is set, queries time out after 60 seconds.

Per-query overrides are available via the HTTP header
[`Statement-Timeout`](/docs/connect/compatibility/rest-api/#headers) or the
Postgres [`options`](/docs/connect/compatibility/pgwire/overview/)
connection property.

### query.timeout.sec

- **Default**: `60`
- **Reloadable**: no

:::note

`query.timeout.sec` is deprecated. Use `query.timeout` instead, which takes a
duration rather than a whole number of seconds. QuestDB reports this key as a
deprecation advisory during config validation at startup.

:::

A global timeout in seconds for long-running queries. When `query.timeout` is
also set, it takes precedence and this key is ignored. Per-query overrides work
the same as for `query.timeout`.

## Commit and write behavior

### cairo.commit.mode

- **Default**: `nosync`
- **Reloadable**: no

How changes are flushed to disk upon commit. Options:

- `nosync`: no explicit flush (relies on OS page cache)
- `async`: flush call is scheduled but returns immediately
- `sync`: waits for flush on appended column files to complete

### cairo.max.uncommitted.rows

- **Default**: `500000`
- **Reloadable**: no

Maximum number of uncommitted rows per table. When pending rows reach this
threshold, a commit is issued automatically.

### cairo.wal.enabled.default

- **Default**: `true`
- **Reloadable**: no

Whether WAL tables are the default when using `CREATE TABLE`.

## Writer settings

### cairo.system.writer.data.append.page.size

- **Default**: `256k`
- **Reloadable**: no

mmap sliding page size that the table writer uses to append data for each
column, specifically for system tables.

### cairo.write.back.off.timeout.on.mem.pressure

- **Default**: `4000`
- **Reloadable**: no

Upper bound, in milliseconds, of the random delay a WAL apply job waits before
retrying a batch that failed with an out-of-memory error, once it has already
reduced its parallelism to one. Up to five such back-offs are attempted; if the
error persists, the table is suspended. See [memory limits](#memory-limits).

### cairo.writer.alter.busy.wait.timeout

- **Default**: `500`
- **Reloadable**: no

Maximum wait timeout in milliseconds for `ALTER TABLE` statements executed
via REST or PostgreSQL wire protocol when execution is asynchronous.

### cairo.writer.command.queue.capacity

- **Default**: `32`
- **Reloadable**: no

Maximum capacity of the writer ALTER TABLE and replication command queue.
Shared between all tables.

### cairo.writer.data.append.page.size

- **Default**: `16M`
- **Reloadable**: no

mmap sliding page size that the table writer uses to append data for each
column.

### cairo.writer.data.index.key.append.page.size

- **Default**: `512K`
- **Reloadable**: no

mmap page size for appending index key data. Key data is the number of
distinct symbol values times 4 bytes.

### cairo.writer.data.index.value.append.page.size

- **Default**: `16M`
- **Reloadable**: no

mmap page size for appending index value data.

### cairo.writer.misc.append.page.size

- **Default**: `4K`
- **Reloadable**: no

mmap page size for mapping small files. Default is the OS page size (4KB
on Linux, 64KB on Windows, 16KB on macOS Apple Silicon). Overriding this
rounds up to the nearest multiple of the OS page size.

### cairo.writer.tick.rows.count

- **Default**: `1024`
- **Reloadable**: no

How often the writer checks its command queue during busy writes, measured
in rows written.

## Reader and writer pools

### cairo.idle.check.interval

- **Default**: `300000`
- **Reloadable**: no

Frequency of the writer maintenance job in milliseconds.

### cairo.inactive.reader.ttl

- **Default**: `120000`
- **Reloadable**: no

Time-to-live in milliseconds before closing inactive readers.

### cairo.inactive.writer.ttl

- **Default**: `600000`
- **Reloadable**: no

Time-to-live in milliseconds before closing inactive writers.

### cairo.reader.pool.max.segments

- **Default**: `10`
- **Reloadable**: no

Number of segments in the table reader pool. Each segment holds up to 32
readers.

### cairo.wal.inactive.writer.ttl

- **Default**: `120000`
- **Reloadable**: no

Time-to-live in milliseconds before closing inactive WAL writers.

### cairo.wal.writer.pool.max.segments

- **Default**: `10`
- **Reloadable**: no

Number of segments in the WAL writer pool. Each segment holds up to 32
writers.

## Out-of-order (O3) ingestion

These settings control the in-memory buffer used for out-of-order data
ingestion. The buffer size is determined dynamically based on the shape of
incoming data, within the bounds set here.

### cairo.o3.column.memory.size

- **Default**: `256k`
- **Reloadable**: no

Memory page size per column for O3 operations. O3 uses 2x this value per
column (so the default effective size is 512KB per column).

### cairo.o3.last.partition.max.splits

- **Default**: `20`
- **Reloadable**: no

Number of partition pieces allowed before the last piece is merged back into
the physical partition.

### cairo.o3.max.lag

- **Default**: `10 minutes`
- **Reloadable**: no

Upper limit for the in-memory O3 buffer size, in milliseconds.

### cairo.o3.min.lag

- **Default**: `1 second`
- **Reloadable**: no

Lower limit for the in-memory O3 buffer size, in milliseconds.

### cairo.o3.partition.purge.list.initial.capacity

- **Default**: `1`
- **Reloadable**: no

Initial allocation for the partition purge job. Extended automatically at
runtime.

### cairo.o3.partition.split.min.size

- **Default**: `50MB`
- **Reloadable**: no

Estimated partition size on disk. This is one of the conditions that triggers
[auto-partitioning](/docs/getting-started/capacity-planning/).

## Symbol and indexing

### cairo.default.symbol.cache.flag

- **Default**: `true`
- **Reloadable**: no

When `true`, symbol values are cached on the Java heap instead of being
looked up in database files.

### cairo.default.symbol.capacity

- **Default**: `256`
- **Reloadable**: no

Approximate capacity for `SYMBOL` columns. Should equal the number of unique
symbol values stored in the table. Getting this value significantly wrong
causes performance degradation. Must be a power of 2.

### cairo.index.value.block.size

- **Default**: `256`
- **Reloadable**: no

Approximation of the number of rows for a single index key. Must be a power
of 2. Applies to bitmap indexes only; posting indexes manage their own block
layout.

### cairo.mat.view.covering.index.enabled

- **Default**: `false`
- **Reloadable**: no

When `false`, the SQL planner skips the covering-index path for
[materialized view](/docs/concepts/materialized-views/) refresh queries
and uses the regular plan instead. Set to `true` to opt the refresh
back into covering for setups where the covering path is faster (small,
highly selective filters with `INCLUDE` columns). Ad-hoc queries against
the view are unaffected and use covering when eligible.

### cairo.parallel.index.threshold

- **Default**: `100000`
- **Reloadable**: no

Minimum number of rows before parallel indexation is used.

### cairo.parallel.indexing.enabled

- **Default**: `true`
- **Reloadable**: no

Enables parallel indexation. Works in conjunction with
`cairo.parallel.index.threshold`.

### cairo.posting.index.auto.include.timestamp

- **Default**: `true`
- **Reloadable**: no

When `true` and the user supplies an `INCLUDE` clause on a
[posting index](/docs/concepts/deep-dive/posting-index/), the designated
timestamp is automatically appended to the `INCLUDE` list if not already
present. Has no effect on bare `INDEX TYPE POSTING` declarations — those
have no covering layer regardless of this setting.

### cairo.posting.index.indexer.spill.bytes.max

- **Default**: `268435456` (256 MiB)
- **Reloadable**: no

Caps the per-key spill arena used by one-shot
[posting index](/docs/concepts/deep-dive/posting-index/) build paths —
`ALTER ADD INDEX`, `REINDEX`, snapshot restore, and O3 partition rewrites
on posting-indexed wide columns. When the cap is reached, the writer
drains pending state into a fresh sparse generation and continues.
Steady-state WAL ingestion is unaffected. Set to `0` or a negative value
to disable back-pressure entirely.

### cairo.posting.index.row.id.encoding

- **Default**: `adaptive`
- **Reloadable**: no

Default row ID encoding for posting indexes when no encoding variant is
specified. Valid values: `adaptive` (trial-encodes both delta +
Frame-of-Reference and Elias-Fano per stride and picks the smaller), `delta`
(delta + Frame-of-Reference only), `ef` (Elias-Fano only).

### cairo.posting.seal.gen.threshold

- **Default**: `16`
- **Reloadable**: no

Maximum number of unsealed generations per partition before
[posting index](/docs/concepts/deep-dive/posting-index/) sealing is triggered.
Sealing compacts active generations into a single dense generation with a
stride-indexed layout.

### cairo.spin.lock.timeout

- **Default**: `1000`
- **Reloadable**: no

Timeout in milliseconds when attempting to acquire index readers (bitmap and
posting).

### cairo.work.steal.timeout.nanos

- **Default**: `10000`
- **Reloadable**: no

Latch await timeout in nanoseconds for stealing indexing work from other
threads.

## File operations

### cairo.file.descriptor.cache.enabled

- **Default**: `true`
- **Reloadable**: no

Enables or disables the file descriptor cache.

### cairo.file.operation.retry.count

- **Default**: `30`
- **Reloadable**: no

Number of attempts to open files.

### cairo.max.swap.file.count

- **Default**: `30`
- **Reloadable**: no

Number of attempts to open swap files.

### cairo.mkdir.mode

- **Default**: `509`
- **Reloadable**: no

File permission mode for new directories.

### cairo.volumes

- **Default**: none
- **Reloadable**: no

A comma-separated list of `alias -> root-path` pairs defining allowed volumes
for use in
[CREATE TABLE IN VOLUME](/docs/query/sql/create-table/#table-target-volume)
statements.

## Snapshot settings

### cairo.snapshot.instance.id

- **Default**: empty string
- **Reloadable**: no

Instance ID to include in disk snapshots.

### cairo.snapshot.recovery.enabled

- **Default**: `true`
- **Reloadable**: no

When `false`, disables snapshot recovery on database start.

## SQL compiler pools

Internal object pool sizes for the SQL compiler. Increasing these reduces
garbage collection pressure at the cost of higher baseline memory usage.

### cairo.character.store.capacity

- **Default**: `1024`
- **Reloadable**: no

Size of the CharacterStore.

### cairo.character.store.sequence.pool.capacity

- **Default**: `64`
- **Reloadable**: no

Size of the CharacterSequence pool.

### cairo.column.pool.capacity

- **Default**: `4096`
- **Reloadable**: no

Size of the Column pool in the SQL compiler.

### cairo.expression.pool.capacity

- **Default**: `8192`
- **Reloadable**: no

Size of the ExpressionNode pool in the SQL compiler.

### cairo.lexer.pool.capacity

- **Default**: `2048`
- **Reloadable**: no

Size of the FloatingSequence pool in GenericLexer.

### cairo.model.pool.capacity

- **Default**: `1024`
- **Reloadable**: no

Size of the QueryModel pool in the SQL compiler.

### cairo.sql.analytic.column.pool.capacity

- **Default**: `64`
- **Reloadable**: no

Size of the AnalyticColumn pool in the SQL parser.

### cairo.sql.column.cast.model.pool.capacity

- **Default**: `16`
- **Reloadable**: no

Size of the CreateTableModel pool in the SQL parser.

### cairo.sql.copy.model.pool.capacity

- **Default**: `32`
- **Reloadable**: no

Size of the CopyModel pool in the SQL parser.

### cairo.sql.insert.model.pool.capacity

- **Default**: `64`
- **Reloadable**: no

Size of the InsertModel pool in the SQL parser.

### cairo.sql.join.context.pool.capacity

- **Default**: `64`
- **Reloadable**: no

Size of the JoinContext pool in the SQL compiler.

### cairo.sql.query.registry.pool.size

- **Default**: auto
- **Reloadable**: no

Pre-sizes the internal data structure that stores active query executions.
Automatically chosen based on the number of shared worker threads.

### cairo.sql.rename.table.model.pool.capacity

- **Default**: `16`
- **Reloadable**: no

Size of the RenameTableModel pool in the SQL parser.

### cairo.sql.with.clause.model.pool.capacity

- **Default**: `128`
- **Reloadable**: no

Size of the WithClauseModel pool in the SQL parser.

## SQL map settings

These settings control the hash maps used internally for GROUP BY, JOIN,
and other operations that build intermediate result sets.

### cairo.compact.map.load.factor

- **Default**: `0.7`
- **Reloadable**: no

Load factor for CompactMaps.

### cairo.default.map.type

- **Default**: `fast`
- **Reloadable**: no

Type of map used. Options: `fast` (speed at the expense of storage) or
`compact`.

### cairo.fast.map.load.factor

- **Default**: `0.5`
- **Reloadable**: no

Load factor for FastMaps.

### cairo.sql.map.key.capacity

- **Default**: `2M`
- **Reloadable**: no

Key capacity in FastMap and CompactMap.

### cairo.sql.map.max.pages

- **Default**: `2^31`
- **Reloadable**: no

Maximum memory pages for CompactMap.

### cairo.sql.map.max.resizes

- **Default**: `2^31`
- **Reloadable**: no

Maximum number of map resizes in FastMap and CompactMap before a resource
limit exception is thrown. Each resize doubles the previous size.

### cairo.sql.map.page.size

- **Default**: `4m`
- **Reloadable**: no

Memory page size for FastMap and CompactMap.

### cairo.sql.unordered.map.max.entry.size

- **Default**: `24`
- **Reloadable**: no

Threshold in bytes for switching from a single-buffer hash table (unordered)
to a hash table with a separate heap for entries (ordered).

## SQL sort and join

Memory settings for sort operations and hash joins.

### cairo.sql.hash.join.light.value.max.pages

- **Default**: `2^31`
- **Reloadable**: no

Maximum pages of the slave chain in light hash joins.

### cairo.sql.hash.join.light.value.page.size

- **Default**: `1048576`
- **Reloadable**: no

Memory page size of the slave chain in light hash joins.

### cairo.sql.hash.join.value.max.pages

- **Default**: `2^31`
- **Reloadable**: no

Maximum pages of the slave chain in full hash joins.

### cairo.sql.hash.join.value.page.size

- **Default**: `16777216`
- **Reloadable**: no

Memory page size of the slave chain in full hash joins.

### cairo.sql.join.metadata.max.resizes

- **Default**: `2^31`
- **Reloadable**: no

Maximum number of map resizes in JoinMetadata before a resource limit
exception is thrown. Each resize doubles the previous size.

### cairo.sql.join.metadata.page.size

- **Default**: `16384`
- **Reloadable**: no

Memory page size for the JoinMetadata file.

### cairo.sql.latest.by.row.count

- **Default**: `1000`
- **Reloadable**: no

Number of rows for LATEST BY.

### cairo.sql.sort.key.max.pages

- **Default**: `2^31`
- **Reloadable**: no

Maximum pages for storing keys in LongTreeChain before a resource limit
exception is thrown.

### cairo.sql.sort.key.page.size

- **Default**: `4M`
- **Reloadable**: no

Memory page size for storing keys in LongTreeChain.

### cairo.sql.sort.light.value.max.pages

- **Default**: `2^31`
- **Reloadable**: no

Maximum pages for storing values in LongTreeChain.

### cairo.sql.sort.light.value.page.size

- **Default**: `1048576`
- **Reloadable**: no

Memory page size for storing values in LongTreeChain.

### cairo.sql.sort.value.max.pages

- **Default**: `2^31`
- **Reloadable**: no

Maximum pages for storing values in SortedRecordCursorFactory.

### cairo.sql.sort.value.page.size

- **Default**: `16777216`
- **Reloadable**: no

Memory page size for storing values in SortedRecordCursorFactory.

## Page frames

### cairo.sql.page.frame.max.rows

- **Default**: `1000000`
- **Reloadable**: no

Maximum number of rows in page frames used in SQL queries.

### cairo.sql.page.frame.min.rows

- **Default**: `1000`
- **Reloadable**: no

Minimum number of rows in page frames used in SQL queries.

## JIT compilation

These settings control Just-In-Time compilation of SQL filter expressions.
JIT compilation can significantly speed up queries with simple filter
predicates.

### cairo.sql.jit.bind.vars.memory.max.pages

- **Default**: `8`
- **Reloadable**: no

Maximum memory pages for storing bind variable values in JIT compiled filters.

### cairo.sql.jit.bind.vars.memory.page.size

- **Default**: `4K`
- **Reloadable**: no

Memory page size for storing bind variable values in JIT compiled filters.

### cairo.sql.jit.debug.enabled

- **Default**: `false`
- **Reloadable**: no

When enabled, prints generated assembly to `stdout`.

### cairo.sql.jit.ir.memory.max.pages

- **Default**: `8`
- **Reloadable**: no

Maximum memory pages for storing intermediate representation during JIT
compilation.

### cairo.sql.jit.ir.memory.page.size

- **Default**: `8K`
- **Reloadable**: no

Memory page size for storing intermediate representation during JIT
compilation.

### cairo.sql.jit.max.in.list.size.threshold

- **Default**: `10`
- **Reloadable**: no

If an `IN` predicate list exceeds this length, JIT compilation is skipped for
that query.

### cairo.sql.jit.mode

- **Default**: `on`
- **Reloadable**: no

JIT compilation for SQL queries. Set to `off` to disable.

### cairo.sql.jit.page.address.cache.threshold

- **Default**: `1M`
- **Reloadable**: no

Minimum cache size to shrink the page address cache after query execution.

## GROUP BY

### cairo.sql.groupby.allocator.default.chunk.size

- **Default**: `128k`
- **Reloadable**: no

Default size for memory buffers in the GROUP BY function native memory
allocator.

### cairo.sql.groupby.allocator.max.chunk.size

- **Default**: `4gb`
- **Reloadable**: no

Maximum allowed native memory allocation for GROUP BY functions.

### cairo.sql.parallel.groupby.enabled

- **Default**: `true`
- **Reloadable**: no

Enables parallel GROUP BY execution. Requires at least 4 shared worker
threads.

### cairo.sql.parallel.groupby.merge.shard.queue.capacity

- **Default**: auto
- **Reloadable**: no

Merge queue capacity for parallel GROUP BY. Used for parallel tasks that
merge shard hash tables.

### cairo.sql.parallel.groupby.sharding.threshold

- **Default**: `100000`
- **Reloadable**: no

Row count threshold for parallel GROUP BY to shard the hash table holding
the aggregates.

## SAMPLE BY

### cairo.sql.sampleby.default.alignment.calendar

- **Default**: `0`
- **Reloadable**: no

Default SAMPLE BY alignment behavior. `true` corresponds to ALIGN TO
CALENDAR, `false` corresponds to ALIGN TO FIRST OBSERVATION.

### cairo.sql.sampleby.page.size

- **Default**: `0`
- **Reloadable**: no

SAMPLE BY index query page size (maximum values returned in a single scan).
`0` means to use the symbol block capacity.

## Window functions

### cairo.sql.analytic.initial.range.buffer.size

- **Default**: `32`
- **Reloadable**: no

Window function buffer size in record counts. Pre-sizes the buffer for
every window function execution.

### cairo.sql.window.max.recursion

- **Default**: `128`
- **Reloadable**: no

Prevents stack overflow errors when evaluating complex nested SQL. The value
is the approximate number of nested SELECT clauses allowed.

## Memory limits

These limits cap the native memory tracked for a single query, materialized view
refresh, live view refresh, or WAL apply batch. They help prevent runaway
workloads from exhausting server memory, and are available since QuestDB
10.0.0. Each workload has its own limit, in
addition to the process-wide native memory limit set by
[`ram.usage.limit.bytes`](#ramusagelimitbytes) and
[`ram.usage.limit.percent`](#ramusagelimitpercent), which is on by default at
90% of the memory visible to the JVM. Allocations still count toward the
process-wide limit, so concurrent workloads can reach it even when each stays
within its own budget. A limit bounds one query, refresh, or batch, not a
connection or a principal: concurrent queries each run under the full limit.

Three of the four workload limits are documented in this section, together
with the two process-wide keys. The fourth workload limit,
[`cairo.live.view.refresh.memory.limit.bytes`](/docs/configuration/live-views/#cairoliveviewrefreshmemorylimitbytes),
lives with the other live view settings.

All four default to `0`, which means unlimited, so behavior matches a server
without limits until you opt in. Set each limit as a byte count or a size with a
`K`, `M`, or `G` suffix, for example `512M` or `2G`. Each suffix multiplies by
1024, so `512M` is 536870912 bytes. The limits are reloadable:
edit `server.conf` and call
[`reload_config()`](/docs/query/functions/meta/#reload_config). New queries,
materialized view refreshes, and WAL apply batches use the updated limits; work
already running keeps its original limit. A live view acquires its limit when it
is first compiled and keeps it across refreshes, so a reloaded value reaches an
existing view only after a full teardown: invalidation, recreation, or a server
restart.

When a workload exceeds its limit, QuestDB raises an out-of-memory error at the
allocation that crossed the line and aborts that workload, while unrelated
workloads keep running. What happens next depends on the workload:

- A user query fails with the error. The client connection stays open, and its
  next statement runs under the same limit.
- A materialized view refresh first retries with smaller refresh intervals where
  possible, up to
  [`cairo.mat.view.max.refresh.retries`](/docs/configuration/materialized-views/#cairomatviewmaxrefreshretries)
  times. If the error persists,
  [incremental and scheduled period refreshes](/docs/concepts/materialized-views/#refresh-strategies)
  are deferred for
  [`cairo.mat.view.refresh.busy.retry.timeout`](/docs/configuration/materialized-views/#cairomatviewrefreshbusyretrytimeout),
  with up to
  [`cairo.mat.view.refresh.busy.retry.limit`](/docs/configuration/materialized-views/#cairomatviewrefreshbusyretrylimit)
  retries before invalidation. `REFRESH ... FULL` and user-requested
  `REFRESH ... RANGE FROM ... TO ...` invalidate without deferred retries. An
  [invalid view](/docs/concepts/materialized-views/#refreshing-an-invalid-view)
  is recovered with a full refresh, which runs under the same limit, so raise
  the limit first.
- A live view refresh invalidates the view immediately. The live view limit
  also counts the state a view retains between refreshes, so size it for the
  view's retained state plus the transient buffers of one refresh. Only a
  breach of this limit invalidates the view; a process-wide memory error during
  a refresh is retried instead.
- A WAL apply first retries under the writer's memory-pressure control, which
  shrinks the transaction block, reduces parallelism, and then backs off between
  attempts for up to five random delays bounded by
  [`cairo.write.back.off.timeout.on.mem.pressure`](#cairowritebackofftimeoutonmempressure).
  If the breach persists after the back-off budget is exhausted, the
  table is suspended and
  [`wal_tables()`](/docs/query/functions/meta/#wal_tables) reports
  `OUT OF MEMORY` in its `errorTag` column. Resume it with
  [`ALTER TABLE RESUME WAL`](/docs/query/sql/alter-table-resume-wal/).

The message names the workload so you can tell it apart from a process-wide
breach:

```
query memory limit exceeded [workload=QUERY, queryId=62179, limit=536870912, used=536346624, size=1048576, memoryTag=27]
```

`workload` is one of `QUERY`, `MAT_VIEW_REFRESH`, `LIVE_VIEW_REFRESH`, or
`WAL_APPLY`. The prefix reads `query memory limit exceeded` for every workload.
`limit` and `used` are bytes, `size` is the allocation that failed, and
`memoryTag` is the numeric id of the allocation category. `queryId` is the
`query_id` reported by
[`query_activity`](/docs/query/functions/meta/#query_activity) for a query, and
the `id` of the table or view itself, as reported by
[`tables()`](/docs/query/functions/meta/#tables), for a WAL apply batch, a
materialized view refresh, or a live view refresh. For a `COPY ... TO` export it
is the copy id, printed here in decimal while `COPY` reports it in hexadecimal.

:::note

Only tracked native allocations count toward a limit. Memory-mapped files, such
as table column files, are excluded, and some native allocations are not yet
covered.

:::

QuestDB Enterprise can additionally set a memory limit per user, group, or
service account, which overrides the query workload limit for a principal's
queries. See [role-based access control](/docs/security/rbac/#memory-limits).

### Sizing a limit

QuestDB does not estimate a workload's memory before running it: how much a
query allocates depends on the data it reads, the plan the engine picks, and
the number of worker threads it runs on. Measure it instead. Live usage and the
effective limit of each running query are exposed by
[`query_activity`](/docs/query/functions/meta/#query_activity) through its
`memory_used` and `memory_limit` columns. `memory_used` is a live gauge with no
peak value, so sample it repeatedly while the query runs and take the largest
value as the floor for the limit. Leave headroom above it: a later run over
more data allocates more.

Run the query to size in one session with the limit at `0`, so it reports
`memory_used` without risk of a breach, and sample it from a second session:

```questdb-sql title="Session 1: the query to size"
SELECT symbol, avg(price) AS avg_price
FROM trades
WHERE timestamp IN '2026-09-14'
SAMPLE BY 1m;
```

```questdb-sql title="Session 2: sample its usage while it runs"
SELECT query_id, memory_used, memory_limit, query
FROM query_activity()
WHERE query LIKE 'SELECT symbol, avg(price)%';
```

| query_id | memory_used | memory_limit | query                                              |
| -------- | ----------- | ------------ | -------------------------------------------------- |
| 57777    | 8388608     | null         | SELECT symbol, avg(price) AS avg_price FROM trades ... |

Background workloads do not appear in `query_activity`, but each runs SQL that
you can reproduce as a plain query and measure the same way:

- A materialized view refresh runs the view's `SELECT`, taken from
  [`SHOW CREATE MATERIALIZED VIEW`](/docs/query/sql/show/#show-create-materialized-view),
  with the base table restricted to the time range being refreshed. Run that
  `SELECT` with a `WHERE` clause on the base table's designated timestamp that
  spans one refresh worth of data. An incremental refresh covers the rows
  committed since the previous refresh, so use the busiest interval you expect
  between refreshes. A full refresh, and a refresh after a large out-of-order
  write, covers far more, so size for the whole base table if you need those to
  succeed under the same limit. The measurement is a close proxy rather than an
  exact figure, because the refresh may pick a different plan or degree of
  parallelism.
- A live view refresh runs the view's window functions over each batch of new
  base table rows, so the same proxy over a batch of rows measures the
  transient buffers of one refresh. The live view limit also counts the state
  the view retains between refreshes: the `IN MEMORY` tier, whose capacity
  [`live_views()`](/docs/query/functions/meta/#live_views) reports in its
  `in_mem_bytes` column, and the window state. Add these to the transient
  figure, and keep the limit above the
  [allocation floor](/docs/configuration/live-views/#cairoliveviewrefreshmemorylimitbytes)
  described with the key.

After a breach, the `used` and `size` values in the error message record the
footprint at the point of failure, and are the only record of it. A limit has
to be at least `used + size` to get past that allocation, and usually more,
because the workload was aborted before it finished. There is no dedicated
metric for breaches, so alert on the message in the server log.

### cairo.mat.view.refresh.memory.limit.bytes

- **Default**: `0`
- **Reloadable**: yes

Maximum native memory a single materialized view refresh may allocate. `0`
disables the limit.

### cairo.query.memory.limit.bytes

- **Default**: `0`
- **Reloadable**: yes

Maximum native memory a single user SQL query may allocate. `0` disables the
limit. It covers `SELECT`, `INSERT ... SELECT`, `CREATE TABLE AS SELECT`,
`UPDATE` on a non-WAL table, and every other statement that runs on the
caller's connection and appears in
[`query_activity`](/docs/query/functions/meta/#query_activity). It also covers
`COPY ... TO` exports, which run in the background under the issuing query's
budget but do not appear in `query_activity`. `CREATE MATERIALIZED VIEW`
charges only its DDL to this limit: the initial population runs as a refresh
under `cairo.mat.view.refresh.memory.limit.bytes`. Subqueries and other nested
work share the top-level query's budget rather than each acquiring their own.
On QuestDB
Enterprise the built-in admin cannot be given a per-principal override and runs
under this limit, so size it with the admin's diagnostic queries in mind.

### cairo.wal.apply.memory.limit.bytes

- **Default**: `0`
- **Reloadable**: yes

Maximum native memory a single WAL apply batch may allocate. `0` disables the
limit.

The limit covers only the SQL that WAL apply runs inside a batch: `UPDATE`
statements and non-structural `ALTER TABLE` changes. Memory used to commit data,
including out-of-order merges, is not tracked and is bounded only by the
process-wide native memory limit. In practice this limit rarely fires. Its main effect is
to keep WAL apply SQL on its own budget, separate from the query limit.

### ram.usage.limit.bytes

- **Default**: `0`
- **Reloadable**: no

Process-wide limit on the native memory QuestDB may allocate, as a byte count or
a size with a `K`, `M`, or `G` suffix. `0` means no byte limit. When both this
key and `ram.usage.limit.percent` resolve to a limit, the smaller one applies.

Despite the name, the limit counts tracked native allocations, not the process
RSS. The JVM heap, thread stacks, and memory-mapped files such as table column
files do not count, so a limit equal to a container's memory limit does not stop
the kernel from killing the process. Compare the `RSS` and `NATIVE_*` rows of
[`memory_metrics()`](/docs/query/functions/meta/#memory_metrics) to see the gap.

An allocation that would cross the resolved limit fails with a
`global RSS memory limit exceeded [usage=..., RSS_MEM_LIMIT=..., size=..., memoryTag=...]`
error. The error lands on whichever workload
allocates last, so a query, a view refresh, or a WAL apply batch can fail
because of another workload's usage. A WAL apply that hits it goes through the
same retries and suspension as a breach of its own limit. The per-workload
[memory limits](#memory-limits) sit underneath this one and isolate workloads
from each other.

### ram.usage.limit.percent

- **Default**: `90`
- **Reloadable**: no

Process-wide native memory limit as a percentage of the memory visible to the
JVM: the host's physical memory, or the container's cgroup memory limit when one
is set. It counts the same tracked allocations as `ram.usage.limit.bytes`. `0`
disables the percentage limit. When both this key and `ram.usage.limit.bytes`
resolve to a limit, the smaller one applies.

## Batch operations

### cairo.create.as.select.retry.count

- **Default**: `5`
- **Reloadable**: no

Number of times table creation or insertion will be attempted.

### cairo.sql.copy.buffer.size

- **Default**: `2M`
- **Reloadable**: no

Size of buffer used when copying tables.

### cairo.sql.create.table.model.batch.size

- **Default**: `1000000`
- **Reloadable**: no

Batch size for non-atomic CREATE AS SELECT statements.

### cairo.sql.insert.model.batch.size

- **Default**: `1000000`
- **Reloadable**: no

Batch size for non-atomic INSERT INTO SELECT statements.

## Type casting and formatting

### cairo.sql.copy.formats.file

- **Default**: `/text_loader.json`
- **Reloadable**: no

Name of the file containing user-defined date and timestamp formats.

### cairo.sql.double.cast.scale

- **Default**: `12`
- **Reloadable**: no

Maximum number of decimal places for types cast as doubles.

### cairo.sql.float.cast.scale

- **Default**: `4`
- **Reloadable**: no

Maximum number of decimal places for types cast as floats.

## Column aliases

Controls the names QuestDB gives result columns when the query does not supply
one with `AS`.

### cairo.sql.column.alias.expression.enabled

- **Default**: `true`
- **Reloadable**: no

When enabled, a column without an explicit alias is named after the expression
that produced it. `SELECT floor(avg(price * amount)) FROM trades` returns a
column named `floor(avg(price * amount))`.

Before QuestDB 9.0.0, the name came from the outermost function instead, so
that query returned a column named `floor`, repeated functions had a number
appended, and a plain expression such as `5 + 2` was named `column`.

Set to `false` to restore the pre-9.0.0 naming. This exists for compatibility
with tools and scripts that select result columns by their generated name.

### cairo.sql.column.alias.generated.max.size

- **Default**: `64`
- **Reloadable**: no

Maximum length of a generated column alias. Expression-derived names can be
long, so they are truncated at this limit. Has no effect when
`cairo.sql.column.alias.expression.enabled` is `false`.

## JSON UNNEST

### cairo.json.unnest.max.value.size

- **Default**: `4096`
- **Reloadable**: no

Maximum byte size of a single VARCHAR or TIMESTAMP field value extracted
during JSON [UNNEST](/docs/query/sql/unnest/). Numeric types (DOUBLE, LONG,
INT, SHORT, BOOLEAN) are unaffected. Each VARCHAR/TIMESTAMP column allocates
`2 * maxValueSize` bytes of native memory per active UNNEST cursor, so
increase with care.

## Random function memory

### cairo.rnd.memory.max.pages

- **Default**: `128`
- **Reloadable**: no

Maximum number of pages for memory used by `rnd_` functions. Supports
`rnd_str()` and `rnd_symbol()`.

### cairo.rnd.memory.page.size

- **Default**: `8K`
- **Reloadable**: no

Memory page size used by `rnd_` functions. Supports `rnd_str()` and
`rnd_symbol()`.

## Parquet encoding

Settings for Parquet-encoded partitions, used by
[table-level Parquet format](/docs/query/sql/create-table/#partition-format),
storage policies, and COPY TO exports.

### cairo.partition.encoder.parquet.bloom.filter.fpp

- **Default**: `0.01`
- **Reloadable**: no

Default bloom filter false positive probability for in-place partition
encoding. Lower values produce larger but more accurate filters. Range:
0.0 to 1.0.

### cairo.partition.encoder.parquet.compression.codec

- **Default**: `ZSTD`
- **Reloadable**: no

Default compression codec for parquet-encoded partitions. Alternatives
include `LZ4_RAW` and `SNAPPY`.

### cairo.partition.encoder.parquet.compression.level

- **Default**: `9` (ZSTD), `0` (otherwise)
- **Reloadable**: no

Default compression level for parquet-encoded partitions. Dependent on
the underlying compression codec.

### cairo.partition.encoder.parquet.data.page.size

- **Default**: `1048576`
- **Reloadable**: no

Default page size for parquet-encoded partitions.

### cairo.partition.encoder.parquet.min.compression.ratio

- **Default**: `1.2`
- **Reloadable**: no

Minimum compression ratio (uncompressed / compressed) for Parquet pages.
When a compressed page does not meet this threshold, it is stored
uncompressed instead. A value of `0.0` disables the check.

### cairo.partition.encoder.parquet.raw.array.encoding.enabled

- **Default**: `false`
- **Reloadable**: no

When `true`, exports arrays in QuestDB-native binary format (less
compatible). When `false`, uses Parquet-native format (more compatible).

### cairo.partition.encoder.parquet.row.group.size

- **Default**: `100000`
- **Reloadable**: no

Default row-group size for parquet-encoded partitions.

### cairo.partition.encoder.parquet.statistics.enabled

- **Default**: `true`
- **Reloadable**: no

Controls whether statistics are included in parquet-encoded partitions.

### cairo.partition.encoder.parquet.version

- **Default**: `1`
- **Reloadable**: no

Output Parquet version for parquet-encoded partitions. Can be `1` or `2`.

### cairo.sql.parquet.cache.memory.size

- **Default**: `256M`
- **Reloadable**: no

Per-cursor memory budget, in bytes, for caching decoded Parquet row groups
while scanning Parquet partitions. It balances memory use against repeated
row-group decoding. Replaces the deprecated slot-based
`cairo.sql.parquet.frame.cache.capacity`, which is still accepted but ignored.

### cairo.sql.parquet.row.group.pruning.enabled

- **Default**: `true`
- **Reloadable**: no

Enables row group pruning for queries on Parquet partitions. When enabled,
QuestDB uses min/max statistics, bloom filters, and null counts to skip row
groups that cannot match the query filter.

## Column purge

These settings control the background job that cleans up stale column files
after UPDATE statements.

### cairo.sql.column.purge.queue.capacity

- **Default**: `128`
- **Reloadable**: no

Purge column version job queue capacity. Increase if column versions are not
automatically cleaned up after UPDATE statements. Reduce to decrease initial
memory footprint.

### cairo.sql.column.purge.retry.delay

- **Default**: `10000`
- **Reloadable**: no

Initial delay in microseconds before re-trying purge of stale column files.

### cairo.sql.column.purge.retry.delay.limit

- **Default**: `60000000`
- **Reloadable**: no

Delay limit in microseconds. Once reached, the retry delay remains constant.

### cairo.sql.column.purge.retry.delay.multiplier

- **Default**: `10.0`
- **Reloadable**: no

Multiplier used to increase retry delay with each iteration.

### cairo.sql.column.purge.retry.limit.days

- **Default**: `31`
- **Reloadable**: no

Number of days the purge system will continue retrying before giving up on
stale column files.

### cairo.sql.column.purge.task.pool.capacity

- **Default**: `256`
- **Reloadable**: no

Column version task object pool capacity. Increase to reduce GC, reduce to
decrease memory footprint.