Back to DAG

Snapshot (point-in-time read)

databases

What is a Database Snapshot?

Prerequisites(1)
Isolation
Builds on to(2)
MVCCSerializable Snapshot Isolation (SSI)

A snapshot captures a consistent, frozen view of the database at a specific point in time. All reads performed through a snapshot see the exact same data, regardless of any concurrent modifications happening after the snapshot was taken. Snapshots are the foundation of Snapshot Isolation (SI) and Multi-Version Concurrency Control (MVCC) — they allow read-only queries to execute without acquiring any locks and without blocking writers.

How Snapshots Work

Every transaction in the database is assigned a unique, monotonically increasing transaction ID (txnId) when it begins. A snapshot records the state of the system at the moment it is created. The core idea: a row version is visible to a snapshot if and only if the transaction that created that version had already committed before the snapshot was taken.

Visibility Rules

Given a snapshot taken at time T, a row version created by transaction Tc is visible if:

  1. Tc committed before the snapshot was taken.
  2. Tc is not in the set of transactions that were still active (in-progress) when the snapshot was created.

A version is not visible if:

  1. Tc had not yet committed when the snapshot was taken (still in-progress or not yet started).
  2. Tc was aborted.

PostgreSQL's Snapshot Implementation

PostgreSQL represents a snapshot as a compact data structure containing:

  • xmin: the lowest still-active transaction ID at snapshot time. All transactions with IDs less than xmin are guaranteed to have completed (committed or aborted).
  • xmax: one past the highest allocated transaction ID at snapshot time. Any transaction with ID >= xmax started after the snapshot.
  • xip[]: the list of transaction IDs that were in-progress (active) at snapshot time. These are between xmin and xmax but had not yet committed.

A version created by transaction Tc is visible if: Tc < xmax AND Tc is not in xip[] AND Tc committed (not aborted).

Snapshot Scopes

Different isolation levels take snapshots at different granularities:

  • READ COMMITTED: a new snapshot is taken for every statement. Each SELECT sees the latest committed data as of its start.
  • REPEATABLE READ / SNAPSHOT ISOLATION: a single snapshot is taken at the start of the transaction. All statements within the transaction see the same consistent view.
  • SERIALIZABLE: same snapshot as REPEATABLE READ, but with additional conflict detection (SSI).

Read-Only Queries and Performance

Because snapshots never require locks for reading, read-only queries run with minimal overhead. A long-running analytical query can scan millions of rows without blocking any concurrent writes, and without being affected by them. This is a major advantage of snapshot-based systems over lock-based systems like pure 2PL.

Real-Life: Analytics Query During Heavy Writes

Real-World Example

An e-commerce platform runs a nightly analytics report that scans all orders for the past month. Meanwhile, hundreds of new orders per second are being inserted by the web application.

With snapshot isolation:

  1. The analytics query starts a REPEATABLE READ transaction. A snapshot is taken at this moment (say, txnId = 1000).
  2. The report scans millions of order rows. It only sees orders committed before txnId 1000.
  3. Meanwhile, transactions 1001 through 1500 insert new orders. These are invisible to the report.
  4. The report finishes after 30 minutes, producing a perfectly consistent result as of the snapshot time.
  5. No locks were held. No writers were blocked. No inconsistencies.

Without snapshot isolation (using locking): The analytics query would need shared locks on every row it reads. Writers inserting new orders would block if they touched the same pages. The report could take hours due to lock contention, and might even cause deadlocks.

Real systems:

  • PostgreSQL: uses snapshots for all isolation levels, represents them as (xmin, xmax, xip[])
  • MySQL InnoDB: creates a "read view" at transaction start for REPEATABLE READ, which is essentially a snapshot
  • Oracle: uses System Change Numbers (SCN) as snapshot timestamps — each snapshot is a single SCN value
  • CockroachDB: uses MVCC with hybrid-logical clock timestamps as snapshot points

Snapshot Visibility Rules

Snapshot Visibility: Which Versions Does Transaction T5 See? Time T5 takes snapshot T1: committed VISIBLE T2: committed VISIBLE T3: still active (in xip[]) NOT VISIBLE T4: aborted NOT VISIBLE T6: started after NOT VISIBLE T5: reading with snapshot xmin=T1 xmax=T6 xip=[T3] T4 aborted (checked via commit log)
Step 1 of 2