Why should you care?

PostgresVerse orders table after flash sale has bloat and wrong reltuples — queries slow and index-only scans fail without vacuum.

See it live — copy this example

Run in pgAdmin or psql.

SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

VACUUM (ANALYZE, VERBOSE) orders;

What happened?

• pg_stat_user_tables shows dead tuple count and vacuum timestamps.
• VACUUM ANALYZE cleans dead rows and updates stats in one manual pass.

Practice next

1. Update many rows in orders to create dead tuples.
2. Check n_dead_tup before vacuum.
3. Run VACUUM ANALYZE and recheck.
4. SET autovacuum_vacuum_scale_factor = 0.01 on hot table via ALTER TABLE storage.
5. Inspect pg_stat_progress_vacuum during long run.

Remember

Dead tuples accumulate from UPDATE/DELETE. VACUUM marks space reusable; FULL rewrites file. ANALYZE feeds accurate stats to planner.

PostgresVerse post-sale cleanup

After Big Billion Days, DBA monitors n_dead_tup and bumps autovacuum on orders.

Outcome: P95 checkout latency returns to baseline within hours.