Prevent Double Booking with PostgreSQL Exclusion Constraints

Two customers see the same appointment as available. Each submits a reservation. Both application servers query the database, find no conflict, and insert a booking. Neither server made a bad local decision; the system failed to put the decision in one authoritative place.

For an exclusive resource, enforce non-overlapping active intervals in the database. An availability read is a user-interface hint. The successful reservation transaction is the authority.

This article considers one resource per booking with arbitrary start and end times. It is not a model for an auditorium with interchangeable seats or a service that permits three simultaneous appointments. Those require a different capacity invariant.

State the invariant before choosing a lock #

For two active reservations, a conflict exists when all three conditions hold:

same tenant
AND same resource
AND time intervals overlap

Ordinary uniqueness on (resource, start_time) cannot reject a booking that starts halfway through another. PostgreSQL range types represent intervals, and exclusion constraints can forbid overlap. The btree_gist extension supplies GiST equality operator classes for scalar identifiers. Range constraints, btree_gist.

Use half-open intervals: start included, end excluded. A booking ending at 11:00 can then sit next to one beginning at 11:00. If cleaning or travel requires a buffer, reserve the buffered interval explicitly; do not quietly redefine overlap in only one client.

A schema that rejects the race #

The following is a PostgreSQL 18 lab schema. It has been reviewed against the documentation but not executed against a database here. Run it in a disposable database with permission to install btree_gist before adopting it.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking_demo (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  resource_id bigint NOT NULL,
  starts_at timestamptz NOT NULL,
  ends_at timestamptz NOT NULL,
  state text NOT NULL CHECK (state IN ('active', 'cancelled')),
  CHECK (isfinite(starts_at) AND isfinite(ends_at)),
  CHECK (starts_at < ends_at),
  CONSTRAINT booking_demo_no_overlap
    EXCLUDE USING gist (
      tenant_id WITH =,
      resource_id WITH =,
      tstzrange(starts_at, ends_at, '[)') WITH &&
    ) WHERE (state = 'active')
);

The finite and ordering checks reject malformed intervals. The exclusion predicate keeps cancelled rows as history without letting them occupy the resource. Partial exclusion constraints are supported through a predicate, which also determines the index’s scope. CREATE TABLE.

Tenant identity belongs in the constraint because identifiers may only be unique inside a tenant. This does not authorize access: authentication and tenant isolation remain separate responsibilities. Derive the tenant from trusted request context rather than accepting arbitrary ownership claims in the payload.

Test concurrent sessions, not just sequential inserts #

In session A, start a transaction and insert an active interval without committing:

BEGIN;
INSERT INTO booking_demo
  (tenant_id, resource_id, starts_at, ends_at, state)
VALUES
  (1, 42, '2026-10-01T10:00:00Z', '2026-10-01T11:00:00Z', 'active');

In session B, attempt the same resource from 10:30 to 11:30. Resolve session A once with COMMIT and once with ROLLBACK in separate test runs. Check the outcome and waiting behavior. Also test adjacent intervals, different tenants, cancellation, and changing a cancelled row back to active.

The important test is not that your first insert succeeds. It is that competing transactions cannot both commit overlapping active reservations. Put a bounded statement timeout around the application transaction so a correctness mechanism does not become an unlimited request wait.

Turn a database conflict into a product outcome #

PostgreSQL identifies an exclusion violation with SQLSTATE 23P01. Match structured error fields and the expected constraint name, not a localized message string. PostgreSQL error codes.

Overlap protection is not request deduplication. A client that loses the success response still needs a stable request identity to recover the booking it already created. Otherwise, the second attempt may look like a conflict with somebody else’s reservation.

Payment and expiry are separate state machines #

Do not hold this transaction open while calling a payment provider. Commit a reservation or explicit hold, then run the payment workflow with a documented expiry and compensation policy. See why saga compensation is not rollback.

An expired hold does not disappear because wall-clock time advances. Use explicit state transitions owned by a worker, with a rule for payment confirmation racing expiry. Avoid an index predicate whose membership is supposed to change automatically with now(); time passing does not rewrite rows or maintain such a lifecycle for you.

There is a commercial decision here: whether an unresolved payment continues occupying scarce capacity. The database cannot make it. Record the policy in product terms and make support staff able to explain why a reservation was released.

Where this design stops #

This model favors a strong invariant within one database over independently accepting reservations everywhere. It adds index maintenance and contention for popular resources. It does not solve cross-database overlap, multi-resource atomic booking, or capacity greater than one without additional modeling.

For multi-resource bookings, acquire resources in a consistent order and test deadlock recovery. Before partitioning the table, revisit constraint support and partition-key restrictions; do not assume the same local index protects conflicts across arbitrary partitions.

A good release criterion is simple: two requests may both observe availability, but only a valid reservation can commit. That is a better guarantee than hoping every future code path remembers to take the right application lock.

Explore more invariant-driven designs in database engineering.

References #

• Range types and overlap constraints
• Scalar equality in GiST indexes
• Exclusion constraint syntax and restrictions
• Structured PostgreSQL error codes