VACUUM in PostgreSQL

In my post about MVCC, I ended up with one question I couldn’t let go of:

If every UPDATE and DELETE leaves the old row version behind, where do all those old versions go?

This wasn’t just theory for me. I had once looked at a table on a server I was responsible for, and it was much bigger on disk than the data inside it. At the time I shrugged and moved on. Now I wanted to know why.

The answer is VACUUM.

A quick recap

From the MVCC post:

UPDATE = old version becomes dead + new version is written
DELETE = existing version becomes dead

A “dead” version is one that no running transaction can see anymore. It’s useless, but it’s still sitting on the page, taking up space. If nothing cleaned these up, every table that gets updated would grow forever.

VACUUM is the cleaner. It walks through the table, finds row versions that no snapshot could possibly need anymore, and marks their space as free to reuse.

The misconception I had

I assumed VACUUM shrinks the table. It doesn’t, at least not the normal one.

Say a table looks like this:

table on disk = 100 GB
  live rows   =  20 GB
  dead rows   =  80 GB

After a normal VACUUM, the file on disk is… still about 100 GB. What changed is that those 80 GB are now marked as free space inside the table. New inserts and updates will reuse it, so the table stops growing, but the operating system doesn’t get the space back. (The only exception is completely empty pages at the very end of the file, which can be cut off.)

If you really want the file to shrink, there’s VACUUM FULL:

VACUUM       → still ~100 GB, but 80 GB is now reusable
VACUUM FULL  → rewritten to ~20 GB

VACUUM FULL rewrites the whole table into a new file. The catch: it holds an ACCESS EXCLUSIVE lock the entire time. Nobody can read or write that table until it finishes. On a big production table, that’s an outage you scheduled yourself. I would think very hard before running this during business hours:

VACUUM FULL huge_table;

The three options side by side

	Lock	Gives space back to the OS	When I’d use it
`VACUUM`	light, reads and writes keep going	no	routine, usually done by autovacuum
`VACUUM FULL`	`ACCESS EXCLUSIVE`, blocks everything	yes	a planned maintenance window
`pg_repack` (extension)	only brief locks	yes	shrinking a large table that has to stay online

Autovacuum: the one doing the work

In practice you rarely run VACUUM yourself. PostgreSQL has autovacuum, a background process that watches how many dead rows each table collects and starts a worker when it’s time.

The default trigger looks like this:

vacuum when dead rows > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × total rows
                      =            50               +            0.2                × rows

That 0.2 surprised me. It means a table is vacuumed when about 20% of it is dead. For a small table that’s fine. For a table with 100 million rows, it means waiting for 20 million dead rows before anything happens.

For big, busy tables I now lower it per table:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

VACUUM also does two other jobs I hadn’t known about:

it updates the visibility map, which lets index-only scans skip reading the table, and
it freezes old rows, which protects against transaction ID wraparound. (Still on my list from the MVCC post.)

When a table keeps growing anyway

This was the part that explained my oversized table. Sometimes autovacuum runs, but can’t remove anything. That’s because something is still holding an old snapshot, and VACUUM won’t remove a version that snapshot might need.

These are the queries I now run first:

-- which tables have the most dead rows, and when were they last vacuumed?
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- who is holding back cleanup?
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start
LIMIT 5;

The usual suspects:

a session sitting idle in transaction for hours (someone ran BEGIN and went to lunch),
a replication slot that nobody is reading from anymore,
a replica with hot_standby_feedback = on running long queries, which asks the primary to keep old versions around.

Looking back, I’m fairly sure one of these was behind the table I shrugged at.

What I took away

MVCC and VACUUM are two halves of one design. MVCC makes reads and writes fast by never overwriting anything. VACUUM pays the bill later by cleaning up what was left behind. If you only know the first half, the second one will surprise you in production, like it surprised me.

What I still want to try

Reproduce bloat locally and watch n_dead_tup grow and drop
Transaction ID wraparound: what happens near 2 billion transactions, and how freezing prevents it
How hot_standby_feedback on a Patroni replica affects the primary
When pg_repack is safe on a busy table