PostgreSQL autovacuum and table bloat, explained
Short answer: In PostgreSQL, updating or deleting a row leaves the old version behind as a "dead" row until a process called vacuum cleans it up. Autovacuum does this automatically, but on busy tables with frequent updates its default trigger can be too slow, so dead rows pile up, the table and its indexes grow ("bloat") and queries slow down. Check dead-row counts and last autovacuum times, tune autovacuum per table for your busiest tables, and avoid long-open transactions that stop clean-up.
Why PostgreSQL leaves dead rows behind
PostgreSQL never overwrites a row in place. An UPDATE writes a new version of the row and marks the old one as no longer current; a DELETE marks the row as deleted. This design lets readers and writers work at the same time without blocking each other, because each transaction can still see the version that was current when it started.
The cost is that old versions, called dead tuples, stay in the table file until vacuum removes them. PostgreSQL's documentation on routine vacuuming explains that standard VACUUM removes dead row versions in tables and indexes and marks the space available for reuse, but generally doesn't return that space to the operating system.
So a table that's updated constantly, such as a job queue, a sessions table, stock levels or order statuses, generates a steady stream of dead rows.
What bloat looks like in practice
When vacuum can't keep up, you typically see:
- A table that takes up far more disk than its row count suggests.
- Queries that scan the table getting slower over weeks, even though the number of live rows is stable.
- Indexes growing, which makes every insert and lookup a little slower.
- Disk usage rising on a database whose data isn't really growing.
It often shows up in small business apps on exactly the tables nobody thinks of as big: a "last seen" timestamp updated on every page view, or a counter updated on every order.
How autovacuum decides when to run
Autovacuum is a background process that vacuums tables when enough rows have changed. According to PostgreSQL's autovacuum settings documentation, it triggers a vacuum when the number of updated or deleted rows passes a threshold made of a fixed minimum, autovacuum_vacuum_threshold, plus a fraction of the table size, autovacuum_vacuum_scale_factor, which defaults to 0.2, or 20% of the table.
That 20% works for many tables. On a large, hot table it can mean waiting for millions of dead rows before clean-up starts. The documentation notes that these settings can be overridden for individual tables through storage parameters, which is usually the right fix rather than changing them for the whole database.
Check what's happening
PostgreSQL's statistics views show you the state of each table. The pg_stat_user_tables view includes n_live_tup and n_dead_tup, the estimated live and dead rows, plus last_autovacuum, the last time autovacuum processed the table. A simple check:
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
Warning signs are a dead-row count that's large relative to live rows, or a busy table whose last_autovacuum is days old. Run the check a few times over a day to see the trend.
Tune the busiest tables
For a hot table, lower the scale factor so vacuum runs more often, in smaller batches:
ALTER TABLE job_queue SET (autovacuum_vacuum_scale_factor = 0.02);
The value is illustrative. Start modestly, watch the dead-row counts and adjust. Other per-table settings exist, including ones that control how aggressively autovacuum works; read the documentation before changing them, as heavier vacuuming uses more I/O.
On managed database services, these settings are usually available, though some global ones may only be changeable through the provider's configuration screens.
Find what's blocking clean-up
Sometimes autovacuum runs but can't remove dead rows. The usual reason is a transaction that's been open a long time. Vacuum can't remove row versions that an open transaction might still need to see, so a single forgotten session can stop clean-up across the database.
Common causes:
- A reporting query or export left running for hours.
- An app connection that started a transaction and never committed, often shown as "idle in transaction".
- An abandoned session in a database admin tool.
Check pg_stat_activity for old transactions, and fix the cause in the app. Setting a timeout for idle transactions is a sensible safety net.
When the table is already bloated
Regular vacuum makes dead space reusable but rarely shrinks the file. To actually reclaim disk, there's VACUUM FULL, which rewrites the table into a new, compact file. PostgreSQL's documentation is clear about the cost: it's much slower and requires an ACCESS EXCLUSIVE lock on each table while it runs, which blocks reads and writes. It also needs extra disk space for the new copy.
For a small table, a short maintenance window may be fine. For large tables, teams often use extension-based tools that rebuild tables with less locking. Either way, fix the vacuum settings first, or the bloat comes back.
Don't switch autovacuum off
When autovacuum seems to slow things down, it's tempting to disable it. Don't. Besides clean-up, vacuuming also protects against transaction ID wraparound, a condition the documentation describes as a risk of data loss, which PostgreSQL guards against by forcing vacuums if they've been skipped too long. Tune it; don't remove it.
A worked example
Picture a hypothetical Christchurch delivery-tracking app whose deliveries table updated each row's status and location dozens of times a day. The table had grown many times larger than its live data, and the driver dashboard was slowing down. The n_dead_tup count was huge and the last autovacuum was days old. The team found an export job holding a transaction open overnight, fixed it, lowered the table's scale factor and rebuilt the table once during a quiet hour. Dead-row counts stayed low afterwards.
FAQ
Does this affect small databases?
Yes, on frequently updated tables. A small table with constant updates can still bloat if clean-up is blocked.
How often should I check?
Add dead-row counts to your regular monitoring, and look properly whenever a busy table's queries slow down.
Will VACUUM FULL fix slow queries?
It can help a bloated table, but if vacuum settings or long transactions aren't fixed, the bloat returns.
Do indexes bloat too?
Yes. Rebuilding an index, ideally with REINDEX CONCURRENTLY where available, can recover index space.
If your PostgreSQL tables keep growing or slowing down, ask us to look at your vacuum settings.