Re: PG19: guidance on temporal tables use for auditable link entities

On Mon, 2026-07-27 at 20:21 +0000, pgmis(at)posteo(dot)net wrote:
> 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.

Let's use an example as a basis for the discussion:

CREATE TABLE reference (
a_id bigint,
b_id bigint,
valid tstzrange NOT NULL,
PRIMARY KEY (a_id, b_id, valid WITHOUT OVERLAPS)
);

> 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?

The primary key index will be a GiST index.

The index will be the same index as an index created for an exclusion
constraint:

EXCLUDE USING gist (a_id WITH =, b_id WITH =, valid WITH &&)

with the small difference that the index is marked as a unique and primary
key index.

> - 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.

"inet" is a different, unrelated affair.

You still have to create "btree_gist" to be able to create a temporal
primary key. I don't think that btree_gist will move into core.
I don't see a real need for that, and it might make upgrades more
challenging.

> - will using only btree on a primary key composite key have significant
> performance impact when searching for active rows?

As I wrote above, the primary key won't be a B-tree index.

> - would temporal tables be even be recommended for usage i mentioned
> above or i should stick with option 1)?

That depends on how exactly you would want to implement the alternative
and what queries you want to run against the table.
B-tree indexes are faster to modify than GiST indexes.
On the other hand, you won't be able to get the same guarantees that you
can get with an exclusion constraint/temporal primary key.

Yours,
Laurenz Albe