Prompt and context
A team stores meeting-room bookings. Each record has a room, a start time, and an end time; windows for one room must not overlap, while adjacent bookings may touch at an endpoint. The application already checks conflicts, but duplicate occupancy still appears under concurrency. Give a PostgreSQL design and explain half-open intervals, nulls, time zones, concurrent writes, error handling, and migration of existing data.
This question fits data engineering, backend, and database roles. The key is expressing a cross-row business rule as a database invariant instead of trusting every caller to run the same query. A strong answer distinguishes the boundaries of UNIQUE, CHECK, triggers, and exclusion constraints, then connects constraint failures to the product workflow.
What the interviewer is testing
A strong answer models booking time as tstzrange or another suitable range type and explicitly uses [start, end) so adjacent windows do not conflict. It combines room equality and time overlap in a GiST exclusion constraint. It explains when btree_gist is needed, why an application pre-check cannot remove a race, how to map a constraint exception, how to handle infinite bounds and empty ranges, and how to find existing conflicts before migration.
Questions to clarify first
- What is the time-zone policy for start and end, and can a booking cross a daylight-saving transition?
- Must the end be after the start, and do zero-length bookings have business meaning?
- Is the conflict scope only one room, or also a floor, device, or tenant?
- May adjacent intervals touch, and do cancelled or soft-deleted rows still consume the resource?
- Does existing data already contain overlaps, and can writes pause briefly during migration?
A 30-second answer framework
“I would normalize start and end to a time-zone-aware half-open range, tstzrange(start_at, end_at, '[)'), and add EXCLUDE USING gist (room_id WITH =, during WITH &&) in the database. Overlapping windows for one room are rejected while adjacent windows coexist; btree_gist lets an integer or UUID room key participate in the GiST comparison. I would attempt the write directly and map a constraint conflict to a retryable business response rather than rely on check-then-insert. Before rollout I would scan and repair old conflicts, enable the constraint gradually, and monitor failures.”
Step-by-step solution
Step 1: choose the time semantics
Use tstzrange for an absolute instant instead of handing local-time strings to the database. [start, end) includes the start and excludes the end, so [10:00, 11:00) and [11:00, 12:00) do not overlap. PostgreSQL documents && as the overlap operator and uses range constraints for this kind of invariant.
Validate start_at < end_at on write and decide whether empty ranges are meaningful. Store a consistent time-zone representation, then format it for the viewer’s time zone; do not infer duration from local clock arithmetic on a daylight-saving transition day.
Step 2: express the rule as an exclusion constraint
The range can be a generated column or constructed in the constraint expression. An explicit range column is convenient for queries and audits:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_reservations (
reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
during tstzrange NOT NULL,
CHECK (NOT isempty(during)),
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);The constraint requires at least one comparison between every pair of rows to be false or null. When both room_id = and during && are true, the second row is rejected. PostgreSQL automatically creates an index of the selected type for an exclusion constraint.
Step 3: understand btree_gist and index cost
Ranges have GiST operator classes. A scalar such as an integer, text value, or UUID usually lacks a default GiST class for equality, so btree_gist can provide a B-tree-like operator class that participates in the same GiST constraint. Treat the extension as a deployment dependency and verify it in the migration environment.
The GiST constraint index adds write and update cost. Reads should use range operators and selective predicates. Do not create a duplicate range GiST index just because an index is visible; inspect plans and confirm whether the constraint index already serves the read workload.
Step 4: handle concurrency and transactions
Do not run SELECT to check for a conflict and then INSERT; two transactions can both observe an empty window. Let the database constraint arbitrate, catch the named constraint, and return “the time window is already occupied,” allowing the user to refresh or choose another slot.
A booking may also trigger payment, notifications, or quotas. Commit the booking in a short transaction, then publish an outbox or reliable event for external side effects. Retry only serialization or transient errors that are safe to retry; a constraint conflict is a business fact, so blind retries will not succeed.
Step 5: define cancellation, tenancy, and deletion
Whether a soft-deleted row still occupies a room must be part of the constraint model. If cancellation releases the window, separate active bookings from history or design a state transition that can be enforced. A WHERE status = 'active' filter in application queries does not make a normal exclusion constraint ignore other rows.
For multi-tenancy, include the tenant key when the resource namespace is tenant-local, for example (tenant_id WITH =, room_id WITH =, during WITH &&), and enforce authorization so one tenant cannot write another’s room. The constraint protects conflicts; it does not replace row permissions or the business state machine.
Step 6: migrate existing data
Find overlapping pairs per room with a self-join or window query, then record counts and owners. Resolve each conflict by merging, splitting, cancelling, or obtaining a business decision; do not silently truncate data. After the data is clean, create the constraint in a low-risk window. For a large table, assess locks, index-build time, rollback, and a backup-restore rehearsal.
Step 7: design errors and observability
Name the constraint, for example room_reservations_no_overlap, so the driver’s constraint name maps to a stable user-facing response. Log the room, request identifier, and a time-window summary without unnecessary personal data. Monitor conflict rate, migration leftovers, transaction latency, and index growth, distinguishing normal contention from a retry storm.
Step 8: test concurrent scenarios
Test an overlap in one room (failure), adjacent windows in one room (success), overlap in different rooms (success), equivalent instants across time zones, an update that creates a conflict, cancellation, empty ranges, and null handling. Use two concurrent transactions, not only sequential scripts, and verify recovery, backup restore, and constraint rebuild behavior.
Trade-offs and boundaries
An exclusion constraint fits a continuously maintained rule that no pair of rows may satisfy a set of comparisons at the same time. It is closer to the data source than an application mutex and avoids a separate race-prone trigger protocol. The costs are GiST write amplification, an extension dependency, and the need for the application to understand constraint errors.
If the rule spans tables, has dynamic capacity, or allows a bounded amount of overlap, one exclusion constraint may not be enough. Consider lockable slots, transaction-level locking, or a scheduling service, while retaining database constraints for the invariants they can express. A CHECK constraint cannot reliably reference other rows to maintain this cross-row rule.
Rollout plan and evidence
Load production data into a shadow table, run an overlap scan, and produce a repair list by room and tenant. Then install the extension and constraint, replay concurrent writes, and verify error mapping, index cost, backup restore, and alerts. Enable it for a small traffic slice, compare constraint conflicts with manually observed conflicts, and switch the main table after the results stabilize.
PostgreSQL’s range documentation defines operators such as && and shows a GiST exclusion constraint preventing overlapping reservations. Its constraints documentation defines the pairwise exclusion semantics and notes that adding the constraint creates the specified index. These primary sources support the data-type, operator, and index claims; deployment details still require testing against the actual PostgreSQL version.
Public booking-system interview material also lists concurrent double-booking prevention and a PostgreSQL exclusion constraint as interview talking points. This article keeps that recognizable scenario but narrows the answer to data invariants, migration, and failure verification instead of repeating a full booking-system design.
Common mistakes and follow-ups
Only doing “check, then insert”
Concurrent transactions can both pass the check. Keep the query as a user-experience hint if useful, but let the database constraint decide the final outcome.
Storing local time in timestamp
The same string can represent different instants across regions and daylight-saving changes. Define a time-zone policy, store absolute time, and convert only for display.
Using UNIQUE(room_id, start_at) for overlap prevention
Unique constraints block the same start value, not a long interval covering several shorter ones. Range operators express overlap directly.
Making a normal constraint ignore soft-deleted rows
The normal exclusion constraint compares every row. Separate active and historical records or redesign the state model; filtering only in application queries is insufficient.
Why not use only a trigger?
A trigger must implement its own concurrency, locking, and error semantics and can create difficult backup and restore edges. If the rule is expressible with ranges and operators, the native exclusion constraint is usually clearer; use a trigger or scheduler when the rule exceeds that model.