Prevent Overlapping Time-Based Bookings in PostgreSQL

An availability check followed by an insert looks correct until two requests check the same free slot at the same time. Both can pass the check, then both can create a booking. The database needs to enforce the non-overlap rule at the point where concurrent writes meet.

Define the boundary rule first

Represent bookings as half-open intervals: start is included and end is excluded, written [start, end). A booking from 10:00 to 11:00 occupies every instant from 10:00 up to, but not including, 11:00.

Under this rule, an 11:00–12:00 booking is allowed next to a 10:00–11:00 booking. An end time equal to the next start time does not overlap. This matches most appointment, room, and equipment schedules and avoids inventing a one-second gap.

Require end_at > start_at; an empty or negative booking should not enter the table.

Use timezone-aware timestamps

Store real-world instants as timestamptz and convert them to the user's zone for display. PostgreSQL stores a timezone-aware input as an absolute instant and renders it in the session time zone. Also store a location or business time-zone identifier when future schedules depend on local rules.

Avoid accepting ambiguous local timestamps during daylight-saving transitions. Parse the request with an explicit IANA time zone or UTC offset, then return the normalized instant to the client for confirmation.

Model the booking range

PostgreSQL has built-in timestamp range types. tstzrange matches timestamptz boundaries:

CREATE TABLE booking (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id bigint NOT NULL,
  starts_at timestamptz NOT NULL,
  ends_at timestamptz NOT NULL,
  status text NOT NULL DEFAULT 'confirmed',
  price_minor bigint NOT NULL CHECK (price_minor >= 0),
  CHECK (ends_at > starts_at)
);

Separate columns remain convenient for APIs and reports. The constraint can construct a range from them.

Add an exclusion constraint

An exclusion constraint can reject rows where the workspace is equal and the time ranges overlap. Scalar equality needs the btree_gist extension so it can participate in a GiST constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;
 
ALTER TABLE booking
ADD CONSTRAINT booking_workspace_time_no_overlap
EXCLUDE USING gist (
  workspace_id WITH =,
  tstzrange(starts_at, ends_at, '[)') WITH &&
)
WHERE (status IN ('pending', 'confirmed'));

&& is the range-overlap operator. '[)' makes the lower bound inclusive and upper bound exclusive. The partial predicate means only active statuses block time. Adapt that list to the actual state machine: a cancelled or rejected row often should not block, while a payment-pending reservation may need a short hold.

Creating an exclusion constraint also creates its supporting index. On an existing table, first find and resolve overlapping data; otherwise the constraint addition fails. CREATE EXTENSION may require provider or database-owner permission.

Keep the operation in a transaction

The constraint is the final authority, but booking creation usually includes pricing, capacity, and payment-hold work too:

BEGIN;
 
INSERT INTO booking (
  workspace_id, starts_at, ends_at, status, price_minor
)
VALUES (
  42,
  '2026-09-01T04:15:00Z',
  '2026-09-01T05:15:00Z',
  'pending',
  250000
)
RETURNING id;
 
COMMIT;

When the exclusion constraint detects a conflict, PostgreSQL raises SQLSTATE 23P01 (exclusion_violation). Translate that specific error into an HTTP 409 Conflict or equivalent domain response. Do not retry the same slot automatically; show that availability changed.

The frontend can still query availability to guide the user. It is not an integrity guarantee because its answer becomes stale immediately.

Cancellation and status changes

The partial constraint permits a cancelled row to coexist with a replacement. A transition from cancelled back to confirmed can then fail if another booking has claimed the period. Perform status changes through the same service rules and handle 23P01.

If every historical status must remain immutable, use an event/history table while keeping the current blocking state on the booking row. Do not delete financial or audit history merely to make the slot reusable.

Calculate duration and price deliberately

Duration-based pricing is separate from overlap detection. Calculate the duration from normalized instants and define the unit and rounding:

SELECT extract(epoch FROM (ends_at - starts_at))::bigint AS seconds
FROM booking
WHERE id = $1;

Multiplying an hourly price by floating hours can create fractional currency problems. Prefer an integer billing unit or decimal arithmetic, then store the final quoted price with the booking. Calendar-day pricing, minimum periods, local opening hours, and daylight-saving transitions need explicit business rules.

Test the boundaries and races

Given an existing [10:00, 11:00) booking, cover at least these cases:

Also run two concurrent transactions for the same workspace and time. The successful commit count must be one. Test different workspace IDs at the same time, cancellation, invalid zero duration, and timestamps expressed with different offsets that represent the same instant.

Common mistakes

• Running SELECT for availability and trusting it without a constraint.
• Using closed [start, end] ranges, which rejects back-to-back appointments.
• Comparing timestamps from different implicit local zones.
• Applying a global constraint without the resource identifier.
• Letting cancelled rows block forever, or excluding payment holds that should block.
• Catching every database error as “slot unavailable” and hiding unrelated failures.

For connection and migration concerns around the constraint, see Prisma Migrate Dev vs Deploy vs DB Push and PostgreSQL direct connections versus poolers.

Verification checklist

• The API defines [start, end) and allows end = next start.
• ends_at > starts_at is enforced in PostgreSQL.
• Times arrive with an offset or known IANA zone and are stored as timestamptz.
• The exclusion constraint includes both resource equality and range overlap.
• Blocking statuses match the application's lifecycle.
• SQLSTATE 23P01 becomes a clear conflict response.
• Automated tests include adjacent, nested, duplicate, cross-resource, and concurrent inserts.

References

Documentation checked on 2026-08-12:

• PostgreSQL range types, bounds, overlap, and constraints
• PostgreSQL exclusion constraints
• PostgreSQL btree_gist extension
• PostgreSQL date and time types