PostgreSQL MVCC and snapshot visibility

MVCC (multi-version concurrency control) lets ordinary reads avoid blocking writes. An UPDATE does not overwrite the value in place for every reader; it creates a new row version, and each query uses its snapshot to decide which version is visible.

Observe snapshots in two sessions

Prepare data:

CREATE TABLE mvcc_demo (
  id integer PRIMARY KEY,
  value text NOT NULL
);
INSERT INTO mvcc_demo VALUES (1, 'before');

Session A:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT value FROM mvcc_demo WHERE id = 1; -- before

Session B:

UPDATE mvcc_demo SET value = 'after' WHERE id = 1;
COMMIT;

Back in session A:

SELECT value FROM mvcc_demo WHERE id = 1; -- still before
COMMIT;
SELECT value FROM mvcc_demo WHERE id = 1; -- after

At READ COMMITTED, each statement gets a new snapshot, so a second query in the same transaction can observe session B's committed value.

Why long transactions hurt

While an old snapshot can still see row versions, vacuum cannot treat them as fully reclaimable. Long transactions therefore increase:

• dead tuples and table/index bloat;
• vacuum work and disk use;
• retention pressure from slots, logical decoding, or standbys;
• the risk window around transaction ID wraparound.

Find sessions holding old transactions or snapshots:

SELECT
  pid, usename, application_name, state,
  now() - xact_start AS transaction_age,
  age(backend_xmin) AS snapshot_xid_age,
  wait_event_type, wait_event,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY xact_start NULLS LAST;

Do not terminate a session merely because it is old. Confirm workload purpose, backup/maintenance activity, retry safety, and termination impact.

Use PostgreSQL 18 concurrency control and transaction isolation as authoritative references.