VACUUM and ANALYZE Mechanics
Read a real VACUUM VERBOSE output line by line, and watch the visibility map change state before and after
VACUUM VERBOSE is not decoration — every line it prints is a real, specific fact about what just happened: how many pages were scanned, how many dead tuples were removed, how the visibility map changed, and how much WAL and buffer traffic the whole operation cost. Most DBAs run VACUUM constantly and read this output rarely, which means the one time it actually matters — diagnosing why autovacuum is falling behind, or why a table is still bloated after a manual VACUUM — they are reading it for the first time under pressure.\n\nThis lab creates a genuinely bloated table, runs VACUUM VERBOSE against it, and goes through the output line by line. Then it goes one level deeper than the statistics collector views used earlier in this course: pg_visibility looks directly at the visibility map itself — the exact bitmap VACUUM updates that lets later scans skip pages entirely, and that autovacuum consults to decide whether a page needs attention at all.
Reading VACUUM VERBOSE, Line by Line
VACUUM VERBOSE vac_demo;
| Line | Meaning |
|------|---------|
| pages: 0 removed, 1031 remain, 1031 scanned | Every page in the table was scanned; none could be removed outright (that needs VACUUM FULL) |
| tuples: 90000 removed, 10000 remain | The actual dead-tuple cleanup — this is the number pg_stat_user_tables.n_dead_tup drops by |
| visibility map: 1031 pages set all-visible | Every page is now marked safe for an index-only scan to skip entirely |
| buffer usage: 3370 hits, 0 reads, 4 dirtied | Almost the whole operation was served from cache — 0 reads means no disk I/O was needed to find the dead tuples |
| WAL usage: 3317 records, ... 752343 bytes | Cleaning up dead tuples is itself logged — VACUUM is not free, it generates real WAL |
ANALYZE Is a Completely Different Job From VACUUM
ANALYZE vac_demo;
SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'vac_demo';
VACUUM reclaims space. ANALYZE samples the table's actual data and writes the planner's statistics — row count estimates, most-common values, histogram boundaries — into pg_statistic (readable through the friendlier pg_stats view). A table can be perfectly vacuumed and still get terrible query plans if it was never analyzed, and vice versa.
Looking at the Visibility Map Directly
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('vac_demo');
This does not infer visibility map state from a side effect — it reads the actual bitmap PostgreSQL maintains on disk, one bit per page, tracking whether every tuple on that page is visible to all current transactions.
Visibility Map
A compact, one-bit-per-page (plus one bit for all-frozen) bitmap PostgreSQL maintains alongside every table, tracking whether every tuple on a page is visible to all current and future transactions. An index-only scan can skip visiting the heap entirely for an all-visible page, and VACUUM itself uses it to skip pages that need no cleaning — which is why a table that is vacuumed often stays cheap to vacuum again.
pg_statistic vs pg_stat_user_tables
These sound similar and do completely different jobs. pg_stat_user_tables (used throughout this course) reports activity counters — scans, dead tuples, timestamps. pg_statistic, populated by ANALYZE and normally read through the pg_stats view, holds the query planner's actual knowledge of data distribution: how many distinct values a column has, its most common values, and histogram boundaries used to estimate selectivity.
🔎 Confirm the Bloat First
Check the dead-tuple ratio before running anything, so the before/after comparison means something.
psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'vac_demo';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'vac_demo';" SET relname | n_live_tup | n_dead_tup ----------+------------+------------ vac_demo | 10000 | 90000 (1 row)
📋 Run VACUUM VERBOSE
Run VACUUM with VERBOSE and read the full, real output it produces.
psql -U postgres -d beer_db -c "VACUUM VERBOSE vac_demo;"student@lab:~$ psql -U postgres -d beer_db -c "VACUUM VERBOSE vac_demo;" SET INFO: vacuuming "beer_db.public.vac_demo" INFO: finished vacuuming "beer_db.public.vac_demo": index scans: 1 pages: 0 removed, 1031 remain, 1031 scanned (100.00% of total), 0 eagerly scanned tuples: 90000 removed, 10000 remain, 0 are dead but not yet removable removable cutoff: 937, which was 2 XIDs old when operation ended new relfrozenxid: 893, which is 1 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen visibility map: 1031 pages set all-visible, 0 pages set all-frozen (0 were all-visible) index scan needed: 1031 pages from table (100.00% of total) had 90000 dead item identifiers removed index "vac_demo_pkey": pages: 221 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 0.000 MB/s, avg write rate: 0.038 MB/s buffer usage: 3370 hits, 0 reads, 4 dirtied WAL usage: 3317 records, 4 full page images, 752343 bytes, 0 buffers full system usage: CPU: user: 0.32 s, system: 0.01 s, elapsed: 0.81 s VACUUM
✅ Confirm the Statistics Collector Agrees
Check pg_stat_user_tables again to confirm the dead-tuple count dropped to near zero.
psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'vac_demo';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'vac_demo';" SET relname | n_live_tup | n_dead_tup ----------+------------+------------ vac_demo | 10000 | 0 (1 row)
📏 Confirm the File Size Did Not Move
Check the table's on-disk size — VACUUM reclaimed the tuples, not the file.
psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('vac_demo'));"student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_size_pretty(pg_total_relation_size('vac_demo'));" SET pg_size_pretty ---------------- 9096 kB (1 row)
🧩 Install pg_visibility
Install the extension that reads the visibility map directly, rather than inferring its state.
psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pg_visibility;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pg_visibility;" SET CREATE EXTENSION
🗺️ Read the Visibility Map Directly
Query pg_visibility_map_summary to see exactly how many pages are marked all-visible after this VACUUM.
psql -U postgres -d beer_db -c "SELECT * FROM pg_visibility_map_summary('vac_demo');"student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM pg_visibility_map_summary('vac_demo');" SET all_visible | all_frozen -------------+------------ 1031 | 0 (1 row)
Lab 2.6.1 complete. VACUUM VERBOSE's output is no longer a wall of text — every line is a specific, checkable fact:\n\n\n VACUUM VERBOSE annotated : ✅ pages, tuples, visibility map, WAL/buffer usage\n ANALYZE vs VACUUM : ✅ told apart — pg_statistic vs pg_stat_user_tables\n pg_visibility : ✅ read the actual bitmap, matched VERBOSE exactly\n File size confirmed unchanged : ✅ tied directly to "0 removed" in the output\n
Enable JavaScript to run the live terminal and track your progress.