Skip to main content
PostgreSQL · lesson 4 of 7

Intermediate

6

Advanced

1

Also part of PostgreSQL

7
Open the PostgreSQL page →
PostgreSQL intermediate Lesson 4 of 7

VACUUM Doesn't Shrink Tables — VACUUM FULL Does, at a Cost

Deleting 80% of a table's rows doesn't shrink it by a single byte, and neither does a plain VACUUM. Only VACUUM FULL actually returns space to the OS — by rewriting the whole table under an exclusive lock.

By SkillByExample · Published

postgresql vacuum mvcc bloat maintenance

Deleting most of a table’s rows and watching the table not shrink is the most common “where did my disk space go” moment in Postgres — and it’s not a bug.

Delete 80% of a table, and the size doesn’t change

SELECT pg_size_pretty(pg_total_relation_size('loans'));
 pg_size_pretty
----------------
 4416 kB
DELETE FROM loans WHERE returned_at IS NOT NULL;
SELECT pg_size_pretty(pg_total_relation_size('loans'));
DELETE 39929
 pg_size_pretty
----------------
 4416 kB

39,929 of 50,000 rows deleted — the same size, byte for byte. Postgres uses MVCC: a DELETE marks each row version as dead, but doesn’t erase it immediately, because a transaction that started before the delete might still need to read the old version.

The dead rows are real, and VACUUM finds them

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'loans';
 n_live_tup | n_dead_tup
------------+------------
      10071 |      39929

pg_stat_user_tables confirms exactly what was deleted: 39,929 dead tuples, still occupying their original disk pages.

VACUUM cleans them up — but still doesn’t shrink the file

VACUUM loans;
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'loans';
VACUUM
 n_live_tup | n_dead_tup
------------+------------
      10071 |          0
SELECT pg_size_pretty(pg_total_relation_size('loans'));
 pg_size_pretty
----------------
 4416 kB

n_dead_tup dropped to zero — the dead rows are gone from Postgres’s bookkeeping. The table is still 4416 kB. Plain VACUUM marks that space as free for reuse by future inserts and updates on this same table; it does not return the space to the operating system. This is the core thing to internalize: VACUUM fixes bloat’s growth, not its footprint.

VACUUM FULL actually shrinks it — at a real cost

VACUUM FULL loans;
SELECT pg_size_pretty(pg_total_relation_size('loans'));
VACUUM
 pg_size_pretty
----------------
 848 kB

4416 kB down to 848 kB — a real, measured 5x reduction. VACUUM FULL rewrites the entire table into a new, compact file. That’s also exactly why it’s dangerous to run casually on a live production table: rewriting the whole table means taking an exclusive lock for the whole operation, blocking every other read and write until it finishes.

Practice

1. Check pg_stat_user_tables for the last_vacuum and last_autovacuum timestamps. 
SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'loans';
 relname |          last_vacuum          |        last_autovacuum
---------+-------------------------------+-------------------------------
 loans   | 2026-09-13 15:21:04.513974+00 | 2026-09-13 15:14:23.216093+00

Both columns are populated — last_autovacuum shows the background autovacuum process already caught the bulk DELETE and ran on its own, several minutes before the manual VACUUM in this lesson. On a real long-running database, a last_autovacuum that’s suspiciously old relative to how much write traffic a table gets is the signal to check whether autovacuum is actually keeping up.

2. Insert rows back into loans and confirm the table doesn't grow past its VACUUMed size until the freed space is used up. 
INSERT INTO loans (book_id, borrower, loaned_at)
SELECT 1 + (random() * 199999)::int, 'New Borrower', NOW()
FROM generate_series(1, 5000);
SELECT pg_size_pretty(pg_total_relation_size('loans'));
INSERT 0 5000
 pg_size_pretty
----------------
 1272 kB

848 kB grew to 1272 kB to fit 5,000 new rows — VACUUM FULL doesn’t pre-allocate any extra headroom for future growth, unlike plain VACUUM’s freed-but-not-returned space, which is exactly why running VACUUM FULL right before a known bulk insert is usually the wrong order of operations.

3. Check whether autovacuum is enabled and its trigger threshold for this table. 
SHOW autovacuum;
SELECT reloptions FROM pg_class WHERE relname = 'loans';
 autovacuum
------------
 on
(1 row)

 reloptions
------------

(1 row)

autovacuum is on by default, and reloptions is empty here, meaning loans uses the cluster-wide default thresholds rather than a per-table override — a table with much heavier churn than this lesson’s dataset might justify a lower per-table autovacuum_vacuum_scale_factor to trigger more often.

Next: transactions and locking — MULTI-statement atomicity, isolation levels, and a real deadlock.

Frequently Asked Questions

Why doesn't DELETE actually free disk space immediately?
Postgres uses MVCC (multi-version concurrency control) — a DELETE marks a row's version as dead rather than physically removing it immediately, because another transaction that started before the DELETE might still need to see the old version. The space only becomes reclaimable once no running transaction could possibly still need it, which is exactly what VACUUM checks for.
If VACUUM doesn't shrink the table, what does it actually do?
It scans the table, identifies dead row versions no transaction still needs, and marks that space as free for Postgres to reuse for future INSERTs and UPDATEs on the same table. That's reuse, not release — the file on disk stays the same size, just with more free space inside it that new rows can occupy instead of the file growing further.
Why does VACUUM FULL need an exclusive lock, and what does that mean in practice?
It rewrites the entire table into a new, compact file and swaps it in — since the whole table is being rebuilt, it locks out both reads and writes for the whole operation. On a large, actively-used production table, that lock duration can mean real downtime, which is why VACUUM FULL is a deliberate, scheduled maintenance action, not a default nightly job the way plain autovacuum is.
Does this mean a table just keeps growing forever if rows are deleted and reinserted repeatedly?
No — once plain VACUUM has freed the dead space, new INSERTs and UPDATEs reuse it before the file grows further, so a table under steady churn (roughly equal deletes and inserts) tends to stabilize at some size rather than growing unboundedly, as long as VACUUM runs often enough to keep up. It's a table that shrinks dramatically and never grows back that specifically needs VACUUM FULL.
Is autovacuum enough, or do I need to run VACUUM manually?
Autovacuum handles plain VACUUM automatically based on how many rows have changed, and is enabled by default — for most workloads, manual VACUUM is unnecessary. VACUUM FULL is never automatic, because of the exclusive lock, so reclaiming space after a one-time bulk delete (like this lesson's 80% purge) is always a deliberate, manual decision.