Shared Buffers, WAL Buffers, Work Memory, Maintenance Memory, and Memory Budgeting

Learning outcomes

Memory tuning is where PostgreSQL folklore can become dangerous. Advice such as “set shared_buffers to X% of RAM” or “raise work_mem until sorts are fast” hides the real mechanism: PostgreSQL uses both shared instance memory and memory that can be allocated repeatedly by many backend processes and many plan operations. A setting that looks small in isolation can become large when multiplied by concurrency.

1. A memory budget has layers

At minimum, think in four layers: PostgreSQL shared memory, backend/session baseline memory, query-operation memory, and maintenance/background memory. The operating system, filesystem cache, monitoring agents, connection pooler, and other applications also need RAM. Therefore “RAM minus shared_buffers equals free for work_mem” is not a valid budget.

2. shared_buffers: important, but not all cache

shared_buffers is PostgreSQL’s shared buffer cache. Data pages used by backends pass through this shared area, but PostgreSQL also relies on operating-system caching. Increasing shared_buffers changes memory allocation and can affect checkpoint/write behavior; it is a postmaster-context setting that requires restart.

SELECT name, setting, unit, context, source, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('shared_buffers','huge_pages','max_connections')ORDER BY name;

The official documentation offers starting-point guidance for dedicated servers, but it is not a law. Container limits, mixed workloads, kernel behavior, checkpoint settings, dataset size, read/write mix, and co-located services can all change the right choice. In this course, any percentage is a hypothesis to test, not a “best setting.”

3. wal_buffers: small by design and usually automatic

wal_buffers holds WAL data that has not yet been written to disk. With the default automatic value, PostgreSQL derives an amount from shared_buffers within documented limits. Because it is also a startup-time setting, casual live experimentation is a poor beginner tuning strategy.

SELECT name, setting, unit, context, sourceFROM pg_catalog.pg_settingsWHERE name IN ('wal_buffers','wal_segment_size')ORDER BY name;

Do not confuse WAL buffers with the amount of WAL retained on disk, archive retention, replication slots, or max_wal_size. Those are separate mechanisms covered later.

4. work_mem is per operation, not per server and not simply per connection

work_mem is a base memory limit used by query operations such as sorts and hash tables before spilling to temporary disk files. A single query can contain several such operations. Several sessions can run those queries concurrently. Hash-based operations can have an effective limit based on work_mem × hash_mem_multiplier. This is why “100 connections × work_mem” is still only a rough lower-resolution model: plan shape matters.

SHOW work_mem;SHOW hash_mem_multiplier;SELECT name, setting, unit, context, sourceFROM pg_catalog.pg_settingsWHERE name IN ('work_mem','hash_mem_multiplier')ORDER BY name;

5. Observe a sort that spills—on a disposable dataset

The ServiceHub seed from Chapter 01 is intentionally tiny, so it will not demonstrate memory spills. Create a temporary lab table or use generate_series to produce enough rows for one session. Keep the data disposable and measure your own result; do not copy timings from this lesson.

CREATE TEMP TABLE ch02_sort_lab ASSELECT g AS id,       md5(g::text || repeat('x', 20)) AS payloadFROM generate_series(1, 250000) AS g;ANALYZE ch02_sort_lab;SET work_mem = '1MB';EXPLAIN (ANALYZE, BUFFERS)SELECT *FROM ch02_sort_labORDER BY payload;RESET work_mem;

Run this inside an explicit transaction if you want SET LOCAL to apply only until commit/rollback:

BEGIN;SET LOCAL work_mem = '1MB';EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM ch02_sort_lab ORDER BY payload;ROLLBACK;

Look for the sort node and its method. If the sort spills, PostgreSQL can report an external sort method and disk usage. Your exact result depends on row width, platform, PostgreSQL build, available memory, and plan. If it does not spill, reduce work_mem only within safe allowed limits or increase the disposable row count—do not pretend an expected spill occurred.

6. Temporary-file logging can make spills observable across sessions

EXPLAIN ANALYZE is excellent for one measured statement, but production diagnosis also needs fleet-level evidence. PostgreSQL can log temporary files above a configured size through log_temp_files. That setting has operational noise implications, so do not enable aggressive logging globally without considering log volume.

SELECT name, setting, unit, context, sourceFROM pg_catalog.pg_settingsWHERE name IN ('log_temp_files','temp_file_limit')ORDER BY name;

Lesson 5 develops the logging subsystem; here the key idea is that query memory decisions should be tied to observed spill frequency, sizes, concurrency, and latency, not isolated microbenchmarks.

7. maintenance_work_mem and autovacuum memory

maintenance_work_mem is used by maintenance operations such as VACUUM and CREATE INDEX. Because one session normally runs one such maintenance operation at a time, administrators often set it higher than work_mem. But autovacuum introduces its own concurrency: multiple autovacuum workers may each allocate maintenance memory unless autovacuum_work_mem controls them separately.

SELECT name, setting, unit, context, sourceFROM pg_catalog.pg_settingsWHERE name IN (  'maintenance_work_mem',  'autovacuum_work_mem',  'autovacuum_max_workers')ORDER BY name;

Do not multiply every setting by every connection blindly. Build a scenario model: maximum concurrent analytical queries × heavy plan nodes; maximum simultaneous maintenance workers; application idle/active connection split; shared allocations; OS and co-located services. Then validate with workload evidence.

8. A practical memory worksheet

The following is a planning framework, not a formula for exact peak RSS. PostgreSQL memory contexts, allocator behavior, parallel workers, JIT, extension code, plan shape, kernel caches, and workload timing make exact prediction more complex.

9. Deliberately wrong approach: set global work_mem to 256MB for “faster sorts”

Suppose one report spills to disk with work_mem = 4MB. An operator raises the cluster-wide default to 256MB because the report becomes faster in an isolated test. That ignores concurrency and plan multiplicity. Ten concurrent reports with several hash/sort nodes can create a radically different memory footprint, and ordinary OLTP sessions inherit the same high default.

A safer repair is targeted experimentation: measure the plan, choose a session/transaction-local override for the known report or role/database default only after capacity analysis, load test at expected concurrency, and preserve headroom.

BEGIN;SET LOCAL work_mem = '32MB';EXPLAIN (ANALYZE, BUFFERS)SELECT customer_id, count(*)FROM app.work_ordersGROUP BY customer_idORDER BY count(*) DESC;ROLLBACK;

On the tiny Chapter 01 dataset this query will not need 32MB; that is the lesson. Increasing a limit that the query does not need proves nothing about production sizing.

10. Hands-on lab: compare low and moderate work_mem safely

1. Record shared_buffers, wal_buffers, work_mem, hash_mem_multiplier, maintenance_work_mem, autovacuum_work_mem, and max_connections.
2. Create the temporary ch02_sort_lab table.
3. Run the same EXPLAIN (ANALYZE, BUFFERS) sort in two transactions using two different SET LOCAL work_mem values.
4. Record sort method, disk spill if present, execution time, and buffer evidence. Treat them as local observations only.
5. Drop/disconnect; the temporary table disappears automatically with the session.
6. Write a short capacity note explaining why you would not simply apply the larger value cluster-wide.

11. Production judgment and next bridge

Memory tuning belongs after workload characterization. Track active connections, plans, temp-file activity, OOM events, host/container memory, swap behavior where applicable, and latency. Use targeted role/session settings for exceptional workloads when justified rather than inflating global defaults.

Lesson 4 explains exactly how those global, database, role, session, and transaction-local settings interact—and how pg_settings.source/context/pending_restart keep the change auditable.

Authoritative references

• PostgreSQL 18 — Resource Consumption / Memory
• PostgreSQL 18 — WAL Configuration / wal_buffers
• PostgreSQL 18 — Logging / temporary-file evidence
• PostgreSQL 18 — EXPLAIN
• PostgreSQL 18 — Using EXPLAIN