Home · About · Services · Industries · Projects · Products · Process · Blog · FAQ · Contact
PostgreSQL VACUUM and table bloat: find it, fix it, prevent it Why PostgreSQL tables bloat, how to measure it, what blocks autovacuum, how to tune it per table, and how to reclaim space without locking production.
A PostgreSQL table holds 2 million rows, yet pg_relation_size says it takes 40 GB and every query on it has slowed down. Nothing is broken, and nobody deleted anything unusual. The table is bloated: most of those pages hold dead row versions or empty space that PostgreSQL has not been able to reuse.
The short answer: in PostgreSQL an UPDATE or DELETE never overwrites a row in place. It leaves the old version behind as a dead tuple. VACUUM marks that space as reusable. Bloat appears when dead tuples pile up faster than vacuum can clear them, usually because autovacuum is too cautious for a busy table or something is holding back cleanup.
Why PostgreSQL needs VACUUM at all PostgreSQL uses multi-version concurrency control (MVCC). When you update a row, it writes a new version and marks the old one as expired by your transaction. Other transactions that started earlier can still see the old version, which is how readers avoid blocking writers. Once no running transaction could possibly need the old version, it is dead, but it still occupies space in the table and in every index that points to it.
A plain VACUUM does three useful jobs. It removes dead tuples and records the free space in the free space map so new rows can reuse it. It updates the visibility map, which lets index-only scans skip reading the table. And it freezes old rows so transaction IDs can be safely reused, which we come back to below. What a plain VACUUM does not do is give disk space back to the operating system, except for empty pages at the very end of the table. A 40 GB bloated table stays 40 GB after VACUUM; it just stops growing.
VACUUM FULL rewrites the whole table into a new file and does return space. The catch is that it takes an ACCESS EXCLUSIVE lock for the entire run, so nothing can read or write the table, and it needs enough free disk for a full copy. On a production table that is usually an outage.
Find out whether you actually have bloat Start with the statistics PostgreSQL already keeps. This query lists the tables with the most dead tuples and when autovacuum last touched them.
SELECT relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum,
last_autoanalyze,
autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;A table with millions of dead tuples and a last_autovacuum that is days old, or null, is the first suspect. These counters are estimates, though. For a real measurement use the pgstattuple extension, which ships with PostgreSQL in the contrib package. The approximate function uses the visibility map to skip pages that are all-visible, so it is much cheaper than a full scan on large tables.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, approx_tuple_percent,
dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('public.orders');
-- B-tree index health: leaf density well below 90% suggests bloat
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('public.orders_pkey');Some free space is normal and healthy, because new rows and updates need room. We start paying attention when free plus dead space passes roughly half the table, or when the table keeps growing while its row count stays flat. Indexes bloat too, and on update-heavy tables index bloat often hurts more than table bloat.
What stops vacuum from cleaning up If autovacuum is running regularly but dead tuples are not going down, something is holding the cleanup horizon back. VACUUM can only remove a row version once it is older than the oldest snapshot anyone might still use. One forgotten session can protect every dead row in the whole cluster. The usual culprits:
Long-running transactions, such as a report or a migration that runs for hours. Sessions stuck idle in transaction, typically an application that opened a transaction and never committed after an error. Replication slots that are inactive or lagging, including logical slots left behind by a removed CDC tool. Standbys with hot_standby_feedback = on running long queries, which holds back cleanup on the primary. Forgotten prepared transactions (two-phase commit). -- Sessions holding back the xmin horizon, oldest first
SELECT pid, usename, state, xact_start,
age(backend_xmin) AS xmin_age,
left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;
-- Replication slots and prepared transactions
SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_age
FROM pg_replication_slots;
SELECT gid, prepared, owner FROM pg_prepared_xacts;Fixing the cause matters more than tuning. We set idle_in_transaction_session_timeout (a few minutes is reasonable for most web applications), drop slots nobody uses, and move long analytical queries to a replica without feedback or to a separate reporting database. PostgreSQL 17 also added transaction_timeout, which caps the total length of a transaction.
Tune autovacuum for the tables that need it Autovacuum starts on a table when its dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the row count. The defaults are 50 and 0.2, so a 50 million row table waits for around 10 million dead rows before anything happens. PostgreSQL 18 added autovacuum_vacuum_max_threshold, which caps that trigger point (100 million by default), but for busy tables it is still far too late.
Rather than changing the global defaults, we set lower thresholds on the handful of large, update-heavy tables that need them:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.02
);The second limit is speed. Autovacuum throttles itself using cost-based delays so it does not swamp disk I/O. The default budget was set for spinning disks; on SSD or cloud block storage it is usually safe to raise it. Note that the cost limit is shared between all running workers, so adding workers without raising the limit just makes each one slower.
# postgresql.conf, values to adjust for your hardware
autovacuum_vacuum_cost_limit = 2000 # default -1 uses vacuum_cost_limit (200)
autovacuum_vacuum_cost_delay = 2ms # default since PostgreSQL 12
autovacuum_max_workers = 5 # default 3
maintenance_work_mem = 1GB # or autovacuum_work_memThese settings take effect on reload, except autovacuum_max_workers, which needed a restart before PostgreSQL 18. From 18 it can be changed with a reload up to autovacuum_worker_slots. While a vacuum runs, pg_stat_progress_vacuum shows which phase it is in and how far through the table it has got.
One more design lever: HOT updates. If an update does not change any indexed column and there is free space on the same page, PostgreSQL can skip writing new index entries entirely. Lowering fillfactor on a frequently updated table (for example ALTER TABLE orders SET (fillfactor = 90)) leaves that space. It only applies to pages written afterwards, and you can track the effect with n_tup_hot_upd in pg_stat_user_tables. Avoid indexing columns that change constantly, such as last_seen_at, unless queries really need it.
Do not ignore transaction ID wraparound Transaction IDs are 32-bit, so PostgreSQL must periodically freeze old rows to keep IDs reusable. Autovacuum forces an aggressive anti-wraparound vacuum when a table's oldest unfrozen ID passes autovacuum_freeze_max_age, 200 million by default, and it runs even if autovacuum is disabled. If freezing falls far behind, PostgreSQL eventually refuses new transaction IDs to protect your data, which is a full write outage. The same blockers that cause bloat also prevent freezing.
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;We alert well before the hard limits, for example when any database passes 500 million. A steadily climbing age is an early warning that vacuum is being blocked somewhere.
Removing bloat that already exists Once the cause is fixed and autovacuum keeps up, the table stops growing, but the existing bloat stays. You have three options to reclaim it:
REINDEX INDEX CONCURRENTLY (PostgreSQL 12 and later) rebuilds a bloated index without blocking reads or writes. It needs disk space for the new index while it runs. pg_repack rebuilds a table and its indexes online. It needs a primary key or a unique index on NOT NULL columns, roughly double the table's size in free disk, and it takes short exclusive locks at the start and end. VACUUM FULL or CLUSTER, only in a maintenance window, since both lock the table for the whole rewrite. Test pg_repack on a staging copy first, check that the pg_repack client binary and the extension installed in the database are the same version, and run it during quiet hours. Make sure no long transaction is open when it starts, or it will wait for its lock and queue other sessions behind it.
A practical checklist Check pg_stat_user_tables weekly for tables with many dead tuples and stale last_autovacuum. Measure real bloat with pgstattuple_approx and pgstatindex before acting. Find and fix whatever holds back the xmin horizon: idle transactions, old slots, long reports. Set idle_in_transaction_session_timeout and consider transaction_timeout. Lower autovacuum scale factors per table on large, busy tables. Raise the autovacuum cost limit on SSD storage, and watch pg_stat_progress_vacuum. Alert on age(datfrozenxid) long before wraparound limits. Reclaim existing bloat with REINDEX CONCURRENTLY or pg_repack, not VACUUM FULL on live tables. If dead tuples keep climbing after these steps, or a database is approaching wraparound, treat it as urgent. That is the point to bring in someone who has handled it before, because the fixes are routine early on and disruptive late.
Infigini IT Services · 11 Kanwali Road, Dehradun 248001, India · info@infigini.com · +91 95684 87789