Snapshots and Visibility: How PostgreSQL Decides What You See

The one thing to understand first

MVCC stores many versions of each row; a snapshot is the rule that selects which versions a statement or transaction may see. Conceptually it captures "which transactions had committed at the instant I started looking." The struct is SnapshotData in src/include/utils/snapshot.h, built by GetSnapshotData() in src/backend/storage/ipc/procarray.c.

An isolation level is not a locking strategy — it is simply a decision about when you take your snapshot. Once you see that READ COMMITTED re-snapshots every statement while REPEATABLE READ freezes one snapshot for the whole transaction, the visibility "surprises" and the link between an idle transaction and runaway bloat both become obvious.

The three pieces that define a snapshot

• xmin — the oldest transaction ID still running. Anything older is definitely committed or aborted (resolved).
• xmax — the first not-yet-assigned XID. Anything at or above this had not started and is invisible.
• xip[] — the list of XIDs that were in progress when the snapshot was taken.

With just these, visibility for any tuple is decidable: a tuple inserted by XID x is visible if x < xmax, x is not in xip[], and x committed. The deleting XID (t_xmax) is checked the same way to decide whether the version has been superseded.

Building a snapshot is the hot path

GetSnapshotData() walks the ProcArray — the shared array of all active backends — under a lock to read everyone's current XID. On high-core machines this was historically a scalability bottleneck; modern PostgreSQL caches results and uses fine-grained techniques to reduce contention, but the principle remains: taking a snapshot is reading the global transaction state.

Isolation levels = when you take the snapshot

PostgreSQL's isolation levels differ almost entirely in snapshot timing:

• READ COMMITTED (default): a new snapshot is taken at the start of each statement. This is why two consecutive SELECTs in one transaction can see different data — each saw a fresh snapshot.
• REPEATABLE READ: one snapshot is taken at the first statement and reused for the whole transaction. You get a stable view; concurrent commits are invisible to you.
• SERIALIZABLE: like REPEATABLE READ plus Serializable Snapshot Isolation (SSI), which tracks read/write dependencies (predicate.c) and aborts transactions that would produce a non-serializable schedule, raising 40001 serialization_failure.

READ COMMITTED's update twist

Under READ COMMITTED, when an UPDATE finds a row that a concurrent transaction has modified and committed, it does not simply fail. It re-reads ("EvalPlanQual") the latest committed version and re-applies the update against it. This is why lost-update anomalies can still occur at READ COMMITTED and why moving critical read-modify-write logic to REPEATABLE READ or SERIALIZABLE (with retry) is the correct fix.

Snapshots pin the vacuum horizon

The oldest snapshot xmin across the whole cluster is the vacuum horizon: no tuple version deleted by an XID newer than that horizon can be removed, because some transaction might still need to see it. A single forgotten transaction left idle in transaction can therefore stop vacuum from reclaiming any recent dead tuples, anywhere — the mechanism behind many runaway-bloat incidents.

Exported and historic snapshots

Snapshots can be exported (pg_export_snapshot()) so multiple sessions share one consistent view — exactly how pg_dump with parallel workers, and logical-replication initial sync, get a coherent copy across connections. Understanding snapshots demystifies how consistent backups are even possible on a live database.

Layer 3 — Watch it happen on your own database

-- Find the transaction holding back the horizon
SELECT pid, state, backend_xmin, age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration, query
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
ORDER  BY age(backend_xmin) DESC;

pid  | state  | backend_xmin | xmin_age | xact_duration |                              query                              
-------+--------+--------------+----------+---------------+-----------------------------------------------------------------
 43680 | active |         1164 |        0 | 00:00:00      | SELECT pid, state, backend_xmin, age(backend_xmin) AS xmin_age,+
       |        |              |          |               |        now() - xact_start AS xact_duration, query              +
       |        |              |          |               | FROM   pg_stat_activity                                        +
       |        |              |          |               | WHERE  backend_xmin IS NOT NULL                                +
       |        |              |          |               | ORDER  BY age(backend_xmin) DESC;
(1 row)

Open a second session, run BEGIN; SELECT 1; and leave it idle. Watch that session appear at the top of this list with a growing xmin_age — it is now pinning the vacuum horizon for the entire cluster. Commit or roll it back and the horizon advances. This is the single most useful query for diagnosing "why won't my dead tuples go away."

Layer 4 — The levers this hands you

• Pick the isolation level deliberately. Use READ COMMITTED for throughput; move read-modify-write logic to REPEATABLE READ or SERIALIZABLE (with a retry loop for 40001) when correctness demands a stable view.
• Cap idle transactions. Set idle_in_transaction_session_timeout so a forgotten BEGIN cannot pin the vacuum horizon indefinitely.
• **Watch backend_xmin, not just query duration.* A long-idle transaction with a held snapshot is more dangerous to vacuum than a long-running* query that keeps re-snapshotting.
• Use exported snapshots for consistent parallel reads (parallel pg_dump, logical sync) instead of trying to freeze the whole database.

Layer 5 — What an Oracle DBA should expect vs what they get

Read consistency is the same goal, reached from the opposite direction:

• No UNDO-based reconstruction. Oracle builds your consistent view by rolling back changes from UNDO using the SCN. PostgreSQL keeps every version in the heap and filters them with xmin/xmax/xip[] — so the cost of a long snapshot is retained dead tuples, not "snapshot too old" from UNDO expiry.
• The snapshot is the SCN's job. Oracle's SCN gives statement- and transaction-level consistency; the SnapshotData struct plays that role, and the isolation level just changes how often it is captured.
• REPEATABLE READ actually repeats. Oracle's default is statement-level read consistency (like Postgres READ COMMITTED). Oracle SERIALIZABLE is snapshot-based; PostgreSQL's REPEATABLE READ is true snapshot isolation, and PostgreSQL SERIALIZABLE adds SSI dependency tracking that Oracle does not have.
• "Snapshot too old" has a different cause. In Oracle it usually means UNDO was overwritten; in Postgres there is no equivalent error by default — the danger flips to a held snapshot blocking vacuum and bloating the heap.

Key takeaway

A snapshot is three values — xmin, xmax, and the in-progress list xip[] — captured from the ProcArray by GetSnapshotData(). Isolation levels are nothing more than when you take that snapshot. The oldest live snapshot pins the cluster-wide vacuum horizon, which is why one idle transaction can stop all dead-tuple cleanup. Master snapshots and you understand isolation, consistent backups, and the most common bloat incident in one model.