Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Looking Forward to Postgres 19: It's About Time

pgedge.com

Post reply on HN
Print view View original

21–30 of 44 posts

Re: Looking Forward to Postgres 19: It's About Time

#21

Post by throwaway7783 »

larsnystrom wrote: post #13
throwaway7783 wrote: post #9

Earlier quoted context omitted.

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

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.

Yeah, but now this is implicit information (no price == marker row) and duplicate the entire row. And it is baking in "price" as the special field. This may not be just one field for another temporal entity

Re: Looking Forward to Postgres 19: It's About Time

#23

Post by pjungwir »

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!

Re: Looking Forward to Postgres 19: It's About Time

#24

Post by bonesmoses »

pjungwir wrote: post #23

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…

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

Re: Looking Forward to Postgres 19: It's About Time

#25

Post by munk-a »

IgorPartola wrote: post #15

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…

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.

Re: Looking Forward to Postgres 19: It's About Time

#26

Post by MBCook »

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.

Re: Looking Forward to Postgres 19: It's About Time

#28

Post by fabian2k »

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?

Re: Looking Forward to Postgres 19: It's About Time

#30

Post by yourMadness »

larsnystrom wrote: post #5

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.

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