Let Postgres refuse the double booking
If two people can book the same court, room or lane for the same hour, your availability check will let both through sooner or later. One constraint in Postgres refuses the second overlapping booking, whatever wrote it: the API, an admin screen, or a backfill script that never called your availability check.
The constraint is the easy part. Cancelled bookings are where it gets subtle. A cancelled row has to free its slot, un-cancelling it has to be checked again, and the new shorthand in Postgres 18 can’t do the first of those.
I built a small booking table to show the race, the fix and the cancellation cases. Everything below ran on PostgreSQL 18.6 in a throwaway container.
The check that races with itself
Most booking code does the same two steps. Look for an overlapping live booking. If there isn’t one, insert.
-- the app-style check, as one block
IF EXISTS (
SELECT 1 FROM bookings
WHERE resource_id = 7
AND status <> 'cancelled'
AND during && tstzrange('2026-11-20 18:00+00', '2026-11-20 19:00+00')
) THEN
RAISE EXCEPTION 'slot taken';
END IF;
PERFORM pg_sleep(0.2); -- stands in for picking a resource, calling a payment API
INSERT INTO bookings (resource_id, guest, during) VALUES (7, ..., ...);
The table is plain: resource_id, guest, during as a tstzrange, and a status of confirmed or cancelled.
I sent two of those at once, one for alice and one for bob, same resource, same hour. Both read an empty slot. Both inserted.
id | guest | during | status
----+-------+-----------------------------------------------------+-----------
1 | bob | ["2026-11-20 18:00:00+00","2026-11-20 19:00:00+00") | confirmed
2 | alice | ["2026-11-20 18:00:00+00","2026-11-20 19:00:00+00") | confirmed
I ran it 10 times on an empty table and got a double booking 10 times. The sleep is what makes it that reliable. In production the gap is however long resource picking or a payment call takes, so you’ll see it rarely. It shows up when two people want the last slot at the same moment.
Wrapping the check in a transaction doesn’t help. Under the default isolation level (read committed), neither transaction can see the other’s row until it commits, so both checks pass.
An app-level lock only helps if it’s keyed on the slot. A create lock keyed by the guest’s email stops one person double-clicking. Two different guests take two different locks and race exactly like above. And any code path that skips the lock (an admin screen, an import) skips the protection with it.
The constraint
An exclusion constraint says no two rows may match on all the listed operators at once. Here that means the same resource (=) and overlapping times (&&), counting only live bookings.
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING gist (resource_id WITH =, during WITH &&)
WHERE (status <> 'cancelled');
btree_gist is there so the GiST index can do plain equality on the integer resource_id. It’s one of the extensions that come with Postgres, and it’s marked trusted, so a role with CREATE on the database can install it without a superuser.
Same race, 10 more runs, constraint in place: 0 doubles. One of the two inserts wins, the other gets this:
ERROR: conflicting key value violates exclusion constraint "bookings_no_overlap"
DETAIL: Key (resource_id, during)=(7, ["2026-11-20 18:00:00+00","2026-11-20 19:00:00+00"))
conflicts with existing key (resource_id, during)=(7, ["2026-11-20 18:00:00+00","2026-11-20 19:00:00+00")).
The second insert doesn’t fail early on a guess, either. I held the first transaction open for 2 seconds and sent the second insert half a second in. It waited. When the first committed, the second failed with the error above. When the first rolled back instead, the second went through. So a booking whose transaction rolls back, a failed payment inside it for example, doesn’t take the slot with it.
Cancelled bookings
The WHERE (status <> 'cancelled') is what lets a cancelled row sit in the table without holding its hour. I put a cancelled booking on the taken 18:00 hour: allowed.
Then I flipped it back with UPDATE bookings SET status = 'confirmed': refused, with the same error. An un-cancel is an UPDATE, and a check written for the create path never runs on it. The constraint checks every write that makes a row live, whichever statement did it.
The same goes for moving a booking. I moved a 19:00 booking an hour earlier, onto the taken hour: refused.
The rest behaved as you’d want:
- A plain
INSERTwith no availability check at all, 18:30 to 19:30 over the 18:00 to 19:00 booking: refused. - Back to back, 19:00 to 20:00 right after 18:00 to 19:00: allowed. The two-argument
tstzrange()builds[)ranges, lower bound in, upper bound out (range types), so they don’t overlap. - The same hour on another resource: allowed.
Why not Postgres 18’s WITHOUT OVERLAPS
Postgres 18 adds UNIQUE (resource_id, during WITHOUT OVERLAPS). The CREATE TABLE docs say it behaves like the same exclusion constraint, and it also needs btree_gist for an integer column.
It takes no WHERE. I tried, and got a syntax error. So a cancelled booking keeps holding its slot: my insert of a cancelled row on a taken hour was refused. If cancelled bookings stay in the same table, use EXCLUDE ... WHERE.
Turn the error into a normal answer
The SQLSTATE is 23P01, exclusion_violation (error codes). Catch that one code where you write bookings and answer the way you’d answer a taken slot today:
try {
await db.query(insertBooking, params);
} catch (err) {
if (err.code === '23P01') {
throw new ConflictError('That time was just booked. Pick another one.');
}
throw err;
}
Keep the availability check in the app too. It’s still the fast path that shows free slots and turns away most taken slots before a write.
Before you add it: find the overlaps you already have
If the table already holds an overlap, the ALTER TABLE fails. Mine did, with the pair left from the last unprotected run:
ERROR: 23P01: could not create exclusion constraint "bookings_no_overlap"
So run this first, on your live bookings, this week. It lists every pair of live bookings on one resource whose times overlap:
SELECT a.resource_id, a.id AS first_id, b.id AS second_id,
a.during * b.during AS overlap
FROM bookings a
JOIN bookings b
ON b.resource_id = a.resource_id
AND b.id > a.id
AND b.during && a.during
WHERE a.status <> 'cancelled'
AND b.status <> 'cancelled'
ORDER BY a.resource_id, a.id;
Every row is two customers who both think they have that slot. Sort those out with whoever owns the customers, cancel or move one booking of each pair, and run the query again. Empty means you can add the constraint. Adding it builds a GiST index on the table (exclusion constraints), so on a big table pick a quiet hour.
Keep the query after that too, as a scheduled check, for any booking data the constraint can’t cover yet: a second table, a resource stored in a join table, a time zone stored apart from the range.