Preventing double-bookings with PostgreSQL exclusion constraints

Lex Mulier — Wed, 20 May 2026 20:25:22 +0000

Most booking apps detect overlapping reservations in application code: pull all stays for the property, loop through them, compare date ranges. This works until two HTTP requests fly in at the same time and the database commits both. Welcome to Saturday lunch with two families standing at the front door of the same vacation home.

The fix is to push the conflict check down into the database, using PostgreSQL's tstzrange type and an EXCLUDE constraint. The result is bulletproof: even under perfectly-concurrent transactions, the database itself rejects the second booking with a constraint violation.

I'll walk through the pattern below, with the schema and a small test. This is the conflict-detection core of Ripazo, the booking app I'm building for families who co-own a vacation home.

The naive approach (and why it breaks)

Here's how most of us reach for it on day one:

async function createBooking(propertyId: string, start: Date, end: Date) {
  const overlapping = await db.query(`
    SELECT id FROM bookings
    WHERE property_id = $1
      AND $2 < end_at
      AND $3 > start_at
  `, [propertyId, start, end]);

  if (overlapping.length > 0) {
    throw new Error('Booking conflicts with existing stay');
  }

  return db.query(`
    INSERT INTO bookings (property_id, start_at, end_at)
    VALUES ($1, $2, $3) RETURNING *
  `, [propertyId, start, end]);
}

This is the textbook "check-then-insert" anti-pattern. The check and the insert are two separate database operations. Between them, a concurrent transaction can sneak its own booking into the gap. Under realistic load, you will eventually get two bookings overlapping for the same property, and the only signal will be an angry email.

Wrapping the function in a transaction doesn't help unless you also escalate the isolation level. And even then, lock contention pushes the problem somewhere else. Let's fix it at the schema level instead.

PostgreSQL range types in 30 seconds

PostgreSQL ships with range types built in: int4range, numrange, tsrange (timestamp without timezone), and the one we want, tstzrange (timestamp WITH timezone). A range value combines a lower bound, an upper bound, and inclusivity flags:

SELECT tstzrange('2026-06-05 14:00+00', '2026-06-12 11:00+00', '[)');
-- ["2026-06-05 14:00:00+00","2026-06-12 11:00:00+00")

The [) means "lower inclusive, upper exclusive" — the canonical convention for date ranges. The 5th overlaps with itself but the 12th is when the next booking can start.

Range types come with overlap and containment operators baked in:

SELECT tstzrange('2026-06-05', '2026-06-12', '[)') &&
       tstzrange('2026-06-10', '2026-06-15', '[)');
-- true (they overlap on Jun 10-11)

That && is the overlap operator. It's also what we'll feed to the constraint.

The exclusion constraint

An EXCLUDE constraint says: across all rows in this table, no two rows can have these column values where this operator returns true. Combine it with a range column and the overlap operator, and the database refuses to insert a second row whose range overlaps an existing row's range.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id uuid NOT NULL REFERENCES properties(id),
  stay        tstzrange NOT NULL,
  status      booking_status NOT NULL DEFAULT 'confirmed',
  created_at  timestamptz NOT NULL DEFAULT now(),

  EXCLUDE USING gist (
    property_id WITH =,
    stay        WITH &&
  ) WHERE (status = 'confirmed')
);

Three things to notice:

btree_gist lets us mix the = equality operator (B-tree backed) with the && overlap operator (GiST backed) in a single GiST index. Without this extension you can only put the range column in the constraint, not property_id as a co-key.
The stay column is a single tstzrange, not two separate timestamp columns. This is a real schema-level choice — your application code now passes a range, not two dates. Most ORMs handle this fine; in Drizzle/Prisma you model it as a custom type.
The WHERE (status = 'confirmed') clause means cancelled or tentative bookings can overlap with confirmed ones. This matters for "options" — a tentative hold that doesn't block confirmed reservations — and for keeping cancelled history in the table.

Insert two overlapping confirmed bookings, and you get:

ERROR:  conflicting key value violates exclusion constraint "bookings_property_id_stay_excl"
DETAIL:  Key (property_id, stay)=(...) conflicts with existing key.

That error bubbles up regardless of concurrency. There is no race window, no isolation-level tuning, no application-side double-check.

Translating the error for users

Catch the database error and translate it into something humans can read:

import { DatabaseError } from 'pg';

try {
  await db.bookings.insert({ propertyId, stay });
} catch (e) {
  if (e instanceof DatabaseError && e.code === '23P01') {
    throw new BookingConflictError(
      'These dates overlap with an existing booking.'
    );
  }
  throw e;
}

Postgres error code 23P01 is "exclusion_violation". Match on the code, not the message — the wording changes between versions and locales.

Tentative holds without blocking

The WHERE (status = 'confirmed') clause is the key to a feature most booking apps don't ship: tentative holds. Let one family say "we'd like this week but aren't sure yet" without locking everyone else out:

-- A tentative hold doesn't block anyone
INSERT INTO bookings (property_id, stay, status)
VALUES ($1, $2, 'option');

-- Someone else confirms the same week, no error
INSERT INTO bookings (property_id, stay, status)
VALUES ($1, $2, 'confirmed');

-- The original tentative tries to confirm — now it fails
UPDATE bookings SET status = 'confirmed' WHERE id = $1;
-- ERROR: exclusion constraint violation. By design.

Tentative bookings can stack. Only confirmed bookings are mutually exclusive. The UPDATE that promotes a tentative to confirmed is checked against the constraint at update time, which is exactly the right moment to surface the conflict.

Half-open intervals: the off-by-one trap

I mentioned [) (lower inclusive, upper exclusive) above. It's not aesthetic, it's structural. With closed intervals on both ends, you can't represent two back-to-back bookings without a false overlap:

Booking A: [2026-06-05, 2026-06-12]   -- 12th inclusive
Booking B: [2026-06-12, 2026-06-19]   -- 12th inclusive
                            ^^^^^^^^
            Both rows touch on the 12th → overlap → exclusion error.

With [):

Booking A: [2026-06-05, 2026-06-12)   -- 12th NOT included
Booking B: [2026-06-12, 2026-06-19)   -- 12th included
            They share a boundary but do not overlap.

In a vacation home, this maps to the real-world handover: family A checks out on the 12th, family B checks in on the 12th, the cleaner shows up between them. The boundary is shared, not duplicated.

Supabase / RLS interaction

If you're on Supabase (or any Postgres with RLS), the exclusion constraint runs BEFORE row-level security policies. That's the right order — you don't want to leak booking ranges through a constraint-violation error message to a user who shouldn't be able to see the conflicting row. Constraint violations include the conflicting key in the error detail, so make sure your app code translates 23P01 to a generic "this slot isn't available" string before it reaches the client. Don't pass the raw DETAIL.

Test it

A minimal smoke test that proves the constraint works:

BEGIN;
  INSERT INTO bookings (property_id, stay)
  VALUES ('11111111-1111-1111-1111-111111111111',
          tstzrange('2026-06-05', '2026-06-12', '[)'));

  -- This second insert must fail with 23P01
  INSERT INTO bookings (property_id, stay)
  VALUES ('11111111-1111-1111-1111-111111111111',
          tstzrange('2026-06-10', '2026-06-15', '[)'));
ROLLBACK;

You can also write a Node.js concurrency test that fires N parallel INSERT requests and asserts that exactly one succeeds. The constraint holds across true concurrent transactions — no application locks needed.

What changes operationally

The day this shipped in Ripazo, the application-side conflict-detection code shrunk to a single try/catch. The schema does the work. There's no "what if two people click confirm at the same time" question anymore.

It also makes the audit trail honest. Every conflict the database refuses is a real concurrent attempt, not a phantom result of stale cache. When two cousins both try to confirm the same week, both requests reach the database, one wins, one gets the constraint error, the loser sees a clear "this week was just taken" message and the calendar updates in real time.

For the product side of why this matters in a co-owned household (and what fails when you try to run a shared house on Google Calendar), there's the longer write-up on ripazo.com.

tl;dr

Don't check-then-insert. Push conflict detection into the schema.
Use tstzrange + EXCLUDE USING gist + the btree_gist extension for equality co-keys.
Half-open intervals ([)) let you do same-day handovers without phantom conflicts.
A WHERE clause on the constraint gives you tentative holds for free.
Catch error code 23P01 in app code and translate to something users can read.

If you're building any kind of reservation, calendar, or scheduling product, this pattern saves a long, miserable Saturday.

DEV Community: Lex Mulier