shared_buffers, work_mem, maintenance_work_mem, huge pages, and Memory Budgeting

Learning outcomes

ServiceHub has 16 GiB of RAM and a DBA raises work_mem to 512 MB because one reporting query spills to disk. The query becomes faster in isolation, then the server is killed by the operating system during the morning concurrency peak. The failure comes from treating a PostgreSQL memory setting as “memory per connection.” It is not.

1. Build the shared performance dataset once

DROP TABLE IF EXISTS app.ch22_perf CASCADE;SET ROLE servicehub_owner;CREATE TABLE app.ch22_perf (  event_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  tenant_id integer NOT NULL,  customer_id bigint NOT NULL,  category text NOT NULL,  occurred_at timestamptz NOT NULL,  amount numeric(12,2) NOT NULL,  sort_key integer NOT NULL,  payload text NOT NULL);INSERT INTO app.ch22_perf(tenant_id,customer_id,category,occurred_at,amount,sort_key,payload)SELECT (g % 64) + 1,       10000 + (g % 5000),       (ARRAY['install','repair','inspection','billing'])[(g % 4)+1],       TIMESTAMPTZ '2026-01-01 00:00+00' + (g || ' seconds')::interval,       ((g % 50000) / 100.0)::numeric(12,2),       (g * 7919) % 1000003,       repeat(chr(65 + (g % 26)), 180)FROM generate_series(1,300000) AS g;CREATE INDEX ch22_perf_customer_time_idxON app.ch22_perf (customer_id, occurred_at DESC);CREATE INDEX ch22_perf_tenant_category_idxON app.ch22_perf (tenant_id, category);RESET ROLE;ANALYZE app.ch22_perf;

The table has 300,000 deterministic rows and two ordinary indexes. Its exact physical size depends on page layout and platform. All five lessons reuse it so performance comparisons are at least about the same logical data.

SELECT count(*) AS rows,       pg_size_pretty(pg_table_size('app.ch22_perf')) AS table_size,       pg_size_pretty(pg_indexes_size('app.ch22_perf')) AS index_size,       pg_size_pretty(pg_total_relation_size('app.ch22_perf')) AS total_sizeFROM app.ch22_perf;

2. Classify memory settings by scope before tuning

SELECT name, setting, unit, context, source, pending_restartFROM pg_settingsWHERE name IN (  'shared_buffers',  'work_mem',  'hash_mem_multiplier',  'maintenance_work_mem',  'autovacuum_work_mem',  'autovacuum_max_workers',  'autovacuum_worker_slots',  'temp_buffers',  'huge_pages',  'huge_page_size')ORDER BY name;

3. work_mem is per operation, not a query reservation

Sorts for ORDER BY/DISTINCT/merge joins and hashes for hash joins/aggregates can each consume their own memory. PostgreSQL does not reserve the total theoretical maximum when planning; memory is consumed while nodes execute.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, MEMORY, TIMING OFF, SUMMARY ON)SELECT sum(length(payload))FROM (  SELECT payload  FROM app.ch22_perf  ORDER BY sort_key, event_id) AS ordered_events;

Inspect the Sort node. It reports a sort method plus either memory use or disk usage. The MEMORY EXPLAIN option in PostgreSQL 18 also reports planner memory; do not confuse that planning-memory report with every executor node's potential work_mem.

4. Induce a controlled disk spill

BEGIN;SET LOCAL work_mem = '64kB';EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT sum(length(payload))FROM (  SELECT payload  FROM app.ch22_perf  ORDER BY sort_key, event_id) AS ordered_events;ROLLBACK;

Expected evidence is an external/disk-backed sort such as Sort Method: external merge with a nonzero disk amount. The exact disk value and execution time vary. This is an intentionally tiny per-sort limit chosen only to make the mechanism observable.

5. Repair locally—not by raising the global default first

BEGIN;SET LOCAL work_mem = '128MB';EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT sum(length(payload))FROM (  SELECT payload  FROM app.ch22_perf  ORDER BY sort_key, event_id) AS ordered_events;ROLLBACK;

On many machines this sort can move to an in-memory method; if it still spills, that is useful evidence about the actual row width and sort requirement. Increase only this diagnostic session in measured steps rather than converting one query's requirement into a global server promise.

6. Hash operations have a separate multiplier

SHOW work_mem;SHOW hash_mem_multiplier;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT tenant_id, customer_id, sum(amount)FROM app.ch22_perfGROUP BY tenant_id, customer_id;

Hash-based operations may use a limit calculated from work_mem × hash_mem_multiplier. A hash aggregate that outgrows its memory budget can batch/spill. Raising the hash multiplier can sometimes help repeated hash spilling while avoiding an equally large increase for every sort—but it still multiplies across concurrent hash nodes and workers.

7. Build a concurrency budget, not a single magic number

The following is a scenario worksheet, not a sizing recommendation. Suppose the host has 16 GiB, the design reserves 5 GiB for OS/filesystem/other processes and 4 GiB for shared PostgreSQL memory, while at peak 40 active query processes each can have two 32 MiB memory-heavy nodes. Eight of those processes are parallel workers and three autovacuum workers can each use 256 MiB.

WITH scenario AS (  SELECT    16384::numeric AS host_mib,    5120::numeric  AS os_reserve_mib,    4096::numeric  AS shared_mib,    40::numeric    AS active_query_processes,    2::numeric     AS work_nodes_each,    32::numeric    AS work_mem_mib,    3::numeric     AS autovac_workers,    256::numeric   AS autovac_mib_each,    768::numeric   AS backend_misc_headroom_mib)SELECT host_mib,       shared_mib,       os_reserve_mib,       active_query_processes * work_nodes_each * work_mem_mib         AS possible_work_nodes_mib,       autovac_workers * autovac_mib_each AS autovac_mib,       backend_misc_headroom_mib,       host_mib - (         shared_mib + os_reserve_mib +         active_query_processes * work_nodes_each * work_mem_mib +         autovac_workers * autovac_mib_each +         backend_misc_headroom_mib       ) AS unallocated_headroom_mibFROM scenario;

The worksheet exposes assumptions. Real operators add logical decoding, WAL buffers, replication, extensions, connection/pooler overhead, kernel slab/cache, maintenance windows, container limits, failover behavior, and safety margin. “Maximum possible” is not the same as simultaneous actual use, but a budget that cannot survive plausible concurrency is unsafe.

8. maintenance_work_mem and autovacuum are different multiplicities

A regular maintenance operation can use maintenance_work_mem. Parallel utility commands such as supported parallel index builds treat that limit as applying to the utility command as a whole rather than independently to every parallel worker. Autovacuum is different: each running autovacuum worker can use up to autovacuum_work_mem, or fall back to maintenance_work_mem when it is -1.

SELECT name, setting, unit, contextFROM pg_settingsWHERE name IN (  'maintenance_work_mem',  'autovacuum_work_mem',  'autovacuum_max_workers',  'autovacuum_worker_slots',  'max_parallel_maintenance_workers')ORDER BY name;

9. huge_pages covers the main shared-memory area

SELECT name, settingFROM pg_settingsWHERE name IN (  'huge_pages',  'huge_pages_status',  'huge_page_size',  'shared_memory_size',  'shared_memory_size_in_huge_pages')ORDER BY name;

huge_pages=try asks for explicit huge/large pages and falls back if unavailable; on refuses startup if the request cannot be satisfied. Explicit huge pages reduce page-table/translation overhead for large shared memory. They do not magically enlarge work_mem, and the setting affects the main shared-memory area rather than arbitrary process allocations.

10. Platform evidence: explicit huge pages are not Linux THP

grep -E 'MemTotal|HugePages|Hugepagesize' /proc/meminfocat /sys/kernel/mm/transparent_hugepage/enabled 2>/dev/null || true

Get-CimInstance Win32_OperatingSystem |  Select-Object TotalVisibleMemorySize,FreePhysicalMemory# PostgreSQL "large pages" additionally require the service account# right "Lock pages in memory" when that feature is enabled.

PostgreSQL documentation currently discourages Linux Transparent Huge Pages for some workloads/versions, while explicit PostgreSQL huge pages are a separate mechanism that can be beneficial. Treat OS memory policy as part of the host baseline.

Authoritative references

Performance settings are hardware-, concurrency-, plan-, operating-system-, and version-sensitive. These primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Resource Consumption
• PostgreSQL 18 — Linux Huge Pages / Kernel Resources
• PostgreSQL 18 — Preset Options / shared_memory_size
• PostgreSQL 18 — Automatic Vacuuming
• PostgreSQL 18 — EXPLAIN