Detecting work_mem Drift Across Sessions #

The most confusing performance report a database team receives is “the same query is fast for me and slow for the batch job.” The SQL text is identical, the data is identical, the plan shape is often identical — yet one connection finishes in memory and another writes gigabytes of temporary files to disk. The variable that has drifted is almost never in the query. It is work_mem, resolved independently on every backend, and it is the classic form of session parameter drift. This page shows how to prove that drift is the cause, trace exactly which layer set the value, and pin it so the drift cannot recur.

Why work_mem Is Per-Session, Not Per-Query #

work_mem bounds the memory a single sort, hash, or materialize operation may use before it spills to temporary files on disk. Critically, it is a resettable GUC evaluated at execution time against whatever value the current backend holds — so two connections executing byte-for-byte identical SQL can make opposite spill decisions. Understanding the resolution order is the whole diagnosis, because a value can be injected at five different layers:

Layer 1 — the compiled default (4MB on most builds). Used only if nothing overrides it.

Layer 2 — postgresql.conf (the configuration file source). The instance-wide baseline every new backend starts from.

Layer 3 — ALTER DATABASE ... SET work_mem. Applied to every connection opened against that database.

Layer 4 — ALTER ROLE ... SET work_mem. Applied to every connection authenticating as that role, overriding the database-level value.

Layer 5 — a session SET work_mem. An explicit statement on the live connection, the highest-priority source, and the one most likely to leak through a connection pooler that does not reset sessions on check-in.

Later layers win. The drift you are chasing is the difference between which layer “won” on a fast backend versus a slow one. The diagram below traces both the resolution order and the two outcomes the same query can produce.

work_mem resolution order and divergent spill outcomes The left column shows work_mem resolved through five layers from default up to a session SET, with later layers overriding earlier ones. Two sessions inherit different effective values: Session A at 4MB spills to disk with external merge, Session B at 256MB sorts in memory with quicksort. Resolution order (later wins) 1. compiled default (4MB) 2. postgresql.conf 3. ALTER DATABASE SET 4. ALTER ROLE SET 5. session SET (may leak) Session A: work_mem = 4MB Sort Method: external merge Disk: 61440kB — spills temp_blks_written > 0 Session B: work_mem = 256MB Sort Method: quicksort Memory: 48128kB — in RAM temp_blks_written = 0 Same SQL, same data — only the resolved work_mem differs

The Query That Behaves Two Ways #

Consider a reporting query over an analytics events table that aggregates page views per session. It sorts a large intermediate result before a GROUP BY. On the batch connection it spills; on the analyst’s psql session it does not.

EXPLAIN (ANALYZE, BUFFERS)
SELECT s.session_id, count(*) AS view_count, max(e.event_ts) AS last_seen
FROM sessions_log s
JOIN events e ON e.session_id = s.session_id
WHERE e.event_type = 'page_view'
GROUP BY s.session_id
ORDER BY view_count DESC;

On the spilling connection, the Sort node underneath the aggregation reveals the problem:

Sort  (cost=184220.5..186720.5 rows=1000000 width=24)
      (actual time=1421.7..1783.2 rows=1000000 loops=1)
  Sort Key: (count(*)) DESC
  Sort Method: external merge  Disk: 61440kB     -- ← spilled: work_mem too low for this backend
  Buffers: shared hit=9200 read=41000, temp read=7680 written=7680  -- ← 7680 temp blocks written
  ->  HashAggregate  (cost=...) (actual rows=1000000 loops=1)
        Group Key: s.session_id
        ->  Hash Join  (cost=...) (actual rows=4200000 loops=1)
              Hash Cond: (e.session_id = s.session_id)

The single diagnostic line is Sort Method: external merge Disk: 61440kB — the sort needed roughly 60 MB but work_mem on this backend capped it below that, so it round-tripped through temporary files. Note that these cost= numbers are the planner’s unitless estimates; the actual time values are the ones measured in milliseconds. The Sort Method and Disk fields are the same signals covered in sort and hash node analysis, applied here to a session-level cause rather than a query-level one.

Auditing work_mem Per Connection #

Do not guess the effective value — read it on the exact backend that spilled.

-- What this backend actually resolved:
SELECT current_setting('work_mem');   -- e.g. '4MB'

Then find where that value came from. The source column of pg_settings is the key:

SELECT name, setting, unit, reset_val, source
FROM pg_settings
WHERE name IN ('work_mem', 'hash_mem_multiplier');
-- source = 'session'        -> a SET on this connection (possibly a leaked one)
-- source = 'role'           -> ALTER ROLE ... SET work_mem
-- source = 'database'       -> ALTER DATABASE ... SET work_mem
-- source = 'configuration file' -> postgresql.conf baseline
-- source = 'default'        -> compiled-in default

A source of session on a pooled connection that the application never deliberately set is the smoking gun for a leaked SET. To see other backends, join pg_stat_activity and inspect roles and databases, then confirm the per-role and per-database overrides directly:

-- Per-role and per-database overrides configured on the cluster:
SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL;
SELECT datname, datconfig FROM pg_database WHERE datconfig IS NOT NULL;

Finally, correlate spills with statements over time using pg_stat_statements, which accumulates temp I/O per normalized query regardless of which backend ran it:

SELECT queryid, calls, temp_blks_written,
       round(temp_blks_written::numeric / NULLIF(calls, 0), 1) AS temp_per_call
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;
-- High temp_blks_written concentrated on one queryid = a repeatedly-spilling sort or hash

Step-by-Step Resolution Workflow #

Step 1 — Reproduce on the affected connection #

Run the report with EXPLAIN (ANALYZE, BUFFERS) on the slow backend and record the Sort Method, any Disk: figure, and the temp written count in the Buffers line. These are your before-state numbers.

Step 2 — Read the effective work_mem #

On that same connection, run SELECT current_setting('work_mem'). If it disagrees with what you expected, the value drifted.

Step 3 — Identify the layer #

Query pg_settings for setting, reset_val, and source. The source value names the exact layer to change — and reset_val shows what the connection would fall back to if the session-level override were reset.

Step 4 — Correlate spills across statements #

Use pg_stat_statements.temp_blks_written to confirm the query is a chronic spiller and not a one-off, so you fix the right workload.

Step 5 — Pin the value at the right scope #

For a durable reporting workload, pin it at the role level so every connection for that workload inherits the same value:

-- Every backend authenticating as reporting_user now starts at 256MB:
ALTER ROLE reporting_user SET work_mem = '256MB';

For a single heavy query inside an otherwise normal session, scope it transactionally so it cannot leak:

BEGIN;
SET LOCAL work_mem = '256MB';   -- reverts automatically at COMMIT/ROLLBACK
EXPLAIN (ANALYZE, BUFFERS)
SELECT s.session_id, count(*) AS view_count, max(e.event_ts) AS last_seen
FROM sessions_log s
JOIN events e ON e.session_id = s.session_id
WHERE e.event_type = 'page_view'
GROUP BY s.session_id
ORDER BY view_count DESC;
COMMIT;

SET LOCAL is the safest tool in a pooled environment because it is bound to the transaction, not the connection, so it cannot survive check-in.

Step 6 — Validate #

Re-run the EXPLAIN (ANALYZE, BUFFERS) and confirm the Sort Method line changed and the temp written count fell to zero.

Before/After Plan Comparison #

-- BEFORE: work_mem = 4MB on this backend, sort spills
Sort  (actual time=1421.7..1783.2 rows=1000000)
  Sort Method: external merge  Disk: 61440kB
  Buffers: ... temp read=7680 written=7680      -- temp files written

-- AFTER: work_mem = 256MB (pinned at role level), sort stays in memory
Sort  (actual time=612.4..701.9 rows=1000000)
  Sort Method: quicksort  Memory: 48128kB
  Buffers: ... (no temp read/written)           -- zero temp I/O

The plan shape is unchanged — same Sort, same HashAggregate, same Hash Join. Only the Sort Method flipped from external merge Disk to quicksort Memory, and the temp I/O vanished. That is the fingerprint of a pure work_mem fix as opposed to an indexing or statistics fix.

Common Pitfalls #

A leaked SET on a pooled connection. If application code issues SET work_mem and the pooler does not run a reset (such as DISCARD ALL) on check-in, the next borrower inherits that value. A backend showing source = session for work_mem that nobody meant to set is this bug. Prefer SET LOCAL inside a transaction, or configure the pooler’s server-reset behavior.

Assuming hash nodes obey work_mem directly. On PostgreSQL 13 and later, hash-based operators multiply work_mem by hash_mem_multiplier (default 2.0). A hash join or hash aggregate therefore gets more memory than a sort at the same work_mem, and can keep spilling even after your sorts fit. When a hash node still shows Batches: N while sorts are clean, raise hash_mem_multiplier, not work_mem.

Forgetting that work_mem is per operation, not per query. A single plan can contain several sorts and hashes, and each may claim up to work_mem (or its hash-multiplied allowance) simultaneously. A generous 256 MB value across a plan with four memory-hungry nodes and 40 concurrent connections can demand tens of gigabytes. Scope large values to specific roles or transactions rather than raising the global baseline.

Fixing the instance instead of the workload. Bumping work_mem in postgresql.conf to cure one reporting query silently hands the same allowance to every OLTP backend, inflating peak memory. Pin it at the role or transaction level so only the workload that needs it pays for it — the core discipline of controlling session parameter drift.

Frequently Asked Questions #

Why does the same query spill to disk on one connection but not another? Because work_mem is resolved per session, not per query. Two connections can inherit different values from ALTER ROLE, ALTER DATABASE, or a leftover session-level SET on a pooled connection. When the effective work_mem is below the sort or hash working-set size, PostgreSQL reports Sort Method: external merge Disk and writes temp files; when it is above, it stays in memory with quicksort.

How do I find the effective work_mem for a specific backend? Run SELECT current_setting('work_mem') on that connection, and query pg_settings for the setting, reset_val, and source columns to see where the value came from. The source column distinguishes session (a SET issued on the connection), role, database, configuration file, and default, which tells you which layer to change.

Does raising work_mem for a hash join require the same value as a sort? Not on PostgreSQL 13 and later. Hash-based nodes multiply work_mem by hash_mem_multiplier (default 2.0), so a hash aggregate or hash join can use more memory than a sort at the same work_mem. If a hash node still spills while sorts fit, raise hash_mem_multiplier rather than work_mem itself.

Related