Skip to main content

Memory

By the end of this page you will know every memory-related config key ScramDB exposes, what each one actually controls, and how to size them for your hardware.

Memory is the highest-payoff tuning lever in ScramDB (see Tuning and Observability). Getting it right means your working set stays resident in RAM instead of round-tripping to disk on every query.

Byte-size values​

Every byte-size field below accepts either a plain integer (bytes) or a human-readable size with a case-insensitive suffix: B (or no suffix), KB/K, MB/M, GB/G, TB/T, PB/P. "4GB", "512MB", "768mb", and "1TB" are all valid; a negative size, or one too large to count in bytes, is refused when the config loads. This grammar is shared by every byte-size field in the config, not just the memory ones.

The buffer pool​

toml
[storage]
buffer_pool_size_bytes = "8GB"
KeyDefaultWhat it controls
buffer_pool_size_bytesunset (0 = auto)Explicit buffer pool size. When unset or 0, ScramDB auto-sizes from buffer_pool_percent of detected available RAM.
buffer_pool_percent0 (auto)Percent of available RAM to use when buffer_pool_size_bytes is not explicitly set. 0 sizes it automatically from [storage.io] backend: a weight of 22 for the default direct-I/O backends (auto, file, io_uring, nvme_passthrough) and 5 for buffered (the OS page cache already holds the same bytes). Automatic shares are weights: the buffer pool, read cache and execution budget are scaled together to fit the memory ScramDB may use, so their ratio is kept at any RAM size. Set 1-100 to pin a percent yourself. Ignored once you set an explicit nonzero buffer_pool_size_bytes.
buffer_pool_cap"64GB"Absolute ceiling on the auto-sizer, regardless of detected RAM.

RAM detection is cgroup-aware: ScramDB reads /sys/fs/cgroup/memory.max (cgroup v2) or memory.limit_in_bytes (cgroup v1) first, and falls back to /proc/meminfo if neither is set. This means a container memory limit is honored automatically when sizing the pool. The buffer pool is resolved once, at startup.

Execution memory​

Per-query execution memory, separate from the buffer pool:

KeyDefaultWhat it controls
execution_memory_bytesunset (0 = auto)Explicit per-query execution memory budget. When unset and execution_memory_percent is also unset, ScramDB sizes it automatically with a weight of 65 on every I/O backend, scaled together with the buffer pool and read cache to fit the memory ScramDB may use (capped by execution_memory_cap, floored at 16MB). An explicit value is honored exactly: it is floor-raised for safety but never silently shrunk to fit; a total that cannot fit is refused at startup with the offending fields named.
execution_memory_percent0 (off)Percent of detected RAM to use, resolved the same cgroup-aware way as the buffer pool and capped by execution_memory_cap. If you set BOTH this and execution_memory_bytes, the explicit byte count wins (it is the more specific statement) and the ignored percent is logged.
execution_memory_cap"64GB"Ceiling on the auto-sizer, consulted whenever the budget is auto-sized (percent set, or neither key set).

A set-returning call such as generate_series(...) streams on the way in: each morsel is generated, consumed, and dropped in turn as the query runs, rather than the whole series being built in memory before the first row is produced. That bounds the input side regardless of query shape.

The memory watchdog​

toml
[storage]
memory_hard_limit_fraction = 0.8

memory_hard_limit_fraction (default 0.8, must be between 0.1 and 0.95) is the fraction of detected RAM the memory watchdog treats as a hard ceiling. If process RSS crosses this line, active queries are cancelled loudly rather than letting the OS OOM-kill the whole process.

Per-operator memory ([storage.memory])​

These are PostgreSQL's work_mem, hash_mem_multiplier and maintenance_work_mem.

toml
[storage.memory]
per_operator_bytes = "128MB"
maintenance_bytes = "512MB"
KeyDefaultWhat it controls
per_operator_bytesunsetPostgreSQL's work_mem: what each sort, hash join build, hash aggregation and window input sort holds before it spills to disk. Unset, each operator holds its share of its statement's execution memory instead, which fits the machine; set it to bound every operator the way PostgreSQL does. 64kB to 2147483647kB.
hash_mem_multiplier2.0A hash join build or hash aggregation holds per_operator_bytes times this before it spills. Applies only when per_operator_bytes (or a session's work_mem) is set. 1 to 1000.
maintenance_bytesunset (0 = auto)Working-memory budget for ANALYZE, CREATE INDEX and CLUSTER (maintenance_work_mem equivalent; a CLUSTER sorts within it and spills sorted runs to disk past it). A vector index's CREATE INDEX and REINDEX take it too, and charge their model training sample and convert buffers to it. Outside a build, a vector index's writes and model retraining charge the same kind of memory to the execution pool directly. Reserved from the shared execution memory pool in ONE call before collection starts, so a value above that pool could never be satisfied (an ANALYZE asks for no more than its table's own pages and column widths need, so a small table reserves kilobytes and this value is only its ceiling): unset, it auto-sizes to a THIRD of the resolved execution pool (comfortably under its own ceiling below, at any machine size); an explicit value above HALF the execution pool is clamped to that half at startup and the clamp is logged. Raise execution_memory_bytes to lift either figure. When the pool is busy at that moment (another build, a background ANALYZE, a heavy query), the statement waits for the memory instead of failing, and a cancel ends the wait. A query never fails for the share a background ANALYZE holds: it waits for that share to return.
read_buffer_pool_bytesunset (0 = auto)Hard cap on the page pool's total working set (checked-out plus idle-recycled bytes combined), with deadlock-free backpressure once reached. When unset, ScramDB auto-sizes it to 8% of detected RAM, capped at 16GB. Leave it unset unless you have measured a reason: a fixed value is wrong at both ends of the hardware range (half of RAM on a small container; only ~28 concurrent page units on a 188GB box, which throttles bulk load badly). Raised at startup, loudly, if it falls below the read pool's floor: one morsel's worth of pages, sized from the buffer pool and the query worker count the same way a morsel itself is (morsel_floor_bytes), so the floor and the morsel size can never disagree.
effective_cache_bytesunset (0 = auto)PostgreSQL's effective_cache_size: a cost hint for the query planner. When unset, ScramDB auto-sizes it to effective_cache_percent (default 50) of detected RAM. Read by the index scan cost: a row the cache still holds is not charged a second random read.

per_operator_bytes, hash_mem_multiplier, maintenance_bytes, read_buffer_pool_bytes and effective_cache_bytes are all read by the engine. working_set_bytes / working_set_percent (default 75) is the envelope the startup budget check below fits every budget into.

SHOW work_mem reports per_operator_bytes when it is set, else PostgreSQL's default 4MB; with per_operator_bytes unset the operators keep their share of the statement's memory until a session or a role sets work_mem, which then bounds them as in PostgreSQL.

A session sets its own operator memory as in PostgreSQL, with the same units, bounds and errors, and it lasts for the session (or the transaction, with SET LOCAL); RESET returns to the server's value:

sql
SET work_mem = '256MB'; -- this session's sorts and hash builds hold up to 256MB
SET hash_mem_multiplier = 4; -- and its hash builds up to 1GB
SHOW work_mem; -- 256MB
RESET work_mem;

An operator past its memory spills and still answers every row. The spills are on /metrics: scramdb_sort_spill_runs_total and scramdb_sort_spill_bytes_written_total for sorts, scramdb_join_build_spill_partitions_total for hash join builds, and scramdb_agg_spill_partition_flushes_total for grouped aggregations.

An INSERT ... SELECT holds a bounded part of its query's rows at a time (about a quarter of the execution memory), never the query's whole result: the rows stream into the table as the query makes them, and the rows past the transaction's write-set share (a quarter of the execution memory) are staged on disk, as a COPY stages its own, until the transaction commits. An UPDATE, a DELETE or an INSERT ... ON CONFLICT DO UPDATE past that share keeps the row images it replaces and writes on disk the same way, and its commit applies them a page at a time under the one commit time, so every other session sees all of the transaction or none of it. Not yet supported lists the statements that still hold their query's whole result.

The startup budget check​

Every budget above is resolved once, at startup, and checked as a sum before the server accepts a connection. The envelope is the lesser of [storage.memory] working_set_bytes and memory_hard_limit_fraction times detected RAM; the WAL ring (64 times [storage.wal] flush_threshold_bytes, with its completion slots) and a fixed runtime margin come out of it first. Unless the threshold is pinned, the ring is sized from the envelope and the query workers: a megabyte per worker, at most 1/256 of the envelope, between 1 MiB and 256 MiB, and its completion slots a sixteenth of that; the startup log line names it.

Two outcomes, both loud, never a silent clamp:

  • Auto-sized budgets are fitted. A budget you did not pin (percent-based, or left unset) is scaled down proportionally to fit the envelope.
  • Explicit budgets are refused, not shrunk. If the byte counts you wrote cannot fit, startup fails naming each field and the envelope. Starting anyway would mean the watchdog cancelling queries for using exactly the memory the config granted them. Lower one, or remove it to have it sized for the machine.

If the deadlock-free floors alone do not fit, ScramDB says so and names the knobs: lower [execution] workers (the buffer-pool floor scales with worker count), raise working_set_bytes if you lowered it by hand, or give the machine more RAM.

How ScramDB accounts for memory​

Every byte ScramDB's own allocator hands out is counted exactly, the instant it is allocated, never only when a query or a background job remembers to record it. That total is on /metrics as scramdb_heap_live_bytes, alongside how much of it belongs to running queries, index builds, vector indexes, background maintenance, caches and the cluster layer (scramdb_heap_class_bytes, one figure per class) and how much is left over once whatever held it has ended (scramdb_heap_escaped_bytes). The unattributed class is not a counter of its own: it is the live heap beyond the other classes and the escaped bytes, never below 0, so the classes and the escaped bytes always add up to scramdb_heap_live_bytes, and scramdb_heap_class_overrun_bytes is above 0 only if the named classes read past the heap. scramdb_heap_unscoped_bytes is the raw count of bytes allocated outside every scope and not yet freed outside one. A statement's work on the execution workers is charged to the statement. The bytes a statement allocates before its memory is tracked, a connection's own bytes and the engine's own threads' bytes have no class of their own and read as unattributed; no memory budget, admission or watchdog decision reads a class figure.

Every budget that bounds one query, one index build or the vector indexes (a query's share of execution_memory_bytes, a session's statement_memory_limit, maintenance_bytes, the vector index's own memory_budget_total) admits or refuses new work by what that work has reserved, never by the allocator's live measurement: the same memory also holds long-lived structures and reused scratch space that no single query or build reserved, and counting those against a small budget would refuse valid work. The measured figures are reported beside the reservations. scramdb_execution_pool_allocated_bytes shows whichever is larger, what the execution pool has reserved or what the allocator measured for it, so it never understates what the pool holds; the vector indexes report theirs in scram_vector_memory (reserved and in_use); and the bytes running statements hold beyond what they reserved are on /metrics as scramdb_heap_uncharged_bytes. None of these measured figures refuses work by itself. per_operator_bytes stays each operator's own spill threshold, measured on the operator's working set.

Where the allocator measured more for a family than the family reserved, the difference is on /metrics as scramdb_memory_uncharged_bytes, one series per family: execution_pool (statements, background work and index builds, past the execution pool's reservations and the working memory compaction holds, itself on scramdb_maintenance_memory_bytes) and cache (past what the compiled-code and plan caches count for themselves; scramdb_jit_cache_bytes against scramdb_jit_cache_budget_bytes shows the compiled-code caches alone). Nine owners that no statement ledger bounds are charged where they live, seven of them with a byte budget derived from the machine: scramdb_memory_charged_bytes and scramdb_memory_budget_bytes carry one series per family for the connection buffers (session_buffers), the table definitions (catalog), the function daemons' reserved memory (udf_daemons), the compiled kernels' buffer pool (fused_pool) the remote-read client's connection buffers (object_store_pool) and the results the subplan cache keeps for reuse (subplan_cache, which drops its entries when a new one does not fit its budget). The seventh, stack_segments, is the stack a deeply nested statement grows while it is read, planned and run: a reservation and not what the statement touched, with no budget and nothing refused at it, because the nesting limits bound it (the segments the SQL reader grows for itself are not counted). The eighth, regex_exec, is the working memory of a regular-expression search whose answer depends on a back-reference or a look-around: the end positions it keeps while it decides what each part of the pattern matches, and the scratch it leaves for the next search; a pattern being compiled also holds the distinct characters it names, at most 256 KiB, until the compile ends. Its budget is a share of the runtime memory margin, and when the next block of a search does not fit it the search drops what it kept and works it out again, so a long subject costs time and never memory past the budget; the refusal is counted in scramdb_memory_refused_bytes_total, and a search stops at statement_timeout or a cancel like any other statement. The budgets of the table definitions, the buffer pool, the subplan cache and the regular-expression working memory are one split of the runtime memory margin, redone on every memory sample: the margin less the resident memory no counter names is divided by what each of the four holds, plus what it asked for and was refused (scramdb_memory_refused_bytes_total), plus an equal share of what none of them has claimed, and no budget is ever below what its family already holds. scramdb_memory_margin_bytes, scramdb_memory_margin_pool_bytes and scramdb_memory_margin_split{reason} show the margin, the part of it divided and why the last split came out as it did. The ninth, function_scratch, is the buffer a string or binary function writes each answer into (a character-set conversion, a bytea rewrite, a bit-string operator): the largest answer of each running batch, held until the batch ends. It has no budget and nothing is refused at it, because the bytes are the answer, so its budget series is absent; it shows how much memory a conversion of a large value holds while the statement runs. Some of these are estimates or reservations, not allocator readings, and the page says so for each. scramdb_memory_unattributed_bytes is the process's resident memory beyond every one of those and every other counter, computed once and the same figure the memory watchdog's breach line gives as held by no live counter: it is where native libraries, thread stacks and the allocator's own overhead sit. The execution_pool series can read above zero on an idle server, because the long-lived structures it holds are not reservations; scramdb_heap_uncharged_bytes and scramdb_vector_memory_uncharged_bytes are, and read zero when nothing holds memory past its reservation.

The values a query moves are counted the same way. Every buffer a column of rows owns (its values, its long strings, an array's elements) is charged, as it is allocated, to the statement whose work made it, and given back when it is freed, whichever thread frees it: scramdb_heap_vector_bytes shows what the vectors of each class hold now. A statement's vectors count toward its memory, so a sort, a hash join or an aggregate spills once the statement nears its budget; and a statement whose vectors would pass the whole execution memory budget stops with PostgreSQL's out of memory (53200), counted in scramdb_heap_vector_over_limit_total, instead of growing the server. The message names the budget it passed and what to do: out of memory: the statement needs more than the N byte execution memory budget. Fix: add a LIMIT, page the read, or raise storage.execution_memory_bytes. A value an unnest or a set-returning function repeats for each of its elements, or a join repeats for every row matching a small table, is stored once per batch of rows, not once per row, and every join and unnest emits its rows in batches bounded by their bytes, not only by their count, so a long value does not multiply into gigabytes of copies.

A table's statistics follow the table. A clone made with CREATE DATABASE ... CLONE plans from the statistics its source saved (scramdb_analyze_statistics_reused_total) until its own rows drift, so a hundred branches do not mean a hundred analyses; and dropping a database or a table takes its statistics, its place in compaction's table list, its vector index and that index's reservation with it.

The memory watchdog above stays the backstop underneath all of this: whatever the budgets and the allocator's own accounting agree on, the watchdog samples the process's actual RSS against memory_hard_limit_fraction and cancels active queries if the process is genuinely approaching the ceiling - a check against the machine's own ground truth, never replaced by the accounting above it.

Verifying a memory change​

There is no buffer-pool hit-ratio metric exposed on /metrics. The honest verification path is comparative timing:

  1. Apply your memory change and restart ScramDB.
  2. Pick a query over data that fits inside your configured working set.
  3. Run it twice back to back:
bash
psql -h 127.0.0.1 -p 5432 -c '\timing on' -c "SELECT count(*) FROM orders WHERE customer_id = 42;"
psql -h 127.0.0.1 -p 5432 -c '\timing on' -c "SELECT count(*) FROM orders WHERE customer_id = 42;"

Expected: the second run is faster than the first, because the data is now served from the buffer pool instead of disk. If the two runs are about the same speed and both are slow, your buffer pool is likely too small for this working set, or the query is not actually memory-bound (check for a missing index first, see Query Performance).

Next​

See Parallelism for the worker-count lever, or Storage Tiering for how the buffer pool fits into the hot/warm/cold picture.