PostgreSQL Exclusion Constraints: Preventing Overlapping Bookings

Two requests can both check whether a room is free, both observe no conflicting row, and both insert successfully. A transaction or application lock does not protect the invariant when one code path skips the protocol. PostgreSQL range types and exclusion constraints solve the problem at the database boundary: for one resource, overlapping time ranges cannot coexist.

This guide builds a practical booking schema using tstzrange, the overlap operator &&, GiST, and btree_gist, then covers boundaries, cancellation status, concurrency, availability queries, and migration. It follows official PostgreSQL documentation for range types, constraints, and btree_gist.

1. Why SELECT then INSERT still double-books

BEGIN;

SELECT 1
FROM bookings
WHERE room_id = 42
  AND starts_at < :new_end
  AND ends_at > :new_start;

INSERT INTO bookings (...);
COMMIT;

At the default isolation level, two transactions can read before either inserts. Both see an empty slot. Row locking cannot lock a conflicting row that does not exist, while advisory locks work only if every path follows the same convention. Let the database enforce the no-overlap invariant; a pre-check remains useful for UX but is not the final barrier.

2. Range types model time intervals

PostgreSQL provides tsrange for timestamp without time zone and tstzrange for timestamp with time zone. Scheduling absolute instants normally favors tstzrange.

SELECT tstzrange(
    '2026-10-03 09:00+07',
    '2026-10-03 10:00+07',
    '[)'
);

[) includes the start and excludes the end. Reservations from 09:00–10:00 and 10:00–11:00 are adjacent rather than overlapping, with no fake microsecond subtraction.

3. A practical production schema

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    starts_at timestamptz NOT NULL,
    ends_at timestamptz NOT NULL,
    status text NOT NULL DEFAULT 'confirmed',
    slot tstzrange GENERATED ALWAYS AS (
        tstzrange(starts_at, ends_at, '[)')
    ) STORED,
    created_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT bookings_time_order
        CHECK (starts_at < ends_at),

    CONSTRAINT bookings_no_confirmed_overlap
        EXCLUDE USING gist (
            room_id WITH =,
            slot WITH &&
        )
        WHERE (status = 'confirmed')
);

btree_gist supplies GiST equality behavior for scalar types such as bigint, while ranges support overlap. The constraint rejects a row pair when both room equality and time overlap are true. Creating the constraint automatically creates its enforcement index.

4. NOT NULL and CHECK are essential

An exclusion constraint permits a row pair when at least one comparison is false or null. Keep resource and time fields non-null when the invariant is absolute. The order check prevents reversed and empty ranges. An empty range overlaps nothing, so omitting the check can admit meaningless reservations without a conflict.

5. Partial constraints and booking status

The predicate lets cancelled and draft rows retain history without occupying a slot. Updating a draft to confirmed is itself checked and may fail if another reservation now owns the range.

ALTER TABLE bookings
ADD CONSTRAINT bookings_status_valid
CHECK (status IN ('draft', 'confirmed', 'cancelled'));

If pending payment also reserves inventory, include it in the predicate or model an explicit blocks_inventory contract. Forgetting one blocking state is a business-rule hole.

6. Concurrent inserts

For concurrent overlapping inserts on one room, PostgreSQL coordinates through the constraint and index. Depending on timing, one transaction may wait; once a conflict is established, one side receives exclusion_violation with SQLSTATE 23P01.

Catch that specific SQLSTATE and return a domain conflict such as “the slot was just booked.” Do not map every database error to HTTP 409.

7. Availability checks remain useful

SELECT NOT EXISTS (
    SELECT 1
    FROM bookings
    WHERE room_id = $1
      AND status = 'confirmed'
      AND slot && tstzrange($2, $3, '[)')
) AS available;

This query can use the GiST index and improves calendar UX. Its answer is only a snapshot; the subsequent write must still handle a constraint conflict.

8. Time boundaries and zones

• Store instants as timestamptz; retain the location's IANA time zone when local reconstruction matters.
• Accept explicit offsets or local time plus a zone ID and resolve DST ambiguity deliberately.
• Use [) consistently in creation, lookup, reporting, and cache keys.
• Avoid casual BETWEEN, which is inclusive on both ends.
• Never add or subtract a millisecond to emulate boundaries.

9. Updates are checked too

UPDATE bookings
SET ends_at = ends_at + interval '30 minutes'
WHERE id = $1;

UPDATE bookings SET room_id = $2 WHERE id = $1;
UPDATE bookings SET status = 'confirmed' WHERE id = $1;

Extending a booking, moving rooms, and restoring a cancellation can all raise 23P01. For an atomic multi-booking rearrangement, order changes consistently to reduce deadlocks and evaluate a deferrable constraint only when end-of-transaction semantics are genuinely required and tested.

10. Finding open slots

WITH candidates AS (
    SELECT tstzrange(t, t + interval '30 minutes', '[)') AS slot
    FROM generate_series(
        $1::timestamptz,
        $2::timestamptz - interval '30 minutes',
        interval '30 minutes'
    ) AS t
)
SELECT c.slot
FROM candidates c
WHERE NOT EXISTS (
    SELECT 1
    FROM bookings b
    WHERE b.room_id = $3
      AND b.status = 'confirmed'
      AND b.slot && c.slot
)
ORDER BY lower(c.slot);

Bound the generation window and candidate count. Real availability may also subtract opening hours, maintenance, and calendar exceptions.

11. Migrating an existing table

1. Audit null, reversed, and empty intervals.
2. Find current overlaps using a self-join and ordered IDs.
3. Resolve conflicts by merging, cancelling, or correcting data.
4. Install btree_gist under the production change process.
5. Add the range representation and checks.
6. Add the constraint with a plan for scans, locks, I/O, and replica lag.
7. Deploy application handling for 23P01 before opening full concurrency.

SELECT a.id, b.id, a.room_id, a.slot, b.slot
FROM bookings a
JOIN bookings b
  ON a.room_id = b.room_id
 AND a.id < b.id
 AND a.status = 'confirmed'
 AND b.status = 'confirmed'
 AND a.slot && b.slot;

12. Common mistakes

• Relying only on SELECT-before-INSERT.
• Using inclusive upper bounds and rejecting adjacent bookings.
• Allowing null or empty ranges while expecting total protection.
• Forgetting a new status in the partial predicate.
• Using timestamp without a clear time-zone contract.
• Mapping every integrity error to “slot already booked.”
• Creating a duplicate index beside the constraint index.
• Adding the constraint before auditing existing overlap.

Production checklist

• The invariant lives in EXCLUDE USING gist, not only application code.
• tstzrange(..., '[)') is used consistently.
• Resource and time fields are NOT NULL with start < end.
• The status predicate covers every inventory-blocking state.
• The application handles SQLSTATE 23P01 specifically.
• Availability checks support UX while writes still trust the constraint.
• Concurrent inserts, updates, cancellation restore, and adjacency are tested.
• Migration audits overlap before creating the constraint.
• Conflict rate, wait latency, and deadlocks are monitored.

Conclusion

Range types and exclusion constraints turn “one resource cannot have overlapping reservations” into a PostgreSQL invariant that every code path and concurrent request must obey. A strong schema combines [) boundaries, NOT NULL, an order check, the correct status predicate, and domain handling for 23P01. With the database as the final guardrail, application pre-checks can focus on UX without gambling correctness on a racy SELECT.