PostgreSQL work_mem: sort spills and local settings

PostgreSQL work_mem controls the base memory allowance for operations such as sorts and hash tables before they need temporary disk storage. It applies to operations within a plan, so one query can use several allowances. Concurrent queries and parallel workers add to the total. Raising it globally requires more care than finding a value that works for one query.

This guide uses a disposable PostgreSQL 18.6 database to show a sort spilling to disk at 64 kB and staying in memory at 32 MB. Both runs return the same 40,000 rows in the same order. The aim is to make the evidence readable and the setting change temporary. Neither value is a production recommendation.

Inspect the settings in the connection you will use

Start by checking the effective settings. The following query includes the unit and configuration context, so a bare number is not mistaken for megabytes:

SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('work_mem', 'hash_mem_multiplier',
               'maintenance_work_mem', 'max_parallel_workers_per_gather')
ORDER BY name;

hash_mem_multiplier|2|<NULL>|user
maintenance_work_mem|65536|kB|user
max_parallel_workers_per_gather|2|<NULL>|user
work_mem|4096|kB|user

In our fresh lab, work_mem is 4096 kB, or 4 MB. The unitless multiplier is 2. The parallel-worker setting initially permits two workers per Gather; the later experiments explicitly set it to zero within their transactions.

The resource configuration reference distinguishes these settings. Hash operations use work_mem multiplied by hash_mem_multiplier. Maintenance operations use separate limits such as maintenance_work_mem. This article measures a sort, not a hash spill or maintenance workload.

Record the current value before a test. An application connection can have settings different from a new administrative connection, so inspecting one does not necessarily explain the behavior of the other.

Build a bounded sorting fixture

Run the SQL below in a disposable database where workmem_lab does not already exist. It creates 40,000 rows with a sortable key and a 128-character payload. There is no index that could supply the requested order:

CREATE SCHEMA workmem_lab;
CREATE TABLE workmem_lab.events AS
SELECT g AS id,
       md5((g * 37)::text) AS sort_key,
       repeat(md5(g::text), 4) AS payload
FROM generate_series(1, 40000) AS g;
ANALYZE workmem_lab.events;
SELECT count(*), sum(id) FROM workmem_lab.events;

40000|800020000

The output confirms the row count and the sum of the IDs. The verification code also checks IDs 1 through 40,000, unique IDs, fixed payload length, and the absence of indexes. ANALYZE supplies statistics for the planner.

The payload is included in the sort query deliberately. Sorting a wider result can require different working space from sorting IDs alone. Keep the selected columns and ordering identical when comparing two settings.

Our test runner creates a new local cluster, binds it to localhost on an available port, uses password authentication, and stops it afterward. The article SQL can also run in an existing disposable database. It should not be pasted into a production database as a tuning exercise.

Capture a sort that spills to disk

The first transaction limits work_mem to 64 kB and excludes parallel workers. EXPLAIN ANALYZE executes the SELECT, while BUFFERS records block activity. TIMING OFF and SUMMARY OFF keep this demonstration focused on plan behavior rather than elapsed-time comparisons:

BEGIN;
SET LOCAL work_mem = '64kB';
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF, COSTS OFF, FORMAT JSON)
SELECT id, sort_key, payload
FROM workmem_lab.events
ORDER BY sort_key, id;
COMMIT;

{
  "Node Type": "Sort",
  "Sort Key": [
    "sort_key",
    "id"
  ],
  "Actual Rows": 40000.0,
  "Actual Loops": 1,
  "Sort Method": "external merge",
  "Sort Space Used": 7176,
  "Sort Space Type": "Disk",
  "Temp Read Blocks": 2678,
  "Temp Written Blocks": 2864
}

The JSON shown after the SQL is an excerpt containing selected fields from the captured Sort node, not the full EXPLAIN document. The complete plan is included in the review package.

In this run, the method is external merge and Sort Space Type is Disk. The node reports 7,176 kB of sort space, 2,678 temporary blocks read, and 2,864 temporary blocks written. Those fields provide direct evidence of a disk-backed sort for this fixture and setting.

The sort-space figure and temporary block counts describe different things. Do not add them together or treat either as the exact work_mem value that would eliminate the spill. The actual row count is 40,000 and the loop count is one, making this a straightforward single-execution example.

The PostgreSQL EXPLAIN documentation explains sort and buffer output. Our EXPLAIN guide covers reading the surrounding plan when a real query has more than one important operation.

Compare a larger transaction-local allowance

Run the same SELECT in another transaction with 32 MB. Keep the fixture, selected columns, ordering, and parallelism unchanged:

BEGIN;
SET LOCAL work_mem = '32MB';
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY OFF, COSTS OFF, FORMAT JSON)
SELECT id, sort_key, payload
FROM workmem_lab.events
ORDER BY sort_key, id;
COMMIT;

{
  "Node Type": "Sort",
  "Sort Key": [
    "sort_key",
    "id"
  ],
  "Actual Rows": 40000.0,
  "Actual Loops": 1,
  "Sort Method": "quicksort",
  "Sort Space Used": 9037,
  "Sort Space Type": "Memory",
  "Temp Read Blocks": 0,
  "Temp Written Blocks": 0
}

For this run, the Sort node reports quicksort, Memory, and 9,037 kB of sort space. Temporary reads and writes are zero. Both plans contain one top-level Sort over the full fixture, and neither contains Gather or Gather Merge.

The in-memory figure is larger than the earlier on-disk sort-space figure. That observed difference is one reason not to use the disk figure as a direct memory-setting prescription. We chose 32 MB to make the change in behavior visible; we did not search for the smallest successful value.

Our verification executes the full query at both settings and compares every returned row, including its payload. All 40,000 rows match in the same order. A separate Python calculation independently reconstructs the sort keys, ordering, and payloads.

This is evidence that the setting changed where the sort worked without changing its result. It is not a speed benchmark. There was one recorded plan at each setting, the cache state was not controlled for performance comparison, and no concurrent workload was running.

Verify that SET LOCAL does not leak into later work

Use an explicit transaction for a temporary change. This example first establishes a 2 MB session value, then changes the local value inside two separate transactions:

SET work_mem = '2MB';
SHOW work_mem;
BEGIN;
SET LOCAL work_mem = '8MB';
SHOW work_mem;
COMMIT;
SHOW work_mem;
BEGIN;
SET LOCAL work_mem = '16MB';
SHOW work_mem;
ROLLBACK;
SHOW work_mem;
RESET work_mem;

2MB
8MB
2MB
16MB
2MB

The sequence is 2 MB, 8 MB, 2 MB, 16 MB, and 2 MB. COMMIT and ROLLBACK both restore the session value that preceded their local changes. The final RESET removes the example's session override. In our lab, the default after RESET is 4 MB.

The SET reference documents this scope. SET LOCAL outside a transaction has no lasting effect and emits a warning. A plain SET can persist for the session after a successful transaction, which matters when a pooled connection is reused.

Keep BEGIN, SET LOCAL, the query, and the transaction end on the same database connection. A connection pool does not make separately issued requests part of the same transaction. Our tests also verify restoration after rolling back to a savepoint, a unitless value of 2048 becoming 2 MB, and rejection of malformed or below-minimum values.

Decide whether more memory addresses the real problem

Before promoting a local experiment into an operational change, identify the specific sort or hash operation, how often the query runs, and what else runs alongside it. Check whether a narrower result, an appropriate index, or an earlier filter removes unnecessary work. A larger allowance cannot fix every poor plan.

The concurrency risk is easy to illustrate without pretending to predict a server's exact memory use. If ten simultaneous queries each have two operations using 32 MB, those operations alone could account for roughly 640 MB. That arithmetic excludes hash multipliers, workers, shared memory, connection overhead, and other allocations. It is a planning example, not a memory cap or a measured workload.

Validate under representative load before adopting a broader setting. Review memory pressure and temporary I/O together, and keep a clear way to restore the previous configuration. Our PostgreSQL hosting guide discusses the operational responsibilities of managed databases and self-managed servers.

Remove the exercise data

When finished, remove only the schema created for this exercise. CASCADE also removes objects inside that schema, so confirm the name before running it:

DROP SCHEMA workmem_lab CASCADE;
SELECT to_regnamespace('workmem_lab') IS NULL AS removed;

t

The final t confirms that the schema no longer exists. Our lab completed 31 checks, removed its fixture, stopped the server, and deleted its temporary password file. The useful result is a repeatable way to connect a temporary setting change to actual plan evidence, with the limits of the experiment kept visible.