An architect proposes declaring every constraint in the schema DEFERRABLE so that batch jobs can always rely on commit-time validation. Would you adopt that as a default? Justify the call.
answer
- Defaults question: who pays for the default
- Immediate = diagnostics + recoverable + intra-tx invariant
- Deferrable unique kills ON CONFLICT inference (PG)
- Real fixes: order the load, chunk, fix the model
- Exception must name its workflow
basics
~20 sNo. Keep constraints NOT DEFERRABLE by default and make individual ones deferrable where a real cycle or swap demands it. Blanket deferrability costs error attribution and pending-check memory, restricts features like upsert conflict inference, and hides modelling problems.
solid answer
~60 sI would reject it as a default and grant deferrability per constraint, on evidence. **Why immediate is the better default:** immediate checking is a *diagnostic* feature. It tells you which statement broke the invariant, keeps the transaction recoverable after a failed statement, and keeps the intra-transaction invariant intact so your own later statements read consistent data. Blanket deferrability also imposes friction even when unused — DEFERRABLE constraints carry a pending-check path, and in PostgreSQL a deferrable unique constraint cannot be used to infer an `ON CONFLICT` target, so schema-wide deferrability quietly outlaws upserts. **Why the stated need is usually not real:** the legitimate cases are a genuine foreign-key cycle and swapping values under a unique constraint. Both are narrow and identifiable. Most batch jobs need parents-before-children ordering and chunked transactions, not deferral. **Portability:** MySQL and SQL Server have no deferral, so a schema that depends on it schema-wide is not reproducible on other engines or in tests using a different one. So: default NOT DEFERRABLE, exceptions documented with the cycle or swap they exist for.
go deeper
Say that constraints are normally checked per statement and that changing that everywhere makes errors much harder to trace.
Give the concrete costs — commit-time errors, whole-transaction rollback, pending-check bookkeeping — and name the two cases that genuinely need deferral.
Add the feature and portability restrictions and propose the alternative batch architecture: ordered, chunked transactions with immediate checking.
Deliver it as a written policy with an exception process, name the one system shape (wholesale snapshot restore) where the opposite default is defensible, and tie it back to diagnosability as a first-class design property.
## Framing the decision This is a defaults question, and defaults questions are answered by asking who pays. A default that costs a little on every constraint to save a lot on three of them is a bad trade if the "little" is diagnostic quality, which is exactly the case here. ## The argument for immediate as the default **Immediate checking is diagnostics, not just enforcement.** When the check runs at the end of the statement, the error names the moment: this INSERT, this key, this table. When it runs at COMMIT, you get a constraint name and possibly a key, detached from the thousands of statements that ran. In an incident at 3am, that difference is the difference between a five-minute fix and a re-run with instrumentation. **Immediate checking keeps transactions recoverable.** A failed statement under immediate checking leaves the transaction alive (or recoverable to a savepoint); you can correct and continue. A deferred violation kills the transaction at COMMIT with no chance to intervene. **Immediate checking preserves the invariant inside the transaction.** Long procedures and triggers that read the data they are writing depend on it. If uniqueness is only guaranteed at COMMIT, a mid-transaction `SELECT` can legitimately see two rows where the model says one. **Deferrability has costs even when unused.** The pending-check machinery is on the table for these constraints, and — critically in PostgreSQL — `INSERT ... ON CONFLICT` refuses to infer a conflict target from a *deferrable* unique constraint. A schema-wide DEFERRABLE policy therefore bans the most common upsert idiom, usually discovered months later by a team that has no idea why their `ON CONFLICT (email)` reports no matching constraint. ## The argument the proposal is actually making The underlying wish is real: batch jobs shouldn't have to think about insertion order. But the right answers to that wish are mostly not deferral: - **Order the load.** Topologically sorting a load by foreign-key dependency is a solved problem and most ETL tooling does it. It also produces resumable, chunkable work. - **Chunk the transactions.** Many small transactions give attributable errors, short lock windows, resumability after failure, and no giant pending-check queue. One heroic transaction gives none of those. - **Fix the model.** An apparent cycle is often an over-constrained mandatory reference ("a department must have a manager") that the business does not actually require, or a relationship that should live in its own assignment table. What remains after those three is small: true modelled cycles, and value swaps under unique constraints. Those deserve deferrability — declared on the specific constraint, with a comment naming the workflow that needs it. ## The policy I would write 1. **Default NOT DEFERRABLE.** No exceptions by convenience. 2. **Deferrability is a reviewed exception.** The DDL comment or migration must name the concrete workflow ("employee/department mutual reference, created together in onboarding") — a constraint whose exception has no named workflow gets tightened back. 3. **Prefer `DEFERRABLE INITIALLY IMMEDIATE`** over `INITIALLY DEFERRED` where the deferral is needed only by one workflow. Then normal traffic still gets per-statement errors, and only the transaction that needs commit-time checking opts in with `SET CONSTRAINTS`. 4. **Never blanket `SET CONSTRAINTS ALL DEFERRED`** in application code; name the constraints, and force `SET CONSTRAINTS ALL IMMEDIATE` before COMMIT in long jobs so failures land at a controllable point. 5. **Audit the upsert paths** before making any unique constraint deferrable. ## Portability and testing MySQL/InnoDB and SQL Server offer no deferred constraint checking at all. If your product supports more than one engine, or if any environment (tests, a lightweight local stack) runs a different one, a schema-wide reliance on deferral is not reproducible: the same load succeeds on one engine and fails on another. Confining deferral to a handful of constraints keeps the portability problem small and visible — you know exactly which two workflows need an engine-specific alternative such as a nullable link plus a follow-up UPDATE. ## What would change my mind If the system's dominant workload were bulk snapshot restores of an entire interlinked schema — a data warehouse staging area rebuilt wholesale each night, where partial state is meaningless and no OLTP traffic reads the tables mid-load — then blanket deferrability is defensible, because none of the costs bite: there is no interactive path to lose diagnostics for, and no upsert traffic. That is a specific system shape, not a general default, and I would still keep it out of the OLTP schema.
- What evidence would justify making one specific constraint deferrable?A concrete workflow with no valid statement order — two rows that must reference each other and are created together — or a reorder that swaps values under a unique constraint. The exception should be recorded next to the constraint with the workflow named, and preferably declared DEFERRABLE INITIALLY IMMEDIATE so only that workflow opts into commit-time checking.
- How would you get batch loads to stop caring about insertion order without schema-wide deferral?Order the load by foreign-key dependency — a topological sort of the tables, which most ETL tooling supports — and chunk it into many small transactions. That gives attributable errors, short lock windows and resumability, none of which one giant deferred transaction provides. Reserve deferral for the genuinely cyclic pairs that ordering cannot resolve.
saying these in an interview costs you the question
- Treating deferrability as free because it changes nothing until you use it
- Ignoring that a deferrable unique constraint blocks ON CONFLICT inference in PostgreSQL
- Assuming every engine supports deferral, so the policy is portable
- Solving batch-ordering pain with one giant transaction instead of chunked, ordered work
- Never asking whether the apparent foreign-key cycle is really an over-constrained model