MIME-Version: 1.0
Date: Mon, 27 Jul 2026 20:21:55 +0000
From: pgmis@posteo.net
To: pgsql-general@postgresql.org
Subject: PG19: guidance on temporal tables use for auditable link entities
Message-ID: <79fa6dc9d70d2d62331f4ec11f4dbac7@posteo.net>
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/79fa6dc9d70d2d62331f4ec11f4dbac7%40posteo.net>
Precedence: bulk

Hello,

PG19 comes with shiny new temporal tables feature which i would like to 
explore - and i would like to ask for some guidance.
It is about modeling link entities which need to be auditable.

In checking options what fits best I've come to two options:
1. having the table as insert only with a surrogate key column, 
created_at/by, deleted_at/by columns and possibly a deleted flag (to 
move it between partitions); delete would be a soft-delete updating a 
flag and two audit columns; relinking would mean a new insert and some 
checks would have to be enforced so only one record can exist with an 
open interval (for a combination of linked entities of course)
2. using temporal table where deletion is just an update to close the 
range

Using temporal tables would mean replacing two created_at/deleted_at 
columns with one range column valid_at.

Since this is a range type, i assume simple btree would be maybe a 
problem? In any case could someone maybe give some more insight into the 
following questions:
- how will indexing work in the case of the tsrange column in PG19? is 
there any change given a range type being shown as part of the PK?
- since temporal tables are now an advertised (and rather cool) feature, 
will btree_gist become part of core? there has been some "fix inet mess" 
thread reagarding this, but im not really sure what the outcome is.
- will using only btree on a primary key composite key have significant 
performance impact when searching for active rows?
- would temporal tables be even be recommended for usage i mentioned 
above or i should stick with option 1)?

Thank you,
Miroslav.