A reservation can pass an availability check and still collide with another reservation. Two requests can both read “no overlap” before either writes. An application-level query is useful for showing available slots, but it is a weak place to enforce the final invariant.

PostgreSQL 18 gives that invariant a direct schema form. A UNIQUE or PRIMARY KEY constraint can use WITHOUT OVERLAPS on its final range column. A temporal foreign key can use PERIOD to require coverage by related rows. The PostgreSQL 18 release notes describe both additions.

I would use these features when the rule is genuinely about time windows: one resource cannot have two active allocations at the same instant, or an entitlement must remain valid for an entire billed period. The database then rejects a conflicting write regardless of which worker, script, or API path sent it.

Model the interval before adding the constraint

For a room reservation, I would store one tstzrange rather than separate start and end columns that every query must interpret the same way:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_reservations (
  reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id bigint NOT NULL,
  reserved_during tstzrange NOT NULL,
  CONSTRAINT no_room_overlap
    UNIQUE (room_id, reserved_during WITHOUT OVERLAPS)
);

The temporal unique constraint allows many rows for one room_id, provided their ranges do not overlap. PostgreSQL implements it with a GiST index and requires the temporal column to be a range or multirange. It rejects empty ranges. The ordinary bigint room key needs a GiST equality operator class, which the supplied btree_gist extension provides. The CREATE TABLE reference documents these requirements and notes that the constraint has the effect of an exclusion constraint using equality and the range overlap operator.

I would construct reservation ranges with an inclusive start and exclusive end:

INSERT INTO room_reservations (room_id, reserved_during)
VALUES
  (42, tstzrange('2026-09-16 10:00+00', '2026-09-16 11:00+00', '[)')),
  (42, tstzrange('2026-09-16 11:00+00', '2026-09-16 12:00+00', '[)'));

These windows meet at 11:00 without sharing an instant. A third reservation for room 42 from 10:30 to 11:30 overlaps and should be rejected by the constraint. PostgreSQL's range documentation defines the boundary notation and the && overlap operator.

The application still needs a policy for positive duration, open-ended ranges, timezone input, and cancellation. In particular, a canceled row in this table still occupies its range. If only rows with a particular status should block availability, I would keep a partial exclusion constraint instead of forcing that rule into an unconditional temporal unique constraint. PostgreSQL supported exclusion constraints before version 18; WITHOUT OVERLAPS adds a clearer key form, not the first possible way to prevent overlap.

Use PERIOD when a reference must cover the whole interval

An ordinary foreign key can prove that a plan exists. It cannot prove that the plan was valid throughout a charge's billing window. PostgreSQL 18's temporal foreign key adds that second question:

CREATE TABLE plan_versions (
  plan_id bigint NOT NULL,
  valid_at tstzrange NOT NULL,
  CONSTRAINT plan_versions_time_key
    UNIQUE (plan_id, valid_at WITHOUT OVERLAPS)
);

CREATE TABLE charges (
  charge_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  plan_id bigint NOT NULL,
  billed_for tstzrange NOT NULL,
  CONSTRAINT charge_plan_coverage
    FOREIGN KEY (plan_id, PERIOD billed_for)
    REFERENCES plan_versions (plan_id, PERIOD valid_at)
);

The reference succeeds when plan-version rows with the same plan_id together cover every instant in billed_for. One version does not have to span the entire charge. Two adjacent versions can provide continuous coverage; a gap cannot. The referenced key must use WITHOUT OVERLAPS, and both period columns must be range or multirange types, as the PostgreSQL 18 foreign-key documentation specifies.

That gives the schema a stronger statement than “the ID exists.” It also means editing or deleting a plan version is a data-integrity operation: the resulting set of versions must still cover any referencing charge. I would decide how to handle plan changes and historical corrections before enabling the foreign key.

Migrate existing tables in the right order

The difficult part is usually the existing data. Before adding an overlap constraint, I would find collisions by joining rows with the same resource key and intersecting ranges:

SELECT a.reservation_id AS first_id, b.reservation_id AS second_id
FROM room_reservations AS a
JOIN room_reservations AS b
  ON a.room_id = b.room_id
 AND a.reservation_id < b.reservation_id
 AND a.reserved_during && b.reserved_during;

I would resolve each pair according to the product's source of truth rather than silently shifting dates. I would also audit empty and unbounded ranges, then confirm the intended treatment of touching boundaries and canceled records.

The PostgreSQL 18 ALTER TABLE documentation sets an important rollout limit: ADD CONSTRAINT ... NOT VALID currently applies to foreign keys, CHECK, and not-null constraints, not unique constraints. A new temporal unique constraint therefore needs its validation and GiST index work planned against the real table size and write traffic. The documented ADD ... USING INDEX route also requires a B-tree index, so it is not a shortcut for attaching this GiST-backed temporal key.

I would make the rollout a measured database change: clean the data, trial the DDL against a production-sized copy, choose a maintenance window or an application-specific migration approach, and monitor lock waits and write latency. Once the referenced temporal key exists, a PERIOD foreign key can be introduced with NOT VALID and validated separately; that staged path applies to the foreign key, not to the temporal unique key.

Keep business rules visible

Temporal constraints settle a narrow but valuable question: can these stored intervals coexist, and does this reference have continuous coverage? They do not decide whether a hold expires after fifteen minutes, whether a canceled booking releases capacity, or which actor may rewrite historical validity.

I prefer putting the interval invariant in the database and leaving those product decisions explicit in the application. That split gives engineers a reliable final guard against concurrent writes while keeping the meaning of a reservation or entitlement open to review.