pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
PG19: guidance on temporal tables use for auditable link entities 2+ messages / 2 participants [nested] [flat]
* PG19: guidance on temporal tables use for auditable link entities @ 2026-07-27 20:21 pgmis@posteo.net 0 siblings, 1 reply; 2+ messages in thread From: pgmis@posteo.net @ 2026-07-27 20:21 UTC (permalink / raw) To: 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PG19: guidance on temporal tables use for auditable link entities @ 2026-07-28 03:18 Laurenz Albe <laurenz.albe@cybertec.at> parent: pgmis@posteo.net 0 siblings, 0 replies; 2+ messages in thread From: Laurenz Albe @ 2026-07-28 03:18 UTC (permalink / raw) To: pgmis@posteo.net; pgsql-general On Mon, 2026-07-27 at 20:21 +0000, pgmis@posteo.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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-07-28 03:18 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-07-27 20:21 PG19: guidance on temporal tables use for auditable link entities pgmis@posteo.net 2026-07-28 03:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox