Because MVCC creates dead row versions, VACUUM cleans them up so space can be reused and query performance stays good. Autovacuum runs it automatically to prevent table bloat and transaction ID wraparound issues.
Advanced answer
Deep dive
Expanding on the short answer — what usually matters in practice:
Context (tags): vacuum, autovacuum, bloat
Data model and access patterns: dominant queries (read/write ratio, sorting, pagination).
Indexes: when they help vs hurt (write amplification, memory).
Consistency & transactions: what’s guaranteed and what can bite you.
Explain the "why", not just the "what" (intuition + consequences).
Trade-offs: what you gain/lose (time, memory, complexity, risk).
Edge cases: empty inputs, large inputs, invalid inputs, concurrency.
Examples
A tiny example (query shape):
-- Example: index + query shape
SELECT *
FROM users
WHERE email = '[email protected]'
LIMIT 1;
Common pitfalls
Too generic: no concrete trade-offs or examples.
Mixing average-case and worst-case (e.g., complexity).