# How to Prevent Double Bookings With a PostgreSQL Exclusion Constraint | Alher Tech

> Why checking availability in code is not enough, how an EXCLUDE constraint with tstzrange makes overlapping bookings impossible, and the design of the booking system we run.

- Canonical page: https://alhertech.com/en/blog/how-to-prevent-double-bookings/
- Site: Alher Tech (custom software, AI agents and SEO engineering, https://alhertech.com/)
- Contact: https://alhertech.com/en/contact/

---

Software · Oct 25, 2026 · 7 min read

Two people open the same booking page, pick the same Tuesday at 10:00 and press Book within a few milliseconds of each other. The code checks whether the slot is free, finds it free both times and saves both. This is the most common bug in booking systems and it never shows up in a demo, because a demo has one user. In the booking system we run ourselves, the one behind our own call booking page, it cannot happen: not because the code checks carefully, but because the database refuses the second booking.

## Why checking first is not enough

The obvious version reads the bookings, decides the slot is free and inserts the new one. Each step is correct. The problem is the gap between them: two requests can both read before either writes, and both see a free slot.

| Moment | Request A | Request B |
| --- | --- | --- |
| 1 | Reads the bookings for Tuesday: 10:00 is free |  |
| 2 |  | Reads the bookings for Tuesday: 10:00 is free |
| 3 | Inserts its booking at 10:00 |  |
| 4 |  | Inserts its booking at 10:00 |
| Result | Confirmed | Confirmed as well: two people, one slot |

Making the check faster, adding a cache or moving it to the front end only makes the window smaller. Under load, or with a retrying client, a small window still opens. The fix is to stop asking the application to guarantee something it cannot see: whatever else is being written at the same instant.

## The rule, written into the database

Two visitors ask the Rust API for the same 10:00 slot; both inserts reach PostgreSQL, where the exclusion constraint accepts the first one (201, booked) and rejects the second with error 23P01, which the API answers as 409 SLOT_TAKEN.
Two requests, one slot: the database is the referee.
PostgreSQL can enforce "no two confirmed bookings overlap" by itself, with an exclusion constraint. A unique constraint says that no two rows may be equal; an exclusion constraint generalises that to any operator, here && (overlaps) between two time ranges. This is the table, taken from our migration:

create extension if not exists btree_gist; create table bookings ( id uuid primary key default gen_random_uuid(), starts_at timestamptz not null, ends_at timestamptz not null, status text not null default 'confirmed' check (status in ('confirmed', 'cancelled')), -- ... constraint bookings_ends_after_start check (ends_at > starts_at), constraint bookings_no_overlap exclude using gist (tstzrange(starts_at, ends_at) with &&) where (status = 'confirmed') ); - tstzrange(starts_at, ends_at) is the booking as a range of instants. Its default bounds are [), closed at the start and open at the end, so a booking from 10:00 to 10:30 and the next from 10:30 to 11:00 do not overlap and both fit. - with && means two rows conflict when their ranges overlap. PostgreSQL checks it with a GiST index, which it builds for the constraint, so the check stays fast as the table grows. - where (status = 'confirmed') limits the rule to live bookings: cancelling one frees its slot without deleting the row, which keeps the history. - btree_gist is the extension that lets GiST index ordinary values next to ranges. With one calendar it is not strictly needed; with several resources it is (below). Now replay the two requests. Both read a free slot, both insert, and PostgreSQL accepts the first and rejects the second with error 23P01, exclusion_violation. The race has not gone away; it simply has a referee.
PostgreSQL's code when a row breaks the exclusion constraint23P01what our API answers then: the slot is taken409half-open ranges, so a booking can start when another ends[ )
## From a database error to a clear answer

A constraint violation is only useful if the application turns it into something a person understands. Our API (Rust, with sqlx) maps that one code to a conflict and lets any other database error through as what it is:

const EXCLUSION_VIOLATION: &str = "23P01"; fn slot_taken_on_overlap(error: sqlx::Error) -> ApiError { let overlapped = error .as_database_error() .and_then(|database| database.code()) .is_some_and(|code| code == EXCLUSION_VIOLATION); if overlapped { return ApiError::slot_taken(); // 409 SLOT_TAKEN } error.into() }The web branches on the status, never on the message: on a 409 the [booking page](https://alhertech.com/en/book-a-call/) says the slot has just been taken and reloads the free ones, so the visitor picks another without starting over. Moving a booking to another time goes through the same checks and is an update under the same constraint, so it cannot land on someone else either.

## What the application still checks first

The constraint is the last word, not the only one. Before inserting, the API works out whether the requested start is bookable at all, and those rules belong in the application because they change with settings and with the outside world:

- Working hours per weekday, a minimum notice, how many days ahead can be booked, a maximum per day and blocked dates, all of them settings in the panel rather than code.
- A buffer around every booking (15 minutes by default), so two calls are never back to back.
- What the team's Google Calendar says is busy. If the calendar cannot be read, the API logs it and offers slots from the bookings alone, rather than closing the page.
- No more than five bookings an hour from the same client, which answers 429 and keeps the calendar from being filled by a script.

**Two layers, two jobs**

The application decides what should be offered and gives a good answer fast. The database guarantees the one thing that must never happen. If they ever disagree, the database wins, and that is the point.

## What the constraint does not cover

Being precise about the limits is part of the design. Our constraint guards the bookings themselves, not the buffer: the buffer is checked in the application. Two requests racing for neighbouring slots could, in theory, both be confirmed closer together than the buffer. They would never overlap, which is the failure that matters, and we accepted that trade-off on purpose, because the buffer is a setting and a constraint would freeze it into the schema.

If the buffer had to be absolute, the range in the constraint can carry it: tstzrange(starts_at - interval '15 minutes', ends_at + interval '15 minutes'). The price is a migration every time the buffer changes. The same reasoning applies to the daily maximum: it is a policy, enforced in code, not an invariant of the data.

## Holding a slot while someone pays is a different problem

A call is confirmed the moment it is saved. A rental is not: between choosing the dates and the payment going through there can be a minute or two, and during that time nobody else should be able to take the slot. In [Boathous](https://alhertech.com/en/blog/boathous-case-study/), the boat rental marketplace we built, the slot is held for five minutes while the customer pays, the check and the hold happen in one atomic step, and the payment's confirmation checks that the hold belongs to the person paying.

Whatever the store for the hold, three properties matter: checking and holding in one atomic operation, an expiry so an abandoned checkout releases the slot, and a final check at confirmation. A database-backed variant is to store the hold as a booking with its own status and expiry, covered by the same exclusion constraint, so the referee is the same for both.

## Several boats, rooms or people

Most real systems book more than one thing. The constraint then says that two bookings conflict only if they are for the same resource and their times overlap, and that equality on an ordinary column is exactly what btree_gist makes indexable next to the range:

exclude using gist ( boat_id with =, tstzrange(starts_at, ends_at) with && ) where (status = 'confirmed')If a booking system is the heart of what you are building, this is the kind of decision we make with the team in the first weeks of a [custom software project](https://alhertech.com/en/services/custom-software-development/): write down which rules must never break, and put each one where it cannot be skipped.

## Questions

### Can a unique index on the start time prevent double bookings?

Only if every booking has the same length and starts on the same grid. As soon as durations differ or a booking can be moved, two bookings can overlap without starting at the same instant, and a unique index will not see it. An exclusion constraint compares ranges, so it catches any overlap.

### Why not use SELECT ... FOR UPDATE?

A free slot has no row to lock, so locking the bookings you read does not stop someone inserting a new one. You would have to lock a parent row, such as the calendar or the boat, which works but serialises every booking for that resource and depends on every code path remembering to take the lock. The constraint holds even for the path that forgets.

### Would the SERIALIZABLE isolation level solve it?

Yes, PostgreSQL would abort one of the two transactions with a serialization failure (40001). But then every write must be ready to retry, and the rule itself is nowhere in the schema. The exclusion constraint states the rule once, next to the data.

### Does this work in MySQL?

MySQL has no exclusion constraints. The usual options there are locking a parent row per resource, or modelling the calendar as a table of fixed slots with a unique key per slot and resource.

## Sources

- [PostgreSQL: Exclusion constraints](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION)
- [PostgreSQL: Range types](https://www.postgresql.org/docs/current/rangetypes.html)
- [PostgreSQL: btree_gist](https://www.postgresql.org/docs/current/btree-gist.html)
- [PostgreSQL: Error codes (23P01 exclusion_violation)](https://www.postgresql.org/docs/current/errcodes-appendix.html)

## Keep reading

- [Claude Code Fixes Our Failed Deployments, and a Person Merges the Fix](https://alhertech.com/en/blog/claude-code-fixes-failed-deployments/)
AI · Oct 26, 2026 · 10 min read

In Fundaia, our own deployment platform, a failed deployment has a Fix with Claude button. How the agent diagnoses, reproduces and fixes inside a container that can only edit files, what the platform checks before opening the pull request, why it never merges it and what happens when the agent cannot fix anything.

- [Leaderbrock Case Study: A Sales Team App for the Agenda, the Route and Receipts](https://alhertech.com/en/blog/leaderbrock-case-study/)
Case studies · Oct 26, 2026 · 11 min read

Leaderbrock's sales team works across Europe and America. How we replaced a shared spreadsheet, an envelope of receipts and the lack of any map with one reactive Java backend, an Expo app and a web panel: expenses from a photo that AI drafts and a person files, an agenda in the phone's time zone and a GPS route that tells a flight from noise.

- [ReservPark Case Study: The Phone That Knows You Are Leaving Your Parking Spot](https://alhertech.com/en/blog/reservpark-case-study/)
Case studies · Oct 26, 2026 · 12 min read

ReservPark notices through the car's Bluetooth that you parked, asks whether you are leaving when you walk back to the car and tells drivers nearby, and lets people rent out their garage by the hour or by subscription. How we built the native detection, the background location rules, the 30-minute free spots and garage bookings that cannot be oversold.

[Blog](https://alhertech.com/en/blog/)