MVCC lets transactions observe appropriate row versions. Old versions need cleanup, and long-lived transactions can prevent timely reclamation.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: A forgotten transaction keeps an old snapshot active while updates accumulate. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Identify version retention
MVCC keeps row versions needed by active snapshots.
Step 2: Inspect long transactions
An old open transaction can delay cleanup while updates accumulate.
Step 3: Diagnose before tuning
Check transaction age, dead tuples and vacuum progress together.
Worked scenario
A forgotten transaction keeps an old snapshot active while updates accumulate.
A forgotten transaction remains open overnight while other sessions update rows. Old versions may need to stay visible to its snapshot, so normal cleanup cannot reclaim everything. Increasing storage does not correct the ownership problem; close inappropriate long transactions and evaluate maintenance behavior afterward.
Common mistake
Vacuum is not merely optional disk tidying; maintenance affects table health.
Verify the behavior
Reproduce in an isolated database and compare cleanup before and after ending the old transaction.
Interview exercise
Investigate table growth.
Answer and reasoning
Check transaction age, dead tuples and vacuum progress before tuning settings or blaming the latest query.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.