Skip to content

Databases · 5 of 9

Step 1 · The pattern

Readers and writers do not block

An update writes a new version of the row and leaves the old one. Readers keep seeing a consistent snapshot, so reads never wait for writes. That is MVCC.

  • update
  • read
  • done

In short

MVCC: how it works and how it breaks

Postgres lets readers and writers work at the same time through multi-version concurrency control. An update does not overwrite a row. It writes a new version and leaves the old one in place, so any transaction that started earlier can keep reading a consistent snapshot of how things were. Old versions that no transaction can still see are dead, and a background process called vacuum removes them. The catch is the phrase 'no transaction can still see'. A single transaction left open, perhaps by a script that began one and went idle, forces the database to keep every version created since it started. Vacuum runs and removes nothing. The table and its indexes fill with dead rows, every query has to step over them, and performance sinks for no visible reason. Keep transactions short, set idle_in_transaction_session_timeout, and monitor the age of the oldest open transaction.

How this comes up in interviews

"A Postgres table is getting slower every hour although traffic is flat and vacuum is running. What would you check?"

Then they ask

  1. Why can one idle transaction that never touched this table stop vacuum cleaning it?
  2. How do readers and writers avoid blocking each other in the first place?
  3. What would you put in place so this cannot happen again?

A strong answer covers

  • An update writes a new row version and leaves the old one, so readers keep a consistent snapshot.
  • Vacuum removes old versions only when no open transaction can still see them.
  • One transaction left open forces the database to keep every version created since it started.
  • Dead rows fill the table and its indexes, and every query has to step over them.
  • Keep transactions short, set idle_in_transaction_session_timeout and monitor the oldest open transaction.

Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.

On one page: the track cheat sheet.

Last updated: Official documentation

Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.