Memory

Local (per-backend) memory

Each backend process has private memory for sorting, hashing, maintenance work and temporary tables. The limits apply per operation, not per query, so totals multiply quickly; whatever does not fit spills to temporary files on disk.

In plain words

Besides the shared cache, every connection has its own private memory. The biggest consumer is work_mem: the amount one sort or one hash table may use before it starts writing to temporary files on disk. Hash tables may use a bit more: work_mem × hash_mem_multiplier (2 by default).

The catch: the limit is per step of the plan, per process. A query with three sorts and hashes, run by a leader and two parallel workers, can use nine such allowances at once. And a hundred connections can each do it at once.

If a step needs more than its limit, it does not fail: it spills to temporary files and gets slower. If limits are set too high, the machine can run out of memory and the Linux OOM killer may end a process, which restarts every session. Move the sliders to find the balance.

For DBAs and operators

What each limit covers

Setting	Applies to	Scope
`work_mem` (4MB)	Sort, incremental sort, Materialize, window aggregation buffers, bitmap heap scan TID bitmaps	each node, each process
`work_mem × hash_mem_multiplier`	Hash (for hash joins), HashAggregate, Memoize, hashed SubPlan, hashed SetOp	each node, each process
`maintenance_work_mem` (64MB)	CREATE INDEX, VACUUM dead-TID storage, ALTER TABLE ADD FOREIGN KEY, CLUSTER	each operation (parallel index builds share it)
`autovacuum_work_mem` (-1)	Autovacuum workers; -1 means use maintenance_work_mem	each worker
`temp_buffers` (8MB)	Pages of temporary tables (local buffers)	each session
`logical_decoding_work_mem` (64MB)	Reorder buffer of a logical walsender, before spilling or streaming	each decoding slot

Since PostgreSQL 17, VACUUM stores dead tuple IDs in a radix tree (TidStore), so it uses far less memory than before and is no longer capped at 1 GB.

Seeing spills

EXPLAIN (ANALYZE) SELECT ... ORDER BY ...;
  Sort Method: external merge  Disk: 280kB
  Buffers: temp read=101 written=107

-- Hash join that did not fit: Batches > 1
  Buckets: 65536  Batches: 4  Memory Usage: 4033kB

With log_temp_files = 0 every temporary file is logged when it is deleted:

LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp955.0", size 280000

Cumulative counters: temp_files and temp_bytes in pg_stat_database, and temp_blks_written per statement in pg_stat_statements. temp_file_limit cancels a query whose temp files grow too large: ERROR: temporary file size exceeds "temp_file_limit" (1048576kB)

Sizing advice

Budget roughly: RAM − shared_buffers must cover max_connections × (a few MB baseline + typical work_mem use) plus maintenance work and the OS cache.
Keep the global work_mem moderate and raise it per role or per session (SET work_mem = '256MB') for reporting jobs.
If hash nodes spill but sorts are fine, raise hash_mem_multiplier instead of work_mem.
Look at real usage with pg_backend_memory_contexts (own session) or pg_log_backend_memory_contexts(pid), which writes another backend's memory contexts to the server log.

An out-of-memory inside PostgreSQL shows as ERROR: out of memory with a memory context dump in the log; a kill by the Linux OOM killer shows as LOG: client backend (PID 12345) was terminated by signal 9: Killed followed by all sessions being reset.

For developers: inside the source

Where it lives

aset.c and mcxt.c: every node allocates in its own memory context, so pg_backend_memory_contexts shows contexts such as "ExecutorState", "TupleSort main", "HashTableContext" or "HashAgg meta context". In 18 the view has type and path columns.
tuplesort.c, logtape.c: sorting and spilling.
nodeHash.c, nodeAgg.c, nodeMemoize.c: hash-based nodes.
buffile.c, fd.c: temporary files.
localbuf.c: temp_buffers.

How the limits are enforced

There is no per-backend total: each node checks only its own budget.

Sort: tuplesort_begin_common() sets availMem from work_mem (or maintenance_work_mem for index builds); USEMEM() / LACKMEM() track it, and when it runs out inittapes() switches to an external merge on a LogicalTapeSetCreate() tape set backed by a BufFile. tuplesort_method_name() gives EXPLAIN its "quicksort" / "external merge" text.
Hash join: ExecChooseHashTableSize() sizes buckets and batches from get_hash_memory_limit() (work_mem × hash_mem_multiplier). While building, exceeding spaceAllowed calls ExecHashIncreaseNumBatches(); first ExecHashIncreaseBatchSize() may double spaceAllowed instead when the per-batch file buffers would cost more than a bigger table.
HashAggregate: hash_agg_set_limits() derives memory and group limits; hash_agg_check_limits() measures the contexts with MemoryContextMemAllocated() and calls hash_agg_enter_spill_mode() when over. Memoize uses the same hash limit.
Temp files: OpenTemporaryFile() creates files under pgsql_tmp in temp_tablespaces or the default tablespace; FileWriteV() adds to temporary_files_size and raise the temp_file_limit error; ReportTemporaryFileUsage() writes the log_temp_files line when a temporary file is deleted.
VACUUM: dead_items_alloc() in vacuumlazy.c picks autovacuum_work_mem or maintenance_work_mem and creates a TidStoreCreateLocal() (or TidStoreCreateShared() for parallel vacuum) radix tree from src/include/lib/radixtree.h.

Local buffers

InitLocalBuffers() allocates the descriptor array and hash on the first temp-table access, so temp_buffers can only be changed before that. LocalBufferAlloc() and GetLocalVictimBuffer() mirror the shared path without locks; GetLocalBufferStorage() allocates the page memory lazily, in growing chunks.

Observe and experiment

SET trace_sort = on (a developer option) logs every sort phase with memory and run counts.
SELECT pg_log_backend_memory_contexts(pid) dumps another backend's tree to the server log.
Regression suites: src/test/regress/sql/tuplesort.sql, join_hash.sql (batch growth, parallel hash) and src/test/modules/test_tidstore.

Parameters that shape this

work_mem hash_mem_multiplier maintenance_work_mem autovacuum_work_mem temp_buffers logical_decoding_work_mem log_temp_files temp_file_limit max_parallel_workers_per_gather max_parallel_maintenance_workers max_connections