Postgres EXCLUDE Constraints: Stop Double-Booking Without Application Locks

The booking endpoint had a SELECT followed by an INSERT, and it had worked for two years. A user picked a time slot, the app checked for overlapping reservations, and if nothing was found it inserted a new row. Then the marketing team ran a flash sale for a popular conference room. Forty users hit the same 9:00 AM slot within a ten-second window. The application served ten overlapping bookings before the cache even caught up. Customer support spent the next morning issuing refunds and apology emails.

The bug was not in the application logic. The application logic was correct. The bug was the assumption that a read followed by a write is an atomic operation. It is not. In a concurrent system, the gap between the SELECT and the INSERT is a window where another transaction can insert the same slot, commit, and make your check a lie. You cannot close that window in application code. You close it in the database.

Postgres has a constraint type that most teams have never used. It is called EXCLUDE, and it is a generalization of UNIQUE that understands ranges, overlaps, and custom operators. This post is the DDL, the extension, the index, and the error handling that turns a double-booking from a concurrency failure into an impossibility.

Why application-level checks always lose

The naive pattern looks like this:

SELECT 1 FROM bookings
WHERE room_id = $1
  AND start_time < $3
  AND end_time > $2;

If the query returns zero rows, the application inserts the booking. The logic is sound, but it is not safe. Here is the window:

1. Transaction A checks for overlaps. Finds none.
2. Transaction B checks for overlaps. Finds none.
3. Transaction A inserts and commits.
4. Transaction B inserts and commits.

Both checkers saw a clean slot because neither insert existed at the time of the check. This is a classic Time-of-Check to Time-of-Use (TOCTOU) race, and it happens at any concurrency level above one.

SELECT FOR UPDATE does not save you because there is no existing row to lock. Each transaction is checking for the absence of a row, and there is nothing to pin. Serializable isolation in Postgres can catch this, but it raises a 40001 (serialization failure) that your application must then detect and retry. If your retry loop is not bulletproof, you trade one bug for another. Worse, serializable mode applies to the entire transaction, which means every other table touch in the same transaction is also subject to serialization conflicts you did not ask for. The overhead is real, and the retry complexity is non-trivial.

Advisory locks are another common workaround. Map (room_id, time_slot) to an integer, lock it with pg_advisory_xact_lock, then insert. This works, but it is an application-layer semaphore that sits outside the schema. It does not survive schema changes gracefully, it requires careful key collision management, and it centralizes load on a single lock namespace that can become a bottleneck under extreme concurrency.

What you actually need is a declarative, schema-level rule that says: “For any given room, two bookings cannot occupy overlapping time ranges.” That is exactly what EXCLUDE does.

EXCLUDE is UNIQUE with superpowers

Most developers think UNIQUE is a separate, primitive constraint. It is not. UNIQUE(email) is syntactic sugar for:

EXCLUDE USING btree (email WITH =)

EXCLUDE says: “For any two rows, if the listed expressions compare as true using the given operators, reject the second insert.” UNIQUE uses the equality operator (=) on the column. EXCLUDE generalizes this to any operator supported by an index access method.

To block overlapping time ranges, we use the GiST index access method and the && (range overlap) operator. The constraint says: “For any two rows with the same room_id, the time ranges must not overlap.”

The prerequisite is the btree_gist extension, which lets GiST indexes handle scalar equality (=) alongside range operators:

CREATE EXTENSION IF NOT EXISTS btree_gist;

Without this, GiST does not know how to compare room_id (an integer) for equality inside an exclusion constraint.

The DDL that makes double-booking impossible

Here is the table. It is ordinary until the last line.

CREATE TABLE bookings (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id int NOT NULL,
    start_time timestamptz NOT NULL,
    end_time timestamptz NOT NULL,
    user_id int NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT no_overlapping_bookings
      EXCLUDE USING gist (
        room_id WITH =,
        tstzrange(start_time, end_time, '[)') WITH &&
      )
);

Break it down:

• EXCLUDE USING gist tells Postgres to build a GiST index to enforce the rule.
• room_id WITH = means the constraint only compares rows that have the same room_id. A booking in room 3 does not interact with a booking in room 7.
• tstzrange(start_time, end_time, '[)') WITH && constructs a timestamptz range from the columns and says: “If the ranges overlap (&&), reject.”
• [) means the start is inclusive and the end is exclusive. A booking ending at 10:00 does not conflict with a booking starting at 10:00. This is the correct semantics for continuous time slots.

The constraint is named (no_overlapping_bookings) so that violation errors are readable, and so you can drop it later without guessing.

What happens when the race reoccurs

With the constraint in place, the same flash sale scenario plays out differently:

1. Transaction A inserts the 9:00 AM slot. GiST index checks room 3, sees no overlap, and allows it.
2. Transaction B tries to insert the same slot. GiST index checks room 3, finds the row committed by transaction A (or sees the overlapping range in progress and waits), and rejects the second insert with a constraint violation.

If transaction A has not yet committed, transaction B blocks until A commits or rolls back. If A commits with an overlap, B gets the error immediately. If A rolls back, B succeeds. Either way, the database guarantees that no two committed rows overlap for the same room.

Handling the exclusion violation in Node.js

The postgres client (pg) throws an error with code 23P01 when an exclusion constraint is violated. Your route handler should catch it and surface a clean, actionable error to the client.

import { Pool } from 'pg';

export const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export async function createBooking(
  roomId: number,
  startTime: Date,
  endTime: Date,
  userId: number,
) {
  try {
    await pool.query(
      `INSERT INTO bookings (room_id, start_time, end_time, user_id)
       VALUES ($1, $2, $3, $4)`,
      [roomId, startTime, endTime, userId],
    );
  } catch (err: any) {
    if (err.code === '23P01') {
      const clean = new Error(
        'This time slot is no longer available. Please select another.',
      );
      (clean as any).statusCode = 409;
      throw clean;
    }
    throw err;
  }
}

The client gets a 409 Conflict with a clear message. The database has already done the hard work of locking, comparing, and rejecting. Your application does not need a retry loop, an advisory lock, or a distributed transaction coordinator.

If you use an ORM like Prisma or Drizzle, the same error bubbles up as a unique-constraint-like exception. Map the Postgres code in your error interceptor, not in the business logic.

Soft deletes and partial exclusion constraints

Most real systems do not physically delete bookings. They mark them as canceled. If you simply add a canceled_at column, the exclusion constraint still sees the canceled row as a conflict. A user cancels a booking and immediately tries to rebook the same slot, and the database rejects it.

The fix is a partial exclusion constraint:

ALTER TABLE bookings
DROP CONSTRAINT no_overlapping_bookings;

ALTER TABLE bookings
ADD CONSTRAINT no_overlapping_bookings
  EXCLUDE USING gist (
    room_id WITH =,
    tstzrange(start_time, end_time, '[)') WITH &&
  )
  WHERE (canceled_at IS NULL);

The WHERE clause makes the constraint apply only to rows where canceled_at is null. Canceled bookings are invisible to the overlap check. This is identical in spirit to partial unique indexes, but for exclusion constraints. The btree_gist extension must still be installed.

If you are migrating an existing table, do this during a low-traffic window. Dropping and re-adding the constraint rebuilds the GiST index. For very large tables, consider building the index with CREATE INDEX CONCURRENTLY first, then adding the constraint using the pre-built index:

CREATE INDEX CONCURRENTLY idx_bookings_exclude
ON bookings USING gist (
  room_id,
  tstzrange(start_time, end_time, '[)')
)
WHERE (canceled_at IS NULL);

ALTER TABLE bookings
ADD CONSTRAINT no_overlapping_bookings
  EXCLUDE USING gist (
    room_id WITH =,
    tstzrange(start_time, end_time, '[)') WITH &&
  )
  WHERE (canceled_at IS NULL)
  USING INDEX idx_bookings_exclude;

This avoids the AccessExclusive lock that a plain ALTER TABLE would take while building the index.

Variations: licenses, delivery windows, and seat limits

Exclusion constraints are not limited to room bookings. Any domain where overlapping ranges for a shared resource must be prevented is a candidate.

Software license seats:

CREATE TABLE license_allocations (
    license_key text NOT NULL,
    user_id int NOT NULL,
    start_date date NOT NULL,
    end_date date NOT NULL,
    EXCLUDE USING gist (
      license_key WITH =,
      daterange(start_date, end_date, '[)') WITH &&
    )
    WHERE (revoked = false)
);

This ensures a user cannot be assigned overlapping license periods for the same key.

Delivery windows per vehicle:

CREATE TABLE routes (
    vehicle_id int NOT NULL,
    delivery_zone tstzrange NOT NULL,
    EXCLUDE USING gist (
      vehicle_id WITH =,
      delivery_zone WITH &&
    )
);

Here the range is stored directly as a tstzrange column, which simplifies the constraint expression.

Parking space reservations:

CREATE TABLE parking_reservations (
    space_id int NOT NULL,
    reserved_during tstzrange NOT NULL,
    EXCLUDE USING gist (
      space_id WITH =,
      reserved_during WITH &&
    )
    WHERE (checked_out_at IS NULL)
);

Performance: what GiST costs you

GiST indexes are slower to insert and update than B-tree indexes. The penalty is usually 2 to 3 times the cost of a B-tree insert. For a booking system with hundreds of inserts per second, this is negligible. For a high-throughput event stream with tens of thousands of inserts per second, an exclusion constraint on the hot path may be the wrong tool. Measure your insert latency before and after adding the constraint.

If the table is large (millions of rows), the GiST index size will also be larger than an equivalent B-tree. Monitor pg_size_pretty(pg_total_relation_size('bookings')) after creation.

Partitioning by room_id or by time range can help if a single table becomes unwieldy. In a partitioned table, you must create the exclusion constraint on each partition. Postgres does not yet support global exclusion constraints across partition boundaries as a single declaration, so plan your partition key with this in mind. Range partitioning by month with a local exclusion constraint per partition works well for time-series bookings.

When not to use EXCLUDE

Exclusion constraints are powerful, but they are not universal.

Aggregate limits that depend on counts. If the rule is “a user may have at most three active bookings,” exclusion constraints cannot express that. Use a trigger, or enforce it in application code with a locking read, or model the remaining capacity explicitly in a room_availability table and lock that row.

Cross-table overlap rules. Exclusion constraints work on a single table. If you need to check that a booking in bookings does not overlap with a maintenance window in maintenance_windows, use a trigger or application logic.

High-frequency, low-cardinality overlap detection. If you are inserting 50,000 events per second and the overlap check is rare (e.g., collision detection in a game), a GiST index may be too expensive. Consider a partitioned in-memory check or a specialized spatial index like R-tree in an extension better suited for the write volume.

The production checklist

Before you add an exclusion constraint to a live table:

1. Install btree_gist in a migration that runs before the constraint DDL. On managed Postgres (RDS, Cloud SQL, Supabase), the extension is usually allowed.
2. Build the index CONCURRENTLY if the table has more than 100,000 rows. Then attach the constraint using the index.
3. Verify your application catches 23P01 and maps it to a 409 or equivalent user-facing error. Do not let the raw Postgres error leak to the client.
4. Add a test that fires two concurrent inserts for the same slot and asserts that exactly one succeeds.
5. Monitor insert latency after shipping. GiST insert cost is real, even if small.

The takeaway

Double-booking is not a bug in your check logic. It is a bug in your trust model. You trusted application-level reads to guard writes, and concurrency proved you wrong. The fix is not more locks, more retries, or more clever JavaScript. The fix is a database constraint that understands time ranges.

Six lines of DDL replace an entire class of race conditions. Two transactions trying to book the same room at the same time now result in one success and one clean, immediate failure that your application catches and surfaces. No middleware. No distributed lock. No guesswork.

Your application code should decide what happens when a slot is taken. Postgres should decide whether the slot is taken at all.

A note from Yojji

The kind of data-modeling discipline that turns a concurrency race into a schema-level guarantee (partial exclusion constraints, range types, and the index strategy that makes them fast) is the kind of backend engineering Yojji builds into the systems they ship for clients.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. Their teams of 50+ senior engineers specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, GCP), and the Postgres data modeling that keeps production systems correct under real load. If your application is still fighting race conditions with application-level checks, Yojji can help you move the guarantees to the right layer.