Stop double bookings at the database level, not just in code
Short answer: Checking "is this slot free?" in your app code and then inserting the booking leaves a gap: two requests can both check, both see the slot free, and both insert. In PostgreSQL you can close the gap in the database itself. Store each booking's time as a range, then add an exclusion constraint that forbids two rows for the same resource with overlapping ranges. The database then rejects the second booking, however close together the requests arrive, and your app shows a friendly "that slot was just taken" message.
Why app-level checks aren't enough
The usual booking code looks like this:
- Query for existing bookings for this room between 2pm and 3pm.
- If none, insert the new booking.
On a quiet site that works. But when two customers click "Book" for the same slot at almost the same moment, both requests run step 1 before either runs step 2. Both see no conflict; both insert. You find out when two groups turn up.
Locks, serialisable transactions or a queue can fix this in application code, but they're easy to get subtly wrong, and every new path that creates bookings, such as an admin screen, an import or an API, must remember to use them. A database constraint protects every path at once.
Step 1: store times as a range
PostgreSQL's range types represent a span of values in a single column. Its documentation calls scheduling the clearest example. For bookings, a timestamp range with time zone (tstzrange) is a natural fit:
CREATE TABLE bookings (
id serial PRIMARY KEY,
resource_id integer NOT NULL,
during tstzrange NOT NULL,
status text NOT NULL DEFAULT 'confirmed'
);
Ranges have bounds that can be inclusive or exclusive. The documentation notes that [) (start included, end excluded) is the default form when constructing ranges. That's what you want for bookings: a booking from 2:00 to 3:00 and another from 3:00 to 4:00 don't overlap.
Step 2: add the exclusion constraint
PostgreSQL's constraints chapter explains that an exclusion constraint ensures that, for any two rows compared on the given columns with the given operators, at least one comparison returns false. Its range types chapter shows the classic example: an exclusion constraint using the overlap operator && prevents overlapping reservations, and inserting an overlapping one fails with "conflicting key value violates exclusion constraint".
You usually need "no overlap for the same room", not "no overlap at all". The docs show combining equality on a column with range overlap using the btree_gist extension:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
EXCLUDE USING gist (resource_id WITH =, during WITH &&)
WHERE (status = 'confirmed');
The WHERE clause means cancelled bookings don't block the slot. The constraint creates the index it needs automatically, so checking is fast.
Step 3: handle the error kindly
When the constraint rejects a booking, your app receives an error. Catch that specific constraint violation and show the customer something useful: "Sorry, that time was just booked. Here are the nearest free slots." Don't show a generic error page, and don't retry automatically with the same slot.
Log these events. A handful is normal on a busy system; many may mean your availability display is stale.
Test it with simultaneous requests
Prove the protection works before customers do. Write a small script that sends two booking requests for the same resource and time at exactly the same moment, run it a few dozen times against a staging copy, and confirm that exactly one succeeds each time while the other gets your friendly message. Repeat through each path that creates bookings: website, admin screen and any API or import. If anything slips through, that path is bypassing the table or writing a different status.
Buffers between bookings
Need 15 minutes to clean a room between bookings? Store the range including the buffer, for example ending at 3:15 for a 3:00 finish, or keep a separate "blocked until" range used only for the constraint. Either way the database enforces the gap.
Existing data
Adding the constraint fails if the table already contains overlaps. Find them first with a self-join on overlapping ranges for the same resource, sort them out with staff, then add the constraint. It's a useful audit in its own right.
Other databases
Exclusion constraints and range types are PostgreSQL features. With other databases, typical approaches include locking the resource row inside a transaction before checking and inserting, or splitting time into fixed slots and using a unique constraint on resource plus slot. Fixed slots work well when bookings always align to, say, 30-minute blocks.
Keep your availability display honest
The constraint guarantees no double bookings; it doesn't make your calendar view current. If customers often lose slots at the last step, consider holding a slot for a few minutes while they check out, stored as a temporary booking with an expiry, which the same constraint also protects.
A worked example
An illustrative Wanaka kayak hire business took bookings for individual kayaks online and by phone through an admin screen. On busy summer mornings, the same kayak was sometimes booked twice because two requests overlapped. The developer converted start and end columns into a tstzrange, added an exclusion constraint on kayak and time for confirmed bookings, cleaned up a few historic overlaps, and changed the booking page to suggest alternatives when the constraint fired. Double bookings stopped across the website, the admin screen and imports.
FAQ
Does the constraint slow down bookings?
It uses an index, so checks are quick for typical booking volumes.
What about all-day or multi-day bookings?
Use date ranges (daterange) or timestamps covering the whole period.
Can a booking cover several resources?
Store one row per resource, so each is protected by the constraint, and insert them together in a transaction.
Is this enough on its own?
It's the safety net. Your app still needs good availability displays and clear messages.
If your booking system has ever double-booked, tell us how bookings are stored and we'll help you make overlaps impossible. You can read how we approach booking and ordering apps on our page about web app development in Vienna.