The reader never blocked the writer. Where did the old row go?

One logical row can have more than one physical history

An update does not have to overwrite the bytes that every reader is currently examining. PostgreSQL uses multiversion concurrency control, or MVCC. For a normal update it creates a new tuple version and leaves the old version available while transactions might still need to see it.

Think of the account as having two physical versions:

Version v7: status ACTIVE       visible before admin commit
Version v8: status SUSPENDED    created by admin transaction

The report started with a stable snapshot. Its questions about the account are answered using the versions visible to that snapshot, not simply whichever version was written last. After the administrator commits, the report still has an older view. A newly started transaction can observe the new committed status.

PostgreSQL uses transaction visibility metadata and snapshot rules to decide which tuple is visible. The decision also accounts for inserts or updates made by the reader's own transaction. It is not implemented by putting a separate full database copy on disk for every client. PostgreSQL MVCC

Why did the second SELECT still see ACTIVE?

Because we specified REPEATABLE READ. PostgreSQL fixes the transaction's snapshot at its first non-control statement, so subsequent ordinary reads use a consistent view of commits that preceded that snapshot. That can be valuable for a report that must not mix different moments of the database inside one computation.

The default READ COMMITTED behaves differently. Each ordinary SELECT gets a snapshot as of that statement's start. Two SELECT statements in one transaction can therefore return different committed values. Saying "MVCC means all reads in a transaction see the same version" would be false without naming the isolation level. PostgreSQL transaction isolation

The advantage is that ordinary readers usually do not block ordinary row updates, and those updates do not force all earlier readers to abandon their snapshots. But do not turn that into "MVCC removes locks". Two writers competing for the same row still coordinate. SELECT FOR UPDATE deliberately acquires locks. Schema operations, unique constraints, and other database operations may also block. MVCC changes how many reads work, not whether coordination ever exists.

The old version cannot disappear immediately

Imagine the writer commits SUSPENDED and the database promptly removes v7. The reporting transaction still needs v7 to give the answer promised by its snapshot. Deleting it too early would make the stable snapshot impossible to serve.

That means update-heavy tables can contain dead or obsolete tuples that newer snapshots no longer need but that an older snapshot may still require. PostgreSQL's normal VACUUM reclaims row versions only when they are safe to remove. It usually makes space reusable inside the table rather than returning the entire freed space directly to the operating system. PostgreSQL routine vacuuming

Consider a dashboard service that begins a transaction, runs one query, and then waits fifteen minutes for a downstream HTTP call before committing. If that transaction retains a snapshot that needs old versions, garbage collection cannot reclaim some of the update history while it remains open. A series of such sessions can contribute to table and index bloat and more maintenance work.

Not every idle transaction pins exactly the same horizon. The isolation mode, actual snapshot state, replication slots, and other retention obligations matter. The useful rule is to observe the oldest relevant transaction or snapshot horizon rather than declaring every long-lived connection equally harmful.

What would you measure?

Start with the age of active transactions and their backend states. PostgreSQL exposes session and transaction information through pg_stat_activity, including transactions left idle in transaction. Look at table-level dead-tuple estimates and vacuum history, then check whether maintenance is keeping up.

If vacuum reports substantial dead versions that are not yet removable, find which snapshots or replication obligations are holding the removal boundary back. Do not run VACUUM FULL as a routine first response. It rewrites data and requires a much stronger lock than ordinary vacuum. That cure could interrupt the workload you were trying to save.

For the reporting service, the simplest fix might be to execute its database queries in a short, explicit read transaction and finish that transaction before doing slow network work. If the report needs a stable snapshot over a large scan, use an intentional deadline and enough resources to finish it. If it only needs eventual reporting totals, a separately maintained projection might be cheaper, with a stated freshness policy.

There is a business side to the example too. A security-sensitive action should not decide whether to allow access based on an indefinitely old reporting snapshot. Snapshot correctness means correct relative to the chosen view, not guaranteed freshness for every business decision.

The important interview insight is the trade. MVCC replaces some read/write waiting with retained history and visibility work. Once you understand how each reader chooses a version, the next failure becomes possible. Two transactions may each read a perfectly consistent snapshot and still commit a combined result that breaks a business rule.