MVCC
Snapshots
16
Copyright
© Postgres Professional, 2016–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Row Version Visibility
Snapshot
Virtual Transaction ID
Transaction and Database Horizon
Snapshot Export
3
Snapshot
Captures a consistent view of data at a specific point in time
only changes committed up to that moment are visible
Created at:
Read Committed —
start of each statement
Repeatable Read, Serializable —
start of the first statement
snapshot
xid
In the previous Page Layout and Row Versions lesson, we saw that a data
page can physically contain several versions of the same row.
Transactions work with a snapshot which determines the visibility of
particular rows. This mechanism provides a consistent view of the data at a
specific point in time (in the figure, the moment the snapshot was created is
shown in blue).
A snapshot sees only the changes committed by the time it was taken, thus
providing a consistent (ACID) view of the data at this specific point in time.
This completely prevents dirty reads at any isolation level.
At the Read Committed isolation level, a snapshot is created at the start of
each transaction statement. This snapshot is active while the statement is
being executed. Thus, each statement sees a consistent data state.
However, changes committed between two statements can cause non-
repeatable reads and phantom reads.
At the Repeatable Read (and Serializable) isolation level, a snapshot is
created once at the start of the transaction's first statement. This snapshot
remains active until the very end of the transaction. In this case, both non-
repeatable reads and phantom reads are prevented.
4
Row Version Visibility
Row version visibility is defined by xmin and xmax fields
A tuple is included in a snapshot if:
changes of xmin transaction are visible to the snapshot,
changes of xmax transaction are not visible to the snapshot
Transaction’s changes are visible if:
it is the same transaction that created the snapshot or
it committed before the snapshot was created
Transaction’s own changes
based on the operation sequence (cmin/cmax) of the transaction
A snapshot is not a physical copy of tuples. Instead, certain visibility rules
determine whether a tuple belongs to a snapshot.
Whether a given tuple is visible to a snapshot is defined by xmin and xmax
fields of the tuple header, i.e., transaction IDs that perform insertion and
deletion. Since xmin–xmax intervals do not intersect, each row is
represented in any snapshot by one of its versions at most.
The exact visibility rules are quite complex, as they take into account a
variety of different scenarios and corner cases. In general, we can describe
them as follows: a tuple is visible in a snapshot if it sees the changes made
by transaction xmin and does not see the changes made by transaction
xmax. In other words, the tuple has already been created and has not been
deleted yet (if deletion occurred).
In turn, transaction changes are visible to the snapshot if it is the transaction
that created the snapshot (it sees its own changes), or if the transaction has
been committed before the snapshot was created.
The visibility rules for a transaction’s own changes are more complex: in
some cases, only part of such changes must be visible. For example, a
cursor that was opened at a particular point in time must not see any
changes that happened later, regardless of the isolation level. For this
purpose, tuple headers contain a special field (cmin and cmax
pseudocolumns) that indicates the sequence number of the operation within
the transaction, and this number is also taken into account.
5
Row Version Visibility
time
snapshot
T3
T2
T1
Let’s take a look at a simple example.
In the figure, the snapshot creation moment is shown in blue. The black lines
show transactions from their start till commit time (aborted transactions are
never taken into account by a snapshot).
In this case, T2 is the only transaction that was committed before the
snapshot creation, so only its changes are visible.
T1 was active at the time of the snapshot creation, so its changes are not
visible. T3 was started after the snapshot creation, so its changes are not
visible either.
7
How to distinguish T1 from T2 after the snapshot has been created?
Row Version Visibility
time
snapshot
we know only
transaction’s current status
at snapshot creation,
not its completion time
T3
T2
T1
The implementation challenge is that we know only a transaction’s start
time, defined by its ID (xid), which can be compared to the xmin or xmax of a
tuple. However, the transaction’s completion time is never recorded. All we
can determine is the current status of transactions.
This means that once T1 was committed, it is impossible to differentiate it
from T2: there is no way to determine that one of them was committed
before the snapshot was created and the other one after.
Since the history of transaction activity is not stored, a snapshot can only
capture the current state. For example, it is impossible to create a snapshot
of the database’s consistent state from five minutes ago, even if all
necessary row versions still exist in heap pages.
8
Snapshot
xmin is ID of the oldest active transaction
xmax is the number following the ID of the last committed transaction
xip_list is a list of active transactions
xmaxxmin
xid
snapshot
T1
T2
T3
changes visibility is
determined by xip_list
changes are
never visible
changes are
always visible
When a snapshot is created, several values are stored in the local memory
of the backend process, so these values define the snapshot:
●
xmin is the snapshot’s lower boundary, which is represented by the ID of
the oldest active transaction.
All transactions with lower IDs, if committed, have their changes visible to
the snapshot; if aborted, their changes are ignored.
●
xmax is the snapshot’s upper boundary, which is represented by the
number following the ID of the last committed transaction. The upper
boundary defines the moment when the snapshot was taken. Note that
the timestamp is defined not by time (as was shown in previous slides),
but by increasing transaction IDs.
All transactions with an ID greater than or equal to xmax were definitely
not committed at the time of the snapshot: they were either active,
aborted, or had not yet started. Consequently, their changes are not
visible.
●
xip_list (xid-in-progress list) is the list of IDs of all active transactions.
This list is used to exclude changes of the transactions that have been
completed but were active at the snapshot creation time.
Use pg_current_snapshot() and other functions to get information about a
snapshot.
10
Virtual Transactions
A read-only transaction does not affect visibility
backend process assigns a virtual ID
virtual ID can be ignored by snapshots
virtual ID is never stored in data pages
permanent xid is assigned upon the first data modification
To consume transaction IDs sparingly, PostgreSQL offers a special
optimization.
If a transaction is read-only, it does not affect row version visibility and its ID
can be ignored by snapshots.
That is why a transaction is at first given a virtual ID (vxid) that consists of an
internal ID of the backend process and a sequential number.
Assigning a virtual ID does not require synchronization between all
processes, so it happens very fast. We will explore another reason for using
virtual IDs in the Freezing lesson of the current module.
This virtual ID must never be written to data pages, as it can become
meaningless upon subsequent access to the page.
Once the transaction starts modifying data, it is immediately assigned a
permanent ID (xid). This ID can appear in data pages within xmin and xmax
fields of row versions.
12
Transaction Horizon
xid
outdated tuples
not needed
to the snapshot
snapshot
xmaxxmin
xip list
The ID of the oldest transaction (the snapshot's xmin) is crucial since it
defines the snapshot's horizon.
In astrophysics, an event horizon is a boundary beyond which an observer
cannot see any events. In our case, the snapshot horizon is a boundary
beyond which a statement using the snapshot cannot see any outdated row
versions.
A snapshot needs an outdated row version only if it was deleted (or
replaced) by a transaction that was in progress. However, beyond that
horizon, it is guaranteed that all such transactions have completed.
13
Transaction Horizon
BEGIN
SELECT
Repeatable Read, Serializable
xid
outdated tuples
not needed
in the transaction
UPDATE
INSERT
permanent xid
snapshot
virtual ID
At different isolation levels, transactions use snapshots differently.
For the Repeatable Read and Serializable levels, a transaction creates a
snapshot upon its first access to the server and uses it until its completion.
The snapshot's left boundary (the beginning of the horizontal segment in the
figure), which defines the transaction horizon, is equal to the ID of the oldest
active transaction. Current transaction is active, so the horizon cannot be
positioned to the right of the current transaction’s ID.
14
SELECT
Transaction Horizon
Read Committed
outdated tuples
not needed
in the transaction
xid
virtual ID
BEGIN
snapshot
In case of the Read Committed isolation level, a transaction can use several
snapshots.
First, the transaction is assigned a virtual ID, and a snapshot is created upon
its first database access.
15
Transaction Horizon
Read Committed
xid
SELECT
BEGIN
virtual ID
The snapshot is released when the query finishes. In our example, since the
transaction’s first statement was read-only, the transaction has not yet been
assigned a permanent xid. Therefore, it will not hold back the horizon until its
next statement begins.
16
Transaction Horizon
Read Committed
outdated tuples
not needed
in the transaction
xid
SELECT
UPDATE
permanent ID
snapshot
BEGIN
virtual ID
When a transaction first modifies data, it is assigned a permanent xid. At that
same moment, a new snapshot is created. As always, this snapshot's
horizon (xmin) is set to the oldest active transaction ID.
As the current transaction is active, the horizon can never be positioned
after this transaction’s ID.
17
Transaction Horizon
Read Committed
outdated tuples
not needed
in the transaction
xid
SELECT
UPDATE
BEGIN
permanent ID
virtual ID
When a statement is completed, its snapshot is released. However, the
transaction continues to hold the horizon at its ID to maintain the ability to
roll back.
If a transaction remains in this state for a long time (idle in transaction)
without executing any commands, the horizon remains in place.
18
Transaction Horizon
Read Committed
xid
outdated tuples
not needed
in the transaction
snapshot
BEGIN
SELECT
UPDATE
INSERT
permanent ID
virtual ID
The next statement uses a new snapshot. The horizon of this snapshot is
defined by considering all other active transactions. The current transaction
itself remains active; therefore, the snapshot’s horizon (xmin) can never be
greater than its own xid.
19
Transaction Horizon
Read Committed
xid
SELECT
UPDATE
INSERT
BEGIN
permanent ID
virtual ID
outdated tuples
not needed
in the transaction
Between statements, without an active snapshot, the horizon shifts towards
the transaction ID. If the transaction has never obtained a permanent ID
(i.e., it has not started modifying data), it will not hold back the horizon at all.
20
Database Horizon
outdated tuples
can be vacuumed
xid
snapshots still require
outdated tuples
T3
T2
T1
UPDATE
permanent ID
of T1
We can define the database horizon as the minimum of all horizons of
transactions and snapshots. When a snapshot is released, the database
horizon shifts forward, towards the horizon of the next active snapshot.
Reverse movement is impossible since the xmin of newly created
snapshots, which represents the minimum ID of an active transaction, can
only shift to the right.
Outdated tuples behind the database horizon will never be required by any
transaction. Such row versions can be safely vacuumed.
The slide shows an example of three transactions with Read Committed
isolation level. A snapshot was created for the UPDATE statement in T1.
When the statement is completed, the snapshot is deleted and no longer
holds the horizon. However, T1 is still active, and therefore the snapshot
horizons of Transactions 2 and 3 cannot move to the right past the ID of T1.
Thus, any active transaction with a permanent ID holds the horizon merely
by the fact of its existence.
This means that outdated tuples in this database cannot be cleaned up. This
holds true even for a long-running transaction that does not interact with the
same data as other transactions, since the database horizon is common for
all transactions.
This is one of the key reasons why you should keep transactions no longer
than absolutely necessary.
21
Database Horizon
xid
T3
T2
T1
UPDATE
idle_in_transaction_session_timeout
permanent ID
of T1
outdated tuples
can be vacuumed
snapshots still require
outdated tuples
If this scenario creates operational problems that cannot be addressed in the
application code, consider using the idle_in_transaction_session_timeout
parameter. It limits the lifetime of an idle transaction. The transaction is
aborted upon reaching this threshold.
23
Task
distribute work among several concurrently running transactions
in such a way that they all see the same data
example: pg_dump --jobs=N
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT ID;
Snapshot Export
BEGIN ISOLATION LEVEL REPEATABLE READ;
...
SELECT pg_export_snapshot();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT ID;
snapshot ID
can be used only
during the lifetime of the
exporting transaction
relevant only
for Repeatable Read
or Serializable
There are situations where several transactions must see the same data
state. Of course, we cannot rely on snapshots being identical just because
transactions were started “at the same time”. Snapshot export-import
mechanism is used for this purpose.
The pg_export_snapshot() function saves snapshot information as a file in
the PGDATA/pg_snapshots directory and returns the snapshot ID. This ID
can be transferred (using means external to the DBMS) to another
transaction.
The exported snapshot exists until the exporting transaction ends. Before
executing the first statement, another transaction can import the snapshot by
running the SET TRANSACTION SNAPSHOT command. Before that, the
isolation level must be set to Repeatable Read or Serializable because
statements use their own snapshots at the Read Committed level.
Snapshot export is used, for example, by the pg_dump utility in parallel
mode or by logical replication to obtain the initial state of tables included in a
subscription (see the DBA3 course for more details).
24
Takeaways
A snapshot contains information necessary to determine
the visibility of row versions
The time of a snapshot creation is determined by the
isolation level
A snapshot determines the transaction horizon, which affects
the ability to clean up outdated versions
25
Practice
1. Reproduce a situation where one transaction still sees a deleted
row, while another no longer does. Examine snapshots of these
transactions, xmin and xmax values of the deleted row. Explain
visibility based on this data.
2. If a query calls a function that contains another query, which
snapshot will be used for the nested query?
Check Read Committed and Repeatable Read isolation levels as
well as volatile and stable function categories.
3. In one transaction, export a snapshot. Then, in another
transaction, modify the data. Import the snapshot and check that
it still shows the unmodified data.
2. Create a function using the template below, provided that a table t has
been created:
CREATE FUNCTION test() RETURNS bigint
VOLATILE -- or STABLE
LANGUAGE sql
BEGIN ATOMIC
SELECT count(*) FROM t;
END;
You may also find the pg_sleep(n) function useful to pause execution for n
seconds.
