PostgreSQL work_mem and Sort Spill

Performance / Guide 37 of 47 / Advanced

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Test data400,000 rows, 2 core / 4 GB host
• TimeAbout 16 min

work_mem is per sort or hash per node per worker, not per query. A parallel query with several sorts can use many multiples of it, which is why raising it globally is riskier than it looks.

This guide includes

Use this when a sort is slow and you suspect it is spilling to disk. This matters because work_mem is per operation rather than per connection - and the spill is measurable both in the plan and server-wide.

• sorting more than work_mem can hold, and watching it spill
• raising work_mem for the session only, and measuring again
• finding spills across the whole database with pg_stat_database

Before you start

• The events table with 400,000 rows.

1. 1

   Sort more than work_mem can hold

2. 2

   Raise work_mem and measure again

3. 3

   Find spills across the whole database

Official sources

• PostgreSQL 18 Documentation - Using EXPLAIN
• PostgreSQL 18 Documentation - Index Types
• PostgreSQL 18 Documentation - Partial Indexes
• PostgreSQL 18 Documentation - Routine Vacuuming
• PostgreSQL 18 Documentation - pg_stat_statements