A batch job opens a transaction, issues SET CONSTRAINTS ALL DEFERRED, and then performs a few million writes. What operational consequences does that have — for error reporting, for concurrent sessions, and for the transaction's resource use?

## What the statement actually does `SET CONSTRAINTS ALL DEFERRED` is transaction-scoped: it switches every constraint that was *declared* DEFERRABLE into commit-time checking for the remainder of this transaction. It does not touch NOT DEFERRABLE constraints — those keep firing per statement. That asymmetry is the first thing to say, because teams routinely add the line to a batch job, see no change in behaviour, and conclude deferral is broken when in fact none of the schema's constraints were deferrable in the first place. Naming a specific non-deferrable constraint in `SET CONSTRAINTS` raises an error, which is the more helpful form when you want to be sure. The symmetric form, `SET CONSTRAINTS ALL IMMEDIATE`, is more useful than it looks: it forces all currently-pending checks to be evaluated *right there*. Running it just before COMMIT converts a mystery commit-time failure into a failure at a known point in the script, and lets you keep the transaction open to inspect what went wrong. ## Error reporting: the real cost With immediate checking, an integrity error names the statement that caused it — you know the table, the row and the moment. With everything deferred, the error is raised by COMMIT after the last statement has long finished. The engine reports the constraint name and often the offending key value, but there is no statement attribution, no line number in your batch, and by the time the exception arrives the transaction is already aborting. Practical consequences for a batch job: - The whole batch is lost, not one statement. There is no "skip the bad row and continue", because you cannot catch the error mid-flight. - Your commit path must handle integrity exceptions. Application code and ORMs typically wrap statement execution in error handling and treat commit as a formality; a deferred violation appears where nobody is catching it. - Debugging usually means a second run with `SET CONSTRAINTS ALL IMMEDIATE`, or writing an anti-join query after the fact to find the orphan rows. For these reasons, defer *specific named constraints* for the specific statements that need it, rather than blanket-deferring everything for convenience. ## Resource use Deferred checks are not free bookkeeping. The engine must remember what to verify. In PostgreSQL, foreign-key and unique constraint checks are implemented as after-triggers, and deferring them means queuing one trigger event per affected row in the transaction's event queue. A transaction touching millions of constrained rows therefore accumulates millions of queued events; the queue lives in memory up to a threshold and spills to disk beyond it. Two effects follow. First, memory pressure inside the backend running the batch. Second, a commit-time burst: all those checks execute at once, so COMMIT — the operation everyone assumes is instant — can run for a long time doing index probes against the referenced tables. If your job has a commit timeout, or a connection pool with a socket read timeout, that burst is where it bites. ## Concurrency and visibility A fear worth putting to rest: deferral does not expose invalid data to anyone else. The rows written by the batch are uncommitted, so under normal isolation other sessions see either the pre-batch state or, after a successful commit, a fully valid state. What deferral relaxes is the invariant *within* the transaction — your own later statements in that transaction can read the inconsistent rows, which matters if the batch itself queries the data it is writing. The genuine concurrency cost is not deferral at all but the long transaction that deferral encourages. Holding one transaction open across millions of writes means: - Row locks on every written row are held to the end, so any concurrent writer touching the same rows blocks for the whole batch. - In PostgreSQL, the transaction pins the horizon, so dead tuples generated anywhere in the cluster cannot be reclaimed by vacuum while it runs — the classic cause of table and index bloat after a big nightly job. - Any failure at the end throws away all the work, so a batch that takes hours has no partial progress. The usual production shape is the opposite: chunk the batch into many small transactions with immediate checking, ordering the work so parents are written before children, and use deferral only for the specific chunks whose contents are genuinely cyclic. You get attributable errors, resumability, short lock windows and no giant pending-check queue. ## When blanket deferral is still right Restoring a consistent snapshot — a logical restore or a cross-table migration where every table must be replaced together — is the honest use case. There, the batch really is one atomic unit, no partial state is meaningful, and topologically sorting the load would be more fragile than deferring. Even then, prefer deferring named constraints and force an early `SET CONSTRAINTS ALL IMMEDIATE` before commit so failures are reported while you can still inspect them.