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