MVCC

At 9 a.m. an analyst starts a slow report over all of Pedal's rentals, while riders keep ending rides. In many older databases one side would have to wait for the other. In PostgreSQL neither waits, and the report still sees a consistent picture. The mechanism is called multi-version concurrency control.

The rule

Never overwrite a row: write a new version, and let each snapshot choose.

When you UPDATE a row, PostgreSQL does not change it in place. It writes a new version of the row and marks the old one as ended. Every version carries two hidden fields:

• xmin: the id of the transaction that created this version.
• xmax: the id of the transaction that deleted or replaced it (empty while it is current).

Each statement (or, in REPEATABLE READ and SERIALIZABLE, the whole transaction) works from a snapshot: a note of which transactions had committed when it started. A version is visible if its creator had committed by then and its remover had not.

What follows from this:

• Readers never block writers, and writers never block readers. The report reads old versions while updates add new ones.
• Two writers on the same row still queue: the second waits for the first to commit or roll back.
• DELETE only sets xmax; the row stays on disk.
• Old versions that no snapshot can see any more are dead tuples. They waste space until VACUUM marks it reusable. The autovacuum background workers do this for you, and also refresh planner statistics.
• Transaction ids are 32-bit and wrap around, so VACUUM also freezes old rows. If autovacuum is blocked for too long, PostgreSQL will eventually refuse new writes to protect data.

The shortcut

Picture every row as a stack of versions, each labelled "born in transaction X, died in transaction Y". To predict what a session sees, ask: which version was alive when my snapshot was taken?

The trap is an idle transaction left open for hours, for example a forgotten psql session after BEGIN. Its snapshot keeps every newer dead tuple "maybe needed", so autovacuum cannot remove them and tables bloat.

Drills

Answer fast — trust the rule.

Remember

• Never overwrite a row: write a new version, and let each snapshot choose.
• Ask which version was alive when the snapshot was taken.
• Dead tuples need VACUUM; idle open transactions stop it working.