We double-booked a meeting room twice in one week, and both times the postmortem read the same way. Two API requests arrived forty milliseconds apart. Each ran the availability check — SELECT, no overlapping rows — each saw a clear calendar, and each inserted. Read-committed check-then-insert has a race window exactly the width of your network round trip plus a context switch, and at two hundred bookings a minute that window is a revolving door. We fixed it by deleting the SELECT: an exclusion constraint makes the database itself refuse overlapping ranges, at write time, with no window at all.
The feature is old, criminally underused, and usually discovered the same way we discovered it — after the duplicate data has already happened and someone asks why UNIQUE could not prevent it.
What does EXCLUDE USING gist actually enforce?
It generalizes UNIQUE from equality to any commute of operators: no two rows may exist where every listed operator comparison returns true. For booking that reads as "no two rows with the same room AND overlapping time". The scalar equality half needs the btree_gist extension, because plain integer equality is not natively GiST-indexable; the range half uses the built-in overlap operator.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE booking (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id integer NOT NULL,
during tstzrange NOT NULL,
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);
INSERT INTO booking (room_id, during)
VALUES (7, tstzrange('2026-03-01 14:00', '2026-03-01 15:00'));
-- A second, overlapping insert for room 7 now fails inside the index:
-- ERROR: conflicting key value violates exclusion constraint "booking_room_id_during_excl"
-- SQLSTATE: 23P01The reason this closes the race — and the reason nothing at the application layer truly can — is where the check lives. An insert or update probes the GiST index as part of the write, and a concurrent uncommitted insert into a conflicting range forces the second writer to wait on the in-flight entry, exactly the way a unique index makes a duplicate inserter wait for the first transaction to commit or roll back. There is no snapshot gap to slip through, because enforcement happens in the index machinery rather than in a SELECT under your isolation level. The constraint is also DEFERRABLE if you need batch reorgs that transiently overlap, though in six years I have deferred one exactly once, during a data migration, and re-enabled it the same hour.
How do exclusion violations reach your application?
As SQLSTATE 23P01, exclusion_violation, with the constraint name in the message and the conflicting key values in the DETAIL line. Treat it as a domain outcome, not a crash: map 23P01 to a 409 with the conflicting range in the response body and let the caller pick another slot. Two practical notes. ON CONFLICT DO NOTHING does work against an exclusion constraint — with no conflict_target it needs no arbiter, and the excluded row is simply skipped — but ON CONFLICT DO UPDATE is impossible, because its arbiter must be a unique index and an exclusion constraint is not one. So there is no true upsert: you catch 23P01, or you DO NOTHING and read the affected-row count, or you pre-check and accept that the pre-check is advisory. And like unique-index waits, multi-constraint write paths can deadlock if two transactions touch rooms in different orders, so keep your batch booking code sorted by room_id; we adopted that rule after one ugly three-way deadlock during a conference-room import.
How does an exclusion constraint perform compared to an application-level check?
Correctly, which the application-level check does not — that is the headline, and the cost is smaller than the folklore says. Measured on our booking table at about two million rows: p95 insert latency went from 0.9 ms to 1.3 ms with the constraint, the difference being the GiST probe plus maintaining a deeper, wider index than an equivalent btree. Read latency did not move at all, because the availability query uses that same index and it got faster if anything — the overlap operator is exactly what the GiST index is for.
Now the honest comparison of correctness strategies. An application SELECT under read committed is simply broken for this problem; it is the bug we started with. SERIALIZABLE isolation catches the phantom via predicate locking, but you pay in serialization failures and retry logic across every endpoint that writes bookings — the retry design from the serializable isolation retry notes becomes a requirement, not an option. Advisory locks keyed by room_id work and are cheap, but every write path must remember to take them, forever, including the migration scripts and the support-console edits nobody reviews. The exclusion constraint is the only option that is correct for a writer you have not met yet. Budget for the GiST index's size — ours ran about three times the equivalent btree — and for slightly heavier writes, and spend the savings on never debugging a double-booking again.
What breaks when you partition a table with an exclusion constraint?
The constraint must include every partition-key column, compared with equality — and that requirement quietly decides whether your partitioning plan survives contact with overlap prevention. Say you range-partition booking by month of the start time to make retention drops cheap. The exclusion constraint now has to include that partition key with the equality operator, which means overlap is only enforced among rows sharing the same partition-key value. A booking that starts on March 31 and ends on April 2 spans a partition boundary, and no exclusion constraint on the partitioned table can forbid a conflicting row on the other side of it. You have rebuilt the race window along the partition edges.
My advice is boring and battle-tested: do not partition a constraint-protected table until its size forces you to. Our booking table held forty million rows on a single partition with the constraint, and insert latency was still fine, because the room_id equality prunes each GiST probe to one room's ranges — the index itself still grows with the table, but the per-insert conflict check does not. When we eventually needed retention-based drops, we partitioned by room group — a key already inside the constraint with equality — and kept cross-group overlap impossible by construction. Partition keys that are not naturally part of your exclusion predicate are a design smell, full stop.
How do you watch constraint-heavy tables with MonPG?
The operational signals are write latency, GiST index growth, and deadlock count — all three visible in counters long before a user reports anything. MonPG graphs per-statement latency from pg_stat_statements so the 0.4 ms GiST tax shows up as a measured line rather than a guess, per-index size so the constraint's index is baselined like any other, and the deadlocks column from pg_stat_database in case your write ordering slips. Exclusion violations themselves live in the application logs as 23P01 — they are domain events, not database illness — but the lock waits behind a contested room do surface in the database, and MonPG's PostgreSQL monitoring puts those waits next to the insert latency that causes them. If your table also leans on deferrable foreign keys, the interaction is worth a read of the deferrable constraints field notes before you mix the two in one write path.