The reporting service had a simple pattern: every request opened a transaction, ran CREATE TEMP TABLE stage_result AS followed by the query of the day, read from it, and let session end do the cleanup. It worked beautifully for three months. Then migrations started timing out, new connections took two hundred milliseconds longer than they used to, and pg_attribute — the system catalog that stores column definitions — weighed nine gigabytes with six million dead tuples. Nobody had shipped a feature. We had just executed DDL four hundred times a minute for a quarter, and the catalogs kept the receipts.
Why do temp tables bloat the system catalogs?
Because a temp table is real DDL with real catalog rows, and every create-and-drop cycle leaves dead tuples behind in the system catalogs that every backend on that database reads. One CREATE TEMP TABLE inserts a pg_class row for the heap, another for its toast table if it needs one, one per index, one pg_attribute row per column, and pg_type rows for the composite type the table implicitly creates — call it fifteen to twenty catalog rows for a modest table. Session end or DROP deletes all of them. Deletes leave dead tuples; dead tuples are bloat. Our pattern burned three temp tables per request, so roughly fifty catalog row-churns per request, twenty-four thousand a minute at peak. The temp data itself is innocent here: temp heaps are unlogged, generate no WAL, and vanish with the session. The catalog churn is the cost nobody budgets for, and it lands on pg_class, pg_attribute, and pg_type — tables the planner, the relcache, pg_dump, and every migration tool read constantly.
Does autovacuum clean the catalogs — and why couldn't it keep up?
Yes, autovacuum treats system catalogs as ordinary vacuum targets — their live and dead tuple counts and vacuum timestamps sit right there in pg_stat_sys_tables — but it could not keep up, for one structural reason and one arithmetic one. Structurally, the temp heaps themselves are exempt: they live in the per-session pg_temp_N schema that no other process can see, so autovacuum can never touch them, only the catalog rows they leave behind. Arithmetically, the trigger is a scale factor on live tuples: once pg_attribute held tens of millions of live rows, the default 0.2 scale factor tolerated millions of dead tuples before firing, so the catalog ran bloated as its steady state. The steady-state tool is plain VACUUM on the hot catalogs from a superuser during quiet windows — VACUUM pg_catalog.pg_attribute works fine and never blocks ordinary reads and writes — its SHARE UPDATE EXCLUSIVE lock only fights other vacuum or analyze runs and stronger DDL. When it has gone too far, the reclamation path is VACUUM FULL on the catalog inside a maintenance window; pg_repack will not process system catalogs, so there is no online escape hatch. Both facts argue for killing the churn at the source rather than grooming it forever.
How do you diagnose catalog bloat from temp churn?
pg_stat_sys_tables is the catalog twin of pg_stat_user_tables, and sorting it by dead tuples names the victims in one query:
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup::numeric / greatest(n_live_tup, 1), 2) AS dead_ratio,
last_autovacuum, last_vacuum
FROM pg_stat_sys_tables
WHERE schemaname = 'pg_catalog'
ORDER BY n_dead_tup DESC
LIMIT 10;
SELECT n.nspname AS temp_schema, count(*) AS temp_tables
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE 'pg_temp_%' AND c.relkind = 'r'
GROUP BY n.nspname
ORDER BY temp_tables DESC;The second query counts live temp tables per backend schema — every session's temp tables are visible in pg_class even though their data is not — and watching it for a minute tells you whether the fleet's temp habit is growing — though a constant count can hide furious create-and-drop cycling, so read the rate from the dead-tuple trend in the first query, not from this census. Corroborate with symptoms: rising time-to-first-query after connect as the relcache warms against fatter catalogs, DDL that keeps getting slower, a pg_dump that grows in step with pg_class. If dead_ratio on pg_attribute is over 0.5 and last_autovacuum is recent, autovacuum is running and losing, which is precisely the signature of churn rather than neglect.
How does connection pooling amplify the churn?
Poolers multiply the number of sessions and rotate them under request traffic, so per-request temp DDL gets re-executed on every checkout instead of once per application process — a transaction-mode pooler turns each backend into a conveyor belt of create-use-drop cycles, and the catalogs see the full rate of all of them. The ON COMMIT options change the arithmetic in a way that surprises people. ON COMMIT DROP removes the catalog rows at commit: convenient, but it converts per-session churn into per-transaction churn, which is strictly worse for the catalogs. ON COMMIT DELETE ROWS truncates the data but keeps the catalog rows for the session's life, which is the cheapest temp pattern there is behind a pooler — create once with IF NOT EXISTS, reuse with DELETE FROM. If your pooling mode fights session-lifetime state, that is a known trap family of its own; the PgBouncer transaction-mode notes cover the surrounding gotchas.
What should you use instead of per-request temp tables?
Whatever removes DDL from the request path. My order of preference, earned in that order: first, CTEs and subqueries — since PostgreSQL 12 the planner inlines the single-use, side-effect-free ones, and most report staging never needed a materialized table at all; ours turned out to need one in about a fifth of cases. Second, pass the row set in as a parameter — an array unnested in the query, or jsonb — when the staging input is small and comes from the application. Third, when you genuinely need a shared scratch area, one permanent UNLOGGED table keyed by a job or session identifier: INSERT ... SELECT into it, read, DELETE by key — unlogged skips WAL just like temp, and one table means one set of catalog rows forever instead of fifty per request. Keep real temp tables for what they are good at: multi-step, session-local work like ETL inside a single connection, where session end is exactly the cleanup you want. The fix for catalog bloat is never "stop using temp tables"; it is "stop running DDL per request", which is a smaller and more achievable rule.
Watching catalog health with MonPG
Catalog bloat is counter-shaped like everything else in this series: n_dead_tup and dead ratios on pg_class, pg_attribute, and pg_type, autovacuum run counts on those same relations, and the temp-table census from pg_class. MonPG's PostgreSQL monitoring graphs dead tuples and vacuum activity per relation — catalogs included — alongside connection and statement latency, so the correlation that took us a week to assemble by hand (temp churn up, catalog dead tuples up, connect latency up) shows up on one screen. For the vacuum half of the story, the PostgreSQL vacuum guide is the companion read. Fix the request pattern first; then let the counters prove the catalogs are healing.