Double-Booking in Postgres: The Availability Check Is Not a Reservation

The availability check works. You query the bookings table for anything overlapping the dates the guest picked, find nothing, insert the row, and show a confirmation. You test it by booking a room, trying to book it again, and getting the "not available" message you expected. It is correct on every run you have ever done.

A read followed by a write is not a check. It is two statements with a gap in the middle, and the gap is where the second guest gets in. Two requests arriving in the same second both run the query, both see an empty result, and both insert. Neither one is wrong about what it read. The room is now sold twice, and nothing in your logs looks like an error.

Payment capture, refunds and who eats the fee are a different post's problem — this one stops at the row. What follows is how to make checking availability and taking it the same operation, in Postgres, with no lock you have to remember to take.

It only reproduces when two people tap at once

The reason this survives testing is that you are one person with one pair of hands. Between your first tap and your second, the first transaction has long since committed, so the second query sees it. The bug needs two transactions in flight simultaneously, which is a thing your test never does and your launch day does constantly.

Wrapping both statements in a transaction does not fix it either, which is the part that catches people who know to reach for one. At Postgres's default read-committed isolation, a transaction cannot see rows another transaction has inserted but not yet committed. Both sessions read an empty result inside a perfectly valid transaction, and both commit. A transaction gives you atomicity, not exclusion.

You can escalate to SERIALIZABLE and have Postgres detect the conflict, which works, at the cost of handling serialisation failures (40001) with a retry loop everywhere you touch bookings. There is a narrower tool built for exactly this shape, and it is one line of DDL.

A booking is a range, not two columns

Two timestamptz columns cannot be indexed for overlap, so every availability query becomes the same hand-written comparison, written slightly differently in each place, and one of them will get the boundary wrong. Postgres has a range type. Use it, and let the row derive it so the two representations can never disagree.

create extension if not exists btree_gist;

create table bookings (
  id          uuid primary key default gen_random_uuid(),
  room_id     uuid not null references rooms(id) on delete cascade,
  guest_id    uuid not null references profiles(id),
  starts_at   timestamptz not null,
  ends_at     timestamptz not null,

  -- Generated, not passed in. A caller cannot send a range that
  -- disagrees with the timestamps it is supposed to describe.
  during      tstzrange generated always as (
                tstzrange(starts_at, ends_at, '[)')
              ) stored,

  status      text not null default 'confirmed',
  created_at  timestamptz not null default now(),

  constraint ends_after_start check (ends_at > starts_at)
);

The '[)' is doing real work. It makes the range inclusive of the start and exclusive of the end, so a stay that ends at 11:00 and one that begins at 11:00 do not overlap — the checkout and the next check-in touch without colliding. The default for tstzrange is already '[)', but writing it makes the decision visible to the next person, and it is the decision that generates support tickets when it is wrong. Get it backwards with '[]' and every back-to-back booking in the system is rejected as a conflict.

Let the database refuse it

An exclusion constraint is a unique constraint where you choose the operator. A unique index says no two rows may be equal; an exclusion constraint says no two rows may overlap. It is enforced by the same machinery, at the same moment, with the same guarantee — which means it holds no matter how many application servers you run, and no matter who writes to the table next year.

alter table bookings
  add constraint bookings_no_overlap
  exclude using gist (
    room_id WITH =,
    during  WITH &&
  )
  where (status <> 'cancelled');

Read it as a sentence: no two rows may have the same room_id and an overlapping during, unless one of them is cancelled. The btree_gist extension at the top of the migration is what allows an equality test on a plain uuid to sit in a GiST index next to a range operator; without it the constraint is rejected at creation with a complaint about no operator class for the equals operator, which is the single most common thing to get stuck on here.

The where clause is not decoration. Cancelled bookings have to stay in the table — you need the history, and the guest needs the record — but they must stop occupying the dates the moment they are cancelled. Without that clause, cancelling a booking frees nothing and the room is blocked forever by a row nobody is honouring.

The check and the write are now one statement. There is no window, because there is no gap between reading and writing to have a window in.

Catch 23P01, not every error

The insert that loses the race now fails. That failure is a product state — somebody else took it, half a second ago — and it deserves a real message rather than the generic something-went-wrong that a catch-all handler produces. Postgres raises exclusion_violation, SQLSTATE 23P01, and it is worth matching on the code rather than the message text, which is localised and changes between versions.

const { data, error } = await supabase
  .from("bookings")
  .insert({ room_id: roomId, guest_id: user.id, starts_at, ends_at })
  .select()
  .single();

if (error) {
  // 23P01 exclusion_violation: someone else holds these dates.
  // 23505 unique_violation: a duplicate idempotency key, which
  // means this is a retry of a request that already succeeded.
  if (error.code === "23P01") {
    return Response.json(
      { error: "unavailable" },
      { status: 409 },
    );
  }
  throw error;
}

return Response.json(data, { status: 201 });

The client handling of that 409 matters more than it looks. The guest has already picked dates, filled in a form and probably reached for a card, so returning them to an empty search is the wrong answer. Re-query availability and show what is actually left, with their dates still in the fields.

A hold is a booking that expires

The moment payment enters the flow, there is a gap you cannot design away: the guest is on the card screen, the dates are not yet paid for, and somebody else is looking at the same room. Leave the row uninserted until payment succeeds and you will sell the room out from under a guest mid-checkout. Insert it as confirmed before payment and you will fill the calendar with bookings nobody paid for.

A hold is the same row in a third state. It must occupy the range — that is its whole purpose — so it has to satisfy the same constraint, which it already does as long as its status is not 'cancelled'. What it needs on top is a deadline.

alter table bookings
  add column expires_at timestamptz;

-- A hold has a deadline; a confirmed booking does not. Enforcing
-- that here means an expiry sweep can never touch a paid stay,
-- however the query is written.
alter table bookings
  add constraint hold_has_expiry check (
    (status = 'pending' and expires_at is not null)
    or
    (status <> 'pending' and expires_at is null)
  );

create index bookings_pending_expiry
  on bookings (expires_at)
  where status = 'pending';

Confirming a payment is then an update that sets the status and clears the expiry in one statement, and the check constraint makes the half-finished version of that update impossible. A booking cannot be confirmed and still expiring.

Expiring the hold is a job, not a timer

The instinct is to expire the hold on the client, or with a setTimeout in the route handler that created it. Both are the same mistake in different clothes: the guest closes the app, the serverless function is frozen the moment it returns a response, the container is recycled, and the timer that was going to release those dates never runs. The room stays blocked, and nobody finds out until a host asks why their calendar is full and their revenue is zero.

Expiry belongs to the database, on a schedule.

create or replace function expire_stale_holds()
returns integer
language sql
as $$
  with released as (
    update bookings
       set status = 'cancelled',
           expires_at = null
     where status = 'pending'
       and expires_at < now()
    returning 1
  )
  select count(*)::int from released;
$$;

-- Every minute is fine. The hold window is measured in minutes,
-- and a stale row costs nothing until the next guest searches.
select cron.schedule(
  'expire-stale-holds',
  '* * * * *',
  $$ select expire_stale_holds(); $$
);

Note that the sweep does not have to be timely for correctness, only for conversion. If it runs a minute late, the dates are unavailable for one extra minute. If you want the calendar to be exact between sweeps, filter on expires_at in the availability query as well and treat the job as cleanup rather than as the source of truth.

The same job is where an abandoned-checkout email belongs, and where a payment webhook that arrives after expiry has to be reconciled — a capture that lands for a hold you already released is a refund, not a booking. That path is worth writing before launch rather than after the first one happens.

Nights are dates; slots are instants

There are two different booking shapes and they want two different column types, which is why generic advice about this is so often wrong for the app in front of you.

A hotel night is a date. The night of the fourteenth is the night of the fourteenth whether the property is in Lisbon or Bangkok, and a guest arriving at 23:00 has still booked that night. Model it with daterange and the whole timezone question disappears, because there is no instant involved to convert. Check-in and check-out times are operational policy, not the identity of the thing being sold.

A tour departure, a table at eight, a ninety-minute appointment — those are instants. They want tstzrange, and they want the property's IANA timezone stored next to them, because timestamptz keeps the instant and throws the zone away. Storing the offset instead of the zone name is the trap: '+02:00' is correct for Lisbon in August and wrong for Lisbon in November, and every recurring slot you generate across a daylight-saving boundary lands an hour out.

alter table properties
  add column timezone text not null default 'UTC';

-- An IANA name, not an offset. 'Europe/Lisbon' knows about
-- daylight saving; '+01:00' is a guess that is right in winter.
-- A check constraint cannot validate this, because it would need
-- to read pg_timezone_names and check constraints cannot contain
-- a subquery. Validate it in the admin form, against that view.

-- 09:00 local on a given day, resolved through the property's
-- own zone rather than the server's.
select (d + time '09:00') at time zone p.timezone
  from properties p, generate_series(
    current_date, current_date + 30, interval '1 day'
  ) as d
 where p.id = $1;

Mixing the two shapes in one table is how you end up with a hotel that cannot be booked on the last Sunday in October. Pick the one your product actually sells, and if it sells both, they are two tables.

Make the race the test

None of the above is worth anything on the claim that it works. It is worth something because you can prove it in about fifteen lines, and the proof fails loudly on any schema that does not have the constraint.

test("two simultaneous bookings, exactly one wins", async () => {
  const body = { roomId, startsAt, endsAt };

  // Sequential requests pass on a broken schema too. These have
  // to be genuinely in flight together.
  const responses = await Promise.all([
    post("/api/bookings", body),
    post("/api/bookings", body),
  ]);

  const codes = responses.map((r) => r.status).sort();
  expect(codes).toEqual([201, 409]);
});

Run it twenty times, not once — a race that fails one run in five is still a race, and a single green run proves nothing about a schema with no constraint on it. Then run it against any booking template you are thinking of buying, before you read a line of its source. It is the fastest way to find out whether the availability logic was written by someone who had shipped one.

Every other defect in a booking app is cosmetic next to selling the same room twice. Move the check into the schema, and it stops being something your code has to remember to do.