The disk alert fired at 03:40 on a PostgreSQL 15 cluster that had been healthy for months. The partition holding base/pgsql_tmp had filled, and by the time I looked, the offending query had already failed and cleaned up after itself, which is the maddening thing about temp-file incidents: the evidence deletes itself. The culprit turned out to be a nightly aggregation whose sort needed just over 4GB, against a work_mem of 256MB. EXPLAIN ANALYZE on a rerun said it plainly: Sort Method: external merge, Disk: 4180224kB. Nobody had ever read the plan, because the query usually finished, and temp files were not being logged.
Six months earlier, on the MySQL 8.0 system this platform replaced, I had chased the same disease with completely different symptoms: no disk alert, just a steadily climbing Created_tmp_disk_tables counter, around 40,000 on-disk temp tables an hour during the reporting window, and a reporting workload that got slower every quarter as the data grew. Same physics, different machinery, different telemetry.
Both engines answer GROUP BY, ORDER BY, DISTINCT, and friends with in-memory work areas that spill to disk when they overflow. This article covers when each engine spills, what the spill looks like in each one's diagnostics, and how to fix the query before you reach for the limit knobs, because raising limits is how you turn a slow query into a full disk.
When does each engine spill to disk?
MySQL spills when an internal temporary table outgrows its in-memory limit; PostgreSQL spills when a single sort or hash operation outgrows work_mem. The two limits sound similar and are not: MySQL's applies to a temporary table that materializes intermediate rows, PostgreSQL's applies per operation, per node of the plan, so one complex query can legitimately use several times work_mem at once.
On MySQL, the optimizer builds an internal temporary table for operations like GROUP BY or DISTINCT when no index can produce the order or grouping for free. You see the decision in EXPLAIN as Using temporary in the Extra column, often next to Using filesort. The in-memory size limit depends on the engine: for the legacy MEMORY engine it is the smaller of tmp_table_size and max_heap_table_size, both 16MB by default; for the default TempTable engine the ceiling is temptable_max_ram, 1GB by default, after which the data moves to disk. One historical trap worth knowing: MEMORY-engine temp tables cannot hold TEXT or BLOB columns, so a query grouping by a long VARCHAR or a TEXT expression could be forced to disk from the very first row, no matter the limits. TempTable removed that particular cliff.
On PostgreSQL, the spill decision is per executor node. A sort that does not fit work_mem becomes an external merge sort writing temp files; a hash aggregate or hash join that does not fit splits into batches and spills partitions to disk, a behavior hash aggregation gained in PostgreSQL 13. The plan tells you afterwards:
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_type, date_trunc('hour', created_at) AS hr, COUNT(*)
FROM events
WHERE created_at >= now() - interval '30 days'
GROUP BY 1, 2
ORDER BY 2, 3 DESC;
-- Sort (actual time=...)
-- Sort Method: external merge Disk: 4180224kB
-- or for a hash aggregate past work_mem (PG 13+):
-- HashAggregate ... Batches: 8 Disk Usage: 913408kBThe number to internalize is that Disk: figure in kilobytes: it is the spill volume for one execution of one node, and it is what your pgsql_tmp filesystem must survive when three reports run at once.
How do you see spills happening in production?
On MySQL, watch the Created_tmp_tables and Created_tmp_disk_tables status counters; on PostgreSQL, turn on log_temp_files and read pg_stat_database's temp_bytes column. Neither engine logs spills by default in a way that pages you, which is why both of my incidents were discovered late.
-- MySQL 8.0: the ratio matters more than the raw numbers
SHOW GLOBAL STATUS
WHERE Variable_name IN ('Created_tmp_tables', 'Created_tmp_disk_tables');
-- a rising on-disk share during reporting windows is the early warning
-- PostgreSQL: cluster-wide cumulative spill volume per database
SELECT datname,
temp_files,
pg_size_pretty(temp_bytes) AS spilled
FROM pg_stat_database
WHERE temp_bytes > 0
ORDER BY temp_bytes DESC;Set log_temp_files to a threshold that means something; I use 64MB, which catches real spills without logging every small sort. The log line includes the size and the statement, which is how the 03:40 incident got its postmortem a day late. On the MySQL side, the sys schema and performance_schema can attribute temp table usage per statement digest, which is the per-query view the global counters lack; the sys schema daily-driver workflow covers that attribution habit. The single-engine deep dives exist too: when MySQL internal temp tables go to disk and PostgreSQL temp files and work_mem spills.
What did MySQL 8.0's TempTable engine actually change?
It replaced the MEMORY storage engine as the default for in-memory internal temporary tables and raised the practical ceiling before disk from 16MB to 1GB. The engine behind internal_tmp_mem_storage_engine defaults to TempTable in MySQL 8.0, and temptable_max_ram, default 1GB, bounds how much RAM TempTable may use before the overflow is written out, by default through memory-mapped files rather than full InnoDB on-disk tables. On-disk internal temp tables have lived in dedicated session temporary tablespaces since 8.0.16, which is why the old advice about the runaway ibtmp1 file mostly belongs to the 5.7 era.
The operational consequence is that MySQL 8.0 spills later and recovers faster than 5.7 did, and many old tuning posts telling you to align tmp_table_size with max_heap_table_size are solving a problem the default configuration no longer has. What did not change: a query whose intermediate result is genuinely bigger than any sane memory limit will still spill, and the counters still count it. The engine upgrade moved the cliff; it did not remove the cliff.
How do you fix a spill instead of just raising limits?
Fix the plan first, because a spill is usually a symptom of intermediate results the query never needed to materialize. On both engines, the wins in rough order of leverage: filter earlier so less data reaches the grouping or sorting step; give ORDER BY and GROUP BY an index that produces their order natively, which eliminates the sort node entirely; and pre-aggregate, because grouping a billion raw rows to produce a thousand daily buckets is what summary tables and materialized views exist for.
When the limit does need raising, do it surgically. On PostgreSQL, set work_mem per role or per session for the reporting workload rather than globally:
-- PostgreSQL: big analytical allowance for one role only
ALTER ROLE reporting SET work_mem = '1GB';
-- and a hard ceiling so a runaway cannot fill the disk:
ALTER ROLE reporting SET temp_file_limit = '8GB';temp_file_limit, unlimited by default, is the guardrail that would have turned my 03:40 disk-full into a clean query cancellation, and it is the first setting I add to any cluster that runs ad-hoc analytics. Remember the per-node arithmetic before you size work_mem: a plan with three sorts and two hash joins can hold five times work_mem live at once, per connection, so global generosity multiplied by connection count is how out-of-memory kills happen.
On MySQL the surgical options are thinner: there is no per-role work_mem, so you raise tmp_table_size, max_heap_table_size, or temptable_max_ram globally, or you fix the query. In practice, on 8.0, I leave the limits alone and treat a rising on-disk share as a query-review trigger, because the default TempTable ceiling is already generous and anything regularly blowing past it is telling you the aggregation belongs somewhere else.
How MonPG watches the spill line on PostgreSQL
Spills are a trend before they are an incident: temp_bytes climbs for weeks while the disk holds, and the first you hear of it is the night it doesn't. MonPG's PostgreSQL monitoring keeps the history that makes the trend visible: per-database temp file volume alongside the statement statistics that identify which normalized query is spilling, so the 03:40 disk alert becomes a Tuesday-afternoon ticket three weeks earlier, while the fix is still a work_mem bump or a rewritten GROUP BY instead of an emergency.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the counters from this article are the ones it will surface: Created_tmp_disk_tables as a first-class metric with its ratio to in-memory temp tables, and per-digest attribution so the guilty query family is named in the alert. Until then, the MySQL monitoring page tracks that work, and the shared lesson holds: internal temp tables are the price of a plan the data has outgrown, and the database will keep paying it quietly until the disk sends the invoice.