intermediatePostgreSQL Specifics — MVCC, JSONB & VACUUM

A high-traffic table is showing 40% bloat (dead tuples). Queries are getting slower. How do you fix it without downtime?

1) Verify bloat: SELECT relname, n_dead_tup, n_live_tup, ROUND(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_pct FROM pg_stat_user_tables WHERE relname = 'orders'. 2) Check if autovacuum is running: SELECT last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'orders'. If never/rarely: autovacuum is overwhelmed. 3) Immediate fix: VACUUM ANALYZE orders — removes de

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to You need to add a NOT NULL column to a 500-million-row production table without downtime. How? →← Back to all SQL questions