Multi-version concurrency control lets PostgreSQL keep different row versions and decide which ones a query may see. An ordinary reader can continue seeing an older committed version while another transaction writes a newer one. This reduces conflicts between reading and writing, but does not give every transaction one permanently frozen view. Snapshot lifetime depends on isolation, and a transaction can see its own earlier changes even when other transactions cannot.

Visibility follows the snapshot

Under Read Committed, an ordinary read normally gets a new snapshot for each statement. Under PostgreSQL Repeatable Read, the transaction reuses the snapshot established by its first non-transaction-control statement. Merely sending BEGIN does not establish the useful reading boundary. A transaction that begins earlier but first reads later can therefore see commits that occurred between those events.

Updating a row creates a new row version. Visibility rules determine whether an older snapshot reads the earlier version while a newer snapshot reads the update. The physical presence of a tuple is not sufficient to make it visible, and a SQL query does not simply return the newest bytes that happen to be stored on a page.

The transaction’s own changes are an important exception to a simplistic historical-picture metaphor. It can read records it inserted after its snapshot was established. Consequently, its visible data can combine an earlier view of other transactions with its own new work. That behavior is necessary for useful transactions, but it is not a photograph of one externally committed moment.

When it breaks

A plain read’s ordinary table lock does not conflict with normal row updates, yet locking has not disappeared. Two writers can contend for the same row. SELECT FOR UPDATE explicitly requests row locks, and an ACCESS EXCLUSIVE table lock conflicts with ordinary readers. MVCC does not promise that every command containing the word SELECT will proceed without waiting.

An old snapshot can also keep obsolete row versions needed by a reader. Vacuum cannot simply remove a version that remains visible to a relevant snapshot. Space pressure depends on update volume, retained versions, and cleanup progress, not just the number of open transactions. Starting another vacuum does not make an active snapshot forget its visibility requirements.

Check it quickly

Open two fresh psql -X -v ON_ERROR_STOP=1 sessions, A and B, in the same disposable PostgreSQL 18 database. Use the same role and normal autocommit outside explicit blocks. The schema name must be unused. Keep the following order so B commits its changes while A’s reading transaction remains open.

  1. In B, create one committed original row.
CREATE SCHEMA mvcc_visibility_demo;
CREATE TABLE mvcc_visibility_demo.items (id integer PRIMARY KEY,note text);
INSERT INTO mvcc_visibility_demo.items VALUES (1,'old');
  1. In A, establish a Repeatable Read snapshot by reading it.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT id,note FROM mvcc_visibility_demo.items ORDER BY id;
  1. In B, update the original row, insert a second row, and inspect the committed result.
UPDATE mvcc_visibility_demo.items SET note='new' WHERE id=1;
INSERT INTO mvcc_visibility_demo.items VALUES (2,'other');
SELECT id,note FROM mvcc_visibility_demo.items ORDER BY id;
  1. In A, read again, insert its own third row, read again, and commit.
SELECT id,note FROM mvcc_visibility_demo.items ORDER BY id;
INSERT INTO mvcc_visibility_demo.items VALUES (3,'mine');
SELECT id,note FROM mvcc_visibility_demo.items ORDER BY id;
COMMIT;
  1. In A, read with a fresh statement after committing. Then clean up in B.
SELECT id,note FROM mvcc_visibility_demo.items ORDER BY id;

DROP SCHEMA mvcc_visibility_demo CASCADE;

On PostgreSQL 18.6, A’s first read returned (1,old). B completed without waiting for that ordinary reader and saw (1,new) plus (2,other). A still saw only (1,old) when it read again. After A’s own insertion, it saw (1,old) and (3,mine), while B’s second row remained outside A’s snapshot.

Once A committed, its next read returned all three current rows: (1,new), (2,other), and (3,mine). This sequence checks visibility of both another transaction’s writes and one’s own writes. A single reader with no concurrent update would not establish that distinction.

End the snapshot when its purpose ends

Choose a transaction snapshot for work that requires that reading contract, and finish it when the operation is complete. Investigate long-lived snapshots alongside write volume and vacuum progress rather than assuming version storage cleans itself immediately. Repeatable visibility also does not prevent every cross-row business anomaly; an operation may still require Serializable isolation, explicit coordination, or constraints. MVCC supplies row-version visibility, while the application’s complete concurrency contract determines whether its decisions remain correct.

Sources