Infrastructure at your Service

One of the features which is currently missing in PostgreSQL is Temporal Tables. Other database systems have that since a long time and many people want to have in PostgreSQL as well. If you don’t know what it is, here is a short overview. Basically you can ask for a row as it was at a specific point in time. The patch which implements this, is currently in status “Ready for Committer”, which does not guarantee that it actually will be committed, but it should be in shape to test the functionality that comes with it.

Having applied the patch and re-compiled PostgreSQL the feature can be tested. The most easy way to enable the feature for a table is this:

postgres=# create table t1 ( a int primary key generated always as identity
                           , b text ) 
                           with system versioning;
CREATE TABLE
postgres=# \d t1
                                      Table "public.t1"
  Column   |           Type           | Collation | Nullable |            Default            
-----------+--------------------------+-----------+----------+-------------------------------
 a         | integer                  |           | not null | generated always as identity
 b         | text                     |           |          | 
 starttime | timestamp with time zone |           | not null | generated always as row start
 endtime   | timestamp with time zone |           | not null | generated always as row end
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a, endtime)

Two additional columns have been added automatically, starttime and endtime. If you don’t like the column names you can also do it like this:

postgres=# create table t2 ( a int primary key generated always as identity
                           , b text
                           , mystart timestamptz generated always as row start
                           , myend timestamptz generated always as row end );
CREATE TABLE
postgres=# \d t2
                                     Table "public.t2"
 Column  |           Type           | Collation | Nullable |            Default            
---------+--------------------------+-----------+----------+-------------------------------
 a       | integer                  |           | not null | generated always as identity
 b       | text                     |           |          | 
 mystart | timestamp with time zone |           | not null | generated always as row start
 myend   | timestamp with time zone |           | not null | generated always as row end
Indexes:
    "t2_pkey" PRIMARY KEY, btree (a, myend)

Note that the two columns need to be defined as timestamptz:

postgres=# create table t3 ( a int primary key generated always as identity, b text, mystart date generated always as row start, myend date generated always as row end );
ERROR:  data type of row start time must be timestamptz

To see the feature in action lets add some data, fire an update and have a look at the history:

postgres=# insert into t1 (b) select md5(a::text) from generate_series(1,10) a;
INSERT 0 10
postgres=# update t1 set b = random()::text;
UPDATE 10
postgres=# select * from t1 for system_time from '-infinity' to 'infinity' order by a;
 a  |                b                 |           starttime           |            endtime            
----+----------------------------------+-------------------------------+-------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  1 | 0.42111793538855835              | 2021-08-24 16:16:55.417076+02 | infinity
  2 | c81e728d9d4c2f636f067f89cc14862c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  2 | 0.5479128803753532               | 2021-08-24 16:16:55.417076+02 | infinity
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  3 | 0.5512468293024142               | 2021-08-24 16:16:55.417076+02 | infinity
  4 | a87ff679a2f3e71d9181a67b7542122c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  4 | 0.4112741522472554               | 2021-08-24 16:16:55.417076+02 | infinity
  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  5 | 0.46017420469036807              | 2021-08-24 16:16:55.417076+02 | infinity
  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  6 | 0.3495216613664702               | 2021-08-24 16:16:55.417076+02 | infinity
  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  7 | 0.2657576876373895               | 2021-08-24 16:16:55.417076+02 | infinity
  8 | c9f0f895fb98ab9159f51fd0297e236d | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  8 | 0.9808748465536858               | 2021-08-24 16:16:55.417076+02 | infinity
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  9 | 0.4533070845652887               | 2021-08-24 16:16:55.417076+02 | infinity
 10 | d3d9446802a44259755d38e6d163e820 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
 10 | 0.20914767879762408              | 2021-08-24 16:16:55.417076+02 | infinity
(20 rows)

The rows with “endtime = infinity” are the current ones, the ones with an actual endtime are historical versions. Doing the almost same thing again makes it more clear:

postgres=# update t1 set b = 'xxxxx';
UPDATE 10
postgres=# select * from t1 for system_time from '-infinity' to 'infinity' order by a, starttime;
 a  |                b                 |           starttime           |            endtime            
----+----------------------------------+-------------------------------+-------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  1 | 0.42111793538855835              | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  1 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  2 | c81e728d9d4c2f636f067f89cc14862c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  2 | 0.5479128803753532               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  2 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  3 | 0.5512468293024142               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  3 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  4 | a87ff679a2f3e71d9181a67b7542122c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  4 | 0.4112741522472554               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  4 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  5 | 0.46017420469036807              | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  5 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  6 | 0.3495216613664702               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  6 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  7 | 0.2657576876373895               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  7 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  8 | c9f0f895fb98ab9159f51fd0297e236d | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  8 | 0.9808748465536858               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  8 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  9 | 0.4533070845652887               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  9 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
 10 | d3d9446802a44259755d38e6d163e820 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
 10 | 0.20914767879762408              | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
 10 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity
(30 rows)

Without specifying any time frame you get the current version of rows, of course:

postgres=# select * from t1 order by a;
 a  |   b   |           starttime           | endtime  
----+-------+-------------------------------+----------
  1 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  2 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  3 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  4 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  5 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  6 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  7 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  8 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
  9 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity
 10 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity

Asking for a specific point in time works as well:

postgres=# select * from t1 for system_time as of '2021-08-24 16:16:25.648976+02'::timestamptz;
 a  |                b                 |           starttime           |            endtime            
----+----------------------------------+-------------------------------+-------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  2 | c81e728d9d4c2f636f067f89cc14862c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  4 | a87ff679a2f3e71d9181a67b7542122c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  8 | c9f0f895fb98ab9159f51fd0297e236d | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02
 10 | d3d9446802a44259755d38e6d163e820 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02

… or asking like this:

postgres=# select * from t1 for system_time between '2021-08-24 16:16:55.417076+02'::timestamptz and '2021-08-24 16:58:24.799322+02'::timestamptz;
 a  |          b          |           starttime           |            endtime            
----+---------------------+-------------------------------+-------------------------------
  1 | 0.42111793538855835 | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  1 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  2 | 0.5479128803753532  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  2 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  3 | 0.5512468293024142  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  3 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  4 | 0.4112741522472554  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  4 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  5 | 0.46017420469036807 | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  5 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  6 | 0.3495216613664702  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  6 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  7 | 0.2657576876373895  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  7 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  8 | 0.9808748465536858  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  8 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
  9 | 0.4533070845652887  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
  9 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity
 10 | 0.20914767879762408 | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02
 10 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity

Really looks promising. Thanks to all involved.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure