Temporary files generated at scale — work_mem too small

Your storage shows heavy writes during query execution, and pg_stat_database shows temp_files accumulating — sorts and hash joins are spilling to disk.

Diagnose it

-- Databases generating temp files:
SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
WHERE temp_files > 0
ORDER BY temp_bytes DESC;

-- Enable logging of large temp file creation for query identification:
-- log_temp_files = 10MB  (in postgresql.conf — log files larger than 10 MB)

Once log_temp_files is set, check the PostgreSQL log for lines like
temporary file: path "base/pgsql_tmp/...", size 524288000 — these identify
the queries responsible.

Why it happens

Sort operations (ORDER BY, DISTINCT, merge joins), hash
aggregations, and hash joins each receive up to work_mem of in-memory space.
If the data exceeds that budget, PostgreSQL writes it to a temporary file in
temp_tablespaces (or the default tablespace). A complex query can create
multiple sort/hash nodes simultaneously, each consuming up to work_mem.