Disk-Based Sort Spill

Scenario

An analytics team reports that monthly report queries with ORDER BY created_at DESC are slow and taking 45+ seconds. Disk I/O on the database server spikes during these runs. The DBA notices large files appearing and disappearing in $PGDATA/base/pgsql_tmp/ during query execution.

How to Identify

Conditions:

• EXPLAIN ANALYZE shows Sort Method: external merge Disk instead of quicksort Memory
• Large files appearing in $PGDATA/base/pgsql_tmp/ during query execution
• pg_stat_bgwriter shows elevated buffers_alloc
• pg_stat_database.temp_files and temp_bytes increasing
• log_temp_files in postgresql.conf captures the events in server logs

Analysis Steps

-- 1. Identify sort spill in query plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events ORDER BY created_at DESC;
-- Look for: Sort Method: external merge Disk

-- 2. Check temp file stats at database level
SELECT datname, temp_files, temp_bytes,
       pg_size_pretty(temp_bytes) AS temp_size
FROM   pg_stat_database
WHERE  datname = current_database();

-- 3. Check current work_mem
SHOW work_mem;

-- 4. Monitor active temp file usage (PG 13+)
SELECT pid, query, wait_event, state,
       query_start
FROM   pg_stat_activity
WHERE  state = 'active';

-- 5. Estimate memory needed to sort in-memory
-- Rule of thumb: ~2x the result set size
-- For 1M rows * 100 bytes avg = 100MB * 2 = ~200MB work_mem needed

Pitfalls

• Do not increase work_mem globally to a high value. Each backend can use multiple sort operations simultaneously. With 100 connections each running 3 sorts at 256MB: 100 × 3 × 256MB = 76GB potential RAM usage.
• external merge Disk in the Sort node is the diagnostic signal, not external sort. Both exist — know the difference.
• Adding an index on the ORDER BY column eliminates the sort entirely for queries that use it — often a better fix than increasing work_mem.
• log_temp_files = 0 logs ALL temp files. Set a reasonable threshold (e.g., log_temp_files = 10240 for 10MB+) to avoid log flooding.

Resolution Approach

Two approaches — choose based on query pattern: