Sort Spill (Temp Files from Sorts)

Overview

When Postgres sorts rows for ORDER BY, GROUP BY, or merge joins and the intermediate result exceeds work_mem, it spills to disk as a temporary file. Disk I/O is orders of magnitude slower than memory—a sort that spills is often 10-100x slower than one that fits in work_mem. Temp file spills also consume disk space and increase I/O pressure on all concurrent queries.

insightral detects spills at both the database level (pg_stat_database.temp_files) and per query (pg_stat_statements.temp_blks_written). The preferred fix is usually an index that allows an index scan to return rows in sorted order rather than requiring a sort step. Increasing work_mem is a secondary lever—it helps for queries where a sort is unavoidable.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Databases with significant temp file usage (likely sort spills)
SELECT
  datname,
  temp_files,
  pg_size_pretty(temp_bytes) AS temp_bytes_pretty,
  temp_bytes,
  blks_read,
  blks_hit,
  round(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 1) AS cache_hit_pct
FROM pg_stat_database
WHERE temp_files > 0
  AND datname NOT IN ('postgres', 'template0', 'template1')
ORDER BY temp_bytes DESC;

-- Per-query sort spill via pg_stat_statements (requires extension)
SELECT
  left(query, 80)          AS query_snippet,
  calls,
  temp_blks_written,
  mean_exec_time::numeric(10,2) AS mean_ms,
  total_exec_time::numeric(12,2) AS total_ms
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Increase work_mem for the specific session or role (not globally)
SET work_mem = '64MB';  -- session-level, reset on disconnect

-- For a specific role that runs heavy sorts
ALTER ROLE <analytics_role> SET work_mem = '128MB';

-- Add an index to avoid sort altogether
CREATE INDEX CONCURRENTLY idx_<table>_<sort_column>
  ON <table> (<sort_column>);

Worked example

Common false positives

• Intentional large batch exports or ETL jobs that always spill by design — consider using a dedicated read replica with higher work_mem for these.
• Temp files from hash joins (pg-r09) show up in the same counters — check EXPLAIN to distinguish sort spills from hash spills.