Prompt and scope
Price, lease, schedule, and permission records combine a business key with a validity period. The interviewer wants the database to reject overlapping periods for one product or tenant, including concurrent writes. The question tests temporal modeling, constraint semantics, and a safe migration plan rather than a query that merely finds conflicts.
What the interviewer evaluates
- Whether you distinguish a
WITHOUT OVERLAPStemporal primary or unique key from a normal B-tree unique key. - Whether you define range endpoints, empty ranges, NULL behavior, discrete dates, and continuous timestamps.
- Whether you understand that a
PERIODforeign key checks time coverage, not only a matching business key. - Whether you can plan historical cleanup, lock impact, rollback, and concurrent validation.
- Whether database constraints, application messages, and audit metrics have separate responsibilities.
Semantics and boundaries
PostgreSQL defines temporal constraints over range columns. WITHOUT OVERLAPS can be used in primary-key and unique constraints; for equal ordinary key parts, the associated ranges must not overlap. The range column is implicitly non-null, and empty ranges or multiranges do not form valid temporal keys. This database invariant cannot be replaced by an application sequence of “check, then insert.”
PERIOD is used for temporal foreign keys. A child business key and period must be covered by one or more parent rows; proving that a parent row with the same business key exists is insufficient. Parent deletion or shortening of a covered period must follow the foreign-key action and transaction ordering.
Modeling steps
Choose half-open or closed intervals and use the same convention on every write path. Date validity commonly uses [start, end); timestamp validity must specify time zone and precision. Put ordinary business-key columns before the range column, choosing daterange, tsrange, or tstzrange as appropriate.
Create the temporal unique constraint on the parent records and a PERIOD foreign key on covered records. The application can provide a friendly message, but the database decides whether the commit succeeds. Before migrating historical data, use a report or an exclusion-style check to identify overlaps, then define whether each conflict is merged, split, or retired.
SQL example
This example prevents overlapping prices for one plan_id and requires rules to be fully covered by a price plan:
CREATE TABLE price_plan (
plan_id bigint,
valid_during daterange NOT NULL,
amount numeric(12, 2) NOT NULL,
PRIMARY KEY (plan_id, valid_during WITHOUT OVERLAPS)
);
CREATE TABLE plan_rule (
plan_id bigint,
valid_during daterange NOT NULL,
rule_code text NOT NULL,
CONSTRAINT plan_rule_plan_period_fk
FOREIGN KEY (plan_id, PERIOD valid_during)
REFERENCES price_plan (plan_id, PERIOD valid_during)
);Validate the syntax and behavior in a shadow table before production, and confirm that the client driver surfaces the database conflict in a stable way. The example shows the core invariant; currency, amount precision, and audit columns still follow product requirements.
Concurrent writes and migration
Before adding the constraint, count conflicting ranges and sort them by business key; do not delete a “duplicate-looking” historical row without a business decision. For a large table, estimate index-build time, lock waits, and replication lag. Use batched cleanup, a low-traffic window, and progress markers that can be observed.
Two concurrent inserts for overlapping periods must be coordinated by the database at commit. The application should turn a uniqueness conflict into a retryable or explainable business error; a successful pre-check does not guarantee a later insert. Keep the old columns and write path for rollback until shadow validation, dual-write reconciliation, and recovery drills pass.
Common mistakes
- Creating only a unique
(plan_id, start_at)index, which still permits overlapping periods. - Failing to define endpoint rules and mixing a date ending on
2026-02-01with the next period’s start. - Treating a
PERIODforeign key as an ordinary foreign key that checks only the business key. - Adding a constraint directly to a large production table without checking history, locks, or replication lag.
- Letting application retries hide constraint conflicts and producing duplicate prices or partial coverage.
Follow-up questions
How do you handle existing overlaps?
Generate a conflict report grouped by business key and sorted by range. Have the business owner choose merge, split, or retire semantics; repair the rows, replay writes in a shadow table, prove that the report is empty, and only then add the constraint.
Why not use an exclusion constraint or trigger?
An exclusion constraint can express interval mutual exclusion, but WITHOUT OVERLAPS directly states temporal primary or unique semantics and composes with PERIOD foreign keys. Triggers can miss concurrency, recursion, or replication paths; use them only for additional cross-table side effects while keeping the core invariant in constraints.
How do you prove the migration preserved business time?
Compare interval counts, boundary samples, rejected-conflict rates, and query plans before and after. Run coverage checks on a read replica and in a recovery environment. During rollout, reconcile with the old logic; if valid writes are rejected, roll back the constraint switch without deleting history.