VACUUM and ANALYZE for SQL Server DBAs
On this page
Dead tuples: what SQL Server never asked you to manage
As a SQL Server DBA you rebuild indexes, update statistics and watch tempdb. You've probably never thought about cleaning up old row versions inside a table, because SQL Server keeps them in tempdb's version store, or, with Accelerated Database Recovery, in a version store managed for you.
PostgreSQL keeps old row versions in the table itself. Every UPDATE writes a new version of the row and leaves the old one behind. Every DELETE only marks the row. Those leftovers are dead tuples, and a process called VACUUM must clear them. If it can't keep up, tables bloat, queries slow down, and in the extreme case the database stops accepting writes. This is the most important new operational skill in the migration.
Table bloat in a demo
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL)
WITH (autovacuum_enabled = off); -- only for this demo
INSERT INTO accounts SELECT g, 100 FROM generate_series(1, 100000) AS g;
SELECT pg_size_pretty(pg_relation_size('accounts')); -- 4328 kB
UPDATE accounts SET balance = balance + 1;
SELECT pg_size_pretty(pg_relation_size('accounts')); -- 8656 kBOne UPDATE of every row doubled the table. The 100,000 old versions are still there, because a transaction that started earlier might need them (MVCC, Day 12). Now clean up:
VACUUM accounts;
SELECT pg_size_pretty(pg_relation_size('accounts')); -- still 8656 kB
UPDATE accounts SET balance = balance + 1;
SELECT pg_size_pretty(pg_relation_size('accounts')); -- still 8656 kBVACUUM didn't shrink the file. It marked the dead space as reusable, and the next update filled that space instead of growing the table. That's the healthy steady state: a table somewhat larger than its live data, but not growing.
VACUUM vs VACUUM FULL vs ANALYZE
| Command | Does | Locking | Use it |
|---|---|---|---|
VACUUM | Makes dead-row space reusable and updates the visibility map | None that blocks reads or writes | Constantly, through autovacuum |
VACUUM FULL | Rewrites the table compactly and returns space to the OS | Exclusive: blocks all reads and writes | Rarely, after a huge one-off delete, in a maintenance window |
ANALYZE | Refreshes the planner's statistics | Light | After big data changes, and through autovacuum |
VACUUM (ANALYZE) accounts does both cleanup and statistics in one pass. For shrinking a table that must stay online, the pg_repack extension rebuilds it while holding the heavy lock only briefly. It's the nearest thing to SQL Server's online index rebuild. PostgreSQL 19 builds this in: a new REPACK command replaces VACUUM FULL and CLUSTER (both still work), and REPACK (CONCURRENTLY) rebuilds a table without blocking reads and writes. That's from the pre-release notes, so check the final release before relying on it.
Don't confuse ANALYZE with VACUUM. A table bulk-loaded once needs ANALYZE but has nothing to vacuum. A table whose rows are updated all day needs VACUUM even if its statistics barely change.
Tuning autovacuum for large tables
The autovacuum background workers vacuum and analyze tables automatically. A table is vacuumed once its dead tuples pass:
autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0.2) × rows in the table
For a table of 10,000 rows, that's about 2,050 dead tuples. For a 500-million-row orders table, it's 100 million, and a vacuum that big takes hours. PostgreSQL 18 added autovacuum_vacuum_max_threshold, 100 million by default, to cap it, but busy large tables usually need more frequent, smaller runs. Set that per table:
CREATE TABLE orders (order_id int PRIMARY KEY, status text);
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum after 1% of rows change
autovacuum_analyze_scale_factor = 0.005
);Then watch whether it keeps up:
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;Steadily rising n_dead_tup with an old last_autovacuum means it's falling behind. Make it trigger sooner, or let it work faster by raising autovacuum_vacuum_cost_limit. Never fix it by turning autovacuum off. That's the one PostgreSQL setting that reliably causes outages.
What blocks vacuum, and transaction ID wraparound
Vacuum can only remove a dead row once no transaction could still need it. So the oldest open transaction holds back cleanup for the whole database. The usual culprits, coming from SQL Server habits:
- Sessions left idle in a transaction, for example a GUI tool with autocommit off, or application code that opens a transaction and waits for user input. Set
idle_in_transaction_session_timeoutto end them. - Very long reports or batch jobs inside one transaction.
- Abandoned replication slots (Day 24).
There's also a hard limit behind all this. Transaction IDs are 32-bit, and vacuum must periodically freeze old rows so the counter can wrap safely. If freezing falls far enough behind, PostgreSQL stops accepting writes to protect your data. Monitor how close each database is:
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;Autovacuum normally keeps this in the low hundreds of millions. Alert well before a billion.
Check yourself
Dead tuples aren't being cleaned up fast enough. Check n_dead_tup and last_autovacuum in pg_stat_user_tables, lower the table's autovacuum scale factor, and look for long-open transactions holding cleanup back.
It takes an exclusive lock and rewrites the whole table, so every read and write on it waits until it finishes. Use plain VACUUM for routine cleanup, and VACUUM FULL or pg_repack only in a planned window.
Dead tuples pile up, tables and indexes bloat, statistics go stale, and eventually transaction ID wraparound forces PostgreSQL to stop writes. Tune autovacuum per table instead of disabling it.
PostgreSQL's MVCC writes a new row version for every update and keeps the old one until no transaction can see it, inside the table itself. VACUUM reclaims those dead versions for reuse. SQL Server keeps old versions in tempdb's version store when snapshot isolation is on, so the cleanup is hidden from you.
ANALYZE samples the table and updates the statistics the planner uses to estimate row counts, like UPDATE STATISTICS. VACUUM reclaims space from dead rows. Autovacuum does both, but after a big data load you should run ANALYZE yourself straight away.