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

[storage]
buffer_pool_size_bytes = "8GB"

Key	Default	What it controls
buffer_pool_size_bytes	unset (0 = auto)	Explicit buffer pool size. When unset or 0, ScramDB auto-sizes from buffer_pool_percent of detected available RAM.
buffer_pool_percent	0 (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:

Key	Default	What it controls
execution_memory_bytes	unset (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_percent	0 (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

[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.

[storage.memory]
per_operator_bytes = "128MB"
maintenance_bytes = "512MB"

Key	Default	What it controls
per_operator_bytes	unset	PostgreSQL'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_multiplier	2.0	A 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_bytes	unset (0 = auto)	Working-memory budget for ANALYZE and CREATE INDEX (maintenance_work_mem equivalent). 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: 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_bytes	unset (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_bytes	unset (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.

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:

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. Limitations 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) - a nonzero escaped figure names a component still holding memory past where its own bookkeeping expected to have given it back.

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.

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:

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.