Looking Forward to Postgres 19: It's About Time<br>Blog Home<br>Looking Forward to Postgres 19: It's About Time
Shaun Thomas|June 12, 2026
Recently, a new type of question has entered the database arena: what did this data look like last Tuesday? Maybe it's the price of a product before the holiday sale kicked in, or which department an employee belonged to before that reorg nobody asked for. Short of adding an entire audit trigger system, how can we know what data looked like before and after a change at that exact date?<br>The SQL:2011 standard formalized a proper solution over a decade ago with temporal tables. Other database engines adopted pieces of it relatively quickly. Characteristically, Postgres took its time. But Postgres 19 is finally bringing native temporal table support to the party — and it's been well worth the wait.<br>Let’s see what we’re working with.<br>The Old-Fashioned Way<br>Before we get to the shiny new stuff, let’s check out the crusty old approach for some perspective. Suppose we want to track product pricing over time. A reasonable first attempt might look like this:<br>CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE products (<br>product_id INT NOT NULL,<br>product_name TEXT NOT NULL,<br>price NUMERIC(10,2) NOT NULL,<br>valid_from DATE NOT NULL,<br>valid_to DATE NOT NULL,<br>CONSTRAINT no_time_travel CHECK (valid_from Simple enough. We've got a product, a price, and a valid date range for the price. Unfortunately, nothing stops us from inserting two rows for the same product with overlapping date ranges. Product number 42 could be $9.99 and $14.99 on the same Tuesday. Your accountant might have some choice words upon discovering that.<br>The traditional Postgres answer here is the btree_gist extension and an exclusion constraint:<br>ALTER TABLE products<br>ADD CONSTRAINT no_overlapping_prices<br>EXCLUDE USING gist (<br>product_id WITH =,<br>daterange(valid_from, valid_to) WITH &&<br>);This works. If we try to insert a conflicting row, Postgres catches it:<br>INSERT INTO products VALUES (1, 'Widget', 9.99, '2025-01-01', '2025-07-01');<br>INSERT INTO products VALUES (1, 'Widget', 12.99, '2025-06-01', '2026-01-01');
ERROR: conflicting key value violates exclusion constraint<br>"no_overlapping_prices"A problem solved by using btree_gist! So what’s the problem? Well, a few things:<br>Everyone knows BTREE and indexing in general, but GiST is specific to Postgres and thus requires experience to understand. This goes double for being an optional extension.
The exclusion constraint syntax is quite non-intuitive. It’s in the documentation, but there’s no reason anyone would think of this as the standard approach otherwise.
There’s no temporal awareness baked into the table itself.
Basically, Postgres doesn't understand that this is temporal data. It's just columns and an esoteric constraint using a fancy index type. Every update that changes the time range requires manual splitting and stitching of rows, which means applications must carry the full burden of temporal correctness.<br>It’s the barest of minimums, and frankly we can do better.<br>A Brief History of Time<br>The desire for proper temporal support in Postgres isn’t new. The SQL:2011 standard introduced APPLICATION TIME periods, WITHOUT OVERLAPS constraints, and FOR PORTION OF syntax for temporal DML. 2011 was a long time ago.<br>Henrietta Dombrovskaya (Hetti to her friends) was one of the earliest champions of temporal data in the Postgres ecosystem. Along with Chad Slaughter, she developed the pg_bitemporal extension. It's a framework for managing bitemporal tables entirely within Postgres using PL/pgSQL. She presented the concepts at multiple conferences since 2015, demonstrating how to track both valid time (when was this fact true in the real world?) and transaction time (when did the database record this fact?) simultaneously.<br>The distinction matters. Valid time says "this price is effective from January through June." Transaction time is the database perspective, saying "this row was inserted at 3:47 PM on March 12th and superseded at 9:01 AM on April 3rd." Combining both produces a bitemporal table that can answer questions like "what did we think the price was last Tuesday, based on what we knew at the time?"<br>The pg_bitemporal approach leaned heavily on the same EXCLUDE USING gist mechanism we discussed earlier, but doubled up: one exclusion for the effective range (valid time) and another for the asserted range (transaction time). A table definition looked something like this:<br>CREATE TABLE bi_temporal.customers (<br>cust_nbr INTEGER,<br>cust_nm TEXT,<br>cust_type TEXT,<br>effective_range TSTZRANGE,<br>asserted_range TSTZRANGE,<br>row_created_at TIMESTAMPTZ,<br>EXCLUDE USING gist (<br>cust_nbr WITH =,<br>effective_range WITH &&,<br>asserted_range WITH &&<br>);That's two temporal dimensions enforced by a single exclusion constraint. The extension also introduced functions for bitemporal inserts, updates, corrections, inactivations, and deletes, plus implementations of Allen's interval relationships for temporal...