VACUUM FULL and CLUSTER

Reclaim real disk space with VACUUM FULL, physically reorder a table with CLUSTER, and watch the AccessExclusiveLock block a second session

VACUUM FULL is the operation Lab 2.4.3 already showed reclaims disk space for real, by rewriting the table into a new, minimally-sized file. What that lab did not show is the cost side of that trade in action: for the entire time it runs, VACUUM FULL holds an AccessExclusiveLock — the single strongest lock mode in PostgreSQL — meaning absolutely nothing else can read or write that table until it finishes.\n\nThis lab makes that lock visible from a second session while it is actually held, the same way Lab 2.4.2 made a lock chain visible. Because this VM's disk budget is tight enough that a big enough table to keep VACUUM FULL busy for several seconds risks running out of space entirely, this lab uses an explicit LOCK TABLE ... IN ACCESS EXCLUSIVE MODE as a controllable stand-in for the exact same lock VACUUM FULL takes — said plainly, not hidden. Then it moves on to CLUSTER, and to two production tools this training image genuinely does not have.

Reclaiming Real Disk Space

SELECT pg_size_pretty(pg_total_relation_size('bloat_demo'));  -- before
VACUUM FULL bloat_demo;
SELECT pg_size_pretty(pg_total_relation_size('bloat_demo'));  -- after

Exactly as in Lab 2.4.3: a new, minimally-sized file replaces the old one — a genuine reduction, not a reuse-in-place.

The Lock, Made Visible

-- Session A (what VACUUM FULL itself would hold, for however long it runs):
BEGIN;
LOCK TABLE bloat_demo IN ACCESS EXCLUSIVE MODE;
SELECT pg_sleep(6);
COMMIT;

-- Session B, from a second connection while A is running:
SELECT count(*) FROM bloat_demo;  -- queues, does not run until A commits

-- From a third connection, while both are in flight:
SELECT pid, wait_event_type, wait_event, state FROM pg_stat_activity WHERE pid <> pg_backend_pid();

A real VACUUM FULL on a large table produces exactly this same picture in pg_locks and pg_stat_activity for however long the rewrite takes — this lab controls that duration explicitly instead of hoping a demo table is bloated enough to take a few observable seconds without risking this VM's disk budget.

Physical Reordering With CLUSTER

CLUSTER bloat_demo USING idx_bloat_id;

CLUSTER rewrites a table's rows on disk to physically match the order of a chosen index — the same AccessExclusiveLock, the same full-rewrite mechanism as VACUUM FULL, but aimed at improving sequential-scan locality for a specific access pattern rather than just reclaiming space. A table CLUSTERed once does not stay clustered automatically — every subsequent INSERT goes wherever there is room, not back into sorted order.

AccessExclusiveLock

The strongest lock mode in PostgreSQL, conflicting with every other lock mode including the lightest one (AccessShareLock, taken by a plain SELECT). VACUUM FULL, CLUSTER, TRUNCATE, DROP TABLE, and most forms of ALTER TABLE all take it — while held, absolutely no other session can read or write the table at all.

pg_repack / pg_squeeze

Third-party extensions that reclaim bloat online: each builds a fresh copy of the table alongside the original while normal reads and writes continue against the original, then swaps the new copy in under a lock held only briefly at the very end — trading a longer total runtime and extra temporary disk space for avoiding VACUUM FULL's full-duration outage.

📏 Measure Before Shrinking

Check the bloated table's current size before running VACUUM FULL.

psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('bloat_demo'));"

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

🔨 Run VACUUM FULL

Run VACUUM FULL and confirm the file genuinely shrinks this time.

psql -U postgres -d beer_db -c "VACUUM FULL bloat_demo;"

psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('bloat_demo'));"

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

🔒 Hold the Same Lock VACUUM FULL Would

Background an explicit ACCESS EXCLUSIVE lock on the table — the same strength and duration control VACUUM FULL itself would exercise on a bigger table.

psql -U postgres -d beer_db -c "BEGIN; LOCK TABLE bloat_demo IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(6); COMMIT;" > /tmp/vf_lock.log 2>&1 &

psql -U postgres -d beer_db -c "SELECT count(*) FROM bloat_demo;" > /tmp/vf_blocked.log 2>&1 &

student@lab:~$ psql -U postgres -d beer_db -c "BEGIN; LOCK TABLE bloat_demo IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(6); COMMIT;" > /tmp/vf_lock.log 2>&1 & [1] 143 student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM bloat_demo;" > /tmp/vf_blocked.log 2>&1 & [2] 147

🔍 Observe the Block From a Third Session

While both backgrounded sessions are in flight, check pg_stat_activity and pg_locks for the block.

psql -U postgres -d beer_db -c "SELECT pid, wait_event_type, wait_event, state FROM pg_stat_activity WHERE pid <> pg_backend_pid();"

✅ Confirm It Runs the Instant the Lock Releases

Wait for the lock to release, then confirm the blocked query finally completed.

sleep 6

cat /tmp/vf_blocked.log

student@lab:~$ sleep 6 student@lab:~$ cat /tmp/vf_blocked.log SET count ------- 20000 (1 row)

📐 Physically Reorder With CLUSTER

Use CLUSTER to rewrite the table's rows in index order.

psql -U postgres -d beer_db -c "CLUSTER bloat_demo USING idx_bloat_id;"

student@lab:~$ psql -U postgres -d beer_db -c "CLUSTER bloat_demo USING idx_bloat_id;" SET CLUSTER

🔎 Check for the Online Alternative

Confirm whether pg_repack or pg_squeeze — tools that avoid the full-table lock entirely — are available on this image, before assuming either one.

psql -U postgres -d beer_db -c "SELECT * FROM pg_available_extensions WHERE name IN ('pg_repack','pg_squeeze');"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM pg_available_extensions WHERE name IN ('pg_repack','pg_squeeze');" SET name | default_version | installed_version | comment ------+------------------+--------------------+--------- (0 rows)

Lab 2.6.2 complete. You reclaimed real disk space, watched its exact cost in action, and checked for the tool that avoids it:\n\n\n VACUUM FULL : ✅ 26MB → 5.4MB, genuinely measured\n AccessExclusiveLock : ✅ watched block a second session directly\n CLUSTER : ✅ physical reorder, same lock mechanism\n pg_repack/pg_squeeze : ✅ confirmed absent, checked not assumed\n

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