VACUUM and ANALYZE

Last reviewed Jun 1, 2026 Content v20260601

Track mode

sql_sandbox

Means

SQL sandbox

Reading

~1 min

Level

intermediate

This lesson

This lesson teaches VACUUM and ANALYZE: the SQL patterns, schema habits, and query reasoning you need before advancing in PostgreSQL.

MVCC leaves dead tuples—VACUUM and autovacuum are operational essentials, not optional DBA trivia.

You will apply VACUUM and ANALYZE in contexts like: Modern startups, geospatial apps, and analytics-friendly OLTP systems.

Copy Postgres SQL into psql, local PostgreSQL, or DB Fiddle (PostgreSQL dialect)—use \d and EXPLAIN ANALYZE where lessons show them. The in-browser lab ships later; psql is the practice path now.

When you can explain the previous lesson's ideas without copying example queries verbatim.

Postgres uses MVCC—UPDATE/DELETE leave dead tuples. VACUUM reclaims space; ANALYZE updates planner statistics. Autovacuum runs both in the background.

Manual maintenance

VACUUM (VERBOSE, ANALYZE) orders;
ANALYZE customers;

Practice: Run on a local Postgres instance you own. Avoid changing production cluster settings.

Monitoring bloat

SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Operational guidance

Sudden bulk DELETE/UPDATE spikes dead tuples—watch autovacuum lag. Tune autovacuum on high-churn tables rather than disabling it.

Important interview questions and answers

Q: VACUUM vs VACUUM FULL?
A: Regular VACUUM reclaims space for reuse; VACUUM FULL locks table and rewrites—last resort.
Q: Why ANALYZE?
A: Stale stats cause bad plans—sequential scans on large tables.

Self-check

What creates dead tuples?
What view shows autovacuum timestamps?

Tip: Watch n_dead_tup after bulk DELETE—autovacuum may need tuning.

Interview prep

Dead tuples?: Left by UPDATE/DELETE under MVCC until VACUUM reclaims.
ANALYZE purpose?: Updates planner statistics for better plans.

Discussion

Past discussion is visible to everyone. Only logged-in users can post comments and replies.

Starter discussion topics

Autovacuum tune?
Bloat symptoms?

No discussion yet. Be the first to ask a question.