How to tune work_mem in PostgreSQL

Adjusting work_mem reduces temporary disk I/O during large sorts and hash operations, improving query latency and keeping storage from becoming a bottleneck on busy databases.

work_mem is a per-operation memory cap used by PostgreSQL for internal Sort and Hash nodes, so a single query can consume multiple allocations at once, with parallel workers further multiplying memory demand. When an operation exceeds the limit, it spills to temporary files, which typically shows up as temp read and temp written buffers or a Disk value in EXPLAIN (ANALYZE, BUFFERS) output, and hash operations may be further scaled by hash_mem_multiplier on newer releases.

A higher value can be a win for specific heavy queries but becomes dangerous when applied globally without a concurrency budget. Changes should be validated on representative workload with EXPLAIN (ANALYZE, BUFFERS) and monitored after deployment to confirm temp file writes drop without creating memory pressure.

1. Check the current work_mem default.

   $ sudo -u postgres psql -Atc "SHOW work_mem;"
   4MB

2. Capture an EXPLAIN (ANALYZE, BUFFERS) plan for a slow query that spills to disk.

   $ sudo -u postgres psql -d appdb -c "SET work_mem = '64kB'; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY total;"
   SET
                                                        QUERY PLAN                                                     
   --------------------------------------------------------------------------------------------------------------------
    Sort  (cost=18349.82..18599.82 rows=100000 width=36) (actual time=46.173..58.948 rows=100000 loops=1)
      Sort Key: total
      Sort Method: external merge  Disk: 4928kB
      Buffers: shared hit=837, temp read=1838 written=1994
      ->  Seq Scan on orders  (cost=0.00..1834.00 rows=100000 width=36) (actual time=0.005..4.981 rows=100000 loops=1)
            Buffers: shared hit=834
    Planning:
      Buffers: shared hit=107
    Planning Time: 0.157 ms
    Execution Time: 61.848 ms
   (10 rows)

   Disk spills typically appear as Sort Method: external merge Disk: … or as non-zero temp read and temp written buffers on Sort/Hash nodes.

   Related: How to explain a query plan in PostgreSQL

3. Set a higher work_mem value for a single session.

   $ sudo -u postgres psql -d appdb -c "SET work_mem = '64MB'; SHOW work_mem;"
   SET
    work_mem 
   ----------
    64MB
   (1 row)

   Session tuning validates impact without changing instance defaults.

4. Re-run the same EXPLAIN (ANALYZE, BUFFERS) to confirm sorts or hashes stay in memory.

   $ sudo -u postgres psql -d appdb -c "SET work_mem = '64MB'; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY total;"
   SET
                                                        QUERY PLAN                                                     
   --------------------------------------------------------------------------------------------------------------------
    Sort  (cost=10138.82..10388.82 rows=100000 width=36) (actual time=30.431..33.756 rows=100000 loops=1)
      Sort Key: total
      Sort Method: quicksort  Memory: 9323kB
      Buffers: shared hit=837
      ->  Seq Scan on orders  (cost=0.00..1834.00 rows=100000 width=36) (actual time=0.004..4.802 rows=100000 loops=1)
            Buffers: shared hit=834
    Planning:
      Buffers: shared hit=107
    Planning Time: 0.147 ms
    Execution Time: 36.687 ms
   (10 rows)

5. Apply a conservative default with ALTER SYSTEM after consistent gains under expected concurrency.

   $ sudo -u postgres psql -c "ALTER SYSTEM SET work_mem = '16MB';"
   ALTER SYSTEM

   work_mem applies per Sort/Hash node and can be allocated multiple times per query, so global increases can cause large memory spikes under high concurrency.

   Scoped defaults: ALTER ROLE appuser SET work_mem = '64MB';
   ALTER DATABASE appdb SET work_mem = '64MB';

6. Reload the PostgreSQL configuration to activate the new default.

   $ sudo -u postgres psql -Atc "SELECT pg_reload_conf();"
   t

   systemd based hosts can reload with sudo systemctl reload postgresql when a service unit is available.

   Over-allocating work_mem can exhaust RAM and trigger severe swapping or an OOM kill during peak load.

7. Verify the new default in a fresh session.

   $ sudo -u postgres psql -Atc "SHOW work_mem;"
   16MB

8. Watch database temp file counters to confirm spill reduction after deployment.

   $ sudo -u postgres psql -d appdb -c "SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_written, stats_reset FROM pg_stat_database WHERE datname = current_database();"
    datname | temp_files | temp_written | stats_reset 
   ---------+------------+--------------+-------------
    appdb   |          2 | 9856 kB      | 
   (1 row)