EXCLUDE USING gist (vehicle_id WITH =, tstzrange(starts_at, ends_at, '[)') WITH &&) makes the overlapping row unwritable, so no code path can create one — not the API, not the importer, not the admin console, not the endpoint nobody has written yet. Application validation fails for two independent reasons, and only the first is obvious: it has to be repeated everywhere, and check-then-write is racy by construction even where it is present.
Why the validator cannot be made correct
The usual implementation of “this car cannot be rented to two people at once” is a lookup followed by a decision: select any rental for this vehicle whose dates overlap the requested ones, and refuse if a row comes back. It reads correctly, it passes review, and it is wrong in two ways.
The first is coverage. The check lives in one code path, and the number of paths that write a booking only grows: the public API, the operator's own screen, an Excel importer, a super-admin console, a data-repair script someone runs at two in the morning, and whatever gets added in eighteen months by a developer who never read this file. Every one of them needs the check. One of them will not have it.
The second is worse, because it survives perfect discipline. Two requests arriving at the same moment both run the lookup, both see no conflict, and both insert. Nothing in either transaction is aware of the other's uncommitted row, so both succeed and the calendar now contains a state the business considers impossible. Under the default READ COMMITTED isolation level this is not an edge case that needs unlucky timing over milliseconds — it is the ordinary behaviour of two concurrent writers, and it is exactly the failure that turns up on the busiest morning of the year rather than in testing.
A double booking is not a bad user experience. It is a customer standing in a car park in front of a vehicle that is already gone, and an operator choosing which of two paying people to disappoint.
The constraint, in full
PostgreSQL can express “no two rows may overlap” directly, which turns the rule from something the application tries to uphold into something the storage layer refuses to break.
-- GiST indexing over ranges; btree_gist is what lets a plain
-- equality column (vehicle_id) sit in the same index as a range.
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE rentals ADD CONSTRAINT rentals_no_overlap
EXCLUDE USING gist (
vehicle_id WITH =,
tstzrange(starts_at, ends_at, '[)') WITH &&
)
WHERE (status <> 'cancelled');If vehicle identifiers are only unique within a tenant — sequential per customer rather than a uuid — add tenant_id WITH = as a third element. Leaving it out in that schema means two different customers' vehicle number 4 are treated as one car, which is a cross-tenant bug in the direction that refuses valid bookings rather than allowing invalid ones. Loud, but still wrong.
Why the range is half-open
The '[)' argument makes the range include its start and exclude its end. With the default '[]', a rental ending at 14:00 and the next starting at 14:00 share the instant 14:00, so they overlap, so the second one is refused — and every back-to-back handover in the business becomes impossible. This is the detail that gets discovered in production, because a test suite that books Tuesday and Thursday never touches it.
Why the constraint is partial
The WHERE clause keeps cancelled rows out of the index, so a cancellation frees the slot without deleting the history of what was cancelled and by whom. Without it there are only two options: keep the row and have the cancelled booking block the calendar forever, or delete the row and lose the audit trail. A partial constraint is what lets a soft delete stay soft.
The same clause is where a status vocabulary earns its keep. Whatever set of states means “this booking no longer holds the asset” belongs in that predicate, and it should be reviewed every time a new status is added — a new 'no_show' value that nobody adds to the clause will silently keep blocking the slot.
Why btree_gist is not optional
A GiST index natively indexes the range side. It does not natively handle a scalar compared with =, and the constraint needs both in one index. btree_gist supplies the operator classes that make that legal; without the extension the ALTER TABLE fails outright, which is the right time to find out. It is a contrib module shipped with PostgreSQL, so this is a statement to run rather than a dependency to argue about, though on a managed database it is worth confirming the extension is on the provider's allow-list before the migration reaches production.
What the application sees when it fires
A refused write arrives as SQLSTATE 23P01, exclusion_violation — the same class 23 as a unique or foreign-key violation, with the constraint name, the table and the conflicting values in the message. Two things follow from that.
First, it is a 409 Conflict, not a 500. The request was valid and the answer is that the slot is taken; returning a server error for it both misleads the client and buries a normal outcome in your error dashboard. Match on the SQLSTATE and the constraint's name — never on the prose around them, which is localised and changes between versions.
Where those two tokens are readable depends entirely on the client, and this is the step worth checking rather than assuming. A driver that speaks the wire protocol hands the code over directly: node-postgres puts it on err.code. An ORM may not. Measured against Prisma 6.1, an exclusion violation on the ORM path arrives as PrismaClientUnknownRequestError — no code, no meta — carrying code: "23P01" and the constraint's name inside the message string. A guard written against PrismaClientKnownRequestError therefore never fires, and the race path answers 500 while the normal path keeps returning a clean 409 from the pre-check, so nothing looks wrong until the day it matters.
// The constraint is the authority. This block is only translation.
const isExclusionViolation = (e, constraint) => {
// Both tokens are stable: 23P01 is a SQLSTATE, the name is one you chose.
const text = `${e?.message ?? ''} ${JSON.stringify(e?.meta ?? '')}`
return text.includes('23P01') && text.includes(constraint)
}
try {
return await this.rentals.create({ data })
} catch (e) {
if (isExclusionViolation(e, 'rentals_no_overlap')) {
throw new ConflictException('This vehicle is already booked for part of that period')
}
throw e
}err.code === '23P01' in node-postgres — match that instead and skip the string handling entirely. Either way the mapping deserves a test, because the shape belongs to the client library and can change under you.Second, keep the friendly pre-check anyway — but demote it. Showing a user that a period is unavailable before they submit is good interface design; it is not the mechanism that keeps the data true. One is a courtesy that can be wrong under concurrency, the other is a guarantee that cannot. Teams that conflate the two end up deleting the constraint because “the API already checks”.
Turnaround time, and the buffer you must not compute in the constraint
Most real booking domains need a gap rather than a touch: a car needs cleaning, a room needs turning over, a machine needs an inspection. The tempting move is to add the buffer inside the constraint's expression — and PostgreSQL will refuse it. An index expression has to be IMMUTABLE, and arithmetic on timestamptz is where that rule bites, because the result can depend on the session's time-zone setting.
So materialise it at write time instead. Either store the buffered end as a generated column, or have the application write an explicit range column that already includes the turnaround, and build the constraint on that. It is one more field and it makes the rule visible in the data: a row shows the period the asset is genuinely unavailable, which is also the period the calendar should be drawing.
When one resource is really five
An exclusion constraint answers “do these two rows conflict”. It cannot express “at most five of these may overlap”, because it has no way to count. The clean fix is to stop modelling capacity and start modelling units: five identical bikes are five rows, and each one inherits the same guarantee. Fleets are naturally like this — a rental company rents a specific registration plate, not an abstract compact car — which is why the constraint fits the domain so exactly.
Where the units genuinely are interchangeable and you will not model them individually, the honest alternative is a SERIALIZABLE transaction that counts and then inserts, with retry handling for serialization failures. That is more moving parts for a weaker promise, and it is worth being clear-eyed that the reason to accept it is a product decision about inventory, not a database preference.
The alternatives, and when each one is right
| Approach | Right when | What it costs |
|---|---|---|
| Exclusion constraint over a range | Periods are arbitrary and each resource is bookable once at a time | A GiST index maintained on write, and one extension |
UNIQUE (resource_id, slot) |
Time is genuinely discrete — fixed 30-minute appointments, seat numbers, a day at a time | Nothing. If the domain is discrete, this is simpler and you should prefer it |
SELECT … FOR UPDATE on the parent row |
The rule cannot be expressed in the schema, or you are on a database without exclusion constraints | Every writer for that resource serialises, and the discipline holds only where it is remembered |
| Advisory locks | The conflict key is computed rather than stored, so there is no row to lock | Same coverage problem as a validator, plus a hashing scheme to get right |
SERIALIZABLE isolation |
The invariant spans several rows or tables — counting capacity, budgets, quotas | Retry logic on serialization failure in every writer, and it must actually be tested |
The ranking is not a matter of taste. Prefer whichever option makes the bad state unrepresentable rather than merely unlikely, and prefer the one that does it in the fewest places: a constraint lives once, next to the data, and applies to writers that do not exist yet. A lock or a check lives in every writer, forever, including the ones written after everyone who read this has left.
What it costs to run
The write path pays for an index probe per insert or update of a covered row, which is the same order of cost as the unique constraint nobody argues about. The read path can benefit: a query that asks for overlaps using the same expression the constraint indexes — tstzrange(starts_at, ends_at, '[)') && $1 — can use that index, and “what is booked this week” is the query a fleet calendar runs on every page load.
The cost that actually shows up is at deploy time rather than at runtime: adding this constraint to a table that already contains overlapping rows fails, correctly and completely. On an existing system the migration is therefore two steps — find the conflicts and decide what they were, then add the constraint — and the first step is usually the interesting one, because those rows are a list of the double bookings the business has already lived through.
Where this one runs
The statement above is not a blog example. It is in ArendaOS, our own live product for car-rental and fleet-rental operators, which is why the product page is allowed to state zero double bookings as an absolute rather than as an intention: the claim is about an enforced invariant, and a reader can go and try to break it. The full delivery story is the ArendaOS case study, and the two decisions either side of this one — tenancy in the schema, and the money model before the documents — are in what building a multi-tenant SaaS actually takes.
If you are building the same shape of product, the service page is SaaS development, and the published bands for that kind of work are on the pricing page.