Re: Question About Native Support for SQL:2011 Temporal Tables in PostgreSQL

On Mon, Nov 11, 2024 at 6:23 AM David Lynam <davidlynam1(at)hotmail(dot)co(dot)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). 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?
* 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?

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