A batch job was slower than it should’ve been. EXPLAIN (ANALYZE, BUFFERS) showed temp read=1.4GB. The query was sorting a big intermediate result.

I knew about work_mem. Today I learned about temp_buffers, which is the per-session cache for temporary tables and temp files. Default: 8MB. If you’re doing big sorts or using temporary tables, you’re hitting it.

SET LOCAL temp_buffers = '256MB';

The session’s batch job got ~30% faster from just this. Didn’t help as much as bumping work_mem, but cheap to do together.

Note: temp_buffers can only be set before any temp tables are used in a session. If you try to change it mid-session after a temp table exists, Postgres will reject it.

Between work_mem, temp_buffers, and maintenance_work_mem, the three per-session memory knobs cover most “I need more RAM for this one query” cases. Each is for a different phase:

  • work_mem: hash/sort operations within a query node.
  • temp_buffers: temporary tables and temp files.
  • maintenance_work_mem: CREATE INDEX, VACUUM, etc.

Future me: when EXPLAIN shows temp read=... or temp written=..., look at temp_buffers, not just work_mem.