Articles

PostgreSQL Exclusion Constraint Interview Questions: Range Overlap and Concurrent Reservations

Practice PostgreSQL exclusion constraints with half-open ranges, finite bounds, competing transactions, conflict handling, and confirmed reservation rules.

Two customers reserve the same room for overlapping hours. Each request first runs an availability query, sees no committed booking, and inserts its own row. Both requests looked reasonable in isolation; together they violated the product's promise.

PostgreSQL exclusion constraint interview questions ask you to move that promise into a rule the database enforces. A strong answer defines which rows compete, which intervals overlap, and what happens when a transaction meets a reservation that has not committed yet.

The SQL and transaction lab below are original interview exercises. PostgreSQL facts refer to version 18 documentation, checked September 30, 2026; these are not candidate reports or predictions of an employer's questions.

What does an exclusion constraint actually forbid?

An exclusion constraint compares pairs of rows using the operators you specify. Official fact: PostgreSQL requires at least one comparison in a conflicting pair to be false or null; a pair for which every comparison is true violates the constraint. It also creates an index supporting the constraint. Exclusion constraint definition.

For a reservation, the forbidden conjunction is straightforward: same tenant, same resource, and overlapping time range. Equality alone is insufficient because one room can have many bookings at different times. Overlap alone is insufficient because different rooms can be occupied simultaneously.

Use tenant_id WITH =, resource_id WITH =, and during WITH &&. The last operator means that the two ranges overlap. This is a conjunction, not three independent uniqueness rules. A conflict requires all three comparisons to hold.

The access method and operator classes matter. Official fact: btree_gist supplies GiST operator classes for scalar types including integers, allowing equality comparisons to accompany a range comparison in one multicolumn GiST exclusion constraint. btree_gist documentation.

A useful interview explanation is: “For eligible reservations, the database must reject two rows sharing a tenant and resource when their occupied intervals intersect.” That sentence is specific enough to test before discussing SQL syntax.

How do range boundaries change the answer?

Use half-open intervals when checkout at 10:00 should allow the next booking to begin at 10:00. The first interval is [09:00,10:00); the second is [10:00,11:00). They have no common instant.

Official fact: PostgreSQL range values distinguish inclusive and exclusive bounds, empty ranges, and missing bounds. The && operator tests overlap. tstzrange represents a range of timestamps with time zones. Range types and bounds.

An original boundary exercise starts with one confirmed reservation from 09:00 to 10:00 UTC. A booking beginning exactly at 10:00 passes. One beginning at 09:59 conflicts. A contained interval, such as 09:15 to 09:45, conflicts too; overlap does not require crossing an endpoint.

Do not carry a rule for discrete date ranges into continuous timestamp ranges without checking the type. For this exercise, require the application and database to use [) consistently. If cleanup time matters, define the occupied range to include that time. The constraint cannot invent a ten-minute turnaround buffer.

Time-zone conversion is another upstream decision. Resolve a user's local time, including daylight-saving ambiguities, before constructing the timestamp range. Comparing actual instants does not settle which occurrence of an ambiguous local time the customer intended.

Which DDL expresses the complete reservation invariant?

The original schema below models one exclusive resource per reservation. Cancelled rows remain as history but do not block bookings. It deliberately requires finite, nonempty, half-open ranges.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservation (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  tenant_id integer NOT NULL,
  resource_id integer NOT NULL,
  status text NOT NULL
    CHECK (status IN ('confirmed', 'cancelled')),
  during tstzrange NOT NULL,
  CHECK (
    NOT isempty(during)
    AND NOT lower_inf(during)
    AND NOT upper_inf(during)
    AND lower_inc(during)
    AND NOT upper_inc(during)
    AND isfinite(lower(during))
    AND isfinite(upper(during))
  ),
  CONSTRAINT no_confirmed_overlap
    EXCLUDE USING gist (
      tenant_id WITH =,
      resource_id WITH =,
      during WITH &&
    ) WHERE (status = 'confirmed')
);

Read it as three layers. NOT NULL makes the identifying fields and range mandatory. The checks enforce allowed status and interval shape. The exclusion constraint enforces competition between eligible rows.

A null equality operand can prevent all comparisons from being true, so optional resource identity undermines the intended rule. An empty range overlaps nothing, making the separate validity check necessary. Missing bounds and explicit timestamp infinities are different cases: the schema rejects both using bound checks and isfinite. Timestamp finiteness function.

Official fact: a predicate on an exclusion constraint limits the indexed and constrained rows. CREATE TABLE exclusion syntax. Here, two cancelled bookings may overlap; changing one to confirmed subjects it to the rule. An application-only status check would leave another write path able to bypass eligibility enforcement.

Foreign keys, authorization, and valid resource ownership would still belong in a production schema. This compact exercise isolates overlap; it does not claim that tenant identifiers alone authorize a booking.

Why is an availability SELECT insufficient under concurrency?

Consider two transactions at ordinary read committed isolation. Both read a committed snapshot before either competing reservation has committed. A prior query returning no overlap is an observation about that read, not a reservation of the future gap. PostgreSQL transaction isolation.

Wrapping that query and insert in a transaction does not automatically serialize requests. Locking the rows returned by an empty result also does not lock an imaginary reservation row. You need an enforcement mechanism covering the missing-row race.

The exclusion constraint provides the relevant database invariant. Alternatively, a carefully designed locking or serializable-transaction protocol can coordinate the decision, but its scope, retries, and coverage must be explained. “We use transactions” leaves the central question unanswered.

My recommendation is to keep the availability query for user feedback and use the constrained write as the final decision. A calendar can display “available” and subsequently return a conflict because another customer won the race. That is an honest outcome, not proof that the constraint failed.

What should a two-session reservation test show?

Use a disposable database with the schema above. In session A, begin a transaction and insert one reservation:

BEGIN;
INSERT INTO reservation (tenant_id, resource_id, status, during)
VALUES (1, 7, 'confirmed',
  tstzrange('2026-10-01 09:00+00',
            '2026-10-01 10:00+00', '[)'));
-- Leave the transaction open.

In session B, begin another transaction and insert an overlapping booking for the same identifiers:

BEGIN;
SET LOCAL lock_timeout = '15s';
INSERT INTO reservation (tenant_id, resource_id, status, during)
VALUES (1, 7, 'confirmed',
  tstzrange('2026-10-01 09:30+00',
            '2026-10-01 10:30+00', '[)'));

The expected competing-insert behavior is that B waits for the relevant uncommitted transaction. Commit A before B's timeout: B should fail with an exclusion violation. Repeat with a clean conflicting slot, rolling A back instead: B can proceed once the conflict disappears, then commit B.

These are expected lab outcomes, not measurements from a running PostgreSQL server in this article. The local verification checked the interval arithmetic with a Python model; PostgreSQL execution was unavailable. Run both sessions to verify engine behavior, error codes, and timeout settings in your environment. A timeout reached before A finishes is a separate outcome, not a failed overlap invariant.

Extend the test by changing only the resource identifier, then only the tenant identifier. Those cases should not conflict. Also test cancellation followed by reconfirmation, adjacent intervals, empty intervals, and invalid bounds. Checking the final committed rows matters as much as recording the first exception.

Should a conflict be retried, deferred, or upserted?

Official fact: PostgreSQL identifies an exclusion violation with SQLSTATE 23P01. Serialization failures and deadlocks have different codes, 40001 and 40P01. PostgreSQL error codes.

Treat a genuinely occupied slot as a business conflict. Returning HTTP 409 with a useful alternative is one application policy, not a PostgreSQL requirement. Retrying the same occupied slot forever cannot create availability.

For a retryable transaction abort, retry the whole transaction within a bounded policy, reconsidering its reads and writes. Idempotency still matters: a client can lose the response after a successful commit and submit again. An idempotency key solves duplicate intent; exclusion solves incompatible reservations. Neither replaces the other.

Official fact: exclusion constraints can be declared deferrable; the default is not deferrable. Deferred checking moves the constraint check later, potentially to commit. Constraint timing, SET CONSTRAINTS. Temporary overlap during a controlled rescheduling transaction may be useful, but success cannot be reported before the final check and commit succeed.

Do not suggest a generic overlap upsert. PostgreSQL does not support exclusion constraints as arbiters for ON CONFLICT DO UPDATE. INSERT conflict handling. Even conceptually, choosing which existing booking to overwrite is a product decision requiring authorization, not a harmless conflict repair.

Where does the model stop being sufficient?

The rule models one exclusive unit, such as a room or seat. A resource allowing three simultaneous reservations needs another model: individually allocated units or a capacity protocol. Pairwise overlap exclusion would reject the second booking even when capacity remains.

Temporary holds need explicit eligibility and expiry handling. A timestamp passing does not automatically rewrite a row's status. Define how expired holds leave the blocking set, and coordinate that transition with confirmation; do not assume a time-sensitive application query supplies a permanent index predicate.

Adding enforcement to an existing table requires finding existing conflicts and planning the write-blocking impact. Official fact: PostgreSQL 18 does not offer NOT VALID for exclusion constraints. ALTER TABLE restrictions. Avoid copying a foreign-key migration recipe and promising an effortless later validation.

Finally, identify every write path. Imports, administrative updates, and status transitions should reach the same enforced invariant. Operational observability should distinguish rejection, waiting, timeout, and commit uncertainty rather than combining them into “booking failed.”