Alexander Fridriksson
|RSS FeedAlexander does product marketing for databases at Aiven. Throughout his career he's worked across marketing, sales, analytics, data engineering and more.
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
Do you like working with time in your code?
If yes, you’re likely one of the lucky ones who are blissfully unaware of how deep the rabbit hole goes.
If you don’t like it, I have good news for you! Postgres can make working with time more enjoyable!
The newly released temporal constraints let you easily maintain referential integrity across temporal relationships.
That might seem simple, but it's kind of a big deal. Let’s explore it through an example.
As always, you can create a free Aiven for PostgreSQL instance with the Aiven CLI to try it out for yourself.
Loading code...
For our example, let’s imagine you are building the booking system for a restaurant called King Crab.
Before we create our tables, we’ll need to add the btree_gist extension, as temporal constraints rely on GiST (Generalized Search Tree) indexes rather than traditional B-tree indexes to handle ranges.
Loading code...
While B-tree indexes are great for searching across one-dimensional data (table_id and created_at), GiST indexes allow us to search across multidimensional data (table_id and available_period).
This will make more sense once we create our tables.
Our booking system needs two tables:
restaurant_capacity, which lists all available tables and their available times.bookings, which have the actual customer bookings.Loading code...
To understand what is happening here, let’s break it down into three parts:
First, for the available_period we’re using the tstzrange data type, which represents a range of timestamps in one row. This makes it multi-dimensional and different from the one-dimensional created_at timestamp, which has one row per timestamp.
Secondly, we’re making a composite primary key based on table_id and available_period. Because Postgres indexes primary keys by default, this means we’re combining a one-dimensional field with a multi-dimensional field in the index. This is why we needed the btree_gist extension.
Lastly, the important addition of WITHOUT OVERLAPS ensures that for each table, available_period cannot overlap.
Remember when I said that this seems simple, but it's kind of a big deal?
Well, this simple combination of tstzrange and WITHOUT OVERLAPS just saved you from having to write a ton of application code with plenty of edge cases and tests to cover them.
Now, regardless of which programming language or library you use, you can be sure that it's impossible to have overlapping periods, because the database won’t allow it.
Loading code...
While some might be opposed to putting business logic like this in the database, there are clear advantages. Especially how easy it is to maintain referential integrity across temporal relationships, by just adding a PERIOD clause to your foreign key.
This constraint ensures that the time period of the booking (booked_period) must be completely contained by the referenced capacity period (available_period). The reference must have a referent for its entire duration.
It's not all sunshine and rainbows though, there are some limitations. The most important being that for the ON DELETE and ON UPDATE clauses, the RESTRICT, CASCADE, SET NULL, and SET DEFAULT actions are not supported for temporal foreign keys.
Now that we’ve created our tables, let's see how it works in practice.
To make it simple, let’s assume our King Crab restaurant has two tables and three availability periods: the entire evening, the early shift and the late shift.
Loading code...
Notice that table 2 has a break between 20:00 and 20:30, where the staff is changing shifts. You can insert as many time periods as you like as long as they don’t overlap, but for our purposes, we are happy with these three.
Let’s also insert three bookings: two successful and one that fails.
Loading code...
For our failed query we get this error message:
Loading code...
This confirms that if you attempted to book table 2 during the gap, such as from 20:00 to 20:45, the temporal foreign key constraint would reject the insert. The key thing to note again is that this eliminates the need for complex application logic or custom triggers to enforce this temporal integrity.
Temporal constraints also make powerful queries easier by using specialized operators and functions to query based on time ranges.
The containment operator (@>) checks if a range contains a specific point in time. This is ideal for performing efficient point-in-time lookups because it is index-supported.
This query finds all reservations active at exactly 6:00 PM UTC on 2025-11-20:
Loading code...
| booking_id | customer_name | booked_period |
|---|---|---|
| 1 | J. Smith | ["2025-11-20 18:00:00+00","2025-11-20 19:30:00+00") |
| 2 | A. Miller | ["2025-11-20 17:00:00+00","2025-11-20 18:30:00+00") |
The lower() and upper() functions allow you to extract the start and end points of any range, such as this query, which finds the exact start and end time for all capacity periods:
Loading code...
| table_id | start_time | end_time |
|---|---|---|
| 1 | 2025-11-20 17:00:00.000000 +00:00 | 2025-11-20 23:00:00.000000 +00:00 |
| 2 | 2025-11-20 17:00:00.000000 +00:00 | 2025-11-20 20:00:00.000000 +00:00 |
| 2 | 2025-11-20 20:30:00.000000 +00:00 | 2025-11-20 23:00:00.000000 +00:00 |
Postgres 18's temporal constraints using WITHOUT OVERLAPS for primary keys and PERIOD for foreign keys helps you conquer the annoyances of working with time by bringing temporal data integrity directly into your schema definition.
This makes it easier to build robust applications by enforcing temporal rules consistently, removing the headache of writing complex application code. You can now maintain complete historical information and complex relationships right where they belong: the database.
Postgres 18 comes with a lot more exciting features, and you can leverage these exciting new features and explore what else Postgres 18 has to offer with Aiven today.
avn service create -t pg cloud do-nyc --plan pg:free-1-1gb \
'king-crab' -c 'pg_version=18'CREATE EXTENSION btree_gist;CREATE TABLE restaurant_capacity (
table_id INTEGER NOT NULL,
max_capacity INTEGER NOT NULL,
available_period tstzrange NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (table_id, available_period WITHOUT OVERLAPS)
);CREATE TABLE bookings (
booking_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
num_guests INTEGER NOT NULL,
booked_table_id INTEGER NOT NULL,
booked_period tstzrange NOT NULL,
reservation_status VARCHAR(20) DEFAULT 'Confirmed',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
FOREIGN KEY (booked_table_id, PERIOD booked_period)
REFERENCES restaurant_capacity (table_id, PERIOD available_period)
);-- Define Table 1 capacity for the whole evening
INSERT INTO restaurant_capacity (table_id, max_capacity, available_period)
VALUES (1, 4,
tstzrange('2025-11-20 17:00:00+00', '2025-11-20 23:00:00+00', '[)'));
-- Define Table 2 capacity for the early shift
INSERT INTO restaurant_capacity (table_id, max_capacity, available_period)
VALUES (2, 6,
tstzrange('2025-11-20 17:00:00+00', '2025-11-20 20:00:00+00', '[)'));
-- Define Table 2 capacity for the late shift
INSERT INTO restaurant_capacity (table_id, max_capacity, available_period)
VALUES (2, 6,
tstzrange('2025-11-20 20:30:00+00', '2025-11-20 23:00:00+00', '[)'));-- Successful booking for Table 1 (18:00 to 19:30)
INSERT INTO bookings (customer_name, num_guests, booked_table_id, booked_period)
VALUES ('J. Smith', 3, 1,
tstzrange('2025-11-20 18:00:00+00', '2025-11-20 19:30:00+00', '[)'));
-- Successful booking for Table 2 early shift (17:00 to 18:30)
INSERT INTO bookings (customer_name, num_guests, booked_table_id, booked_period)
VALUES ('A. Miller', 5, 2,
tstzrange('2025-11-20 17:00:00+00', '2025-11-20 18:30:00+00', '[)'));
-- Failed booking for Table 2 (20:00 to 20:45)
-- because there is a 30 min gap between the early and late shifts
INSERT INTO bookings (customer_name, num_guests, booked_table_id, booked_period)
VALUES ('L. Baker', 4, 2,
tstzrange('2025-11-20 20:00:00+00', '2025-11-20 20:45:00+00', '[)'));[23503] ERROR: insert or update on table "bookings" violates foreign key constraint "bookings_booked_table_id_booked_period_fkey"
Detail: Key (booked_table_id, booked_period)=(2, ["2025-11-20 20:00:00+00","2025-11-20 20:45:00+00")) is not present in table "restaurant_capacity".
SELECT
booking_id,
customer_name,
booked_period
FROM
bookings
WHERE
booked_period @> '2025-11-20 18:00:00+00'::timestamptz;SELECT
table_id,
lower(available_period) AS start_time,
upper(available_period) AS end_time
FROM
restaurant_capacity;