4 Snapshots

4.1 What is a Snapshot?

A data page can contain several versions of the same row, although each transaction must see only one of them at the most. Together, visible versions of all the different rows constitute a snapshot. A snapshot includes only the current data committed by the time it was taken, thus providing a consistent (in the ACID sense) view of the data for this particular moment. (p. 44)

To ensure isolation, each transaction uses its own snapshot. It means that different transactions can see different snapshots taken at different points in time, which are nevertheless consistent.

At the Read Committed isolation level, a snapshot is taken at the beginning of each statement, and it remains active only for the duration of this statement.

At the Repeatable Read and Serializable levels, a snapshot is taken at the beginning of the first statement of a transaction, and it remains active until the whole transaction is complete.

4.2 Row Version Visibility

A snapshot is not a physical copy of all the required tuples. Instead, it is defined by several numbers, while tuple visibility is determined by certain rules.

Tuple visibility is defined by xmin and xmax fields of the tuple header (that is, IDs of transactions that perform insertion and deletion) and the corresponding hint bits. Since xmin–xmax intervals do not intersect, each row is represented in any snapshot by only one of its versions.

The exact visibility rules are quite complex,¹ as they take into account a variety of different scenarios and corner cases. Very roughly, we can describe them as follows: a tuple is visible in a snapshot that includes xmin transaction changes but excludes xmax transaction changes (in other words, the tuple has already appeared and has not been deleted yet).

In their turn, transaction changes are visible in a snapshot if this transaction was committed before the snapshot creation. As an exception, transactions can see their own uncommitted changes. If a transaction is aborted, its changes will not be visible in any snapshot.

Let’s take a look at a simple example. In this illustration line segments represent transactions (from their start time till commit time):

Here visibility rules are applied to transactions as follows:

• Transaction 2 was committed before the snapshot creation, so its changes are visible.

• Transaction 1 was active at the time of the snapshot creation, so its changes are not visible.

• Transaction 3 was started after the snapshot creation, so its changes are not visible either (it makes no difference whether this transaction is completed or not).

4.3 Snapshot Structure

Unfortunately, the previous illustration has nothing to do with the way PostgreSQL actually sees this picture.² The problem is that the system does not know when transactions got committed. It is only known when they were started (this moment is defined by the transaction ID), while their completion is not registered anywhere. (default: off)

It is only the current status of a transaction that we can learn. This information is available in the server’s shared memory: the ProcArray structure contains the list of all the active sessions and their transactions. Once a transaction is complete, it is impossible to find out whether it was active at the time of the snapshot creation. (p. 164)

So to create a snapshot, it is not enough to register the moment when it was taken: it is also necessary to collect the status of all the transactions at that moment. Otherwise, later it will be impossible to understand which tuples must be visible in the snapshot, and which must be excluded.

Take a look at the information available to the system when the snapshot was taken and some time afterwards (the white circle denotes an active transaction, whereas the black circles stand for completed ones):

Suppose we did not know that at the time the snapshot was taken the first transaction was still being executed and the third transaction had not started yet. Then it would seem that they were just like the second transaction (which was committed at that time), and it would be impossible to filter them out.

For this reason, PostgreSQL cannot create a snapshot that shows a consistent state of data at some arbitrary point in the past, even if all the required tuples are present in heap pages. Consequently, it is impossible to implement retrospective queries (which are sometimes also called temporal or flashback queries).

Thus, a snapshot consists of several values saved at the time of its creation:⁵

xmin is the snapshot’s lower boundary, which is represented by the ID of the oldest active transaction.

All the transactions with smaller IDs are either committed (so their changes are included into the snapshot) or aborted (so their changes are ignored). (p. 123)

xmax is the snapshot’s upper boundary, which is represented by the value that exceeds the ID of the latest committed transaction by one. The upper boundary defines the moment when the snapshot was taken.

All the transactions whose IDs are equal to or greater than xmax are either still running or do not exist, so their changes cannot be visible.

xip_list is the list of IDs of all the active transactions except for virtual ones, which do not affect visibility in any way. (p. 75)

Snapshots also include several other parameters, but we will ignore them for now.

In a graphical form, a snapshot can be represented as a rectangle that comprises transactions from xmin to xmax:

To understand how visibility rules are defined by the snapshot, we are going to reproduce the above scenario on the accounts table.

=> TRUNCATE TABLE accounts;

The first transaction inserts the first row into the table and remains open:

=> BEGIN;
=> INSERT INTO accounts VALUES (1, 'alice', 1000.00);
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                790
(1 row)

The second transaction inserts the second row and commits this change immediately:

=> BEGIN;
=> INSERT INTO accounts VALUES (2, 'bob', 100.00);
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                791
(1 row)
=> COMMIT;

At this point, let’s create a new snapshot in another session. We could simply run any query for this purpose, but we will use a special function to take a look at this snapshot right away:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> --  txid_current_snapshot() before v.13
SELECT pg_current_snapshot();
 pg_current_snapshot
---------------------
 790:792:790
(1 row)

This function displays the following snapshot components, separated by colons: xmin, xmax, and xip_list (the list of active transactions; in this particular case it consists of a single item).

Once the snapshot is taken, commit the first transaction:

=> COMMIT;

The third transaction is started after the snapshot creation. It modifies the second row, so a new tuple appears:

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100 WHERE id = 2;
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                792
(1 row)
=> COMMIT;

Our snapshot sees only one tuple:

=> SELECT ctid, * FROM accounts;
 ctid  | id | client | amount
-------+----+--------+--------
 (0,2) |  2 | bob    | 100.00
(1 row)

But the table contains three of them:

=> SELECT * FROM heap_page('accounts',0);
 ctid  | state  | xmin  | xmax
-------+--------+-------+-------
 (0,1) | normal | 790 c | 0 a
 (0,2) | normal | 791 c | 792 c
 (0,3) | normal | 792 c | 0 a
(3 rows)

So how does PostgreSQL choose which versions to show? By the above rules, changes are included into a snapshot only if they are made by committed transactions that satisfy the following criteria:

• If xid < xmin, changes are shown unconditionally (like in the case of the transaction that created the accounts table).

• If xmin ⩽ xid < xmax, changes are shown only if the corresponding transaction IDs are not in xip_list.

The first row (0,1) is invisible because it is inserted by a transaction that appears in xip_list (even though this transaction falls into the snapshot range).

The latest version of the second row (0,3) is invisible because the corresponding transaction ID is above the upper boundary of the snapshot.

But the first version of the second row (0,2) is visible: row insertion was performed by a transaction that falls into the snapshot range and does not appear in xip_list (the insertion is visible), while row deletion was performed by a transaction whose ID is above the upper boundary of the snapshot (the deletion is invisible).

=> COMMIT;

4.4 Visibility of Transactions’ Own Changes

Things get a bit more complicated when it comes to defining visibility rules for transactions’own changes: 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.

To address such situations, tuple headers provide a special field (displayed as cmin and cmax pseudocolumns) that shows the sequence number of the operation within the transaction. The cmin column identifies insertion, while cmax is used for deletion operations. To save space, these values are stored in a single field of the tuple header rather than in two different ones. It is assumed that a particular row almost never gets both inserted and deleted within a single transaction. (If it does happen, PostgreSQL writes a special combo identifier into this field, and the actual cmin and cmax values are stored by the backend in this case.⁶)

As an illustration, let’s start a transaction and insert a row into the table:

=> BEGIN;
=> INSERT INTO accounts VALUES (3, 'charlie', 100.00);
=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                793
(1 row)

Open a cursor to run the query that returns the number of rows in this table:

=> DECLARE c CURSOR FOR SELECT count(*) FROM accounts;

Insert one more row:

=> INSERT INTO accounts VALUES (4, 'charlie', 200.00);

Now extend the output by another column to display the cmin value for the rows inserted by our transaction (it makes no sense for other rows):

=> SELECT xmin, CASE WHEN xmin = 793 THEN cmin END cmin, *
FROM accounts;
 xmin | cmin | id | client  | amount
------+------+----+---------+---------
  790 |      |  1 | alice   | 1000.00
  792 |      |  2 | bob     |  200.00
  793 |    0 |  3 | charlie |  100.00
  793 |    1 |  4 | charlie |  200.00
(4 rows)

The cursor query gets only three rows; the row inserted when the cursor was already open does not make it into the snapshot because the cmin < 1 condition is not satisfied:

=> FETCH c;
 count
-------
     3
(1 row)

Naturally, this cmin number is also stored in the snapshot, but it is impossible to display it using any SQL means.

4.5 Transaction Horizon

As mentioned earlier, the lower boundary of the snapshot is represented by xmin, which is the ID of the oldest transaction that was active at the moment of the snapshot creation. This value is very important because it defines the horizon of the transaction that uses this snapshot.

If a transaction has no active snapshot (for example, at the Read Committed isolation level between statement execution), its horizon is defined by its own ID if it is assigned.

All the transactions that are beyond the horizon (those with xid < xmin) are guranteed to be committed. It means that a transaction can see only the current row versions beyond its horizon.

PostgreSQL tracks the current horizons of all its processes; transactions can see their own horizons in the pg_stat_activity table:

=> BEGIN;
=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          793
(1 row)

Virtual transactions have no real IDs, but they still use snapshots just like regular transactions, so they have their own horizons. The only exception is virtual transactions without an active snapshot: the concept of the horizon makes no sense for them, and they are fully “transparent” to the system when it comes to snapshots and visibility (even though pg_stat_activity.backend_xmin may still contain an xmin of an old snapshot).

We can also define the database horizon in a similar manner. For this purpose, we should take the horizons of all the transactions in this database and select the most remote one, which has the oldest xmin.⁷ Beyond this horizon, outdated heap tuples will never be visible to any transaction in this database. Such tuples can be safely cleaned up by vacuum—this is exactly why the concept of the horizon is so important from a practical standpoint.

Let’s draw some conclusions:

• If a transaction (no matter whether it is real or virtual) at the Repeatable Read or Serializable isolation level is running for a long time, it thereby holds the database horizon and defers vacuuming.

• A real transaction at the Read Committed isolation level holds the database horizon in the same way, even if it is not executing any operators (being in the “idle in transaction” state).

• A virtual transaction at the Read Committed isolation level holds the horizon only while executing operators.

There is only one horizon for the whole database, so if it is being held by a transaction, it is impossible to vacuum any data within this horizon—even if this data has not been accessed by this transaction.

Let’s get back to our current experiment. The active transaction of the first session still holds the database horizon; we can see it by incrementing the transaction counter:

=> SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
                794
(1 row)

=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          793
(1 row)

And only when this transaction is complete, the horizon moves forward, and outdated tuples can be vacuumed:

=> COMMIT;
=> SELECT backend_xmin FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 backend_xmin
--------------
          795
(1 row)

In a perfect world, you should avoid combining long transactions with frequent updates (that spawn new row versions), as it will lead to table and index bloating. (p. 141)

4.6 System Catalog Snapshots

Although the system catalog consists of regular tables, they cannot be accessed via a snapshot used by a transaction or an operator. The snapshot must be “fresh” enough to include all the latest changes, otherwise transactions could see outdated definitions of table columns or miss newly added integrity constraints.

Here is a simple example:

=> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
=> SELECT 1; -- a snapshot for the transaction is taken
    => ALTER TABLE accounts
      ALTER amount SET NOT NULL;

=> INSERT INTO accounts(client, amount)
  VALUES ('alice', NULL);
ERROR:  null value in column "amount" of relation "accounts"
violates not-null constraint
DETAIL:  Failing row contains (1, alice, null).

=> ROLLBACK;

The integrity constraint that appeared after the snapshot creation was visible to the INSERT command. It may seem that such behavior breaks isolation, but if the inserting transaction had managed to reach the accounts table before the ALTER TABLE command, the latter would have been blocked until this transaction completed. (p. 204)

In general, the server behaves as if a separate snapshot is created for each system catalog query. But the implementation is, of course, much more complex⁸ since frequent snapshot creation would negatively affect performance; besides, many system catalog objects get cached, and it must also be taken into account.

4.7 Exporting Snapshots

In some situations, concurrent transactions must see the same snapshot by all means. For example, if the pg_dump utility is run in the parallel mode, all its processes must see the same database state to produce a consistent backup.

We cannot assume that snapshots will be identical simply because transactions were started “simultaneously.” To ensure that all the transactions see the same data, we must employ the snapshot export mechanism.

The pg_export_snapshot function returns a snapshot ID, which can be passed to another transaction (outside of the database system):

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT count(*) FROM accounts;
 count
-------
     4
(1 row)
=> SELECT pg_export_snapshot();
 pg_export_snapshot
---------------------
 00000004-0000006E-1
(1 row)

Before executing the first statement, the other transaction can import the snapshot by running the SET TRANSACTION SNAPSHOT command. The isolation level must be set to Repeatable Read or Serializable because operators use their own snapshots at the Read Committed level:

=> DELETE FROM accounts;
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SET TRANSACTION SNAPSHOT '00000004-0000006E-1';

Now the second transaction is going to use the snapshot of the first transaction, and consequently, it will see four rows (instead of zero):

=> SELECT count(*) FROM accounts;
 count
-------
     4
(1 row)

Clearly, the second transaction will not see any changes made by the first transaction after the snapshot export (and vice versa): regular visibility rules still apply.

The exported snapshot’s lifetime is the same as that of the exporting transaction.

=> COMMIT;

=> COMMIT;