MVCC in PostgreSQL

MVCC in PostgreSQL

Table of Contents

1. What is MVCC?

MVCC stands for Multi-Version Concurrency Control. It’s a technique databases use to let multiple transactions read and write data at the same time without stepping on each other.

Instead of locking a row every time someone reads it, PostgreSQL keeps multiple versions of a row. Readers see the version of the row that existed when their transaction (or statement) started, while writers can create new versions without blocking those readers. No one waits around just to read data.

2. Why PostgreSQL Uses MVCC

Traditional locking databases force readers and writers to queue up behind each other — a writer updating a row can block every reader trying to access it, and vice versa. At scale, this kills throughput.

PostgreSQL avoids this entirely with MVCC. The core benefits:

  • Readers never block writers, and writers never block readers.
  • Each transaction gets a consistent snapshot of the database, so results don’t change mid-query even if other transactions are committing changes concurrently.
  • Better concurrency under high-traffic workloads compared to purely lock-based systems.

3. How MVCC Works Internally

Whenever a row is updated or deleted in PostgreSQL, the old row version isn’t overwritten in place. Instead:

  • UPDATE creates a brand-new row version and marks the old one as expired (it isn’t deleted immediately).
  • DELETE just marks the row as expired — it stays on disk until cleaned up later.
  • INSERT simply adds a new row version.

Every transaction is assigned a Transaction ID (XID), an incrementing counter. PostgreSQL uses XIDs to decide which row versions a given transaction is allowed to “see” — this is what creates the illusion of a private, consistent snapshot for every transaction.

4. The Hidden System Columns

Every table in PostgreSQL has a few hidden columns that make MVCC possible. You can query them directly:

SELECT xmin, xmax, ctid, * FROM your_table;
  • xmin — the XID of the transaction that inserted this row version.
  • xmax — the XID of the transaction that deleted or updated (expired) this row version. It’s 0 if the row is still live.
  • ctid — the physical location (page + offset) of this row version on disk.

5. A Practical Example

Let’s see MVCC in action:

CREATE TABLE accounts (id INT, balance INT);
INSERT INTO accounts VALUES (1, 100);

-- Check the hidden columns
SELECT xmin, xmax, * FROM accounts;

Now update the row:

UPDATE accounts SET balance = 200 WHERE id = 1;
SELECT xmin, xmax, * FROM accounts;

You’ll notice the xmin value changes to the new transaction’s XID — because PostgreSQL didn’t modify the row in place, it created an entirely new row version. The old version still physically exists on disk until VACUUM reclaims it.

6. MVCC and Transaction Isolation Levels

MVCC is what makes PostgreSQL’s isolation levels possible. The two most commonly used:

  • Read Committed (default) — each statement within a transaction sees the latest committed data at the time that statement runs.
  • Repeatable Read — the entire transaction sees one consistent snapshot taken at its start, no matter how many other commits happen while it’s running.

Both are built entirely on the row-versioning mechanism described above — no extra locking needed for readers.

7. Dead Tuples, Bloat, and VACUUM

Since old row versions aren’t deleted immediately, they pile up as dead tuples. Over time, this causes table bloat — wasted disk space and slower scans.

PostgreSQL’s VACUUM process cleans this up:

VACUUM accounts;
VACUUM ANALYZE accounts;
  • Reclaims space from dead tuples so it can be reused by future inserts/updates.
  • Autovacuum runs this automatically in the background based on configured thresholds.
  • Neglecting vacuum on high-write tables is one of the most common causes of PostgreSQL performance degradation.

8. Transaction ID Wraparound

XIDs are stored as a 32-bit counter, so they can theoretically wrap around after about 4 billion transactions. If left unchecked, wraparound can make old data appear to vanish, since PostgreSQL relies on XID comparisons to decide visibility.

PostgreSQL protects against this with autovacuum’s “freeze” process, which marks old row versions as permanently visible so they’re no longer subject to XID comparison. You can monitor how close a database is to wraparound with:

SELECT datname, age(datfrozenxid) FROM pg_database;

9. Summary

  • MVCC lets PostgreSQL handle readers and writers concurrently without blocking each other.
  • Every row change creates a new version rather than overwriting the old one.
  • Hidden columns (xmin, xmax, ctid) track which transaction created/expired each row version.
  • Old versions become dead tuples, cleaned up by VACUUM/autovacuum.
  • XID wraparound is a real risk in long-running databases, mitigated by the freeze process.

Understanding MVCC is essential for anyone tuning PostgreSQL performance, debugging bloat, or reasoning about transaction isolation behavior.