Understanding PostgreSQL MVCC: How It Handles Concurrency

One of PostgreSQL's biggest selling points is its way of handling concurrency. The goal is simple: reads never block writes and writes never block reads. PostgreSQL achieves this with Multi-Version Concurrency Control (MVCC), a technique also used by other databases such as Oracle, Berkeley DB, CouchDB, etc.

How MVCC Works

In PostgreSQL each transaction receives a transaction ID (XID). The XID is assigned when the transaction starts, even for single‑statement INSERT, UPDATE or DELETE. Every row stores the XID of the transaction that created it (xmin) and, for updates/deletes, the XID that deleted it (xmax). A row is visible to a transaction only if it was created by a committed transaction with xmin less than the current XID and, for updates, its xmax is either null or greater than the current XID.

Example: when you insert a row, PostgreSQL stores the current XID as xmin. The row remains invisible to other transactions until you COMMIT. After commit, new transactions can see the row because its xmin < their XID.

For DELETE and UPDATE the same mechanism applies, using xmax to determine visibility.

Below is a simple table creation example: CREATE TABLE numbers (value int); You can query the hidden xmin and xmax values directly: SELECT *, xmin, xmax FROM numbers; And obtain the current transaction’s XID: SELECT txid_current(); Transaction isolation levels control what happens when two transactions try to modify the same row. PostgreSQL’s default level is READ COMMITTED, which retries a statement if the target row was changed by a concurrent transaction before the statement finishes. If you need stricter guarantees, you can set the isolation level to SERIALIZABLE, which will abort one of the conflicting transactions with an error such as “could not serialize access due to concurrent update”.

When SERIALIZABLE is used, the application must catch the error and retry or abort the operation.

Drawbacks of MVCC

Because each transaction sees its own version of the data, PostgreSQL must retain old row versions even after they become invisible. UPDATE creates a new row, and DELETE merely marks a row as deleted, leaving “dead rows” that are never visible to future transactions. These dead rows accumulate and must be reclaimed.

Another limitation is that XIDs are 32‑bit counters, supporting roughly four billion transactions. When the counter wraps around, all existing rows appear to belong to future transactions and become inaccessible unless vacuuming is performed.

Both dead‑row buildup and XID wraparound are addressed by the VACUUM command, which should be run regularly. PostgreSQL includes an auto‑vacuum daemon that performs this cleanup automatically, but its schedule may need adjustment depending on the deployment environment.