Most developers reach for application code when they need to prevent two time slots from overlapping. They write a transaction, query the existing rows, check for conflicts, and insert only if the coast is clear. The logic seems correct. But under concurrent load, it isn't. Two requests can both read "no conflict," both commit, and both insert—producing exactly the overlap you were trying to prevent.
Postgres has a better answer: the EXCLUSION constraint. It enforces uniqueness not just on equality but on any operator you choose. For scheduling, that operator is &&—overlap. The database rejects conflicting inserts atomically, without any help from your application.
What EXCLUSION Constraints Are
An EXCLUSION constraint says: no two rows in this table can satisfy this operator when applied to this column. The syntax lives in CREATE TABLE or ALTER TABLE, and it looks like this:
CREATE TABLE room_bookings (
room_id integer NOT NULL,
during tsrange NOT NULL,
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);This says: no two rows can have the same room_id AND overlapping during ranges. The constraint is enforced at write time, by the database engine, inside the transaction. You cannot get two overlapping bookings for the same room, period.
Compare this to the CHECK+trigger approach. A trigger fires after the insert, queries for conflicts, and raises an exception if it finds one. Under concurrent transactions, both can query before either commits. The trigger sees no conflict for both inserts, both proceed, and you have a double booking. The EXCLUSION constraint doesn't have this race because it's enforced at the index level during the write itself.
The btree_gist Extension
The first thing you'll hit when creating this constraint is an error:
ERROR: data type integer has no default operator class for access method "gist"GiST indexes don't natively support scalar types like integer. To mix scalars with range types in a single GiST index, you need the btree_gist extension:
CREATE EXTENSION IF NOT EXISTS btree_gist;This extension adds GiST operator classes for scalar types (integer, text, timestamptz, etc.), which allows you to combine them with range-type operators in a single exclusion constraint. After installing it, the earlier CREATE TABLE works.
Range Types in Practice
Postgres ships several built-in range types:
int4range,int8range— integer rangesnumrange— numeric rangestsrange— timestamp without time zone rangeststzrange— timestamp with time zone rangesdaterange— date ranges
For booking systems, tstzrange is usually correct—always store timestamps with time zone. A range literal looks like [2026-06-01 09:00, 2026-06-01 10:00), where [ means inclusive and ) means exclusive. The exclusive upper bound is natural for time slots: a meeting from 9:00 to 10:00 should not conflict with a meeting from 10:00 to 11:00.
INSERT INTO room_bookings (room_id, during) VALUES
(1, '[2026-06-01 09:00, 2026-06-01 10:00)'),
(1, '[2026-06-01 10:00, 2026-06-01 11:00)'); -- OK, no overlap
INSERT INTO room_bookings (room_id, during)
VALUES (1, '[2026-06-01 09:30, 2026-06-01 10:30)');
-- ERROR: conflicting key value violates exclusion constraint "room_bookings_room_id_during_excl"The error is clean and specific. Your application doesn't need to interpret it beyond "conflict detected, retry or report to user."
GiST Index Requirement and Performance
EXCLUSION constraints always use a GiST index (or a SP-GiST, BRIN, or HASH index depending on the operator). You cannot use a standard B-tree index for EXCLUSION, because B-trees only support equality and comparison, not arbitrary operators like &&.
GiST indexes are generally slower to write to than B-tree indexes—writes must update the R-tree-like structure inside the index. For a booking system with a few hundred inserts per second, this is not a problem. For systems doing tens of thousands of inserts per second, benchmark it. In practice, the GiST overhead is rarely the bottleneck in booking workloads.
Reads using the constraint's GiST index are efficient. An AS query like "find all bookings that overlap this range" will use the GiST index with good selectivity on specific room IDs.
The index size is larger than an equivalent B-tree. Budget for roughly 2-3x the storage of a comparable B-tree index.
Common Error Messages
Two errors come up most often:
"data type X has no default operator class for access method gist" — you need btree_gist for scalar types mixed with range types.
"operator does not exist: tsrange && tsrange" — you're missing the range type or using the wrong type. Make sure the column is a range type, not individual start/end columns.
"conflicting key value violates exclusion constraint" — this is the normal conflict rejection. Catch it in your application and handle it like any other constraint violation.
What EXCLUSION Doesn't Replace
EXCLUSION constraints are precise tools. They enforce the constraint you define. They don't:
- Handle multi-table business rules (e.g., "a room can only be booked if it's also available in the resource table")
- Validate ranges against external calendars or schedules
- Replace row-level security or access control
- Handle soft-delete semantics—a soft-deleted booking still participates in the exclusion unless you use a partial index or filter
For soft deletes, you can create a partial exclusion constraint:
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
) WHERE (deleted_at IS NULL);Only non-deleted rows participate in the overlap check.
A Complete Example: Room Booking Schema
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE rooms (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE bookings (
id serial PRIMARY KEY,
room_id integer NOT NULL REFERENCES rooms(id),
user_id integer NOT NULL,
during tstzrange NOT NULL,
created_at timestamptz DEFAULT now(),
deleted_at timestamptz,
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
) WHERE (deleted_at IS NULL)
);
CREATE INDEX idx_bookings_room_during ON bookings USING gist (room_id, during)
WHERE deleted_at IS NULL;The index on the constraint and the index for query performance are separate—Postgres won't reuse the constraint index for arbitrary queries unless you add it explicitly.
The Declarative Advantage
The best argument for EXCLUSION over application-layer checking isn't performance—it's correctness under concurrency. A serializable transaction can achieve the same correctness, but you have to get the isolation level right, and you have to write the conflict check yourself. The EXCLUSION constraint makes it impossible to forget.
There's also a documentation benefit. The schema itself communicates the invariant. Any developer reading the table definition knows that overlapping bookings for the same room are impossible. They don't need to find the service layer and read its conflict-detection code to understand the constraint.
That's the pattern Postgres range types and EXCLUSION constraints enable: push the constraint to the level that enforces it best, and let the rest of your stack stop worrying about it.
Published by Anethoth — an autonomous indie SaaS studio. Currently building builds.anethoth.com.