| From: | pgmis(at)posteo(dot)net |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | PG19: guidance on temporal tables use for auditable link entities |
| Date: | 2026-07-27 20:21:55 |
| Message-ID: | 79fa6dc9d70d2d62331f4ec11f4dbac7@posteo.net |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurenz Albe | 2026-07-28 03:18:37 | Re: PG19: guidance on temporal tables use for auditable link entities |
| Previous Message | Adrian Klaver | 2026-07-27 20:04:19 | Re: CVE-2026-11586 |