Exclusion Constraints - No Two Rows May Conflict

Day 29’s CHECK polices one row. Today’s EXCLUDE polices pairs of rows: same room, overlapping time → refuse the double-book.

You open a meeting-room product. Two users click the same 10:00–11:00 slot. Both pass the app’s “is free?” check. Both insert. Without a multi-row rule, you have two confirmed bookings for one room. Exclusion constraints are PostgreSQL’s way to say: for any two rows, this combination of columns must not satisfy a forbidden operator — typically = on room and && (overlaps) on a range.

Worked example: the double-book race

This is the failure mode that ships. App validation is not a lock. Two sessions can both read “free” before either writes.

-- Shared table, no multi-row rule yet
CREATE TABLE bookings (
  id int PRIMARY KEY,
  room_id int NOT NULL,
  during tstzrange NOT NULL
);

-- Session A (T1)                    -- Session B (T2)
BEGIN;                               BEGIN;
-- SELECT: is room 1 free 10–11?
-- both see zero rows → “free”
INSERT INTO bookings VALUES
  (1, 1, tstzrange(
    '2026-07-01 10:00+00',
    '2026-07-01 11:00+00','[)'));
COMMIT;  -- lands                     INSERT INTO bookings VALUES
                                       (2, 1, tstzrange(
                                         '2026-07-01 10:30+00',
                                         '2026-07-01 11:30+00','[)'));
                                     COMMIT;  -- also lands

Both app checks passed. Both commits succeeded. Query the damage:

SELECT COUNT(*) AS rows,
       COUNT(*) FILTER (
         WHERE a.id < b.id
           AND a.room_id = b.room_id
           AND a.during && b.during
       ) AS overlapping_pairs
FROM bookings a, bookings b;
-- without EXCLUDE: 2 rows, 1 overlapping pair

Now add the multi-row wall and retry B’s insert alone:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (
    room_id WITH =,
    during WITH &&
  );

-- A already holds 10:00–11:00 room 1
INSERT INTO bookings VALUES
  (2, 1, tstzrange(
    '2026-07-01 10:30+00',
    '2026-07-01 11:30+00','[)'));
-- ERROR:  conflicting key value violates
-- exclusion constraint "bookings_no_overlap"
-- DETAIL:  Key (room_id, during)=(1, ...)
-- conflicts with existing key ...

Outcome with EXCLUDE: second insert rejected, 1 row left, 0 overlapping pairs. The race still happens in the app; the database is the last line of defense (Day 10: illegal state never commits).

The problem: CHECK cannot see the other row

-- CHECK can force non-empty ranges — still both land
ALTER TABLE bookings
  ADD CONSTRAINT during_nonempty
  CHECK (NOT isempty(during));

INSERT INTO bookings VALUES
  (1, 1, tstzrange('2026-07-01 10:00+00','2026-07-01 11:00+00','[)')),
  (2, 1, tstzrange('2026-07-01 10:30+00','2026-07-01 11:30+00','[)'));
-- Both succeed. Double-booked. CHECK never saw the sibling.

A CHECK on during can force non-empty ranges. It cannot say “no other row for this room overlaps me.” That needs a constraint over pairs.

The mechanism: EXCLUDE USING gist

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id int PRIMARY KEY,
  room_id int NOT NULL,
  during tstzrange NOT NULL,
  CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (
      room_id WITH =,
      during WITH &&
    )
);

Read it as: forbid any two rows where room_id is equal and during overlaps. PostgreSQL builds a GiST index to enforce it. Second overlapping insert → exclusion_violation. The row never lands.

Ranges matter: half-open [)

With half-open ranges, 10:00–11:00 and 11:00–12:00 touch but do not overlap (&& is false). That is what you want for back-to-back meetings. Closed-closed ranges would fight at the boundary. Prefer tstzrange(…, ’[)’) for bookings.

Pair (same room)	Bounds	&& overlaps?	EXCLUDE result
10:00–11:00 and 10:30–11:30	[) both	true	REJECT
10:00–11:00 and 11:00–12:00	[) both	false (touch only)	OK (adjacent)
10:00–11:00 and 11:00–12:00	[] both (closed)	true at 11:00	REJECT (false conflict)
Room 1 10–11 and Room 2 10–11	[)	n/a (room ≠)	OK (different rooms)

Lab confirmed: adjacent same room plus an overlapping slot in another room → 3 rows allowed. Half-open is a product decision encoded in the type, not a UI nicety.

btree_gist: equality + range on one GiST index

GiST natively understands range operators like &&. Plain integer equality (= on room_id) is a B-tree operator class. Mixing them in one EXCLUDE USING gist (…) needs the btree_gist extension, which installs B-tree-equivalent operator classes for GiST.

-- Without btree_gist:
-- ERROR:  data type integer has no default operator class
-- for access method "gist"
CREATE EXTENSION IF NOT EXISTS btree_gist;
-- Now: room_id WITH =  and  during WITH &&  share one GiST index

Pure range exclusions (EXCLUDE USING gist (during WITH &&) only) do not need it. The moment you partition conflict by an equality key (room, resource, tenant), install btree_gist in migrations before creating the constraint.

CHECK vs EXCLUDE vs UNIQUE vs FK

Constraint	Sees	Question it answers	Typical failure
CHECK (Day 29)	This row only	Is this row’s value legal in isolation?	check_violation
EXCLUDE (this lesson)	Pairs of rows	Do these two rows conflict under operators?	exclusion_violation
UNIQUE	Pairs of rows	Are these columns equal on two rows? (EXCLUDE with =)	unique_violation
FOREIGN KEY (Day 15)	This row + parent table	Does the referenced key exist?	foreign_key_violation

UNIQUE is “no two rows equal on these columns.” EXCLUDE generalizes that to “no two rows related by these operators.” FK is a different axis: reference to another table, not mutual exclusion among peers.

Benchmark — integrity first

PostgreSQL 18.4 (Podman). Extension btree_gist. Tables: bookings_no_exclude vs bookings_with_exclude with EXCLUDE USING gist (room_id WITH =, during WITH &&). Headline is double-book prevention; latency is the tax.

Scenario	Result
Overlap inserts without EXCLUDE	2 rows, 1 overlapping pair
Same overlap with EXCLUDE	Rejected; 1 row left; 0 overlapping pairs
Adjacent same room + other room same time	3 rows (both allowed)
Valid bulk 2000 non-overlapping (30-run median)	No EXCLUDE 2130.489 ms · With EXCLUDE 2204.263 ms · 1.035× (+3.46%)
100 intentional overlaps / rejection path (30-run median)	1960.519 ms

What breaks? — Anti-patterns

1. SELECT free → INSERT in the app only. Two transactions both see free; both insert; calendar lies. Fix: EXCLUDE (or lock the room range) in the database — app checks are UX, not integrity.

2. Using CHECK for multi-row rules. CHECK never sees siblings. Fix: EXCLUDE or a trigger (prefer EXCLUDE when operators fit).

3. Wrong range bounds. Closed [] makes “back-to-back” look like overlap at the shared endpoint; product says adjacent is fine, constraint says no. Fix: half-open [) consistently on every writer.

4. Forgetting btree_gist. Equality on int + range on GiST needs the extension; CREATE TABLE fails with “no default operator class for gist.” Fix: CREATE EXTENSION btree_gist in migrations before the constraint.

5. Assuming EXCLUDE is free or “just an index.” Lab bulk tax was 1.035×. Wrong claim is “zero cost”; right claim is “small tax, correctness headline.”

How it connects

• Day 29 (CHECK): single-row domain integrity → multi-row conflict integrity. Same “push the rule to the system of record” theme; different scope (one row vs pairs).
• Day 30 (idempotency keys): retries must not double-charge; calendars must not double-book — same “exactly once effect” mindset, different layer (API key store vs peer-row exclusion).
• Day 34 (deferred constraints): deferral is a timing axis (check at COMMIT vs mid-statement). EXCLUDE answers a scope axis (pairs of rows). You can make some constraints DEFERRABLE; do not confuse “when” with “what conflict.” Multi-statement loaders may need Day 34; double-books need Day 31 now.
• Day 36 (deadlocks): multi-row integrity under concurrency can contend on the same GiST leaf / room key. EXCLUDE prevents the bad state; lock order and short transactions still matter when two writers race the same room.
• Day 10 (ACID) / Day 15 (FK): failed EXCLUDE aborts the statement; no half-booked calendar. FK = reference to a parent; EXCLUDE = mutual exclusion between peers.

Transfer questions

1. You store hotel stays as [check_in, check_out). Can two stays for the same room touch at noon checkout/check-in? How do half-open ranges help, and what goes wrong if writers mix [] and [)?
2. Why does EXCLUDE USING gist (room_id WITH =, during WITH &&) need btree_gist when a pure range EXCLUDE might not?
3. Two concurrent transactions both run “is free?” then insert. Explain the race and how EXCLUDE ends it without application locks. How is that different from Day 34’s deferral (timing) vs this lesson’s multi-row scope?

What you should be able to do

• Write an EXCLUDE that prevents overlapping bookings per room.
• Walk the double-book race: both app checks pass; only EXCLUDE rejects the second write.
• Explain why CHECK is not enough for double-booking.
• Choose half-open ranges for adjacent slots; read a timeline table of OK vs REJECT.
• Name the extension required for int equality + range on GiST.
• Contrast CHECK / EXCLUDE / UNIQUE / FK in one table (scope + question each answers).
• Connect multi-row integrity (29/31) to deferral timing (34) and concurrent contention (36).
• Report the lab: 2 rows / 1 overlap without EXCLUDE; reject + 1 row with EXCLUDE; bulk 1.035×; rejection path 1960.519 ms.

Your teach step

Close this lesson. From memory: explain like I’m 10 (two kids can’t sit in the same seat), then a 60-second LinkedIn version with the race (both apps say free), 2 rows / 1 overlap vs rejected, half-open adjacent OK, and the ~1.035× honest tax. Post it, paste the link.

Questions? Ask the agent — exclusion with && on int4range, soft-delete partial EXCLUDE, or serializable vs EXCLUDE tradeoffs.