Timestamp-Based Snapshots

The Timestamp-Based Snapshots feature of PG Extended allows reading data ina consistent manner via a user-specified timestamp rather than the usualMVCC snapshot. This can be used to access data on different BDR nodesat a common point-in-time; for example, as a way to compare data onmultiple nodes for data quality checking. At this time, this feature doesnot work with write transactions.

!!! Note * This feature is currently only available on EDB Postgres Extended and EDB Postgres Advanced.

The use of timestamp-based snapshots are enabled via the snapshot_timestampparameter; this accepts either a timestamp value ora special value, ‘current’, which represents the current timestamp (now). Ifsnapshot_timestamp is set, queries will use that timestamp to determinevisibility of rows, rather than the usual MVCC semantics.

For example, the following query will return state of the customers table at2018-12-08 02:28:30 GMT:

SET snapshot_timestamp = '2018-12-08 02:28:30 GMT';
SELECT count(*) FROM customers;

In plain PG Extended, this only works with future timestamps or the abovementioned special ‘current’ value, so it cannot be used for historical queries (though that is on the longer-term roadmap).

BDR works with and improves on that feature in a multi-node environment. Firstly,BDR will make sure that all connections to other nodes replicated anyoutstanding data that were added to the database before the specifiedtimestamp, so that the timestamp-based snapshot is consistent across the wholemulti-master group. Secondly, BDR adds an additional parameter calledbdr.timestamp_snapshot_keep. This specifies a window of time during whichqueries can be executed against the recent history on that node.

You can specify any interval, but be aware that VACUUM (including autovacuum)will not clean dead rows that are newer than up to twice the specifiedinterval. This also means that transaction ids will not be freed for the sameamount of time. As a result, using this can leave more bloat in user tables.Initially, we recommend 10 seconds as a typical setting, though you may wishto change that as needed.

Note that once the query has been accepted for execution, the query may runfor longer than bdr.timestamp_snapshot_keep without problem, just as normal.

Also please note that info about how far the snapshots were kept does notsurvive server restart, so the oldest usable timestamp for the timestamp-basedsnapshot is the time of last restart of the PostgreSQL instance.

One can combine the use of bdr.timestamp_snapshot_keep with thepostgres_fdw extension to get a consistent read across multiple nodes in aBDR group. This can be used to run parallel queries across nodes, when used inconjunction with foreign tables.

There are no limits on the number of nodes in a multi-node query when using thisfeature.

Use of timestamp-based snapshots does not increase inter-node traffic orbandwidth. Only the timestamp value is passed in addition to query data.