Range/Multirange Types, Exclusion Constraints, Temporal Scheduling, and Overlap Models

Learning outcomes

ServiceHub schedules technicians in time windows. Storing start_at and end_at as unrelated scalar columns makes overlap logic repetitive and easy to race. PostgreSQL range types make containment, overlap, adjacency, emptiness, and unbounded intervals first-class operations, while exclusion constraints let the database enforce non-overlap under concurrent transactions.

1. Range values encode their boundary semantics

SELECT  '[2026-08-01,2026-08-31]'::daterange AS input_canonicalized,  lower('[2026-08-01,2026-08-31]'::daterange) AS lower_bound,  upper('[2026-08-01,2026-08-31]'::daterange) AS upper_bound,  lower_inc('[2026-08-01,2026-08-31]'::daterange) AS lower_inclusive,  upper_inc('[2026-08-01,2026-08-31]'::daterange) AS upper_inclusive;

daterange is discrete and canonicalizes equivalent representations to lower-inclusive/upper-exclusive [). Thus an inclusive August 31 upper date displays as September 1 exclusive. Continuous timestamp ranges do not have the same discrete next-value canonicalization.

SELECT  isempty('[4,4)'::int4range) AS empty_range,  '(,)'::tstzrange AS all_timestamps,  lower_inf('(,2026-08-20 00:00+00)'::tstzrange) AS no_lower_bound,  upper_inf('[2026-08-20 00:00+00,)'::tstzrange) AS no_upper_bound;

Missing bounds are special unbounded range semantics, not ordinary finite subtype values. The literal empty represents a range containing no points.

2. Overlap and adjacency are different

SELECT  tstzrange('2026-08-18 10:00+00','2026-08-18 11:00+00','[)')    && tstzrange('2026-08-18 10:30+00','2026-08-18 11:30+00','[)')       AS overlaps,  tstzrange('2026-08-18 10:00+00','2026-08-18 11:00+00','[)')    -|- tstzrange('2026-08-18 11:00+00','2026-08-18 12:00+00','[)')       AS adjacent_not_overlapping,  tstzrange('2026-08-18 10:00+00','2026-08-18 12:00+00','[)')    @> TIMESTAMPTZ '2026-08-18 11:30+00'       AS contains_time;

Half-open scheduling ranges let one appointment end exactly when the next begins without overlap. The adjacency operator -|- captures that “touching but non-overlapping” relationship.

3. Multiranges represent several disjoint windows

SELECT tstzmultirange(  tstzrange('2026-08-18 08:00+00','2026-08-18 12:00+00','[)'),  tstzrange('2026-08-18 13:00+00','2026-08-18 17:00+00','[)')) AS working_windows;SELECT tstzmultirange(  tstzrange('2026-08-18 08:00+00','2026-08-18 12:00+00','[)'),  tstzrange('2026-08-18 13:00+00','2026-08-18 17:00+00','[)')) @> TIMESTAMPTZ '2026-08-18 14:00+00' AS available_at_14;

A multirange is an ordered set of non-empty, non-overlapping ranges. Constructors normalize overlaps/adjacent pieces as needed. It is useful for availability windows, but if each interval needs separate identity, reason, owner, or approval state, model them as child rows.

4. Build a range-indexed booking table

DROP TABLE IF EXISTS app.ch17_booking_bad;DROP TABLE IF EXISTS app.ch17_booking;CREATE TABLE app.ch17_booking_bad (  booking_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  technician_id bigint NOT NULL,  during tstzrange NOT NULL CHECK (NOT isempty(during)));CREATE INDEX ch17_booking_bad_during_gistON app.ch17_booking_bad USING GIST (during);EXPLAIN (COSTS OFF)SELECT *FROM app.ch17_booking_badWHERE during && tstzrange(  '2026-08-18 10:00+00','2026-08-18 11:00+00','[)');

GiST can accelerate range operators including overlap &&, containment, positional comparisons, and adjacency. The index accelerates search; it does not by itself enforce a no-overlap business rule.

5. Wrong approach: application-only precheck races

Consider this common transaction pattern on ch17_booking_bad:

BEGIN;SELECT count(*) AS conflictsFROM app.ch17_booking_badWHERE technician_id = 7  AND during && tstzrange(    '2026-08-18 10:00+00','2026-08-18 11:00+00','[)'  );-- returns 0; keep the transaction open

BEGIN;SELECT count(*) AS conflictsFROM app.ch17_booking_badWHERE technician_id = 7  AND during && tstzrange(    '2026-08-18 10:30+00','2026-08-18 11:30+00','[)'  );-- also returns 0INSERT INTO app.ch17_booking_bad(technician_id,during)VALUES (  7,  tstzrange('2026-08-18 10:30+00','2026-08-18 11:30+00','[)'));COMMIT;

INSERT INTO app.ch17_booking_bad(technician_id,during)VALUES (  7,  tstzrange('2026-08-18 10:00+00','2026-08-18 11:00+00','[)'));COMMIT;SELECT * FROM app.ch17_booking_badWHERE technician_id = 7ORDER BY booking_id;

Under ordinary MVCC both SELECT checks can truthfully observe no committed conflicting row and both inserts can succeed. A transaction boundary around the application check is not enough to make the invariant atomic.

6. Repair with a GiST-backed exclusion constraint

To combine scalar technician equality with range overlap in one GiST constraint, use the PostgreSQL-supplied btree_gist extension. It is an optional bundled extension, not a third-party service.

On some operating-system packages, supplied contrib modules are installed through a separate postgresql-contrib-style package. If pg_available_extensions does not list btree_gist, install the matching PostgreSQL 18 contrib package rather than substituting an unrelated third-party extension.

SELECT name, default_version, installed_versionFROM pg_available_extensionsWHERE name = 'btree_gist';-- btree_gist is trusted in PostgreSQL 18:-- a non-superuser may install it with CREATE privilege on this database.CREATE EXTENSION IF NOT EXISTS btree_gist;CREATE TABLE app.ch17_booking (  booking_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  technician_id bigint NOT NULL,  during tstzrange NOT NULL CHECK (NOT isempty(during)),  CONSTRAINT ch17_booking_no_overlap  EXCLUDE USING GIST (    technician_id WITH =,    during WITH &&  ));

The exclusion constraint creates its supporting GiST index. Its rule is: for any two rows, PostgreSQL must not find both the same technician and overlapping ranges simultaneously.

INSERT INTO app.ch17_booking(technician_id,during) VALUES(7, tstzrange('2026-08-18 10:00+00','2026-08-18 11:00+00','[)'));-- Adjacent is allowed:INSERT INTO app.ch17_booking(technician_id,during) VALUES(7, tstzrange('2026-08-18 11:00+00','2026-08-18 12:00+00','[)'));-- Different technician can overlap:INSERT INTO app.ch17_booking(technician_id,during) VALUES(8, tstzrange('2026-08-18 10:30+00','2026-08-18 11:30+00','[)'));-- Same technician + overlap is rejected:INSERT INTO app.ch17_booking(technician_id,during) VALUES(7, tstzrange('2026-08-18 10:30+00','2026-08-18 11:30+00','[)'));

7. The database invariant also handles concurrency

Repeat the two-session race against app.ch17_booking. When two concurrent inserts could violate the exclusion rule, the constraint participates in concurrency control: one transaction can wait on the other; after the first commits, the conflicting second insert fails with an exclusion-constraint violation rather than allowing two committed overlaps.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT booking_id, technician_id, duringFROM app.ch17_bookingWHERE technician_id = 7  AND during && tstzrange(    '2026-08-18 10:15+00','2026-08-18 10:45+00','[)'  );

The constraint is stronger than “we usually precheck.” It declares the invariant in the database where every writer—including scripts and future services—must satisfy it.

8. Cleanup and production judgment

DROP TABLE IF EXISTS app.ch17_booking CASCADE;DROP TABLE IF EXISTS app.ch17_booking_bad CASCADE;DROP TABLE IF EXISTS app.ch17_article CASCADE;DROP TABLE IF EXISTS app.ch17_work_order_technician CASCADE;DROP TABLE IF EXISTS app.ch17_technician CASCADE;DROP TABLE IF EXISTS app.ch17_work_order_bad CASCADE;DROP TABLE IF EXISTS app.ch17_array_work_order CASCADE;DROP TABLE IF EXISTS app.ch17_profile CASCADE;DROP TABLE IF EXISTS app.ch17_json_lab CASCADE;

Authoritative references

These data types and index/operator contracts are version-sensitive. The lesson uses the PostgreSQL 18 primary documentation below.

• PostgreSQL 18 — Range Types
• PostgreSQL 18 — Range/Multirange Functions and Operators
• PostgreSQL 18 — Exclusion Constraints
• PostgreSQL 18 — btree_gist