Thread: Question About Native Support for SQL:2011 Temporal Tables in PostgreSQL
Hi Postgres Community,
I wanted to ask about support for temporal tables as defined in the SQL:2011 standard. Right now, I know that we can get similar functionality using extensions like
pg_temporal, which has been really useful. But for those of us running PostgreSQL on managed platforms like AWS RDS (where adding custom extensions isn’t an option), it creates a bit of a challenge. Here are a few questions I’d love your thoughts on:
- Are there any plans or discussions about adding native support for SQL:2011 temporal tables, so we don’t need extensions?
- What are the main reasons behind needing an extension for this feature? Is it tough to build directly into PostgreSQL’s core?
- For those who can’t use extensions (like on AWS RDS), is it practical to build temporal table features using only RAW SQL. I'm aware of the Nearform trigger solution but I'd really love the syntactical sugar "with SYSTEM VERISON" gives? If so, are there any best practices or tips for recreating some of that extension-like functionality?
Having native temporal table support would be a huge help for users needing built-in tools for audits, historical tracking, and meeting data compliance needs. I think adding these features natively could make PostgreSQL even more powerful and flexible for different use cases.
I've been looking into https://github.com/xocolatl/periods but I can't tell if its an extension or part of the regular deployment.
Thanks so much for taking the time to read this and share your insights. Looking forward to hearing what you all think!
Dave
Re: Question About Native Support for SQL:2011 Temporal Tables in PostgreSQL
From
Greg Sabino Mullane
Date:
On Mon, Nov 11, 2024 at 6:23 AM David Lynam <davidlynam1@hotmail.co.uk> wrote:
No concrete plans I've heard of (but see below). For the record, "so we don't need extensions" is not a winning argument. Postgres is designed to be extensible.
> What are the main reasons behind needing an extension for this feature? Is it tough to build directly into PostgreSQL’s core?
By default, Postgres focuses on safely storing and retrieving your data in a relational database system. Having to use extensions to go beyond this in various ways is considered a feature, not a limitation. Things do eventually make it into core, but there are a number of prereqs that need to happen first. Being "tough" technically is only part of the equation. Other things, off the top of my head:
Are there any plans or discussions about adding native support for SQL:2011 temporal tables, so we don’t need extensions?
No concrete plans I've heard of (but see below). For the record, "so we don't need extensions" is not a winning argument. Postgres is designed to be extensible.
> What are the main reasons behind needing an extension for this feature? Is it tough to build directly into PostgreSQL’s core?
By default, Postgres focuses on safely storing and retrieving your data in a relational database system. Having to use extensions to go beyond this in various ways is considered a feature, not a limitation. Things do eventually make it into core, but there are a number of prereqs that need to happen first. Being "tough" technically is only part of the equation. Other things, off the top of my head:
* is it something many people will benefit from?
* is it something that will not impact the people not using it?
* is it something that will not impact the people not using it?
* how will it interact with other parts of the system?
* is it worth the added lines of code, complexity, and maintenance costs?
* is it already handled quite well as an extension?
* are there resources (i.e. people) available to champion it and maintain it in perpetuity?
I cannot speak to Nearform et. al., but for the record here, AWS RDS does support extensions - and a lot of them. No, you cannot install your own custom extensions, but that's the tradeoff for using a managed service. Since you are paying them money, however, you can certainly ask if they will make particular extensions available.
* is it worth the added lines of code, complexity, and maintenance costs?
* is it already handled quite well as an extension?
* are there resources (i.e. people) available to champion it and maintain it in perpetuity?
For those who can’t use extensions (like on AWS RDS), is it practical to build temporal table features using only RAW SQL. I'm aware of the Nearform trigger solution but I'd really love the syntactical sugar "with SYSTEM VERISON" gives? If so, are there any best practices or tips for recreating some of that extension-like functionality?
I cannot speak to Nearform et. al., but for the record here, AWS RDS does support extensions - and a lot of them. No, you cannot install your own custom extensions, but that's the tradeoff for using a managed service. Since you are paying them money, however, you can certainly ask if they will make particular extensions available.
Having native temporal table support would be a huge help for users needing built-in tools for audits, historical tracking, and meeting data compliance needs. I think adding these features natively could make PostgreSQL even more powerful and flexible for different use cases.
It's certainly been discussed over the years. Nobody denies it can be useful, but putting things in core is a high bar. You can always argue your case on pgsql-hackers
> I've been looking into https://github.com/xocolatl/periods but I can't tell if its an extension or part of the regular deployment.
Looks like an extension to me.
Cheers,
Greg
Looks like an extension to me.
Cheers,
Greg
Greg Sabino Mullane <htamfids@gmail.com> writes: > On Mon, Nov 11, 2024 at 6:23 AM David Lynam <davidlynam1@hotmail.co.uk> > wrote: >> Are there any plans or discussions about adding native support for >> SQL:2011 temporal tables, so we don’t need extensions? > No concrete plans I've heard of (but see below). See this long-running thread: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com regards, tom lane