VACUUM, ANALYZE, and Bloat

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind VACUUM, ANALYZE, and Bloat
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

Because of MVCC, old row versions from UPDATE and DELETE are not removed immediately. Standard VACUUM makes space reusable, while ANALYZE refreshes planner statistics. `VACUUM FULL` rewrites and exclusively locks the table, so it is not routine maintenance. Long transactions can prevent cleanup.

Connect it to a real scenario

Inspect live/dead tuple estimates and the latest autovacuum/analyze times for the high-update `lesson_progress` table. Run ANALYZE after bulk loads, and change per-table autovacuum thresholds only from measurements.

VACUUM makes dead-row space reusable
ANALYZE refreshes planner statistics
VACUUM FULL is not routine maintenance

Try the working example

sql

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'app'
ORDER BY n_dead_tup DESC;

VACUUM (ANALYZE, VERBOSE) app.lesson_progress;

You should see

You can inspect table maintenance state and run targeted VACUUM/ANALYZE.

5-minute try-it

Heavily update/delete a test table and compare its statistics before and after VACUUM ANALYZE.

One important caution

Disabling autovacuum risks not only bloat but also transaction-ID wraparound.

PostgreSQL — Routine Vacuuming — PostgreSQL Global Development Group

Easy traps

Disabling autovacuum risks not only bloat but also transaction-ID wraparound.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Heavily update/delete a test table and compare its statistics before and after VACUUM ANALYZE.

You'll know it worked when: You can inspect table maintenance state and run targeted VACUUM/ANALYZE.