For AI agents: the complete documentation index is at llms.txt. Every page is also available as markdown by appending .md to its URL, or by sending an Accept: text/markdown request header.

Cairo engine

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.

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 or the Postgres options 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.

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 out-of-order buffering, partition splits and the opt-in merge-append layout. Buffer size adjusts to the shape of incoming data within the configured bounds.

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

Deprecated fallback for cairo.o3.partition.max.splits, which takes precedence when both are set.

cairo.o3.max.lag​

  • Default: 10 minutes
  • Reloadable: no

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

cairo.o3.mid.partition.max.splits​

Default: 1; Reloadable: no

Target folder count for older partitions of non-WAL tables only; values below 1 are clamped to 1.

cairo.o3.min.lag​

  • Default: 1 second
  • Reloadable: no

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

cairo.o3.partition.cluster.bin.width​

Default: 1m; Reloadable: no

Finest timestamp bin width for finding gaps between incoming writes; narrower bins allow more precise cuts at greater per-commit cost.

cairo.o3.partition.cluster.max.bins​

Default: 4096; Reloadable: no

Maximum bins used to locate gaps for one partition, clamped to 1–1048576 to bound work and memory.

cairo.o3.partition.max.splits​

Default: 20; Reloadable: no

Target folder count for all WAL partitions and the latest non-WAL partition, falling back to cairo.o3.last.partition.max.splits if set; hot folders can temporarily exceed it and values below 1 are clamped to 1.

cairo.o3.partition.merge.append.enabled​

Default: false; Reloadable: no

Enables merge-append partitions for WAL tables to reduce copying during backdated writes.

cairo.o3.partition.merge.append.frame.cache.size​

Default: 2; Reloadable: no

Partitions whose merge-append column files stay open between commits; lower this to reduce file descriptors on wide tables, or use 0 to reopen every time.

cairo.o3.partition.presplit.max.cuts​

Default: 10000; Reloadable: no

Maximum cuts per partition per commit in the row-based pre-split pass, excluding clustering and replace-range boundary cuts; clamped to at least 1.

cairo.o3.partition.presplit.min.piece.multiple​

Default: 2; Reloadable: no

Minimum existing rows an optional pre-split cut must spare, as a multiple of cairo.partition.compaction.avg.rows.piece.lim, clamped to at least 1; raise it to make fewer cuts.

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.

Partition compaction​

Partition compaction reclaims dead space and merges idle split folders, including when merge-append is disabled for new writes; the background sweep covers WAL tables only. Most users only need cairo.partition.compaction.check.interval, cairo.partition.compaction.idle.timeout and cairo.partition.compaction.squash.idle.timeout.

cairo.partition.compaction.avg.rows.piece.lim​

Default: 4096; Reloadable: no

Target average rows per piece used to scale the piece-count threshold, clamped to at least 1; raise it to trigger compaction with fewer pieces.

cairo.partition.compaction.check.interval​

Default: 120000; Reloadable: no

Milliseconds from the end of a WAL-table background sweep until the next starts; negative disables periodic sweeps but not requests from storage policies, and 0 checks continuously.

cairo.partition.compaction.dead.min.size​

Default: 50M; Reloadable: no

Minimum estimated dead bytes required along with cairo.partition.compaction.dead.rows.ratio to trigger the waste-ratio rule.

cairo.partition.compaction.dead.rows.ratio​

Default: 1.0; Reloadable: no

Dead rows as a multiple of live rows above which the waste-ratio rule fires, subject to the minimum dead size.

cairo.partition.compaction.decline.backoff.max​

Default: 60m; Reloadable: no

Maximum retry delay for a partition whose commit-time compaction was declined.

cairo.partition.compaction.decline.backoff.min​

Default: 1m; Reloadable: no

Initial retry delay after declined commit-time compaction, clamped between 0 and cairo.partition.compaction.decline.backoff.max; 0 disables backoff.

cairo.partition.compaction.decline.backoff.multiplier​

Default: 2; Reloadable: no

Multiplier for successive declined-compaction retry delays, clamped to at least 1 and limited by the maximum delay.

cairo.partition.compaction.hot.commits​

Default: 10; Reloadable: no

Recent-commit window protecting written folders from table-pressure rewrites and WAL split squashing; 0 disables this protection and negative values are clamped to 0.

cairo.partition.compaction.idle.timeout​

Default: 60m; Reloadable: no

Idle duration before individual merge-append folders or local Parquet partitions are candidates for background compaction, also used by commit-time age-based compaction.

cairo.partition.compaction.io.budget​

Default: 1G; Reloadable: no

Estimated read-plus-write bytes a background sweep may start copying; its first partition runs even if it exceeds the budget.

cairo.partition.compaction.io.cost.multiplier​

Default: 2; Reloadable: no

Multiplier of estimated live bytes charged against the sweep I/O budget, clamped to at least 1; 2 accounts for one read and one write.

cairo.partition.compaction.move.tail.dead.rows.percent​

Default: 10; Reloadable: no

Dead rows as a percentage of live rows above which a commit may split off the tail, also requiring dead bytes above cairo.o3.partition.split.min.size; 0 removes the percentage barrier.

cairo.partition.compaction.move.tail.piece.threshold​

Default: 1000; Reloadable: no

Piece count above which a commit may split off the tail even below the dead-row percentage trigger, still requiring the minimum dead bytes.

cairo.partition.compaction.move.tail.prefix.multiple​

Default: 2; Reloadable: no

The retained prefix must exceed this multiple of the copied tail plus incoming rows for a tail split, clamped to at least 1; raise it for rarer, cheaper splits.

cairo.partition.compaction.piece.threshold​

Default: 20; Reloadable: no

Minimum piece-count threshold, clamped to at least 1; compaction triggers above max(piece.threshold, liveRows / avg.rows.piece.lim) using the corresponding compaction settings.

cairo.partition.compaction.squash.idle.timeout​

Default: 30m; Reloadable: no

Idle duration required for every folder before the sweep merges a non-active split partition, clamped to at most cairo.partition.compaction.idle.timeout.

cairo.partition.compaction.squash.target.size.multiple​

Default: 4; Reloadable: no

Size threshold as a multiple of cairo.o3.partition.split.min.size below which a merge-append squash target is first rewritten to standard layout; 0 disables this preliminary rewrite.

cairo.partition.compaction.swap.timeout​

Default: 30m; Reloadable: no

Wait before a stalled compaction publication can be cleared after draining the writer queue, clamped to at least one microsecond.

cairo.partition.compaction.table.dead.stop.percent​

Default: 10; Reloadable: no

Dead rows as a percentage of live rows below which table-pressure compaction may stop, clamped to at most cairo.partition.compaction.table.dead.threshold.percent.

cairo.partition.compaction.table.dead.stop.trigger.percent​

Default: 50; Reloadable: no

Percentage of cairo.partition.compaction.table.dead.trigger that dead bytes must also fall to before table-pressure compaction stops, from 0 to 100.

cairo.partition.compaction.table.dead.threshold​

Default: 50M; Reloadable: no

Minimum estimated table dead bytes needed to activate table-pressure compaction through its dead-row percentage check.

cairo.partition.compaction.table.dead.threshold.percent​

Default: 50; Reloadable: no

Dead rows as a percentage of table live rows at which table-pressure compaction activates, subject to cairo.partition.compaction.table.dead.threshold, from 0 to 100.

cairo.partition.compaction.table.dead.trigger​

Default: 10G; Reloadable: no

Estimated table dead bytes above which table-pressure compaction activates regardless of its percentage threshold or minimum dead size.

cairo.partition.compaction.table.pressure.dead.ratio​

Default: 0.5; Reloadable: no

Dead-to-live row ratio for rewrites under table pressure, required to be finite, positive and below cairo.partition.compaction.dead.rows.ratio; its default is the smaller of 0.5 and half that ratio.

cairo.partition.compaction.time.budget​

Default: 1000; Reloadable: no

Milliseconds allowed per compaction housekeeping pass or background sweep, checked between operations rather than interrupting a copy.

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 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, 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 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 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 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. Takes effect only when the query worker pool has at least one thread.

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: 10000
  • Reloadable: no

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

Parallel GROUP BY over hash joins​

cairo.sql.parallel.hash.join.groupby.build.parallel.min.rows​

  • Default: 300000
  • Reloadable: no

Minimum number of build-side rows for the join hash table to be built on the query workers. Smaller hash tables are built on the query's own thread.

cairo.sql.parallel.hash.join.groupby.build.rows.per.partition​

  • Default: 32768
  • Reloadable: no

Target number of rows per partition of a join hash table built on the query workers.

cairo.sql.parallel.hash.join.groupby.enabled​

  • Default: true
  • Reloadable: no

Enables parallel GROUP BY over INNER, LEFT and RIGHT hash joins. Takes effect only when cairo.sql.parallel.groupby.enabled is also true.

cairo.sql.parallel.hash.join.groupby.payload.copy.max.size​

  • Default: 128M
  • Reloadable: no

Maximum size of the build-side column copy that speeds up probing of the join hash table. 0 disables the copy.

cairo.sql.parallel.hash.join.groupby.payload.copy.min.probe.ratio​

  • Default: 0.4
  • Reloadable: no

Minimum ratio of probe-side to build-side rows for a join hash table built on the query's own thread to copy build-side columns.

cairo.sql.parallel.hash.join.groupby.payload.copy.parallel.min.probe.ratio​

  • Default: 0.125
  • Reloadable: no

Minimum ratio of probe-side to build-side rows for a join hash table built on the query workers to copy build-side columns.

HORIZON JOIN​

These settings cap the offsets of a HORIZON JOIN and control whether it matches rows on the query workers.

cairo.sql.horizon.join.max.offsets​

  • Default: 10000
  • Reloadable: no

Maximum number of offsets that the RANGE or LIST clause of a HORIZON JOIN can generate. A query that generates more fails with RANGE generates too many offsets or LIST has too many offsets. Every left-hand row is matched once per offset, so the limit stops a RANGE with a small STEP over a long span from multiplying the work and the result size.

cairo.sql.parallel.horizon.join.enabled​

  • Default: true
  • Reloadable: no

Enables parallel HORIZON JOIN execution, shown in EXPLAIN as an Async [JIT] Horizon Join, Async Multi Horizon Join or Async [JIT] Horizon Join Projection node. Takes effect only when the query worker pool has at least one thread. Set to false to match HORIZON JOIN rows on the query's own thread. A WHERE filter on the left-hand table can still run on the query workers, as an Async Filter or Async JIT Filter node below the HORIZON JOIN. See Parallel execution.

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.

cairo.sql.subsample.max.rows​

  • Default: 100000000
  • Reloadable: no

Maximum number of input rows accepted by the count-based and stride-based SUBSAMPLE methods: lttb, m4, minmax, uniform, and cadence. Exceeding this limit returns an error. Every input row counts, including rows that lttb, m4, or minmax skip because of a NULL value. The limit is independent of the targetPoints argument. Must be between 1 and 2,147,483,647.

This limit does not apply to sdt, which remains subject to the query's normal memory limits.

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 and ram.usage.limit.percent, 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, 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(). 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 the view is recreated or the server restarts.

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 times. If the error persists, incremental and scheduled period refreshes are deferred for cairo.mat.view.refresh.busy.retry.timeout, with up to cairo.mat.view.refresh.busy.retry.limit retries before invalidation. REFRESH ... FULL and user-requested REFRESH ... RANGE FROM ... TO ... invalidate without deferred retries. 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. If the breach persists after the back-off budget is exhausted, the table is suspended and wal_tables() reports OUT OF MEMORY in its errorTag column. Resume it with 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 for a query, and the id of the table or view itself, as reported by 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.

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 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:

Session 1: the query to size
SELECT symbol, avg(price) AS avg_price
FROM trades
WHERE timestamp IN '2026-09-14'
SAMPLE BY 1m;
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_idmemory_usedmemory_limitquery
577778388608nullSELECT 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, 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() reports in its in_mem_bytes column, and the window state. Add these to the transient figure. Window state grows with the number of distinct partition keys the view has seen and does not shrink when keys go quiet, so leave headroom for key growth, and keep the limit above the allocation floor 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. 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() 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 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. 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, 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.