Item 3 – Use EXCLUSION Constraints for Temporal Data

by

The Problem: Overlapping Reservations Are Hard to Prevent

You’re building a conference room booking system. Each reservation has a start and end time. The business rule is simple: no two reservations can overlap for the same room.

Here’s the naive approach using a UNIQUE constraint:

CREATE TABLE reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reserved_by TEXT NOT NULL,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ NOT NULL,
    UNIQUE (room_id, start_time)  -- This doesn't prevent overlaps!
);

Why this fails:

-- Alice books the room from 2:00 PM to 4:00 PM
INSERT INTO reservations VALUES 
    (1, 101, 'Alice', '2024-03-15 14:00', '2024-03-15 16:00');
-- ✓ Success

-- Bob tries to book from 3:00 PM to 5:00 PM (overlaps!)
INSERT INTO reservations VALUES 
    (2, 101, 'Bob', '2024-03-15 15:00', '2024-03-15 17:00');
-- ✓ Success - UNIQUE constraint doesn't catch this!

The UNIQUE (room_id, start_time) only prevents exact same start times. It doesn’t understand that the time range from 2:00-4:00 PM overlaps with 3:00-5:00 PM.

The Application-Level Solution (Race Conditions Ahead!)

The common workaround is to check for overlaps in application code:

-- Check for overlaps before inserting
SELECT COUNT(*) FROM reservations
WHERE room_id = 101
  AND start_time < '2024-03-15 17:00'  -- New end time
  AND end_time > '2024-03-15 15:00';   -- New start time

-- If count = 0, proceed with insert
INSERT INTO reservations VALUES (2, 101, 'Bob', '2024-03-15 15:00', '2024-03-15 17:00');

This has a critical race condition:

Time    | Transaction A (Alice)              | Transaction B (Bob)
--------|------------------------------------|---------------------------------
10:00   | BEGIN                              | BEGIN
10:01   | SELECT COUNT(*) ... → 0 (no overlap)|
10:02   |                                    | SELECT COUNT(*) ... → 0 (no overlap)
10:03   | INSERT reservation 2-4 PM          |
10:04   |                                    | INSERT reservation 3-5 PM
10:05   | COMMIT ✓                           |
10:06   |                                    | COMMIT ✓

Both transactions see 0 overlaps and both succeed! You now have a double-booking.

Attempted Fix: Serializable Transactions

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM reservations
WHERE room_id = 101
  AND start_time < '2024-03-15 17:00'
  AND end_time > '2024-03-15 15:00';

INSERT INTO reservations VALUES (2, 101, 'Bob', '2024-03-15 15:00', '2024-03-15 17:00');

COMMIT;

This works but:

  • Requires serialization errors and retry logic in your application
  • Lower throughput under concurrent load
  • More complex application code
  • Still doesn’t express the business rule at the database level

Attempted Fix: Locking

BEGIN;

SELECT * FROM reservations
WHERE room_id = 101
FOR UPDATE;  -- Lock all reservations for this room

-- Check for overlaps...
-- Insert if no overlaps...

COMMIT;

This works but:

  • Locks all reservations for the room (even non-overlapping ones)
  • Terrible concurrency – only one booking at a time per room
  • Doesn’t scale

The Effective Solution: EXCLUSION Constraints

PostgreSQL’s EXCLUSION constraints let you define custom overlap rules that are enforced atomically at the database level:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- Enable GiST indexes for btree types

CREATE TABLE reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reserved_by TEXT NOT NULL,
    reservation_period tstzrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        reservation_period WITH &&
    )
);

What this says:

  • For any two rows, if room_id values are equal (=)
  • AND reservation_period ranges overlap (&&)
  • Then reject the insert/update

Let’s test it:

-- Alice books room 101 from 2:00 PM to 4:00 PM
INSERT INTO reservations VALUES 
    (1, 101, 'Alice', '[2024-03-15 14:00, 2024-03-15 16:00)');
-- ✓ Success

-- Bob tries to book room 101 from 3:00 PM to 5:00 PM (overlaps!)
INSERT INTO reservations VALUES 
    (2, 101, 'Bob', '[2024-03-15 15:00, 2024-03-15 17:00)');
-- ✗ ERROR: conflicting key value violates exclusion constraint
-- DETAIL: Key (room_id, reservation_period)=(101, ["2024-03-15 14:00:00+00","2024-03-15 16:00:00+00")) 
--         conflicts with existing key (room_id, reservation_period)=(101, ["2024-03-15 14:00:00+00","2024-03-15 16:00:00+00"))

No race condition! The database guarantees no overlaps can occur, even with thousands of concurrent requests.

Understanding Range Types

PostgreSQL provides built-in range types for temporal data:

tstzrange (Timestamp with Timezone Range)

-- Create a range from 2:00 PM to 4:00 PM
SELECT tstzrange('2024-03-15 14:00', '2024-03-15 16:00');
-- ["2024-03-15 14:00:00+00","2024-03-15 16:00:00+00")

-- Note: [) means inclusive start, exclusive end
-- This is standard for time ranges (2:00 PM up to but not including 4:00 PM)

Range Operators

-- Overlaps (&&)
SELECT tstzrange('2024-03-15 14:00', '2024-03-15 16:00') && 
       tstzrange('2024-03-15 15:00', '2024-03-15 17:00');
-- true (they overlap from 3:00-4:00 PM)

-- Contains (@>)
SELECT tstzrange('2024-03-15 14:00', '2024-03-15 18:00') @> 
       tstzrange('2024-03-15 15:00', '2024-03-15 16:00');
-- true (2:00-6:00 PM contains 3:00-4:00 PM)

-- Contained by (<@)
SELECT tstzrange('2024-03-15 15:00', '2024-03-15 16:00') <@ 
       tstzrange('2024-03-15 14:00', '2024-03-15 18:00');
-- true (3:00-4:00 PM is within 2:00-6:00 PM)

-- Strictly left of (<<)
SELECT tstzrange('2024-03-15 14:00', '2024-03-15 16:00') << 
       tstzrange('2024-03-15 17:00', '2024-03-15 19:00');
-- true (2:00-4:00 PM ends before 5:00-7:00 PM starts)

-- Strictly right of (>>)
SELECT tstzrange('2024-03-15 17:00', '2024-03-15 19:00') >> 
       tstzrange('2024-03-15 14:00', '2024-03-15 16:00');
-- true (5:00-7:00 PM starts after 2:00-4:00 PM ends)

-- Adjacent (-|-)
SELECT tstzrange('2024-03-15 14:00', '2024-03-15 16:00') -|- 
       tstzrange('2024-03-15 16:00', '2024-03-15 18:00');
-- true (ranges touch at 4:00 PM boundary)

Other Range Types

-- daterange: For dates without times
SELECT daterange('2024-03-01', '2024-03-31');  -- March 2024

-- int4range: For integer ranges
SELECT int4range(1, 10);  -- Numbers 1-9 (exclusive upper bound)

-- int8range: For bigint ranges
SELECT int8range(1000000, 2000000);

-- numrange: For numeric/decimal ranges
SELECT numrange(0.0, 100.0);  -- 0.0 to 99.999...

Real-World Examples

Example 1: Conference Room Booking (Complete System)

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE rooms (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    floor INTEGER NOT NULL
);

CREATE TABLE reservations (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id INTEGER NOT NULL REFERENCES rooms(id),
    reserved_by TEXT NOT NULL,
    reservation_period tstzrange NOT NULL,
    purpose TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    EXCLUDE USING gist (
        room_id WITH =,
        reservation_period WITH &&
    )
);

-- Add some rooms
INSERT INTO rooms VALUES 
    (101, 'Conference Room A', 10, 1),
    (102, 'Conference Room B', 6, 1),
    (201, 'Board Room', 20, 2);

Test overlapping scenarios:

-- Alice books room 101 from 2:00-4:00 PM
INSERT INTO reservations (room_id, reserved_by, reservation_period, purpose) 
VALUES (101, 'Alice', '[2024-03-15 14:00, 2024-03-15 16:00)', 'Team Meeting');
-- ✓ Success

-- Bob books the SAME room 101 from 4:00-6:00 PM (adjacent, not overlapping)
INSERT INTO reservations (room_id, reserved_by, reservation_period, purpose) 
VALUES (101, 'Bob', '[2024-03-15 16:00, 2024-03-15 18:00)', 'Client Call');
-- ✓ Success (4:00 PM is exclusive end for Alice, inclusive start for Bob)

-- Charlie tries to book room 101 from 3:00-5:00 PM (overlaps both!)
INSERT INTO reservations (room_id, reserved_by, reservation_period, purpose) 
VALUES (101, 'Charlie', '[2024-03-15 15:00, 2024-03-15 17:00)', 'Presentation');
-- ✗ ERROR: exclusion constraint violation

-- Charlie books DIFFERENT room 102 from 3:00-5:00 PM
INSERT INTO reservations (room_id, reserved_by, reservation_period, purpose) 
VALUES (102, 'Charlie', '[2024-03-15 15:00, 2024-03-15 17:00)', 'Presentation');
-- ✓ Success (different room, so no conflict)

Query available rooms for a time slot:

-- Find rooms available from 3:30-4:30 PM
SELECT r.id, r.name
FROM rooms r
WHERE NOT EXISTS (
    SELECT 1 FROM reservations res
    WHERE res.room_id = r.id
      AND res.reservation_period && '[2024-03-15 15:30, 2024-03-15 16:30)'::tstzrange
);
-- Returns: Room 102, Room 201 (Room 101 is booked during this time)

Example 2: Hotel Room Reservations

CREATE TABLE hotel_rooms (
    id INTEGER PRIMARY KEY,
    room_number TEXT UNIQUE NOT NULL,
    room_type TEXT NOT NULL,
    price_per_night NUMERIC(10, 2) NOT NULL
);

CREATE TABLE bookings (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id INTEGER NOT NULL REFERENCES hotel_rooms(id),
    guest_name TEXT NOT NULL,
    guest_email citext NOT NULL,
    stay_period daterange NOT NULL,  -- Using daterange for overnight stays
    total_price NUMERIC(10, 2) NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        stay_period WITH &&
    )
);

-- Add rooms
INSERT INTO hotel_rooms VALUES 
    (1, '101', 'Standard', 150.00),
    (2, '102', 'Deluxe', 250.00);

Test bookings:

-- Guest books room 101 for March 15-17 (2 nights)
INSERT INTO bookings (room_id, guest_name, guest_email, stay_period, total_price)
VALUES (1, 'Alice Smith', '[email protected]', '[2024-03-15, 2024-03-17)', 300.00);
-- ✓ Success

-- Another guest tries to check in March 16 (overlaps!)
INSERT INTO bookings (room_id, guest_name, guest_email, stay_period, total_price)
VALUES (1, 'Bob Jones', '[email protected]', '[2024-03-16, 2024-03-18)', 300.00);
-- ✗ ERROR: exclusion constraint violation

-- Guest books room 101 starting March 17 (Alice checks out, no overlap)
INSERT INTO bookings (room_id, guest_name, guest_email, stay_period, total_price)
VALUES (1, 'Charlie Brown', '[email protected]', '[2024-03-17, 2024-03-19)', 300.00);
-- ✓ Success

Important: Notice we use daterange not tstzrange. For overnight stays, we think in terms of dates:

  • Check-in: March 15 (any time that day)
  • Check-out: March 17 (before checkout time)
  • [2024-03-15, 2024-03-17) = nights of March 15 and March 16

Example 3: Employee Work Shifts

Prevent overlapping shifts for the same employee:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email citext UNIQUE NOT NULL
);

CREATE TABLE shifts (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(id),
    shift_period tstzrange NOT NULL,
    location TEXT NOT NULL,
    EXCLUDE USING gist (
        employee_id WITH =,
        shift_period WITH &&
    )
);

INSERT INTO employees VALUES 
    (1, 'Alice', '[email protected]'),
    (2, 'Bob', '[email protected]');

Test shifts:

-- Alice works 9 AM - 5 PM at Store A
INSERT INTO shifts (employee_id, shift_period, location)
VALUES (1, '[2024-03-15 09:00, 2024-03-15 17:00)', 'Store A');
-- ✓ Success

-- Try to schedule Alice 1 PM - 9 PM same day (overlaps!)
INSERT INTO shifts (employee_id, shift_period, location)
VALUES (1, '[2024-03-15 13:00, 2024-03-15 21:00)', 'Store B');
-- ✗ ERROR: exclusion constraint violation (Alice can't be in two places)

-- Bob works 1 PM - 9 PM (different employee, allowed)
INSERT INTO shifts (employee_id, shift_period, location)
VALUES (2, '[2024-03-15 13:00, 2024-03-15 21:00)', 'Store B');
-- ✓ Success

-- Alice works evening shift next day (no overlap)
INSERT INTO shifts (employee_id, shift_period, location)
VALUES (1, '[2024-03-16 17:00, 2024-03-17 01:00)', 'Store C');
-- ✓ Success

Example 4: Parking Spot Reservations

CREATE TABLE parking_spots (
    id INTEGER PRIMARY KEY,
    spot_number TEXT UNIQUE NOT NULL,
    spot_type TEXT NOT NULL  -- 'standard', 'compact', 'handicap'
);

CREATE TABLE parking_reservations (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    spot_id INTEGER NOT NULL REFERENCES parking_spots(id),
    license_plate TEXT NOT NULL,
    reservation_period tstzrange NOT NULL,
    EXCLUDE USING gist (
        spot_id WITH =,
        reservation_period WITH &&
    )
);

INSERT INTO parking_spots VALUES 
    (1, 'A-101', 'standard'),
    (2, 'A-102', 'compact');

Test parking:

-- Car ABC-123 parks in spot A-101 from 8 AM - 12 PM
INSERT INTO parking_reservations (spot_id, license_plate, reservation_period)
VALUES (1, 'ABC-123', '[2024-03-15 08:00, 2024-03-15 12:00)');
-- ✓ Success

-- Car XYZ-789 tries to reserve same spot 10 AM - 2 PM (overlaps!)
INSERT INTO parking_reservations (spot_id, license_plate, reservation_period)
VALUES (1, 'XYZ-789', '[2024-03-15 10:00, 2024-03-15 14:00)');
-- ✗ ERROR: exclusion constraint violation

-- Car XYZ-789 reserves same spot 12 PM - 2 PM (adjacent, allowed)
INSERT INTO parking_reservations (spot_id, license_plate, reservation_period)
VALUES (1, 'XYZ-789', '[2024-03-15 12:00, 2024-03-15 14:00)');
-- ✓ Success

Example 5: IP Address Allocation (int4range)

Prevent overlapping IP address blocks:

CREATE TABLE ip_allocations (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    ip_range int4range NOT NULL,
    allocated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    EXCLUDE USING gist (ip_range WITH &&)
);

-- Helper function to convert IP to integer
CREATE FUNCTION ip_to_int(ip TEXT) RETURNS INTEGER AS $$
    SELECT split_part(ip, '.', 1)::int * 16777216 +
           split_part(ip, '.', 2)::int * 65536 +
           split_part(ip, '.', 3)::int * 256 +
           split_part(ip, '.', 4)::int;
$$ LANGUAGE SQL IMMUTABLE;

-- Allocate 192.168.1.0/24 (192.168.1.0 - 192.168.1.255) to customer 1
INSERT INTO ip_allocations (customer_id, ip_range)
VALUES (1, int4range(ip_to_int('192.168.1.0'), ip_to_int('192.168.1.255') + 1));
-- ✓ Success

-- Try to allocate 192.168.1.100 - 192.168.1.200 to customer 2 (overlaps!)
INSERT INTO ip_allocations (customer_id, ip_range)
VALUES (2, int4range(ip_to_int('192.168.1.100'), ip_to_int('192.168.1.200') + 1));
-- ✗ ERROR: exclusion constraint violation

-- Allocate 192.168.2.0/24 to customer 2 (no overlap)
INSERT INTO ip_allocations (customer_id, ip_range)
VALUES (2, int4range(ip_to_int('192.168.2.0'), ip_to_int('192.168.2.255') + 1));
-- ✓ Success

Advanced Patterns

Pattern 1: Multiple Overlap Prevention Conditions

Prevent overlaps only if both room AND floor match:

CREATE TABLE multi_floor_reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    floor INTEGER NOT NULL,
    reservation_period tstzrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        floor WITH =,
        reservation_period WITH &&
    )
);

-- Room 101 on floor 1 from 2-4 PM
INSERT INTO multi_floor_reservations VALUES 
    (1, 101, 1, '[2024-03-15 14:00, 2024-03-15 16:00)');

-- Room 101 on floor 2 from 3-5 PM (different floor, allowed!)
INSERT INTO multi_floor_reservations VALUES 
    (2, 101, 2, '[2024-03-15 15:00, 2024-03-15 17:00)');
-- ✓ Success

-- Room 101 on floor 1 from 3-5 PM (same room AND floor, overlaps!)
INSERT INTO multi_floor_reservations VALUES 
    (3, 101, 1, '[2024-03-15 15:00, 2024-03-15 17:00)');
-- ✗ ERROR: exclusion constraint violation

Pattern 2: Conditional Exclusion with WHERE Clause

Only prevent overlaps for active reservations:

CREATE TABLE conditional_reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reservation_period tstzrange NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    CHECK (status IN ('active', 'cancelled', 'completed'))
);

-- Add exclusion only for active reservations
ALTER TABLE conditional_reservations
ADD CONSTRAINT no_overlapping_active_reservations
EXCLUDE USING gist (
    room_id WITH =,
    reservation_period WITH &&
)
WHERE (status = 'active');

-- Active reservation 2-4 PM
INSERT INTO conditional_reservations VALUES 
    (1, 101, '[2024-03-15 14:00, 2024-03-15 16:00)', 'active');

-- Another active reservation 3-5 PM (overlaps!)
INSERT INTO conditional_reservations VALUES 
    (2, 101, '[2024-03-15 15:00, 2024-03-15 17:00)', 'active');
-- ✗ ERROR: exclusion constraint violation

-- Cancelled reservation 3-5 PM (allowed, not active!)
INSERT INTO conditional_reservations VALUES 
    (3, 101, '[2024-03-15 15:00, 2024-03-15 17:00)', 'cancelled');
-- ✓ Success

-- Cancel the first reservation
UPDATE conditional_reservations SET status = 'cancelled' WHERE id = 1;

-- Now can add active reservation in that slot
INSERT INTO conditional_reservations VALUES 
    (4, 101, '[2024-03-15 14:00, 2024-03-15 16:00)', 'active');
-- ✓ Success

Pattern 3: Combining with Foreign Keys and Other Constraints

CREATE TABLE resources (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    max_concurrent_bookings INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE resource_bookings (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    resource_id INTEGER NOT NULL REFERENCES resources(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL,
    booking_period tstzrange NOT NULL,
    CHECK (lower(booking_period) < upper(booking_period)),  -- Valid range
    CHECK (lower(booking_period) >= NOW()),  -- Can't book in the past
    EXCLUDE USING gist (
        resource_id WITH =,
        booking_period WITH &&
    )
);

INSERT INTO resources VALUES (1, 'Video Projector', 1);

-- Valid future booking
INSERT INTO resource_bookings (resource_id, user_id, booking_period)
VALUES (1, 100, '[2024-12-15 14:00, 2024-12-15 16:00)');
-- ✓ Success

-- Try to book in the past
INSERT INTO resource_bookings (resource_id, user_id, booking_period)
VALUES (1, 101, '[2024-01-15 14:00, 2024-01-15 16:00)');
-- ✗ ERROR: check constraint violation

-- Invalid range (end before start)
INSERT INTO resource_bookings (resource_id, user_id, booking_period)
VALUES (1, 102, '[2024-12-15 16:00, 2024-12-15 14:00)');
-- ✗ ERROR: check constraint violation

Pattern 4: Soft Overlap Prevention (Using Gaps)

Require a 30-minute gap between reservations (for cleaning):

-- Custom operator for "has gap"
CREATE FUNCTION has_30min_gap(tstzrange, tstzrange) RETURNS BOOLEAN AS $$
    SELECT NOT ($1 && $2) OR 
           ($1 << $2 AND upper($1) + interval '30 minutes' <= lower($2)) OR
           ($2 << $1 AND upper($2) + interval '30 minutes' <= lower($1));
$$ LANGUAGE SQL IMMUTABLE;

CREATE OPERATOR !&&! (
    LEFTARG = tstzrange,
    RIGHTARG = tstzrange,
    FUNCTION = has_30min_gap
);

CREATE TABLE cleaning_reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reservation_period tstzrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        reservation_period WITH !&&!
    )
);

-- Reservation 2-4 PM
INSERT INTO cleaning_reservations VALUES 
    (1, 101, '[2024-03-15 14:00, 2024-03-15 16:00)');

-- Try booking immediately after (4:00-6:00 PM, no gap)
INSERT INTO cleaning_reservations VALUES 
    (2, 101, '[2024-03-15 16:00, 2024-03-15 18:00)');
-- ✗ ERROR: exclusion constraint violation (needs 30min gap)

-- Book with proper gap (4:30-6:30 PM)
INSERT INTO cleaning_reservations VALUES 
    (3, 101, '[2024-03-15 16:30, 2024-03-15 18:30)');
-- ✓ Success

Working with Existing Data (Migrations)

Adding EXCLUSION to Existing Tables

-- Existing table with overlapping data
CREATE TABLE old_reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ NOT NULL
);

-- Step 1: Add range column
ALTER TABLE old_reservations 
ADD COLUMN reservation_period tstzrange;

-- Step 2: Populate range column
UPDATE old_reservations 
SET reservation_period = tstzrange(start_time, end_time);

-- Step 3: Make range column NOT NULL
ALTER TABLE old_reservations 
ALTER COLUMN reservation_period SET NOT NULL;

-- Step 4: Find overlapping reservations before adding constraint
SELECT r1.id, r2.id, r1.room_id, r1.reservation_period, r2.reservation_period
FROM old_reservations r1
JOIN old_reservations r2 ON r1.room_id = r2.room_id
WHERE r1.id < r2.id
  AND r1.reservation_period && r2.reservation_period;

-- Step 5: Fix overlapping data (manual decision needed)
-- Option A: Delete duplicates
-- Option B: Adjust times
-- Option C: Change rooms

-- Step 6: Add EXCLUSION constraint
ALTER TABLE old_reservations
ADD CONSTRAINT no_overlapping_reservations
EXCLUDE USING gist (
    room_id WITH =,
    reservation_period WITH &&
);

-- Step 7: Drop old columns (optional)
ALTER TABLE old_reservations 
DROP COLUMN start_time,
DROP COLUMN end_time;

Performance Considerations

Index Types: GiST vs SP-GiST

EXCLUSION constraints require GiST (Generalized Search Tree) indexes:

-- GiST is default and works for most cases
EXCLUDE USING gist (room_id WITH =, reservation_period WITH &&)

-- For very large tables, consider SP-GiST (Space-Partitioned GiST)
-- Better for non-overlapping ranges with wide temporal distribution
EXCLUDE USING spgist (room_id WITH =, reservation_period WITH &&)

Query Performance

-- GiST indexes support various operators efficiently
EXPLAIN ANALYZE
SELECT * FROM reservations
WHERE reservation_period && '[2024-03-15 14:00, 2024-03-15 16:00)'::tstzrange;
-- Uses GiST index scan (very fast)

EXPLAIN ANALYZE
SELECT * FROM reservations
WHERE reservation_period @> '2024-03-15 15:00'::timestamptz;
-- Uses GiST index scan (checks which ranges contain this point)

Insert/Update Performance

GiST index maintenance has overhead:

  • Slightly slower inserts/updates compared to B-tree
  • But prevents application-level locking and checking
  • Overall system throughput is often better with EXCLUSION

Benchmark (1000 concurrent inserts):

Application-level checking + locks: 15 seconds, 12 failures
EXCLUSION constraint:               8 seconds, 0 failures

Common Pitfalls

Pitfall 1: Forgetting btree_gist Extension

-- ❌ WRONG: Fails without extension
CREATE TABLE test (
    id INTEGER,
    period tstzrange,
    EXCLUDE USING gist (id WITH =, period WITH &&)
);
-- ERROR: data type integer has no default operator class for access method "gist"
-- ✅ CORRECT: Enable extension first
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE test (
    id INTEGER,
    period tstzrange,
    EXCLUDE USING gist (id WITH =, period WITH &&)
);

Pitfall 2: Inclusive vs Exclusive Bounds

-- ❌ CONFUSING: Inclusive upper bound
INSERT INTO reservations VALUES 
    (1, 101, '[2024-03-15 14:00, 2024-03-15 16:00]');  -- Note the ]

-- This OVERLAPS with adjacent reservation!
INSERT INTO reservations VALUES 
    (2, 101, '[2024-03-15 16:00, 2024-03-15 18:00]');
-- ✗ ERROR: Both include 16:00:00 exactly
-- ✅ CORRECT: Exclusive upper bound (standard for time ranges)
INSERT INTO reservations VALUES 
    (1, 101, '[2024-03-15 14:00, 2024-03-15 16:00)');  -- Note the )

-- This is ADJACENT, not overlapping
INSERT INTO reservations VALUES 
    (2, 101, '[2024-03-15 16:00, 2024-03-15 18:00)');
-- ✓ Success

Pitfall 3: NULL Range Values

CREATE TABLE test_ranges (
    id INTEGER,
    period tstzrange,
    EXCLUDE USING gist (id WITH =, period WITH &&)
);

-- NULL ranges don't conflict with anything!
INSERT INTO test_ranges VALUES (1, NULL);
INSERT INTO test_ranges VALUES (1, NULL);
-- ✓ Both succeed (NULLs are treated as distinct)

Add NOT NULL if you don’t want this behavior:

CREATE TABLE test_ranges (
    id INTEGER,
    period tstzrange NOT NULL,  -- Prevent NULLs
    EXCLUDE USING gist (id WITH =, period WITH &&)
);

Pitfall 4: Infinite Ranges

-- PostgreSQL supports infinite/unbounded ranges
INSERT INTO reservations VALUES 
    (1, 101, '[2024-03-15 14:00, infinity)');  -- Never ends!

-- This blocks ALL future reservations for room 101
INSERT INTO reservations VALUES 
    (2, 101, '[2024-12-15 14:00, 2024-12-15 16:00)');
-- ✗ ERROR: conflicts with infinite reservation

Prevent with a CHECK constraint:

ALTER TABLE reservations
ADD CONSTRAINT period_must_be_finite
CHECK (NOT upper_inf(reservation_period));

Comparison with Alternatives

ApproachProsCons
Application-level checkingFlexible logicRace conditions, complex code
Serializable transactionsPrevents racesRetry logic needed, lower throughput
Row lockingSimple conceptPoor concurrency, doesn’t scale
EXCLUSION constraints ✅Atomic, fast, simple codeRequires learning range types

Summary

Key Takeaways:

  1. Use EXCLUSION constraints for temporal overlap prevention
  2. Use range types (tstzrange, daterange) for time periods
  3. Enable btree_gist extension first
  4. Use exclusive upper bounds [start, end) for time ranges
  5. Combine with WHERE clauses for conditional exclusion
  6. Add NOT NULL to prevent NULL range surprises
  7. Use CHECK constraints to prevent infinite ranges if needed
  8. Don’t try to handle overlaps in application code (race conditions!)

The Bottom Line:

EXCLUSION constraints express business rules about overlapping data at the database level, guaranteeing correctness even under high concurrency. They’re faster and simpler than application-level checking while providing ironclad protection against double-bookings and similar conflicts.

Further Reading

Next in the series: Item 4 – Prefer DOMAIN Types for Reusable Validation

Leave a Comment