Table and Index Bloat

Measure real bloat with pgstattuple, watch VACUUM reclaim space without shrinking the file, and see why VACUUM FULL is the one that actually does

DELETE and UPDATE never actually remove anything from a table's file the instant you run them — MVCC just marks old row versions as dead, invisible to new transactions but still physically present on disk. VACUUM's job is to reclaim that space, but "reclaim" does not mean "shrink the file": ordinary VACUUM marks the dead space as free for PostgreSQL itself to reuse on future inserts into the same table, and stops there. It never returns that space to the operating system, because doing so safely would require exclusively locking the table and rewriting it — exactly what VACUUM is designed to avoid needing.\n\nIn this lab you will build genuine bloat by deleting 80% of a table, measure it precisely with pgstattuple instead of guessing, run VACUUM and watch the file size not move at all, and then run VACUUM FULL to see the one operation that actually does shrink the file — along with the lock it demands to do it.

Measuring Size

SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));

pg_total_relation_size includes the table's heap, its indexes, and its TOAST data — the full on-disk footprint, not just the main file.

Exact Bloat, Not an Estimate

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('monitoring.sessions');

pgstattuple physically scans the table to report the real dead-tuple percentage and free-space percentage — an exact measurement, unlike bloat-estimation queries built from planner statistics, which approximate.

Why VACUUM Does Not Shrink the File

VACUUM scans the table, marks dead tuples' space as reusable, and updates the free space map — but it only returns space to the operating system in the rare case where entirely-empty pages happen to sit at the very end of the file. Otherwise the file stays exactly the size it was; PostgreSQL simply now knows it can reuse the freed space internally for future rows in that same table.

The One Operation That Does Shrink It

VACUUM FULL monitoring.sessions;

VACUUM FULL builds an entirely new file containing only the live rows, in the smallest space they need, then swaps it in and drops the old one — genuinely shrinking the table. The cost is an ACCESS EXCLUSIVE lock for the entire operation: no reads or writes to the table are possible until it finishes, which is why VACUUM FULL is a deliberate, scheduled maintenance operation, never something ordinary autovacuum ever does on its own.

Dead Tuple vs Free Space

A dead tuple is a row version MVCC has marked invisible but not yet reclaimed. Free space is space VACUUM has already reclaimed and marked available for new rows in that same table. pgstattuple reports both separately — high dead_tuple_percent right after a bulk DELETE, which VACUUM converts into high free_percent, not into a smaller file.

VACUUM vs VACUUM FULL

Plain VACUUM is non-blocking (readers and writers can continue) and reclaims space for reuse in place. VACUUM FULL rewrites the entire table into a new, minimally-sized file and requires an ACCESS EXCLUSIVE lock for the whole operation — the only PostgreSQL command mentioned so far in this course that genuinely returns disk space to the operating system for a bloated heap.

📏 Measure the Baseline Size

Check the on-disk size of the freshly-loaded sessions table before deleting anything.

psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));" SET pg_size_pretty ---------------- 26 MB (1 row)

🗑️ Delete 80% of the Rows

Delete four out of every five rows, then immediately re-check the file size.

psql -U postgres -d beer_db -c "DELETE FROM monitoring.sessions WHERE id % 5 != 0;"

psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));"

student@lab:~$ psql -U postgres -d beer_db -c "DELETE FROM monitoring.sessions WHERE id % 5 != 0;" SET DELETE 80000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));" SET pg_size_pretty ---------------- 26 MB (1 row)

🧩 Install pgstattuple

Install the extension that measures bloat exactly, by physically scanning the table.

psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pgstattuple;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pgstattuple;" SET CREATE EXTENSION

🔬 Measure the Real Bloat

Run pgstattuple and see the exact dead-tuple and free-space percentages, not an estimate.

psql -U postgres -d beer_db -c "SELECT * FROM pgstattuple('monitoring.sessions');"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM pgstattuple('monitoring.sessions');" SET table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 25600000 | 20000 | 4880000 | 19.06 | 80000 | 19520000 | 76.25 | 725000 | 2.83 (1 row)

🧹 Run VACUUM

Reclaim the dead space with a plain VACUUM, then check the file size again.

psql -U postgres -d beer_db -c "VACUUM monitoring.sessions;"

psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));"

student@lab:~$ psql -U postgres -d beer_db -c "VACUUM monitoring.sessions;" SET VACUUM student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));" SET pg_size_pretty ---------------- 26 MB (1 row)

✅ Confirm What VACUUM Actually Did

Run pgstattuple again and see that the dead tuples are genuinely gone — reclaimed as free space, not shrunk away.

psql -U postgres -d beer_db -c "SELECT * FROM pgstattuple('monitoring.sessions');"

🔨 VACUUM FULL: The One That Actually Shrinks

Run VACUUM FULL and watch the file size finally drop.

psql -U postgres -d beer_db -c "VACUUM FULL monitoring.sessions;"

psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));"

student@lab:~$ psql -U postgres -d beer_db -c "VACUUM FULL monitoring.sessions;" SET VACUUM student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('monitoring.sessions'));" SET pg_size_pretty ---------------- 5376 kB (1 row)

Lab 2.4.3 complete. You can now measure real bloat and explain exactly what VACUUM does and does not do:\n\n\n pg_total_relation_size : ✅ measured before and after every step\n pgstattuple : ✅ exact dead-tuple and free-space percentages\n VACUUM : ✅ reclaims space for reuse, file size unchanged\n VACUUM FULL : ✅ genuinely shrinks the file, at the cost of a full lock\n

Enable JavaScript to run the live terminal and track your progress.