Thank you to everyone here saying they are excited about it. I often hear doubts that anyone wants this. Perhaps that's why vendors have been so slow to add it. And thank you 'bonesmoses for writing about it!
We are still missing system time, but if no one else wants to work on it, I hope to tackle that soon.
I have a lot of other ideas for improvement beyond SQL:2011, too. Here is a talk I gave last month about my personal roadmap:
https://illuminatedcomputing.com/pages/pgconf2026-temporal-r...
I've also been vibecoding a lisp REPL to play with the algebra of temporal relational operators (important for the planner): https://github.com/pjungwir/relsim
That overlaps with my attempt to write implementations for temporal semi/anti/outer-join and other relops: https://github.com/pjungwir/temporal_ops
If anyone has comments about what you'd like to see, I'm happy for feedback!
This was actually a key feature that was greatly desired by a large organization's HR function when they were working to setup a consolidated enterprise data warehouse a decade back.
It would have made it much easier for retention specialists to be able to do things like answer how much a missed retention bonus should have been once the paperwork had been fixed up.
Likewise for the same organization's board for correction of records, and in general any offices that have to make sense of what the world was like for the computer-based records years ago, and how that would change if certain data would have been different years ago.
So basically what WHITOUT OVERLAPS and FOR PORTION OF do.
The system time is also interesting in the context of financial data and backtesting, as companies might republish a statement with corrections, and it would help tracking why the system made a decision at a given time.
This bridges the gap to something like xtdb.
Question: Could this be useful to store history of changes? One pesky trouble is that after you close an invoice all the data there becomes immutable but you need to continue change the base tables.
So, I have a convoluted way of double write rows and keep the "current" as main. All for "just" the case that an invoice need to see the data as was when finalized.
Is this feature for this case too?
Can it apply to other types (eg geometries) which can be subdivided?
Let’s say for 2026 you have it at 7.25% and you entered that into the system ahead of time (say December 2025). Today, June 12 you learn that it should have been 7.35%. It would be incorrect to say that the rate changed today: it was 7.35% since January 1. But you also don’t want to lose the fact that all your invoices have been generated using the wrong rate because if you go to recalculate them you will get a different answer.
In this case what you do is create version 2 of the rate in your database with the same time period but the correct rate. This would allow your other database objects to reference either version 1 or 2 and to even recalculate all the objects that reference version 1 to now reference version 2 such that you can get line item corrections and figure out what to do about them.
It is cumbersome to use but for the specific use case of modeling real world laws that are not available as machine-readable info it is the best option I came up with.
My hope is that Postgres making this kind of thing a first class citizen feature will mean that ORMs and other tooling incorporate it in a standard way so that developers can internalize using it. I am sure it will take time but this is a big step. Doing all this has been possible but having standardized tooling and frameworks would be nice.
Another alternative I have used is basically having a current state table and a migrations table that get applied at a specific time. The migrations table act as both a history and a set of scheduled updates. The trick is that you have to apply the migrations and so you don’t have perfect atomic changes without some sort of locking system and timing might be off. But it is a decent system that also works.
One thing I'm wondering about is the performance of temporal tables for the common case, when you only query current rows. When you manually version tables, one strategy is to have a second table that contains archived versions. So your main table only has the current rows, avoiding a performance hit for having many versions per entry. Is there a way to do this with temporal tables? For example partitioning between active and old rows?
For system time, a separate history table is a common implementation, sometimes also with partitioning. Here is what other vendors are doing: https://illuminatedcomputing.com/posts/2019/08/sql2011-surve...
I think the end date should be nullable though, but valid_to is NOT NULL in the starting example... later in the article, when showing the "new way" using date ranges, it inserts a row with an open-ended range, which is more what I'd expect.
It never came to pass when we used Oracle, maybe now with Postgres I will finally have a chance at it.
This is WAY easier.
And really, ranges are an amazing substrate for this. I've had to do this by hand in a ... less featuresome ... SQL-speaking DB and it was clunky and performed fairly unimpressively.
This question has been answerable in Dolt for years now.
Say you have one time series with CPU-core task switches:
T=1 task=A T=3, task=B, T=5 task=A, ...
... and another of CPU frequency changes ...
T=2 freq_hz=800, T=5 freq_hz=1200, T=6 freq_hz=900
How, in SQL, do you express the question "How many CPU cycles did each task use?"? Try to do it with more complex examples. You'll tear your hair out.
Having worked on this sort of data analysis quite a lot, I'm strongly of the opinion that SQL needs syntax, not just table-valued functions, for expressing questions about timelines.
See https://dancol.org/dctv/index.xml
I still think there's a lot of value in first-class syntax for time queries. Splitting ranges like Postgres 19 does is a good first step, but there's also a lot of power in broadcasting over these ranges, combining them in various ways, and storing multiple, independent ranges in a single table.
Ignore the bit about active development: these days, it'd take more sense to add the operators I describe to Postgres and DuckDB than to make a numpy-based engine just to host the analysis. This work predates DuckDB, and it's reassuring that DuckDB (and now Postgres) are thinking along similar lines.
I'm also glad that in the intervening years "data lake"-style analysis has become more prominent. My ideal data processing pipeline consists of sourcing from raw data and pipelining views all the way to human-meaningful outputs. Materialization, if it occurs, is just an optimization.