PostgreSQL as a temporal database | xata
11.9k<br>11.9kLog inGet Started
Back to Blog<br>PostgreSQL as a temporal database<br>Postgres 18 introduced temporal keys (WITHOUT OVERLAPS, PERIOD).Postgres 19 expands (UPDATE/DELETE ... FOR PORTION OF) temporal capabilities further.
By:<br>Gulcin Yildirim Jelinek<br>Published:<br>Jun 30, 2026<br>Reading time:<br>5 min read
Back to Blog<br>Tags<br>PostgreSQL<br>Open source
Featured<br>A thousand Postgres branches for $1Inside Xatastor: ZFS + NVMe-oF for Postgres databasesIntroducing Xata OSS: Postgres platform with branching
PostgreSQL 18 introduced a lot of improvements around constraints but temporal keys got me most excited. With the release of PostgreSQL 19 Beta 1 on June 4, PostgreSQL continues to build on that foundation with even more temporal features. It feels like PostgreSQL is steadily closing the gap toward becoming a truly temporal database.<br>In this post, we'll explore non-overlapping primary and unique keys with the WITHOUT OVERLAPS and how we can achieve temporal referential integrity with foreign key constraints with the PERIOD. We'll also look at PostgreSQL 19's UPDATE/DELETE ... FOR PORTION OF which let you modify or remove application-time history while automatically preserving the unaffected time periods.<br>If you want to read more about constraints in general, I wrote a blog post about what you should know about constraints in PostgreSQL.<br>What makes a database temporal<br>Before getting into temporal keys, let's step back and think about what makes a database temporal. Temporal databases support managing and accessing temporal data by providing one or more of the following features:
As usual, whenever I learn about a new database concept, I end up discovering that PostgreSQL already supports a large part of it either directly or by providing most of the necessary building blocks. In the table above (based on PostgreSQL 18), I marked the new temporal keys in purple. A green check is for what is fully supported and yellow warning sign is for partial support.<br>PostgreSQL provides built-in range types such as daterange, tsrange and tstzrange for representing intervals between two values. These types support infinite bounds, making it possible to model "no end" or "valid forever" periods naturally. While PostgreSQL does not currently implement a dedicated SQL-standard PERIOD data type, its range types provide much of the same functionality.<br>When it comes to how "time" is treated temporal model and relational model are quite different. In temporal databases, time is a must-have dimension and they have different time concepts like valid time and system (transaction) time .<br>Semantics of time<br>In temporal databases, valid time refers to the period during which a fact is true in the real world or from the business/application perspective. Imagine if we have an employee (employee_id) and her salary (salary) was 5000 from January until June (valid_period [2026-01-01, 2026-06-01)) and starting in June, the salary became 6000 (valid_period [2026-06-01, infinity)). Rather than storing only the salary, the database also stores the period during which each salary was valid.<br>For example, HR might enter a salary change on June 10, but specify that it became effective on June 1. From the business perspective, the salary changed on June 1 , this is the valid time . From the database perspective, the change was recorded on June 10 , this is the transaction time .<br>Temporal databases often distinguish these two concepts because they answer different questions:<br>What was true on date X? → valid time<br>What did the database know on date X? → transaction time
On the valid time side (also called application or business time), PostgreSQL is already quite strong. Range types combined with constraints and temporal predicates allow modeling non-overlapping intervals, validity periods and temporal relationships in a fairly natural way. On the transaction time side (system-managed history and automatic versioning), PostgreSQL still does not provide native support. Features such as automatic row versioning, built-in history tables and native time-travel queries still typically require triggers, audit tables or extensions.<br>PostgreSQL 18 closed an important gap by introducing temporal primary keys and unique constraints with WITHOUT OVERLAPS, together with temporal foreign keys using PERIOD clause. These features make temporal integrity constraints declarative and enforce them directly in the database engine.<br>PostgreSQL 19 introduces native temporal DML via UPDATE/DELETE ... FOR PORTION OF, allowing application-time history to be surgically modified while automatically preserving unaffected time periods. In simpler terms, we can now update or delete temporal data in PostgreSQL natively. If we revisit the table above, we can mark the criterion "Update and deletion of temporal records with automatic splitting and coalescing of time periods" as partially (only for valid time) supported.
However, PostgreSQL still lacks...