Why fake time travel?

PostgreSQL has no native system-versioned temporal tables, but the desire for them is understandable. When a stray UPDATE reports 1000 rows affected instead of 10, it would be handy to inspect the table as it existed an hour earlier - without restoring from a cold backup. The usual workarounds exist: insert-only data modeling can preserve history at the application level, and other database systems offer AS OF syntax or equivalent features. PostgreSQL has some community extensions that attempt this, but none are built in.

The workaround described here is different: combine two existing PostgreSQL features - dblink and replication apply delay - to build something that behaves like a temporal table.

The building blocks

PostgreSQL has supported recovery_min_apply_delay for some time. This parameter tells a standby node to wait a specified period before applying changes received from the primary. The parameter is set in human-readable units, and the actual delay can exceed the configured minimum if the standby is busy.

The cost is disk space: WAL must be retained until it is no longer needed for the delayed replay. For busy databases, a delay of days can become expensive. Starting with hours is safer. Alternatives for longer lag include archive recovery or periodic delta restores with a tool like pgBackRest.

The second ingredient is dblink, which allows direct querying of remote PostgreSQL instances. No setup is required on either end - just a connection string and a declared result type. A minimal dblink call looks like:

1

2

3

4

5

6

7

8

CREATE EXTENSION dblink;

/* opening a remote connection to the same instance I’m on */

SELECT * from dblink('host=localhost port=5432', 'select 1') as t(x int);

x

───

1

(1 row)

Combining the two is fairly straightforward. Point a dblink query at a standby that is intentionally lagging, and you can query the table as it appeared at the lag interval. The reverse direction - querying further back in time - is not possible without rebuilding the standby at an older point. But running multiple standbys at different lag intervals gives you several historical snapshots simultaneously.

Test setup

The demonstration used two 4 vCPU, 8 GB RAM nodes in the same availability zone, running PostgreSQL v13.2. The primary was at 10.110.0.4 and the standby at 10.110.0.5. Both had work_mem=512MB, shared_buffers=2GB, and trust authentication between them. The test data was the standard pgbench schema at scale factor 100 - roughly 1.3 GB and 10 million rows in the main table. The initial streaming replication setup is a standard process and is not covered here.

Before enabling a delay, verify that dblink works from the primary to the standby:

1

2

3

4

# on the primary node, assuming you are logged in as a “postgres” user

# let’s initialize 10 million test bank accounts

pgbench -i -s 100

psql -c  “CREATE EXTENSION dblink”

That returns successfully, so the basic wire connection checks out. Now enable a delay on the standby. The parameter can be changed dynamically:

1

2

3

4

5

6

7

postgres=# ALTER SYSTEM SET recovery_min_apply_delay TO '1h';

ALTER SYSTEM

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

t

(1 row)

After the delay is active, we can generate some changes on the primary and watch them not appear on the standby:

1

2

3

4

5

6

7

8

9

10

# let’s run “simple” version of the built-in TPC-B test for the duration of 1 second on the

primary

pgbench -T 1 -N -n

# and check the sum of all account balances

psql -c 'SELECT sum(abalance) from pgbench_accounts'

  sum  

--------

110304

(1 row)

The run on the standby confirms the delay is working as intended:

1

2

3

4

5

psql -c 'SELECT sum(abalance) from pgbench_accounts'

sum

-----

   0

(1 row)

Diffing the two states

A natural use case is detecting which rows changed in the last hour. Logins to a historical state, the query is a FULL OUTER JOIN between the current table on the primary and the delayed version via dblink. The full join is necessary to catch rows that were inserted or deleted, not just updated. For insert/update detection only, a LEFT JOIN would be faster.

To make the test meaningful, the demo changed roughly 100,000 rows (about 1% of the total) on the primary:

1

2

3

4

5

6

7

8

9

10

11

# let’s also enable asynchronous commit here to speed up the test, we’re not really

# interested in testing nor waiting on the disk

PGOPTIONS=“-c synchronous_commit=off” pgbench -t 100000 -N -n -M prepared

# let’s check how many rows actually have been changed - some rows were changed many times

# due to random selection

psql -c 'SELECT count(*) FROM pgbench_accounts WHERE abalance <> 0'

count

-------

99201

(1 row)

The diff query, in its simplest form:

1

2

SELECT * FROM $current c FULL OUTER JOIN $lag l using (id) where c.$datacol IS DISTINCT FROM

l.$datacol OR c.id IS NULL OR l.id IS NULL;

With all the data cached and using EXPLAIN ANALYZE with timing disabled to minimize overhead, this took about 20 seconds on 10 million rows on each side.

How does local compare?

To give the number context, the same data was pulled into a local table on the primary, and the same diff was run against two local tables:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

CREATE UNLOGGED TABLE pgbench_accounts_lag_local (LIKE pgbench_accounts INCLUDING ALL);

INSERT INTO pgbench_accounts_lag_local SELECT * FROM dblink('host=10.110.0.5', 'select aid,

bid, abalance, filler from pgbench_accounts') AS x(aid int, bid int, abalance int,

filler text);

VACUUM ANALYZE pgbench_accounts_lag_local;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT* FROM pgbench_accounts c FULL OUTER JOIN

pgbench_accounts_lag_local l using (aid) where c.abalance IS DISTINCT FROM l.abalance OR c.aid

IS NULL OR l.aid IS NULL;

                                        QUERY PLAN                                        

--------------------------------------------------------------------------------------------

Hash Full Join (actual rows=99201 loops=1)

   Hash Cond: (c.aid = l.aid)

   Filter: ((c.abalance IS DISTINCT FROM l.abalance) OR (c.aid IS NULL) OR (l.aid IS NULL))

   Rows Removed by Filter: 9900799

   ->  Seq Scan on pgbench_accounts c (actual rows=10000000 loops=1)

   ->  Hash (actual rows=10000000 loops=1)

         Buckets: 16777216  Batches: 1  Memory Usage: 1390838kB

         ->  Seq Scan on pgbench_accounts_lag_local l (actual rows=10000000 loops=1)

Planning Time: 0.130 ms

Execution Time: 11233.989 ms

(10 rows)

That completed in roughly 11 seconds after warm-up. So the remote access does add meaningful overhead, but not by an order of magnitude. For a rare investigative query, 20 seconds is perfectly acceptable.

Practical takeaways

Durability has real costs. A replica holding a delay of two hours stores all the changes from that window. Still, the approach works remarkably well for a zero-license-cost solution. It does not require any special extensions or schema changes, and it can catch accidental mass updates for a period without paying for backup restore time.

The test scenario was intentionally heavy - 1% of a large table is a lot of churn for one hour. In most realistic cases, far fewer rows change, and indexes would allow far more targeted diff queries. Performance for very large datasets could become a concern, but not one that makes the approach unusable.

A natural future improvement would be built-in support for this pattern in postgres_fdw, but as of v13 there is no such integration. Attempts to use postgres_fdw for remote queries in this context run into recursive query plans and connection exhaustion rather than a working solution.