The incident that taught me to respect boundary semantics happened on a booking platform I helped operate: about 38 million rows in a reservations table on MySQL 8.0, two DATETIME columns named starts_at and ends_at, and an availability query that ran the classic overlap predicate against them. For two years it worked well enough. Then a Tuesday at 09:40, right in the check-in rush for a large property group, the availability endpoint started timing out. The overlap query was scanning hundreds of thousands of rows per lookup, and p95 on that endpoint climbed past 900 milliseconds. While we were staring at the slow log, customer support reported the more embarrassing problem: two guests had been confirmed into the same room for the same night.
The double-booking was not a race in the way we first assumed. It was a boundary bug. One reservation ended at 11:00, the next began at 11:00, and a code path using BETWEEN instead of strict inequalities had decided they overlapped, while a second code path using the strict version had decided they did not. Two different definitions of "overlap" lived in the codebase, and the database had no opinion of its own because two columns are not an interval; they are two numbers that the application promises to keep coherent.
That platform eventually moved to PostgreSQL, and the reservations table became a single tstzrange column with a GiST index and an exclusion constraint. The overlap query dropped to single-digit milliseconds, the boundary semantics became exact and lived in one place, and the double-booking class of bug became impossible rather than merely unlikely. This article is the full comparison: what PostgreSQL range types give you, why the GiST index makes overlap queries fast, how the exclusion constraint enforces no-double-booking at the database level, what the MySQL two-column pattern can and cannot achieve, and how to migrate between the two models.
How do PostgreSQL range types model an interval?
A range type stores the entire interval, bounds included, in a single column with defined semantics, instead of spreading one fact across two columns. PostgreSQL ships with int4range and int8range for integers, numrange for numerics, tsrange and tstzrange for timestamps without and with time zone, and daterange for dates. For a booking system, tstzrange is the usual choice because it stores absolute time; daterange fits all-day concepts like hotel nights where time-of-day is meaningless.
The part that fixes the boundary bug is that inclusivity is part of the value. Square brackets mean inclusive, round brackets mean exclusive, and each bound carries its own marker. PostgreSQL normalizes discrete ranges like int4range and daterange to canonical form, so daterange '2025-03-10' to '2025-03-12' inclusive is stored as [2025-03-10, 2025-03-13). Continuous ranges like tstzrange keep the bounds you wrote, but the convention that prevents an entire category of bugs is half-open: start inclusive, end exclusive. Checkout at 11:00 and check-in at 11:00 simply do not overlap under [) semantics, and the database agrees with every code path because there is one definition.
CREATE TABLE bookings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
stay tstzrange NOT NULL,
CHECK (NOT isempty(stay))
);
INSERT INTO bookings (room_id, stay)
VALUES (7, tstzrange('2025-03-10 14:00+00', '2025-03-12 11:00+00', '[)'));The operators then read like the questions you actually ask. The overlap operator && answers "do these two intervals share any time at all," the containment operator @> answers "does this interval contain this point or interval," and <@ asks the reverse. An availability check for a room becomes one predicate:
SELECT id, stay
FROM bookings
WHERE room_id = 7
AND stay && tstzrange('2025-03-11 14:00+00', '2025-03-13 11:00+00', '[)');
SELECT id
FROM bookings
WHERE stay @> timestamptz '2025-03-11 09:00+00';The honest cost: range types are PostgreSQL-specific, so an ORM or a shared SQL layer that must run on multiple engines will fight you, and client drivers need a small amount of setup to serialize range values cleanly. You are buying exactness at the price of portability.
Why is a GiST index on a range column so fast for overlap queries?
A GiST index on a range column can answer overlap directly, because GiST organizes entries by their bounding boxes and can prune any subtree whose bounds cannot intersect the query range. A B-tree cannot do this for overlap, and this is the heart of the comparison, because the MySQL table's problem was exactly this: a B-tree orders by one column at a time, and overlap is a two-dimensional question.
On the MySQL table, the best available index was (room_id, starts_at). The optimizer could use it to find the room and walk starts_at forward, but the overlap predicate needs starts_at < requested_end and ends_at > requested_start, and a B-tree can apply a range condition to only the first column it ranges on. The ends_at half was evaluated row by row after the index lookup, which is why the query degenerated into scanning most of a busy room's history. As the table grew toward 38 million rows, the scan grew with it.
CREATE INDEX bookings_stay_gist ON bookings USING gist (stay);
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX bookings_room_stay_gist ON bookings USING gist (room_id, stay);The composite GiST index needs the btree_gist extension because plain GiST has no built-in operator class for bigint equality; btree_gist teaches GiST how to index scalar equality types. With it, the availability query prunes by room and by time in one index descent, and it scales with the number of overlapping candidates rather than the size of the table. That is the difference between the 900-millisecond Tuesday and a query that stays in single-digit milliseconds at ten times the data. GiST itself, and when to reach for it over B-tree, BRIN, or GIN, is covered in the index types field guide; ranges are the workload where GiST is not optional but the whole point.
The cost sheet is real. GiST indexes are larger and slower to build than B-trees on the same data, writes pay more per insert, and under heavy concurrent write load GiST pages see more contention than a B-tree on a monotonic column. On the booking workload, writes were a tiny fraction of reads, so the trade was obviously right. Measure yours before assuming the same.
How do you stop double-booking at the database level?
An exclusion constraint stops double-booking in the database itself: it rejects any insert or update that would create a row violating the rule, using the same operators the index understands. For the bookings table, the rule is "for the same room, no two stays may overlap," and it is one DDL statement:
ALTER TABLE bookings
ADD CONSTRAINT no_double_booking
EXCLUDE USING gist (
room_id WITH =,
stay WITH &&
);Read that as: for any two rows, it is an error if room_id is equal AND stay overlaps. PostgreSQL enforces this with the GiST index, so the check on every write is an index probe, not a table scan, and it behaves correctly under concurrency in a way that application-side check-then-insert logic does not. Two transactions inserting overlapping stays for the same room serialize on the index, one commits, the other gets a clean constraint violation to translate into a friendly "that room just sold" response. No SELECT FOR UPDATE ceremony, no serializable isolation required, no trust in every code path remembering the same predicate.
This was the feature that ended the incident class for us. Before it, correctness depended on every writer using the identical overlap definition inside the identical transaction pattern, and the postmortem showed we had two definitions in three code paths. After it, the worst the application could do was handle the violation poorly. MySQL has no equivalent mechanism; the closest it gets is the application-side pattern in the next section.
What does the MySQL two-column pattern get right, and where does it break?
The MySQL pattern is two columns plus one well-known predicate, and its virtues are real: it is portable, every ORM understands it, and it is perfectly adequate at modest scale. The overlap idiom is worth memorizing because it is the only correct formulation for half-open intervals: two intervals overlap if and only if each starts before the other ends.
CREATE TABLE bookings (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
room_id BIGINT NOT NULL,
starts_at DATETIME NOT NULL,
ends_at DATETIME NOT NULL,
CONSTRAINT chk_bounds CHECK (starts_at < ends_at),
KEY idx_room_start (room_id, starts_at)
);
SELECT id, starts_at, ends_at
FROM bookings
WHERE room_id = 7
AND starts_at < '2025-03-13 11:00'
AND ends_at > '2025-03-11 14:00';Three things to say about that SQL. First, the CHECK constraint is enforced on MySQL 8.0.16 and later, so use it; before that version CHECK was parsed and silently ignored, which is a fact that has bitten many migrations. Second, the strict inequalities encode half-open semantics, and the classic bug is writing BETWEEN, which is inclusive on both ends and declares back-to-back bookings a conflict, or its mirror image, which permits the double-booking we shipped. Third, the index genuinely helps but has a hard ceiling: (room_id, starts_at) serves the equality on room and the range on starts_at, and ends_at is a filter applied after. There is no index shape in MySQL that makes both range conditions selective at once, because InnoDB secondary indexes are B-trees. At a few million rows per property this is fine. At our scale, on hot rooms, it was the 900 milliseconds.
Integrity is the bigger gap. MySQL cannot express "no overlaps for the same room," so the application must enforce it, and the workable pattern is pessimistic: serialize writers on something they all touch.
START TRANSACTION;
SELECT id FROM rooms WHERE id = 7 FOR UPDATE;
-- overlap check with the predicate above, then:
INSERT INTO bookings (room_id, starts_at, ends_at)
VALUES (7, '2025-03-11 14:00', '2025-03-13 11:00');
COMMIT;Locking the parent room row serializes all bookings for that room, which is correct and usually fast enough, but it couples your write throughput to the hottest room and adds a lock ordering rule that every booking-related transaction must follow or you get deadlocks instead. InnoDB's next-key locking under REPEATABLE READ can also block concurrent inserts into the scanned index gap, which helps and hurts: it closes some races you did not explicitly handle, and it produces lock waits and deadlocks you did not explicitly design. Either way, correctness lives in convention. The day a new service writes bookings without the lock, the constraint is gone and nothing tells you. If you run this pattern, the deadlock postmortem workflow is not optional reading, because the serialization points will eventually collide.
How do you migrate start/end columns to range types and back?
The migration from two columns to a range is a backfill plus a validation pass, and the order matters: add the column, backfill, validate the boundaries, then add the index and the constraint last, because the constraint will fail loudly on every latent boundary bug the old model tolerated. On the platform I described, the validation step found 214 rows out of 38 million where starts_at equaled ends_at (empty intervals the application had created by accident) and a handful where the ends had been swapped during a timezone mishap. Cleaning those was the real work; the DDL was an afternoon.
ALTER TABLE bookings ADD COLUMN stay tstzrange;
UPDATE bookings
SET stay = tstzrange(starts_at, ends_at, '[)');
SELECT count(*)
FROM bookings
WHERE stay IS NULL OR isempty(stay);Decide the canonical bound convention before the backfill and write it down, because the application and the database must agree on half-open semantics from the first deploy. The other genuine hazard is time zones. tstzrange stores absolute time, which is what you want; a MySQL DATETIME column stores a wall-clock value with no time zone, and what that wall clock meant depends on connection settings at write time, sometimes over years. Audit the session time_zone history before trusting a bulk conversion, and if the old data mixed zones, fix the data rather than encoding the ambiguity into the new column.
Migrating the other direction, from PostgreSQL ranges back to two columns, is lossless only if you also preserve the bound flags:
SELECT id,
lower(stay) AS starts_at,
upper(stay) AS ends_at,
lower_inc(stay) AS start_inclusive,
upper_inc(stay) AS end_inclusive
FROM bookings;If you standardized on [) everywhere, the flags are constant and you can drop them; if you did not, collapsing to two DATETIME columns throws away exactly the information whose absence caused the original bugs. Whichever direction you travel, keep the old columns readable until every code path has moved, dual-write through a transition window, and treat the constraint addition as a deploy of its own so its failure, if the data is dirtier than you believed, rolls back nothing else.
How MonPG helps once the ranges live in PostgreSQL
Range workloads have a monitoring signature of their own: the GiST index grows faster than the table, the overlap query's buffer usage tells you whether pruning is still working, and the exclusion constraint turns contention into lock waits that show up when a hot room gets hammered. On the booking platform, the signal we wish we had been watching was the availability query's buffer hits per call climbing for months before the Tuesday incident; the slowdown was visible long before it was painful. That is the layer MonPG's PostgreSQL monitoring covers: pg_stat_statements history so a degrading overlap query trends on a graph instead of surfacing as a timeout, lock and wait-event evidence for the writes serializing on the exclusion constraint, and index and table bloat signals for the GiST side of the house.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap, so during a dual-running migration it will not watch the InnoDB side, where the signals that matter are the slow-log share of the overlap predicate and lock waits around the room-row serialization. When MySQL support lands, those are the counters it will surface. Until then, keep your existing MySQL instrumentation for the old system, and let the new one start accumulating its baseline on day one, because the next boundary bug will be subtle and the evidence is the only thing that finds it early.