Postgres Table Bloat: When Autovacuum Falls Behind

A few weeks ago I noticed the disk graph on my smallest Postgres box only knew one direction: up. The database behind it holds device status for a side project. Each device updates a last_seen_at column every 30 seconds, and about 40,000 rows live there. Row count flat for months. Disk usage tripled since spring. The table had eaten more storage than everything else in the cluster combined, and pgAdmin told me autovacuum had "run recently" every time I checked. So why was the table still fat? This is Postgres table bloat, the boring kind that never shows up in a query plan.

It took me an embarrassing amount of time to connect three separate facts I technically already knew. If you run any table with heavy UPDATE churn, you will hit this eventually, so here is the whole thing laid out.

Where the space actually goes

Postgres does not update rows in place. Every UPDATE writes a new physical row version and marks the old one dead. Same for DELETE: the row stays, flagged as invisible to new transactions. This is how Postgres implements MVCC, and it is usually great for concurrency. The cost is that churn leaves corpses everywhere.

-- every heartbeat leaves the previous row version behind
UPDATE devices
SET last_seen_at = now(), status = 'online'
WHERE id = 1234;

With a 30 second heartbeat, each device produces 2,880 dead rows per day. Multiply by 40,000 devices and the table manufactures dead tuples faster than most workloads ever will. Dead space is not wasted, exactly. Insertions can reuse it. But nothing returns it to the operating system on its own.

VACUUM does not shrink the file

Here is the part that cost me a morning. Plain VACUUM scans the table, marks dead tuples as reusable, and updates free space maps. The file on disk does not get smaller. Ever. Autovacuum runs VACUUM, so autovacuum will never shrink your table either. When I finally understood this, the whole disk graph made sense: autovacuum was doing its job fine, and the file size was simply not its job.

To actually reclaim disk you have two options. VACUUM FULL rewrites the whole table into a compact new file. It works, but it holds an exclusive lock for the duration and needs free disk space roughly equal to the table size, because it writes the new copy while the old one still exists. On a production table that is a maintenance window, not a Tuesday afternoon fix.

The online alternative is pg_repack. It rebuilds the table behind the scenes and swaps it in with only brief locks. It needs a primary key or a unique index on NOT NULL columns, which nearly every churn-heavy table has anyway.

How far behind is autovacuum, really

Before reaching for either tool, check what the stats say.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0)) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

The numbers are approximate, since they refresh as autovacuum works, but they are good enough to spot trouble. The default trigger for autovacuum is 50 dead tuples plus 20 percent of the table. On my 40,000 row table that is about 8,000 dead rows, so it ran often. On a 50 million row table the same math waits for 10 million dead tuples before lifting a finger. Postgres 16 added a cap, autovacuum_vacuum_max_threshold, that limits this to 100 million by default, but 100 million dead rows on one table is still a lot of bloat to carry around.

The fix for big churn tables is a per-table override. Global defaults cannot know which tables matter.

ALTER TABLE devices SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000
);

Two percent instead of twenty. On hot tables I have gone down to one percent. The extra vacuum cycles cost some I/O, and on any storage made in the last decade it is noise.

The long transaction that blocked everything

Per-table tuning was not my actual problem, though. While checking the stats I found something better: an idle reporting connection, open since the previous evening, sitting there with state = 'idle in transaction'. It was a dashboard query someone (fine, me) had left uncommitted. A vacuum cannot remove any row version that is still visible to an open transaction snapshot. One forgotten connection pinned every dead tuple in the database.

SELECT pid, state, now() - xact_start AS age, backend_xmin, left(query, 60)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age DESC;   -- or: WHERE state = 'idle in transaction'

I terminated the connection with pg_terminate_backend(pid) and the next autovacuum run tore through months of backlog in minutes. If you want to see what autovacuum is actually doing, set log_autovacuum_min_duration = 0 and read the log. It names the tables, the dead tuples removed, and how long it took. I check this query now whenever a disk graph misbehaves, before I check anything else.

What I do now

The routine is boring, which is the point. Watch the dead percentage on tables with UPDATE or DELETE churn. Set per-table scale factors on the hot ones instead of touching globals. Keep transactions short and never let anything sit idle in transaction, connection pooler settings included. Reach for pg_repack when a table needs real compaction, and save VACUUM FULL for scheduled windows. And never disable autovacuum on a busy table to "fix" I/O pressure. That trade buys quiet now and a wraparound emergency later.

One honest note to close: none of this showed up in any query plan. The table was slow in exactly zero ways my app could feel. It was pure wasted disk. If you liked this, I keep a running checklist for when Postgres ignores my indexes, which is the more common way this database surprises me. And the monitoring query above lives in Snippet Ark under a snippet named "why is the disk full". Future me has needed it more than once.