VACUUM and autovacuum
What does VACUUM do in PostgreSQL?
VACUUM reclaims the space taken by dead row versions that updates and deletes leave behind, updates the visibility map and prevents transaction ID wraparound. Autovacuum runs it for you.
Because of MVCC, an UPDATE or DELETE in PostgreSQL does not remove the old row version right away; the old version becomes a dead tuple once no transaction can still see it. VACUUM finds dead tuples and marks their space as reusable for new rows in the same table.
Plain VACUUM runs alongside normal reads and writes and usually does not shrink the table's files on disk. VACUUM FULL rewrites the table to give space back to the operating system, but it takes an exclusive lock for the whole time. VACUUM also updates the visibility map, which helps index-only scans, and freezes old rows to prevent transaction ID wraparound. The autovacuum daemon runs VACUUM and ANALYZE automatically, so manual runs are mostly useful after large batch changes.
Example
The example updates every row of a table, which leaves 10,000 dead row versions, and runs VACUUM (VERBOSE) to report what it cleaned up. The statistics query afterwards shows when the table was last vacuumed by hand.
CREATE TABLE items (id int PRIMARY KEY, qty int);
INSERT INTO items SELECT g, 0 FROM generate_series(1, 10000) AS g;
UPDATE items SET qty = qty + 1;
VACUUM (VERBOSE) items;
SELECT relname, last_vacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'items';Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).