Postgres 19 can finally answer: what did this row look like last Tuesday?
For years, time-travel queries in Postgres meant hand-rolled date ranges and GIST index gymnastics. PG19 brings the SQL:2011 temporal features native — overlap-proof primary keys, time-sliced updates, and period foreign keys. The engineer who built it showed up in the thread.
Postgres 19 adds native application-time temporal tables: PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) replaces the EXCLUDE-USING-gist dance, FOR PORTION OF lets an UPDATE rewrite just one time slice, and PERIOD foreign keys enforce validity across a duration.
The developer who implemented it joined the thread to say it still lacks system-time, which he hopes to tackle next — and the article's author called it the biggest single release since v10.
The query Postgres couldn't answer natively until now
The pun in pgEdge's preview is literal: PostgreSQL 19 is about time. It finally ships native application-time temporal tables from the SQL:2011 standard — the kind of thing that answers "what did this data look like last Tuesday?" without a homemade audit log.
What's actually new in PG19
Three features carry the release, per author Shaun Thomas:
WITHOUT OVERLAPSprimary keys:PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS)replaces the clunkyEXCLUDE USING gistconstraint and the manualbtree_gistsetup, while still preventing two overlapping validity periods.FOR PORTION OF: anUPDATE … FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01'rewrites only that slice of time, with Postgres automatically splitting and adjusting the surrounding rows.PERIODforeign keys: temporal referential integrity, so a referenced row must exist for the entire validity span of the referencing one.
Applications store a single range column (e.g. DATERANGE) instead of separate valid_from / valid_to fields. The notable gap: system-time (transaction-time) tables are still absent, so you emulate them with triggers or the pg_bitemporal extension.
The person who built it walked in
The best confirmation came from the implementer:
"Hey I worked on this!… I often hear doubts that anyone wants this. Perhaps that's why vendors have been so slow to add it… We are still missing system time, but if no one else wants to work on it, I hope to tackle that soon." — the developer who implemented temporal tables
The article's author, a DBA, framed the scale of the release:
"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." — a DBA working with PostgreSQL since 2005
Practitioners showed up with the exact pain it removes. A developer who'd hand-built versioned time periods gave the canonical use case: a sales-tax rate entered as 7.25% for 2026, later corrected to 7.35% — and you need to record that it was always 7.35% since January, not changed on the day you fixed it. Others cited trading systems handling trade-correction and bust messages.
One assumption it quietly breaks
Not everyone is comfortable. The feature makes UPDATE insert new rows when it splits a range, which trips a long-held mental model:
"I'm a little uneasy with UPDATE operations adding new rows to a table. It upsets a lot of a DBA's assumptions." — ris
The pitch for anyone doing audit trails, pricing history, or scheduling: the bookkeeping you've been writing by hand for a decade is becoming a column constraint. The honest caveat: it's the application-time half of bitemporal. The other half waits for PG20 — or for the implementer to find the time.
Get breakdowns like this every day
The top Hacker News stories, unpacked — by email, Telegram, or RSS.
Apple rewrote a font bytecode interpreter from C to Swift — and it got 13% faster
TrueType fonts ship tiny bytecode programs that an interpreter runs on every glyph. Apple's was C, parsing untrusted data from PDFs and web pages. The Swift rewrite kept the rendering pixel-identical, killed the memory-bug class, and ran faster anyway.