PostgreSQL in Practice
PostgreSQL MVCC and Snapshots
PostgreSQL stores tuple versions whose transaction and command metadata are interpreted against a snapshot. Visibility changes by isolation level, while long-lived snapshots retain heap versions and can turn application transaction duration into maintenance debt.
- xmin and xmax describe version history, not business ownership.
- READ COMMITTED normally takes a new snapshot per statement.
- REPEATABLE READ and SERIALIZABLE retain a transaction snapshot.
- Command IDs order effects inside one transaction.
- HOT can avoid new index entries.
- Old snapshots pin cleanup horizons.
| Isolation | Snapshot | Application consequence |
|---|---|---|
| READ COMMITTED | new snapshot at command start | recheck assumptions across statements or lock/encode invariant |
| REPEATABLE READ | first non-control statement for transaction | stable reads; update conflicts can abort |
| SERIALIZABLE | transaction snapshot plus SSI dependencies | retry SQLSTATE 40001 as a complete unit |
| READ UNCOMMITTED | behaves as READ COMMITTED | do not expect dirty reads |
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT txid_current_if_assigned(), pg_current_snapshot();
SELECT pg_snapshot_xmin(pg_current_snapshot()), pg_snapshot_xmax(pg_current_snapshot());
-- Inspect xmin only for diagnosis; never build business logic on tuple headers.
SELECT order_id, xmin FROM commerce.orders WHERE order_id = $1;
COMMIT;