By Gaurav · 2026-11-03 · 18 min read

• databases
• postgres
• transactions
• concurrency
• mvcc

A transaction is not a global lock around the database. If it were, every checkout would block every report, and throughput would collapse under read/write contention. PostgreSQL instead uses Multiversion Concurrency Control: writers create new row versions, readers keep seeing an older consistent view, and the two do not block each other.

That design is one of the best things about PostgreSQL. It is also the source of some of its most confusing behavior. Two transactions can look at the same table and see different rows. A SELECT repeated inside a transaction can return different data at Read Committed. An update can fail with a serialization error that has nothing to do with a lock wait. A long-running transaction can silently prevent vacuum from reclaiming gigabytes.

This article builds a working model of MVCC: how row versions are stored, how snapshots decide visibility, what each isolation level actually guarantees, where write skew comes from, how row locks and deadlocks fit in, and why vacuum is not an optional maintenance task. The SQL examples run against the sample dataset, so you can inspect tuple versions and isolation behavior directly.

Row versions, not rows

A PostgreSQL heap tuple is a row version, not a row. It carries a header with transaction metadata. The two fields that matter most are:

• xmin: the transaction ID that created this version.
• xmax: the transaction ID that deleted or superseded this version, or 0 if the version is still live from the perspective of the tuple header.

There is also a ctid: the physical address (block, offset) of the tuple. An index stores the ctid, not the row identity. When a row is updated, PostgreSQL writes a new tuple version, usually in the same page if there is space, and marks the old version as deleted by the updating transaction. The old version remains physically present until vacuum can remove it.

You can see this directly. Run the query, then update one row and run it again:

SELECT id, xmin, xmax, ctid, total_cents
FROM orders
WHERE id = 1;

UPDATE orders
SET total_cents = total_cents + 1
WHERE id = 1;

SELECT id, xmin, xmax, ctid, total_cents
FROM orders
WHERE id = 1;

The CTID changes because a new physical tuple was written. The xmin changes because a different transaction created the new version. The old version is still on disk, marked with an xmax, waiting for vacuum. Every UPDATE in PostgreSQL is effectively a delete plus an insert at the storage level, even though the SQL statement is a single logical modification.

This is why "update in place" is a misleading phrase for PostgreSQL. The logical row is updated; the physical representation is versioned.

Snapshots: the time machine

A snapshot is a description of which transactions are visible at a point in time. It contains:

• xmin: the lowest transaction ID that was still active when the snapshot was taken.
• xmax: the first transaction ID not yet assigned at snapshot time.
• xip: the list of transaction IDs that were in progress at snapshot time.

The simplified visibility rule is:

1. If the tuple's xmin is a transaction that was still in progress or not yet started at snapshot time, the tuple is not visible.
2. If the tuple's xmin committed before the snapshot, the creating transaction is visible.
3. If the tuple's xmax is 0, the version has not been deleted.
4. If the tuple's xmax committed before the snapshot, the version is deleted from the snapshot's perspective.
5. If the tuple's xmax is in progress or aborted, the version is still visible.

The real implementation has more corner cases — subtransactions, command IDs, hint bits, frozen tuples, multixacts — but this model explains the behavior you will debug in practice. A tuple is visible if it was created by a transaction that committed before your snapshot and was not deleted by a transaction that committed before your snapshot.

You can inspect the current snapshot in PostgreSQL:

SELECT pg_current_snapshot() AS snapshot;

The output is a compact representation of xmin:xmax:xip. Each transaction gets a snapshot at the appropriate moment, and each statement may get a new snapshot depending on the isolation level.

flowchart LR
  T1[Transaction 100<br/>snapshot] --> V1[Tuple xmin=90<br/>visible]
  T1 --> V2[Tuple xmin=110<br/>not visible]
  T2[Transaction 120<br/>snapshot] --> V1
  T2 --> V2
  V2 --> C[Committed at 110]
  style V1 fill:#e8f5e9
  style V2 fill:#fff3e0

The same physical tuple can be visible to one snapshot and invisible to another. That is the entire point of MVCC: readers do not block writers, and writers do not block readers.

Read Committed: a new snapshot per statement

Read Committed is the default isolation level in PostgreSQL. Its rule is simple: each statement sees a snapshot taken at the start of that statement.

That means two identical SELECTs in the same transaction can return different data if another transaction commits between them. This is a non-repeatable read, and Read Committed allows it.

SHOW transaction_isolation;

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT count(*) AS pending_before
FROM orders
WHERE status = 'pending';

-- In another session, an order could be marked paid here.

SELECT count(*) AS pending_after
FROM orders
WHERE status = 'pending';

COMMIT;

The runnable block executes sequentially, so it will not show a change caused by a concurrent transaction. The lesson is in the rule, not the output: inside one transaction, each statement gets a fresh snapshot at Read Committed.

Read Committed is a good default for many OLTP workloads because it avoids holding a snapshot for the entire transaction, which reduces vacuum pressure and lets each statement see recent commits. The cost is that invariants checked across multiple statements are not stable. If you read a row, decide something, and write based on that decision, another transaction can change the row in between. That is the lost-update problem, and it exists even though each individual statement is atomic.

Repeatable Read: one snapshot per transaction

Repeatable Read takes a snapshot at the start of the transaction and uses it for every statement. Within the transaction, repeated reads see the same data. Non-repeatable reads and phantoms are prevented for reads.

BEGIN ISOLATION LEVEL REPEATABLE READ;

SHOW transaction_isolation;

SELECT count(*) AS pending
FROM orders
WHERE status = 'pending';

-- The count will stay stable for the rest of this transaction.

COMMIT;

PostgreSQL's Repeatable Read is snapshot isolation. It is stronger than the SQL standard's minimum Repeatable Read, but it is not serializable. The crucial difference is that snapshot isolation prevents read anomalies, not all write anomalies.

The most important write anomaly under snapshot isolation is write skew. Two transactions read overlapping data, each makes a decision based on what it read, and both write disjoint rows. Neither write conflicts with the other at the row level, so both commit, and the combined result violates an invariant that neither transaction individually violated.

A classic example: an on-call schedule requires at least two doctors. Two doctors are on call. Each starts a transaction, counts the doctors on call (two), decides it is safe to go off call, and updates their own row. The writes touch different rows, so there is no row-level conflict. Both commit. Now zero doctors are on call.

Snapshot isolation cannot see the problem because each transaction's snapshot is consistent with its own writes. Serializable isolation has to detect the dangerous read/write dependency structure and abort one transaction.

Serializable: optimistic, not free

Serializable in PostgreSQL is implemented with Serializable Snapshot Isolation (SSI). It runs transactions concurrently, tracks read/write dependencies, and aborts one transaction when a dangerous structure of dependencies could produce a serialization anomaly.

You do not get serializable behavior by taking more locks. You get it by allowing optimistic execution and paying for conflicts at commit time. The failure mode is a serialization error with SQLSTATE 40001:

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

Applications must be prepared to retry these transactions. A serializable transaction that is not wrapped in a retry loop is a correctness bug waiting for load. The same is true for deadlock errors (40P01): the database aborts one participant, and the application must retry.

Serializable is the right choice when:

• The invariant spans multiple rows or tables.
• The transaction reads data and writes based on what it read.
• You can tolerate retries and the extra monitoring they require.
• The workload does not have extreme contention on the same predicate space.

It is a poor choice for long-running analytics or reporting transactions, because SSI tracks dependencies for the life of the transaction, and conflicts become more likely as the transaction grows.

Lost updates and how to prevent them

The lost-update anomaly is the most common concurrency bug in application code. It happens when two transactions read the same value, compute a new value independently, and write it back. The second write overwrites the first.

This is not a PostgreSQL bug. It is a read-modify-write race:

Session A: SELECT balance FROM accounts WHERE id = 1;  -- 100
Session B: SELECT balance FROM accounts WHERE id = 1;  -- 100
Session A: UPDATE accounts SET balance = 110 WHERE id = 1;
Session B: UPDATE accounts SET balance = 120 WHERE id = 1;  -- A's update lost

There are three standard fixes:

1. Do the arithmetic in the database.

UPDATE accounts
SET balance = balance + 10
WHERE id = 1;

The update reads and writes in one statement, under the row lock acquired by the update. Concurrent updates serialize on the row, and both increments apply.

2. Lock the row before reading.

BEGIN;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

-- Compute the new balance in the application.

UPDATE accounts
SET balance = 110
WHERE id = 1;

COMMIT;

FOR UPDATE locks the selected row until the transaction ends. Other transactions that try to lock the same row wait, or fail with NOWAIT, or skip it with SKIP LOCKED.

3. Use optimistic concurrency with a version column.

UPDATE accounts
SET balance = 110, version = version + 1
WHERE id = 1 AND version = 7;

If the row count is zero, someone else changed the row first. The application can retry or return a conflict. This avoids holding locks during user think time, at the cost of retry logic.

Each approach has a different contention profile. Database-side arithmetic is best for simple counters and increments. Pessimistic locking is simple but holds locks. Optimistic concurrency scales better for long user interactions but requires a retry path and a monotonic version.

Row locks, table locks, and queues

PostgreSQL row-level locks are implemented through the tuple header (xmax and multixact information), not as a separate lock entry per row. Table-level locks, predicate locks, and some waiting states do appear in pg_locks. This distinction matters when you are debugging: a blocked UPDATE may not show a neat per-row lock object.

The common row-lock modes are:

For queue-like workloads, FOR UPDATE SKIP LOCKED is the standard pattern: workers claim different rows without waiting for each other.

WITH claimed AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running', started_at = now()
FROM claimed
WHERE jobs.id = claimed.id
RETURNING jobs.id;

This is not a substitute for a real queue at high scale, but it is a robust pattern for database-backed work queues.

Deadlocks happen when two transactions hold locks the other needs. PostgreSQL detects cycles after deadlock_timeout (default 1 second) and aborts one transaction with 40P01. The application must retry. Preventing deadlocks is mostly about consistent lock ordering: if every transaction locks rows or tables in the same order, cycles are much less likely.

You can inspect the current session's locks:

SELECT locktype, mode, granted, relation::regclass AS relation
FROM pg_locks
WHERE pid = pg_backend_pid()
ORDER BY locktype, mode;

For a production incident, look at pg_locks joined with pg_stat_activity to see who is waiting on whom, and use pg_blocking_pids(pid) to identify blockers.

Vacuum: the cleanup problem

MVCC creates dead tuples. A dead tuple is a row version that no active or future snapshot can see: its deleting transaction committed, and no snapshot still needs the old version. Vacuum reclaims that space so it can be reused.

Vacuum cannot remove a tuple while any snapshot might still need it. That is why long-running transactions are dangerous:

• A transaction that started an hour ago holds an old snapshot.
• Vacuum cannot remove tuples deleted after that snapshot began, because the transaction might still need to see them.
• Dead tuples accumulate.
• Tables and indexes grow.
• Scans read more pages.
• Autovacuum falls behind.
• Eventually, transaction ID wraparound becomes a risk.

This is not theoretical. A single idle in transaction connection, a forgotten BEGIN, or a reporting query that runs for hours can hold back cleanup for the entire database. PostgreSQL exposes the problem through pg_stat_activity:

SELECT pid, state, xact_start, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

In production, set idle_in_transaction_session_timeout and monitor long transactions. Also monitor dead tuples and vacuum progress:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Vacuum has a second job: freezing. Transaction IDs are 32-bit and wrap around after roughly 2.1 billion transactions. To prevent wraparound, vacuum "freezes" old tuples, marking them as permanently visible. If freezing falls too far behind, PostgreSQL will refuse new transactions rather than risk data corruption — an emergency that is much better prevented than debugged.

Monitor the age of the oldest unfrozen transaction ID:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

A healthy system keeps this well below the wraparound threshold. Autovacuum tuning, aggressive vacuum during low-traffic windows, and avoiding long transactions are the levers.

Autovacuum is not optional

Autovacuum runs in the background and decides when to vacuum or analyze a table based on thresholds. The defaults are conservative. On large or high-churn tables, you often need per-table settings:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000
);

The exact values depend on the table size and write rate. The important idea is that a fixed percentage threshold behaves very differently on a 1,000-row table and a 1-billion-row table. Large tables need lower scale factors or explicit thresholds.

Vacuum also interacts with indexes. Dead index entries remain until vacuum cleans them, and index-only scans depend on the visibility map being up to date. A table that is not vacuumed enough can have correct query results but poor plans, because the planner sees stale statistics and the executor cannot use index-only scans effectively.

The visibility map, hint bits, and the hidden cost of a lookup

There is a second data structure you need in your mental model: the visibility map. It is a bitmap with two bits per heap page. One bit says "all tuples on this page are visible to every snapshot." The other says "all tuples on this page are frozen." Vacuum sets these bits; ordinary writes clear them.

The visibility map is what makes index-only scans possible. When the executor walks an index and finds a matching ctid, it normally has to fetch the heap tuple to check visibility: was this version created by a transaction that committed before my snapshot, and is it still live? If the page's all-visible bit is set, the executor can skip that heap fetch entirely. The index entry is enough.

This has an operational consequence that surprises people. You add a covering index, expect an index-only scan, and still see Heap Fetches: 128403 in EXPLAIN. The query is correct; the optimization did not apply because the visibility map was stale. A freshly bulk-loaded table, a table with a high update rate, or a table whose autovacuum cannot keep up will all fall into that state. VACUUM (not VACUUM FULL) is usually enough to refresh the map:

VACUUM (VERBOSE, ANALYZE) orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents
FROM orders
WHERE status = 'pending';

The other hidden mechanism is hint bits. Every visibility check requires knowing whether the creating and deleting transactions committed, which means consulting the commit log (pg_xact). Rather than repeat that lookup for every tuple, PostgreSQL caches the answer in two bits in the tuple header: HEAP_XMIN_COMMITTED and HEAP_XMAX_COMMITTED (and their aborted counterparts). Setting a hint bit is a write to the page, even though it does not change any user-visible data.

Hint bits are why the first read after a crash or a restore can be slower, and why a read-only query can generate WAL traffic in some configurations. They are also why VACUUM FREEZE matters: a frozen tuple does not need its creating transaction checked at all, because it is treated as ancient history visible to every snapshot. Freezing is not just housekeeping for wraparound; it is a permanent performance optimization for old data.

This is the part of MVCC that rarely appears in diagrams. Visibility is not free. Every index lookup either consults the heap or relies on a map that vacuum maintains. Every tuple read either trusts a hint bit or pays for a commit-log lookup. The system is fast because those caches are usually warm and accurate, not because visibility checks are cheap in principle.

Subtransactions and the cost of a savepoint

PostgreSQL implements savepoints as subtransactions. Each SAVEPOINT gets its own transaction ID, and ROLLBACK TO SAVEPOINT marks the work done since the savepoint as aborted without aborting the whole transaction. This is genuinely useful: an application can attempt a risky insert, catch the unique-violation error, roll back to a savepoint, and continue the outer transaction.

The cost is less obvious. Subtransactions consume transaction IDs, and the mapping from subtransaction IDs to parent transactions is stored in pg_subtrans. When a tuple's xmin belongs to a subtransaction, visibility checking has to follow that mapping to find the parent's commit status. Deeply nested subtransactions therefore make visibility checks more expensive, not just the transaction itself.

Worse, PostgreSQL has a hard limit on how many subtransactions can be tracked as a single cache entry. When an application opens savepoints in a loop without releasing them — a common pattern in ORMs and in "try each row individually" batch jobs — the backend can overflow its subtransaction cache and start treating subtransactions as top-level for visibility purposes. That condition is logged as subtransactions are starting to overflow and eventually becomes a performance cliff: every visibility check has to consult pg_subtrans on disk.

The practical rule is: use savepoints deliberately and release them. Wrap a risky statement in a savepoint, then RELEASE SAVEPOINT when it succeeds. Do not put a savepoint inside a per-row loop over a large batch and leave hundreds of thousands of them open. If you need per-row error isolation, consider INSERT ... ON CONFLICT DO NOTHING, a separate transaction per batch, or an explicit error-handling design that does not depend on an enormous subtransaction stack.

Long-running subtransactions also interact with vacuum in the same way as long transactions: the oldest active transaction ID holds back cleanup, and an old subtransaction can be the one doing it. pg_stat_activity shows the backend's backend_xid and backend_xmin, but the subtransaction that is actually pinning the horizon may be less visible. When vacuum refuses to clean a table despite no obvious long transaction, check for leaked savepoints.

Bloat is not just wasted disk

Bloat is the gap between the space a table occupies and the space its live rows need. It is the accumulated cost of MVCC: dead tuples, dead index entries, partially empty pages, and free space that is not in the right place.

The naive view is that bloat costs disk. The real cost is worse:

• Scans read more pages. A sequential scan of a bloated table touches pages that contain no live rows. Latency rises even though the result set is unchanged.
• Indexes grow. Every dead tuple has index entries until vacuum removes them. Larger indexes mean deeper trees, more cache pressure, and slower index scans.
• Vacuum takes longer. The cleanup job has more work to do, which makes it more likely to fall behind, which creates more bloat. This is the feedback loop that turns a temporary spike into a permanent condition.
• Cache efficiency drops. Shared buffers hold dead pages that could have held live data or index pages for other tables.
• Backups and replicas pay for it. Physical backups copy dead space, and replicas replay the writes that created it.

Measuring bloat accurately is harder than it looks. pg_stat_user_tables.n_dead_tup tells you how many dead tuples the statistics collector thinks exist, but it is an estimate updated by vacuum and analyze. pgstattuple reads the relation and gives exact free-space and dead-tuple counts, at the cost of scanning the table. Extensions such as pgstattuple and third-party bloat-estimation queries use heuristics based on page density and are useful for triage, not for precise accounting.

Reclaiming bloat has three main strategies, with very different lock profiles:

VACUUM FULL is not an incremental improvement on VACUUM; it is a different operation. It rewrites the entire table, which means it needs free disk space roughly equal to the table size, and it holds an exclusive lock for the whole rewrite. On a large table, that is an outage. It is the right tool when you need to return space to the operating system, and the wrong tool for routine maintenance.

The deeper point is that bloat is a symptom, not the disease. If a table is bloated because of a long transaction, VACUUM FULL will make it clean and the long transaction will immediately bloat it again. If it is bloated because autovacuum thresholds are too conservative for its write rate, the rewrite buys time but not a fix. Diagnose the cause before scheduling the rewrite: long transactions, leaked savepoints, insufficient autovacuum settings, or an update pattern that defeats HOT.

Repeatable Read, Serializable, and application design

The isolation level is not a global setting you can crank to maximum without consequences. It is a per-transaction contract with a cost:

A practical policy:

• Use Read Committed by default for simple OLTP operations.
• Use Repeatable Read for multi-statement reads that need a stable view, or for transactions that update a set of rows consistently.
• Use Serializable for invariants that span rows and cannot be enforced by a single UPDATE or a unique constraint.
• Always implement retry loops for 40001 and 40P01.
• Keep transactions short. A long Serializable transaction is both a conflict magnet and a vacuum hazard.

A retry loop should use exponential backoff with jitter, a bounded number of attempts, and idempotent operations. Retrying a non-idempotent side effect (charging a card, sending an email) can duplicate work; use an idempotency key or move the side effect outside the transaction.

Connection pooling changes the rules

Application connection pools and external poolers such as PgBouncer interact badly with session-level features. In transaction pooling mode, a server connection is assigned to a client only for the duration of a transaction, then returned to the pool. That breaks:

• Session-level SET commands
• Advisory locks held across transactions
• LISTEN/NOTIFY
• Temporary tables
• Cursors with WITH HOLD
• Some prepared-statement workflows, depending on pooler version and configuration

The isolation level itself is usually fine, because BEGIN and COMMIT are inside the transaction. But any state you assume persists between transactions may not. If you rely on session state, use session pooling or make the state explicit in each transaction.

Pool size is another concurrency decision. More connections are not free: each backend has memory overhead, and too many concurrent transactions increase contention, vacuum lag, and context switching. A common starting point is a small pool relative to CPU cores, measured under real load, not a large number chosen "to be safe."

Monitoring the machinery

A useful MVCC dashboard includes:

• Long-running transactions: pg_stat_activity.xact_start, state = 'idle in transaction'
• Lock waits: pg_locks where granted = false, pg_blocking_pids()
• Dead tuples and vacuum progress: pg_stat_user_tables.n_dead_tup, pg_stat_progress_vacuum
• Transaction ID age: age(datfrozenxid)
• Rollback rate: pg_stat_database.xact_rollback
• Serialization failures and deadlocks: application logs, pg_stat_database.deadlocks
• Index bloat: pg_stat_user_indexes, pgstattuple

The two metrics people forget are long transactions and transaction ID age. Both are leading indicators of an outage that will not show up in query latency until it is too late.

The model to keep

MVCC is a time machine: each transaction sees a consistent snapshot of row versions, and writers create new versions instead of overwriting. That gives you non-blocking reads, but it also means:

• A row is a logical identity with many physical versions.
• xmin, xmax, and snapshots decide visibility.
• Read Committed takes a snapshot per statement; Repeatable Read takes one per transaction.
• Snapshot isolation prevents read anomalies but not write skew.
• Serializable detects dangerous dependencies and aborts; retries are part of the contract.
• Row locks and deadlocks are separate from visibility; FOR UPDATE is how you opt into pessimistic locking.
• Vacuum is the price of MVCC. Long transactions and insufficient autovacuum turn that price into bloat, latency, and wraparound risk.

If you remember one sentence: PostgreSQL does not lock rows for readers; it keeps old versions around and asks vacuum to clean them up later. Everything else — isolation levels, write skew, deadlocks, bloat, connection pooling — follows from that design.