Problem and context
A system stores room bookings and their details. The old flow checked for conflicts in the application before inserting, but concurrent requests could still create overlaps. Use PostgreSQL 18 temporal constraints to enforce non-overlap in the database and let a child table reference a parent's covered validity period.
What the interviewer evaluates
The key is that WITHOUT OVERLAPS belongs on the final range column of a primary-key or unique constraint, while PERIOD belongs on a temporal foreign key. For equal prefix keys, non-empty ranges form a non-overlapping set. Explain half-open boundaries, empty ranges, NULLs, updates, dirty-data migration, and concurrent conflicts.
Clarifying questions to ask first
Time model
Ask whether the schema uses tstzrange or daterange, which time zone applies, and whether ranges are half-open. Endpoint and adjacency rules directly determine constraint results.
Business key and references
Confirm the room ID as the business prefix, whether a detail period must be fully covered by a parent period, and whether one booking may span multiple versions.
Migration and concurrency
Ask whether legacy rows contain overlaps or empty ranges, the migration window, and rollback strategy. Concurrent inserts must rely on database constraints and transaction error handling, not only an application lock.
30-second answer framework
“I put room_id first and the validity range last, using PRIMARY KEY (room_id, during WITHOUT OVERLAPS) to prevent overlaps for one room. The detail table uses FOREIGN KEY (room_id, PERIOD during) to reference the temporal key. I clean overlaps and empty ranges before adding constraints in stages; concurrent conflicts become database errors that the transaction retries or reports, with explicit half-open and time-zone rules.”
Detailed solution steps
Step 1: Choose range type and boundaries
Use the discrete or continuous range type that matches the business and standardize half-open intervals. Reject empty ranges, define adjacency, and normalize time zones so daylight-saving transitions cannot create accidental overlaps.
Step 2: Define the temporal primary key
Place identity columns first and the range column last, then use WITHOUT OVERLAPS. This expresses non-overlap within one entity prefix while retaining primary-key identity and non-null semantics.
CREATE TABLE room_booking (
room_id bigint NOT NULL,
during tstzrange NOT NULL,
guest_id bigint NOT NULL,
PRIMARY KEY (room_id, during WITHOUT OVERLAPS)
);Step 3: Define the period foreign key
When details also have a period, use PERIOD to reference a temporal primary or unique constraint. Confirm that full coverage is required and test parent-period splitting or shortening.
CREATE TABLE booking_charge (
room_id bigint NOT NULL,
during tstzrange NOT NULL,
amount numeric NOT NULL,
FOREIGN KEY (room_id, PERIOD during)
REFERENCES room_booking (room_id, PERIOD during)
);Step 4: Clean historical data
Before rollout, find overlaps, empty ranges, NULLs, and invalid endpoints per room, then choose merge, split, or void policies. Validate on a shadow table and repair in batches to avoid locking a large table at once.
Step 5: Handle concurrent writes
If two transactions insert overlapping periods for one room, let the database arbitrate. The application catches the constraint error, rereads availability, and retries or returns a clear conflict. A check-then-insert flow is not sufficient, and an uncontrolled global lock is not a substitute.
Step 6: Evaluate update and delete semantics
An updated period can conflict with itself or another row, so booking splits belong in one transaction. Before deleting or shortening a parent period, verify temporal-foreign-key actions to avoid orphan details or silently expanding coverage.
Step 7: Verify queries and operations
Test adjacent, containing, identical, empty, cross-time-zone, and precision-boundary cases. Monitor constraint-error rate, migration lock waits, and index size; cap write retries to prevent a high-contention retry storm.
High-quality sample answer
I would choose tstzrange with a half-open convention, define (room_id, during WITHOUT OVERLAPS) as the primary key, and use (room_id, PERIOD during) for a detail foreign key whose period must be covered by the parent. I would clean legacy overlaps and invalid endpoints first. Concurrent inserts rely on the database constraint; the application catches conflicts and retries or returns availability. Tests cover adjacency, overlap, parent splitting, time-zone conversion, and high contention.
Common mistakes
- Mistake: Only checking before inserting in the application. → Why: Concurrent transactions can pass the check together. → Fix: Use the temporal constraint as the final arbiter.
- Mistake: Putting the range column before the key. → Why: Syntax and prefix semantics require the range last. → Fix: List business keys first, then
WITHOUT OVERLAPS. - Mistake: Assuming adjacent ranges always conflict. → Why: Boundary semantics decide the result. → Fix: Standardize half-open ranges and test endpoints.
- Mistake: Enabling the constraint immediately during migration. → Why: Historical overlaps or empty ranges cause failure and long locks. → Fix: Audit and repair first, then stage rollout.
Follow-up questions and answers
Follow-up 1: Do [10:00, 11:00) and [11:00, 12:00) conflict?
Under a consistent half-open convention they do not, because 11:00 belongs only to the second range. Closed or mixed boundaries require an explicit business rule before defining the constraint.
Follow-up 2: Why must the range column be last?
The temporal key groups rows by prefix columns and then requires the final ranges not to overlap. Putting the range first cannot express the same-entity grouping.
Follow-up 3: Does a period foreign key check only one instant?
No. PERIOD expresses that the referenced period must be covered by parent periods. Confirm the exact combination and boundaries against PostgreSQL 18 semantics and tests; it is not a point foreign key.
Follow-up 4: How do you avoid a retry storm under contention?
Cap retries and add jitter, reread an available period, then return a conflict or queue the request after the limit. Monitor constraint errors and lock waits, and shard writes by room when necessary.