Iurii Guards: Preventing Double Booking in PostgreSQL Without EXCLUDE

Ask anyone who knows PostgreSQL how to stop two clients booking the same specialist at the same time, and the answer comes fast: an EXCLUDE constraint with btree_gist, one && on a tstzrange, done at the database level, no application code to trust. It’s the right answer to the question most people are asking. It’s the wrong answer to the one KRI, the booking SaaS I designed and built for beauty businesses, actually has.

The reason isn’t performance, and it isn’t that nobody thought of it. The rule KRI needs isn’t ‘no two bookings for this specialist may overlap’. It’s that a booking a client makes must not overlap any live booking for that specialist – whether the other one came from a client or the owner – while a booking the owner makes by hand, from their own dashboard, may overlap anything at all. Imagine a stylist starting a colour that needs twenty minutes to sit, and using that gap to take a quick trim for someone else: the owner needs to place that second booking directly on top of the first, on purpose. That’s not a bug in the schedule.

This order-dependent rule does not fit a plain UNIQUE or EXCLUDE constraint over the booking intervals. So the schema has no UNIQUE, no EXCLUDE, nothing at all on (specialist, starts_at) – just a plain index for lookups. KRI implements the client-facing guard in an application transaction. A database routine or trigger with appropriate locking could be another design; the choice here makes consistent use of the application path essential.

What Used to Be There, and Why It Wasn’t Enough

The table used to carry a UNIQUE(specialist, starts_at) constraint. It did catch something: two clients racing for the same displayed slot – the most common shape of this race, since KRI’s slot grid steps in fixed 10-minute increments – share an identical starts_at, and the old constraint rejected the second insert outright.

What it never caught was a partial overlap. A 60-minute service starting at 10:00 and a different booking starting at 10:30 don’t share a start time, so the unique index saw two different keys and let both through, even though the first booking is still running when the second one starts. Once services vary in duration, that’s routine, not rare. The constraint also blocked the one deliberate overlap the business needed, whenever two of the owner’s own bookings happened to share a start.

The transactional overlap check that exists today covers everything the UNIQUE constraint caught, plus the partial overlaps it couldn’t: an identical start is just the special case of two ranges overlapping completely. That makes the UNIQUE constraint redundant for every caller that goes through this transaction – but not for a caller that doesn’t. A write to the table that bypasses this transaction now has no backstop at all, not even the identical-start catch the old constraint gave for free.

The Guard That Replaced It

Client-facing booking runs as one transaction that does six things in order:

await db.transaction(async (tx) => {
  // Taken first, for a reason unrelated to double booking. As a side effect,
  // this outer lock is what actually serializes two client bookings in the
  // same salon.
  await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${"tenant:" + tenantId}))`);
 
  // Scoped to one specialist: coordinates this booking with the owner
  // editing this specialist's schedule, which takes the same key elsewhere
  // in the codebase.
  await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${"booking:" + specialistId}))`);
 
  // Re-read under the lock: the owner may be deactivating this specialist,
  // or setting their last working day, at this exact moment.
  const specialist = await getBookableSpecialist(tx, specialistId);
  if (!specialist || !isBookableOn(specialist, localDate)) {
    throw new BookingRejected(409, "not_working", "This time is not available");
  }
 
  const blocks = await workingBlocksFor(tx, specialistId, localDate);
  const fitsAWorkingBlock = blocks.some((b) => b.start <= startsAt && b.end >= endsAt);
  if (!fitsAWorkingBlock) {
    throw new BookingRejected(409, "not_working", "This time is not available");
  }
 
  // The overlap check itself. Live bookings only — a cancelled or no-show
  // slot doesn't block a new one.
  const [conflict] = await tx
    .select({ id: bookings.id })
    .from(bookings)
    .where(
      and(
        eq(bookings.specialistId, specialistId),
        lt(bookings.startsAt, endsAt),
        gt(bookings.endsAt, startsAt),
        LIVE_BOOKING
      )
    )
    .limit(1);
  if (conflict) throw new BookingRejected(409, "slot_taken", "This time was just taken");
 
  // Only now: upsert the customer, insert the booking, still in the same tx.
});

The end time never comes from the client – it’s derived server-side from the service’s duration, so nothing here trusts a caller’s arithmetic. Two client bookings for the same salon can’t run this transaction at the same time; the next section covers why, and what that buys.

A rejected request fails closed into a typed reason – not_working, slot_taken – mapped to a 409 the client can show as ‘this time was just taken’. Nothing here relies on a database-level uniqueness violation as a backstop, because there isn’t one to fall back on.

What Each Lock Is For

Two locks are taken above, and they do different jobs.

The salon-wide lock is taken first, and on its own it already serialises two client bookings for the same salon. Both transactions hold their lock until commit, so whichever request gets there first finishes its whole check-then-insert before the second one starts reading. The second request re-reads the specialist’s bookability flags and the day’s working blocks, and re-runs the overlap query, only after the first has committed.

The specialist-scoped lock underneath it isn’t what stops two client bookings for that person from racing – the outer lock already does that for the whole salon. Its job is to bring a different writer into the same serialisation: the owner editing that specialist’s schedule – deactivating them, setting a last working day, changing a recurring pattern – takes the same booking:<specialist> key. Without it, a schedule change and an in-flight booking could each act on a version of the specialist’s availability that the other has just invalidated. With it, a booking and a schedule edit for the same person can never interleave, whichever one gets there first.

One real cost falls out of this: the online booking path for one salon is fully serial. Every client booking for that salon, whatever the specialist, queues behind the salon lock one at a time. That’s fine for the volume a single salon sees – nobody fields thousands of concurrent bookings for one location – but the ceiling is per-salon throughput, not the database’s.

The advisory lock is a design choice here, not a necessity. The specialist has a real row in the schema, so SELECT ... FOR UPDATE on that row would serialise a booking and a schedule edit just as well as the hashed key does – nothing about this case rules a row lock out. What the advisory lock buys is a key that names only the resource that needs serialising, ‘bookings and schedule edits for this specialist’, without also locking out every other write to that row – the owner updating the specialist’s photo or name, say, which has nothing to do with double booking and shouldn’t wait behind it. What it costs in exchange is below.

A second, smaller cost: the lock key is hashtext() of a string, and hashtext returns a 32-bit integer – about four billion possible values, shared across every advisory-lock key in the database, not a fresh keyspace per lock. Two unrelated locks – two different specialists, or a specialist and an unrelated salon – can hash to the same integer. When that happens, one booking waits behind a lock meant for someone else, for no reason connected to either of them. In the worst case, two transactions each waiting on a lock the other holds end in a deadlock; Postgres detects it and aborts one side, which surfaces as a failed request the client can retry. Either way it costs latency or a retry, never correctness: the transaction that gets the lock still re-reads and re-checks everything for its own specialist, whoever else happened to hold that number, and a collision never causes a double booking.

Why the Guard Depends on READ COMMITTED

The lock and the read solve different problems. The lock makes participating writers wait; the transaction isolation level determines which committed data a subsequent read can see.

At PostgreSQL’s default READ COMMITTED, each statement gets a fresh snapshot. The overlap query starts after the lock wait, so it sees the booking committed by the previous holder.

At REPEATABLE READ, the snapshot is fixed at the first non-transaction-control statement. If that statement is the lock acquisition, the snapshot predates the wait. The second writer can acquire the lock and still query a snapshot that does not contain the first writer’s booking. Serialising entry is not enough if the read remains stale.

SERIALIZABLE adds protection against non-serialisable outcomes, but conflicting transactions can fail with SQLSTATE 40001. That design requires transaction retries that this path does not implement. These are the documented PostgreSQL isolation semantics, not a reason to treat a higher isolation level as a drop-in fix.

Where EXCLUDE Would Be Right

None of this is an argument against EXCLUDE constraints in general – just that this rule doesn’t fit one. When the no-overlap rule is symmetric and absolute – nothing, from any writer, may overlap anything else for this resource – and more than one code path can insert into the table, EXCLUDE is the better answer, not the advisory-lock pattern above. A meeting-room booking system with no special-cased owner override is the textbook case:

CREATE EXTENSION IF NOT EXISTS btree_gist;
 
CREATE TABLE room_booking (
  room_id    uuid NOT NULL,
  starts_at  timestamptz NOT NULL,
  ends_at    timestamptz NOT NULL,
  status     text NOT NULL DEFAULT 'confirmed',
  EXCLUDE USING gist (
    room_id WITH =,
    tstzrange(starts_at, ends_at, '[)') WITH &&
  ) WHERE (status <> 'cancelled')
);

btree_gist supplies equality support for room_id alongside the range-overlap operator. The partial predicate excludes cancelled bookings. The [) bounds include the start and exclude the end, so [10:00, 11:00) and [11:00, 12:00) can coexist. KRI’s strict startsAt < end AND endsAt > start check uses the same boundary convention. A production schema should also reject invalid durations, for example with CHECK (ends_at > starts_at).

Could a partial constraint exempt the owner? WHERE (created_by = 'client') would stop client-versus-client overlaps, but it would omit owner bookings from the index entirely. A client could then overlap an existing owner booking.

Adding an exclusion with created_by WITH <> can reject overlaps between the two roles; btree_gist supports that operator. But it rejects both directions: a client arriving after an owner and an owner arriving after a client. The business rule permits the latter. An exclusion constraint evaluates pairs of rows, not the sequence of authorised decisions that produced them.

Working-hour validation also needs the separate schedule data. Whatever mechanism enforces the booking rule must coordinate those reads with schedule changes as well.

The relevant PostgreSQL references are exclusion constraints, range bounds and btree_gist operators.

The Cost of Putting the Invariant in Application Code Instead of Schema

The guarantee between two client requests is clear: they take the same lock, so the second overlap check sees the first committed booking. The owner’s manual flow takes no lock and performs no overlap check. That creates a separate boundary the earlier description of the business rule must not hide.

Consider this sequence:

1. A client transaction checks the interval and finds it free.
2. The owner inserts an overlapping manual booking and commits.
3. The client inserts its booking and commits too.

The client did not see the owner’s write. Current KRI code therefore guarantees rejection of conflicts visible to the overlap query, and serialisation between participating writers; it does not serialise that query against every manual booking. An overlap in the final calendar can be intentional, but the code cannot establish a single client-versus-owner order from those locks alone.

If the requirement is ‘every booking decision has a serial order, and only an owner decision may create an overlap,’ the manual path must acquire the same specialist lock around its write. It can still omit the overlap check. The client then either sees the owner’s committed booking and rejects, or completes before the owner deliberately overrides it. Updates that move existing bookings into a conflicting interval need the same discipline, with a consistent lock order when multiple resources are involved. This is an improvement to make if that stronger rule is required, not a description of the current manual path.

That cost is real. A constraint protects every writer automatically – a script, an import job, a second booking surface – without anyone having to remember it exists. A transaction only protects the callers that go through it. The day something else writes to the bookings table for a client-facing reason without going through this function, the schema won’t save it. There’s no partial-unique-index second line of defence like the one KRI’s billing guard against double-charging a payment has, because this rule can’t be expressed as a table constraint the way a ‘one live payment per invoice’ rule can. The guard is as strong as the discipline of routing every client-facing write through it, and no stronger.

The overlap query alone has no conflicting booking row to lock when the interval is empty. That does not mean there is no usable row anywhere: the specialist row exists and can act as the shared lock target, just as a stock row does in inventory locking. Advisory locking is the choice here because it names a coordination resource separately from ordinary profile edits. Whichever primitive is used, all writers covered by the promised ordering must participate.

One more gap: KRI has DB-backed race tests for its billing paths – the gates and observation requirements covered in a separate article – but the booking path has no such test. The same technique would apply cleanly to either lock covered here. For two clients racing each other: park one transaction holding the salon-wide lock, fire a real booking attempt at it, and observe that the second is blocked before releasing the first, then verify that it sees the committed booking on re-read. For a schedule edit racing a booking: park one transaction holding the specialist lock inside a schedule-edit call, fire a booking attempt at the same specialist, and assert the same thing.

Building a booking or scheduling product where the ‘no overlap’ rule isn’t quite as simple as it sounds? Get in touch – working out which invariants belong in the schema and which ones can’t live there is part of MVP development work, not something to discover after the first double-booked slot.

Further reading:

• kri.rocks – Booking Website and Online Scheduling SaaS for Beauty Businesses – the project this article’s booking guard is drawn from
• Preventing Overselling: Inventory Locks Under Concurrent Checkouts – the same class of concurrency problem where a row already exists to lock, unlike a free time slot
• A Green Test Can Still Miss the Race: Testing Concurrency in PostgreSQL – the technique this article’s untested gap would use
• The Hour That Doesn’t Exist: Booking Slots Across Daylight Saving Time – where the working blocks this article’s overlap check relies on actually come from
• PostgreSQL documentation – Transaction Isolation – the READ COMMITTED, REPEATABLE READ and SERIALIZABLE semantics cited above
• PostgreSQL documentation – CREATE TABLE (EXCLUDE) – the exclusion-constraint guarantee and partial-constraint WHERE clause cited above
• PostgreSQL documentation – Range Types – the range bound syntax ([)) cited above
• PostgreSQL documentation – btree_gist – the <> operator support cited above