Scenario
A CI/CD pipeline creates and drops hundreds of temporary schemas and tables per day for test isolation. After 3 months, DDL operations take 10× longer than on a fresh database. pg_attribute has grown to 2GB. Simple ALTER TABLE commands that should take milliseconds now take seconds.
How to Identify
Conditions:
- DDL operations (CREATE TABLE, ALTER TABLE, DROP) progressively slower
pg_attribute, pg_depend, pg_class consuming unexpectedly large disk space- High dead tuple counts in system catalog tables
SELECT * FROM pg_tables noticeably slow
Analysis Steps
-- 1. Check size of system catalog tables
SELECT
relname,
pg_size_pretty(pg_relation_size(oid)) AS heap_size,
pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
reltuples::BIGINT AS estimated_rows
FROM pg_class
WHERE relnamespace = 'pg_catalog'::regnamespace
AND relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 15;
-- 2. Check dead tuples in catalog tables
SELECT relname, n_live_tup, n_dead_tup,
ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1)
AS dead_pct
FROM pg_stat_sys_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
-- 3. Count actual rows to compare with bloated estimates
SELECT count(*) FROM pg_attribute; -- compare to reltuples in pg_class
Pitfalls
VACUUM FULL on system catalog tables requires an exclusive lock and will block ALL DDL operations for its duration — run only in a maintenance window.- System catalog autovacuum thresholds are separate from user table thresholds. High DDL churn can overwhelm the default catalog autovacuum settings.
- This problem is most common in environments with high DDL churn: CI/CD pipelines, ORM frameworks that auto-create temp tables, or development databases with frequent migrations.
Resolution Approach
Run VACUUM on the specific bloated catalog tables during a low-traffic window. For severe cases, VACUUM FULL reclaims disk space but requires exclusive lock. Prevent future bloat by reducing unnecessary DDL churn and tuning catalog autovacuum settings.