Pangram verdict · v3.3
We believe that this document is a mix of AI-generated, AI-assisted, and human-written content
AI likelihood · overall
MixedArticle text · 1,433 words · 7 segments analyzed
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?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.Let’s see what we’re working with.The Old-Fashioned WayBefore 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:CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE products ( product_id INT NOT NULL, product_name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL, valid_from DATE NOT NULL, valid_to DATE NOT NULL, CONSTRAINT no_time_travel CHECK (valid_from < valid_to) );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.The traditional Postgres answer here is the btree_gist extension and an exclusion constraint:ALTER TABLE products ADD CONSTRAINT no_overlapping_prices EXCLUDE USING gist ( product_id WITH =, daterange(valid_from, valid_to) WITH && );This works.
If we try to insert a conflicting row, Postgres catches it:INSERT INTO products VALUES (1, 'Widget', 9.99, '2025-01-01', '2025-07-01'); INSERT INTO products VALUES (1, 'Widget', 12.99, '2025-06-01', '2026-01-01');
ERROR: conflicting key value violates exclusion constraint "no_overlapping_prices"A problem solved by using btree_gist! So what’s the problem? Well, a few things: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.It’s the barest of minimums, and frankly we can do better.A Brief History of TimeThe 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.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.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?"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:CREATE TABLE bi_temporal.customers ( cust_nbr INTEGER, cust_nm TEXT, cust_type TEXT, effective_range TSTZRANGE, asserted_range TSTZRANGE, row_created_at TIMESTAMPTZ, EXCLUDE USING gist ( cust_nbr WITH =, effective_range WITH &&, asserted_range WITH && ) );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 reasoning. It was a lot of machinery built on top of what Postgres offered at the time.And it worked! But an extension can only go so far. It can't change how the query planner sees temporal predicates, can't integrate with the constraint system at the engine level, and can't provide native DML syntax. For that, the feature needed to land in core.Now Postgres 19 accommodates the application-time half of a bi-temporal system. It's not the full picture, but that's still a huge step in the right direction.Ranges to the RescueLet’s rebuild our products table the Postgres 19 way. Instead of separate valid_from and valid_to columns, we use a single range type column:CREATE TABLE products ( product_id INT NOT NULL, product_name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL, valid_at DATERANGE NOT NULL, PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) );That’s it. No btree_gist extension needed. No exclusion constraint. The WITHOUT OVERLAPS clause in the primary key tells Postgres that product_id must be unique at any given point in time, but the same product can have multiple rows as long as their valid_at ranges don’t overlap.How does this compare to our old approach? Let’s put them side by side. The old way:CREATE EXTENSION IF NOT EXISTS btree_gist;
-- Two separate columns for the range valid_from DATE NOT NULL, valid_to DATE NOT NULL,
-- Manual range construction in the constraint EXCLUDE USING gist ( product_id WITH =, daterange(valid_from, valid_to) WITH && )The new way:-- Single range column, no extension valid_at DATERANGE NOT NULL,
-- Temporal awareness built into the primary key PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS)It's cleaner, more expressive, and Postgres actually understands what we're doing.
Under the hood, the WITHOUT OVERLAPS constraint still uses a GiST index, and it still requires btree_gist for the non-temporal columns in the key. But Postgres handles that dependency automatically when initializing the constraint. Very convenient.Let’s populate our table with some data to work with throughout the rest of this article:INSERT INTO products VALUES (1, 'Widget', 9.99, '[2025-01-01, 2025-07-01)'), (1, 'Widget', 12.99, '[2025-07-01, 2026-01-01)'), (1, 'Widget', 11.99, '[2026-01-01, 2026-07-01)'), (2, 'Gadget', 24.99, '[2025-01-01, 2025-04-01)'), (2, 'Gadget', 22.99, '[2025-04-01, 2026-01-01)'), (2, 'Gadget', 26.99, '[2026-01-01,)');
SELECT * FROM products ORDER BY product_id, valid_at;
product_id | product_name | price | valid_at ------------+--------------+-------+------------------------- 1 | Widget | 9.99 | [2025-01-01,2025-07-01) 1 | Widget | 12.99 | [2025-07-01,2026-01-01) 1 | Widget | 11.99
| [2026-01-01,2026-07-01) 2 | Gadget | 24.99 | [2025-01-01,2025-04-01) 2 | Gadget | 22.99 | [2025-04-01,2026-01-01) 2 | Gadget | 26.99 | [2026-01-01,)Notice the range notation: [ means inclusive, ) means exclusive. So [2025-01-01, 2025-07-01) includes January 1st but not July 1st. The final Gadget row has an open-ended range [2026-01-01,), meaning the current price has no defined end date. And the overlap protection works exactly as we'd expect:-- Try to add an invalid range INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-01, 2025-01-01)');
ERROR: range lower bound must be less than or equal to range upper bound LINE 1: INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-0...'
-- Try to add an overlap INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-01, 2025-09-01)');
ERROR: conflicting key value violates exclusion constraint "products_pkey" DETAIL: Key (product_id, valid_at)=(1, [2025-03-01,2025-09-01)) conflicts with existing key (product_id, valid_at)=(1, [2025-01-01,2025-07-01)).Two validation checks for the price of one! That’s what we get for working with ranges rather than two separate unrelated columns.Slicing and DicingNow for the really fun stuff.
Let's say Widget's price needs to change to $10.99, but only from March through September of 2025. In the old world, we'd have to manually split the existing row into pieces: delete or update the original, insert the new price range, and insert leftover rows for the portions we didn't change. Get any of that wrong, and we've got gaps or overlaps in our timeline.With temporal tables, we just say what we mean:UPDATE products FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01' SET price = 10.99 WHERE product_id = 1;Let’s see what the rows look like now:SELECT * FROM products WHERE product_id = 1 ORDER BY valid_at;
product_id | product_name | price | valid_at ------------+--------------+-------+------------------------- 1 | Widget | 9.99 | [2025-01-01,2025-03-01) 1 | Widget | 10.99 | [2025-03-01,2025-07-01) 1 | Widget | 10.99 | [2025-07-01,2025-09-01) 1 | Widget | 12.99 | [2025-09-01,2026-01-01) 1 | Widget | 11.99 | [2026-01-01,2026-07-01)Wait... what happened here?