How to tune work_mem in PostgreSQL

PostgreSQL queries that sort or hash more data than work_mem permits spill to temporary files. Raising the setting for the right workload can move those operations back into memory, reducing temp file I/O without changing the SQL text.

work_mem is a per-operation memory cap for internal Sort and Hash nodes, not a per-session total. One query can use several allocations, parallel workers can multiply them, and hash operations can use work_mem multiplied by hash_mem_multiplier. A spill usually appears in EXPLAIN (ANALYZE, BUFFERS) as Sort Method: external merge Disk: … or non-zero temp read and temp written buffers.

A higher value should be tested against a representative query before changing defaults. Apply broad settings only after estimating concurrent sessions, active Sort or Hash nodes, and available memory, then monitor pg_stat_database temp file counters to confirm disk spills fall without causing swap or out-of-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=17288.82..17538.82 rows=100000 width=32) (actual time=115.755..145.872 rows=100000.00 loops=1)
      Sort Key: total
      Sort Method: external merge  Disk: 4680kB
      Buffers: shared hit=805, temp read=1744 written=1873
      ->  Seq Scan on orders  (cost=0.00..1802.00 rows=100000 width=32) (actual time=0.009..10.715 rows=100000.00 loops=1)
            Buffers: shared hit=802
    Planning:
      Buffers: shared hit=88
    Planning Time: 0.314 ms
    Execution Time: 152.437 ms
   (10 rows)

   EXPLAIN (ANALYZE, BUFFERS) executes the statement. Use read-only test queries, a staging database, or an explicit rollback plan before analyzing write statements on production data.

   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=10106.82..10356.82 rows=100000 width=32) (actual time=80.192..105.927 rows=100000.00 loops=1)
      Sort Key: total
      Sort Method: quicksort  Memory: 8278kB
      Buffers: shared hit=805
      ->  Seq Scan on orders  (cost=0.00..1802.00 rows=100000 width=32) (actual time=0.013..15.192 rows=100000.00 loops=1)
            Buffers: shared hit=802
    Planning:
      Buffers: shared hit=88
    Planning Time: 0.473 ms
    Execution Time: 114.523 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 or Hash node and can be allocated several times by one query. A global increase can create memory spikes when many sessions run sorting or hashing queries at the same time.

   Use scoped defaults when only one workload needs the larger value: 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   |          1 | 4680 kB      | 
   (1 row)