How to Prevent Double Bookings: Let the Database Have the Last Word

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.

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

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 constraint

23P01

what our API answers then: the slot is taken

409

half-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 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.

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, 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: write down which rules must never break, and put each one where it cannot be skipped.