A booking system must prevent two reservations for the same resource at overlapping times. The obvious implementation queries for conflicts, finds none, and inserts.
Two requests arriving simultaneously both find no conflict and both insert. The double booking exists, and no amount of care in the application prevents it — the gap between checking and inserting is where it happens, and that gap cannot be closed in application code alone.
What changed in 2026
- Range types became the standard model. Representing an interval as a single range column, rather than two timestamps, became normal practice where supported.
- Scheduling systems adopted them. Booking, resource allocation, and calendaring workloads moved to database-enforced overlap prevention.
- Multi-tenant patterns spread. Combining equality on a tenant column with overlap on a range became a common shape.
- Engine support stayed uneven. Availability remained concentrated in engines with rich type systems, so portability requires checking.
Beyond equality
A unique constraint asks whether two rows have equal values for some columns, and rejects the second if so.
An exclusion constraint generalises that: it asks whether two rows relate by any operator you specify, and rejects the second if they do. Equality is one option; range overlap is the useful one.
So the rule "no two rows may have the same room and overlapping time ranges" becomes a constraint combining an equality test on the room and an overlap test on the range. The database enforces it across concurrent transactions the same way it enforces uniqueness.
| Rule |
Mechanism |
| No duplicate email |
Unique constraint |
| No duplicate active email |
Partial unique index |
| No overlapping bookings per room |
Exclusion constraint |
| No overlapping validity periods per record |
Exclusion constraint |
| No two rows within a distance of each other |
Exclusion constraint, spatial operator |
Model the interval as a range
The practical enabler is representing the interval as a single range value rather than separate start and end columns.
Two separate timestamp columns make overlap a condition you write out, and there is no operator the constraint can use directly. A range type gives you an overlap operator the database understands and can index.
Ranges also settle the inclusive-exclusive question explicitly. A booking ending at 10:00 and another starting at 10:00 do not overlap if the ranges are half-open, which is almost always the intended behaviour and a recurring source of off-by-one bugs when modelled as separate columns.
Where ranges are unavailable, a generated column combining two timestamps into a range is a workable bridge — see generated columns.
What it needs and costs
Exclusion constraints are backed by an index supporting the operators involved — typically a generalised search tree index rather than a B-tree, since B-trees only understand ordering.
That has consequences. The index is larger and slower to write than an equivalent B-tree, and the constraint check on insert is more expensive than a uniqueness check. On a high-write table with many overlapping candidates, that cost is real.
It is still dramatically cheaper than the alternatives: locking the whole table around every insert, or serialising bookings through application-level locks, both of which sacrifice concurrency to achieve what the constraint does natively.
A practical note on error handling: a violation raises an error, so the application must catch it and present something sensible. A user attempting to book a taken slot should see a clear message, not a stack trace — which means the constraint name matters, per check constraints.
Common mistakes
- Check-then-insert in application code. The race the constraint exists to close.
- Separate start and end columns. No operator to constrain on.
- Inclusive ranges where half-open was meant. Adjacent intervals falsely conflict.
- Forgetting the tenant or resource column. Prevents all overlaps globally rather than per resource.
- Not handling the violation error. Users see a raw database error.
- Using one where a unique constraint suffices. Heavier for no benefit.
- Assuming portability. Support varies considerably by engine.
FAQ
Does this work across transactions?
Yes — that is the point. The database enforces it against concurrent transactions the same way it enforces uniqueness, closing the race that application checks cannot.
Can I allow overlaps in some cases?
Combine it with a partial index condition so the constraint applies only to rows meeting a predicate — cancelled bookings excluded, for instance. Same reasoning as partial indexes.
What if my database does not support them?
The fallbacks are serialisable isolation with retry, or explicit locking on a parent row. Both work and both cost concurrency — see database isolation levels.
Are they slow?
More expensive than a unique constraint, and far cheaper than the locking strategies they replace. Measure on your write volume.
Where to go next
For simpler constraint types, read check constraints and partial indexes. For the concurrency problem they solve, database isolation levels.