Looking Forward to Postgres 19: It's About Time

https://www.pgedge.com/blog/looking-forward-to-postgres-19-its-about-time

by xngbuilds • 1 month ago

154 43 1 month ago

munk-a2• 1 month ago

It can be super ugly to try and hand-manage date time range manipulation in a system with moving parts. It is, of course, possible, but it's a headache to try and pull it off when there is complex business logic focused on those datetimes and interactions between ranges. The period constraint is an excellent tool for trivially guaranteeing range coverage (in a case where, for instance, a customer is known to be active from a to b it helps ensure that there aren't any gaps created during the juggling of different sub-ranges) while the new DELETE FOR PERIOD OF syntax makes manipulating swathes of history (I don't care what was happening here this interval should now be X) much more trivial than before.

bhaak• 1 month ago

I remember reading about this feature for Oracle in the 2000s and was always interested to use it in a production environment.

It never came to pass when we used Oracle, maybe now with Postgres I will finally have a chance at it.

horticulturist• 1 month ago

I always found it very difficult to explain to nearly anyone bitemporal data, despite it being critical and used by nearly every critical data domain in our system. As one senior developer (who actually understood the concept) it gives people “melty brain”.

larsnystrom3• 1 month ago

Why are they storing a time period (start and end date) in the first example? Why not just store the date when the price comes into effect? That would make both overlaps and time travel impossible without using any constraints.

quotemstr4• 1 month ago

It's a trade-off. If you store both endpoints you can continue to think of rows as order-invariant tuples. If you store only one endpoint, you have to impose a meaningful order on the rows in order for them to make sense.

larsnystrom3• 1 month ago

Sure, from a theoretical perspective, but in practice there’s got to be some sort of order at some point even when storing timespans.

throwaway77832• 1 month ago

Works when there is always an active price. Having an explicit end date allows certain rows to be inactive automatically after validity period. Think of seasonal categories/products etc which dont exist after a specific period

larsnystrom3• 1 month ago

You could also make the price column nullable and just insert a row with price null and the date from which there should be no price.

promano• 1 month ago

And even if you don't have seasonal products, you still need an end date to mark when you stop selling a product; otherwise you have to do something hacky like defining "<price> = NULL means we stopped selling the product after <valid_from>" and inserting an extra record.

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.

advisedwang• 1 month ago

JOINs and other operations become really difficult if you can't evaluate whether a row applies or not based on that row alone.

warmwaffles• 1 month ago

It's a contrived example.

yourMadness• 1 month ago

If you sell a product where the customer can buy the future version today (for delivery in the future), that doesn't work.

evdubs2• 1 month ago

Recently, a new type of question has entered the database arena: what did this data look like last Tuesday?

This question has been answerable in Dolt for years now.

quotemstr4• 1 month ago

This kind of DB isn't really answering this question. There's a lot more subtlety to time-span analysis than snapshotting. In particular, aligning two series is non-obvious.

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.

evdubs2• 1 month ago

The cool thing about Dolt is that you [eventually] get the features of the databases (MySQL, PostgreSQL, SQLite, MongoDB) they emulate, so you can have your PG 19 temporality features as well as branching and merging.

quotemstr4• 1 month ago

Great! I've been wanting native time-based tables for ages. Years and years ago, I even wrote my own DB engine to get them!

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.

IgorPartola2• 1 month ago

This is something that is incredibly useful. I built a system like this a while back that also adds versioning to each time period. The use case is this: let’s say you are tracking your state’s sales tax rate. You do not control this and data entry is manual so it is error prone. The rate is updated typically annually but sometimes more frequently.

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.

munk-a2• 1 month ago

Yeah, I think folks that under appreciate this new functionality look at the approaches that existed and say "That works, we could just sorta, wrap that in a function" but when you start getting into useful and entangled data the overhead of implementing proper bounds checking on ranges when you're more focused on the preservation of the linkages to existing data - it gets complicated quickly. This feature doesn't make anything building a system like you've described trivial but it moves us towards a higher level of expression - when building such a system you'd be focused on the complex logic of preserving the invoice integrity and reconciling it with the actual enforced tax rate rather than needing to step down to the lower level code and fiddling with date range boundaries. Much like moving from assembly to C a higher level of expression allows clearer focus on the actual problems the software is solving rather than getting tripped up in the procedural rules of implementation.

IgorPartola2• 1 month ago

Exactly. This is non-trivial and the API for something like this is unusual in how you work with this data.

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.

cherryteastain• 1 month ago

Very useful feature for trading systems where the exchange might tell you your order got filled but then send a trade correction or bust message

jacques_chester• 1 month ago

Exciting. Honestly I expect this will do more to advance bitemporal design than decades of jawboning has.

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.

oveja• 1 month ago

Oh man this is really good. I have had to do some gist exclusions for payment data and it was clunky. Hopefully we get the other half of bitemporal support with postgres 20!

pjungwir4• 1 month ago

Hey I worked on this!

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!

bonesmoses2• 1 month ago

Awesome! I'll look forward to that. :)

mpyne• 1 month ago

I often hear doubts that anyone wants this.

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.

danielheath2• 1 month ago

How tied is the implementation to time specifically?

Can it apply to other types (eg geometries) which can be subdivided?

pjungwir4• 1 month ago

In the Postgres version, you can use a rangetype or multirangetype over any base type (integers, inets, frammishes, etc). This is a generalization of SQL:2011, where PERIODs have to be on date/timestamp/timestamptz columns. I have ambitions to support non-range/multirange types as well, even user defined types, but I'll need to add a way for them to communicate a few things to core Postgres, like how to do intersect/contains/overlaps. I talked about that in my "roadmap" talk linked here if you're interested in details.

indiosmo• 1 month ago

There’s definitely interest in this in finance domains. I’ve done DATERANGE and GiST exclude constraints based solutions for a symbology database for example, where any given ticker might represent different securities at different times without overlap, and relying on functions to keep the dateranges in sync when updating a row.

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.

mamcx• 1 month ago

Ok this looks like interesting, and now rebuilding my ERP it sounds the kind stuff I need to look at.

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?

pjungwir4• 1 month ago

I think yes. You're saying that the invoice is generated from other data in separate tables, and it should be immutable even if that data changes. So today you are copying everything used by the invoice.

That's exactly why I started learning about temporal tables. I had a customer whose app used questionnaires to measure the effectiveness of government social services, and they let people change the questions (and multiple choice options) even after there were answers! Obviously the data was trashed.

I've seen this bug over & over again, where you have a foreign key relationship, and the referenced table changes, but the referencing table needs the old data. Another example is a sale that doesn't capture the product's current price.

Temporal tables mean that you can run your 2022 financial reports and get the same answer you got before.

My own time-tracking and invoicing app has to solve this problem. I also "copy everything" when something changes. I just gave a talk about migrating it to temporal tables: https://illuminatedcomputing.com/pages/pgdata2026-migrating-...

You'll have to decide if your use-case is more "system time" (history of the database) or "application time" (history of the entities). The features here are for application time. I want to make sure Postgres gets system time too, but it's not in v19.

If you want system time today, there are several widely-adopted Postgres extensions that can do that already. I cover them here: https://illuminatedcomputing.com/posts/2017/12/temporal-data...

And actually, application time is managed by you (while system time is managed automatically by the database), so really you can use it for whatever you want.

MBCook• 1 month ago

Ooh fantastic. I’ve been using date ranges plus GIST indexes for like a decade to do this. It’s really nice. But the lack of foreign keys can be painful. I’ve resorted to stored procedures for crates and updates to ensure everything is done right and enforced.

This is WAY easier.

ElmerWallace• 1 month ago

The FK piece is what I'm most excited about. PRIMARY KEY WITHOUT OVERLAPS plus FOREIGN KEY ... PERIOD means a child row's range is enforced to stay inside an actual parent version, no triggers or sprocs needed. Not free (GiST lookups add up on hot tables), but for slowly changing dimensions it kills a whole class of footgun.

ris• 1 month ago

Cool feature, but I'm a little uneasy with UPDATE operations adding new rows to a table. It upsets a lot of a DBA's assumptions.

fabian2k• 1 month ago

Some kind of versioning is extremely important for certain use cases. And having it a core DB feature makes it easier to show that you implement that checkbox.

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?

pjungwir4• 1 month ago

For application time, everything lives in one table (although you could partition it). The biggest performance hit, I suspect, will come from GiST indexes instead of B-Trees. Some general GiST improvements are on my TODO list, and I learned at PGConf.dev that several other people already have patches for cool perf-related GiST enhancements.

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

bonesmoses2• 1 month ago

Postgres 19 is looking to be a solid release. I don't think I've seen this much "new" stuff in a single version since v10 came out.

frrlpp• 1 month ago

Una breve historia del tiempo. J. L. Borges, cool subtitle.

mrinterweb• 1 month ago

This would have made my life so much easier. I wrote a medical scheduling calendar application about 1.5 years ago.

qsera• 1 month ago

Couldn't this be done by having an update log with a timestamp?

speedbird• 1 month ago

Talk about back to the future. Pre-SQL Postgres had time travel.

Looking Forward to Postgres 19: It's About Time

Related Stories