With a unique index, the second insert waits, then fails
PostgreSQL makes T2 wait to see whether T1 commits. T1 commits, so T2 gets 23505. Map it to a 409.
API validation is enough?
Bad: Only the API checks
Admin panels, imports and backfills never read your code.
Good: API for UX, database for truth
The constraint binds every writer, race-free.
The toolbox, rule by rule
- NOT NULL: a value is present
- CHECK: a rule about one row
- UNIQUE, or partial UNIQUE: no two share a key
- EXCLUDE: no two rows overlap
A CHECK compares a nullable discount with the amount. The discount arrives as NULL.
The catch
It passes: a CHECK fails only when false, and NULL isn’t false. Add NOT NULL where NULL isn’t real.
EXCLUDE: no two bookings of one room share a night
Half-open ranges let one guest check in the day another checks out. But 26–28 December collides with 24–27 on the 26th, so 23P01.
200 s
full lock for a plain ADD CHECK
That’s 100 GB scanned at 500 MB/s, with 400,000 writes queued behind it. Add it NOT VALID, then VALIDATE without blocking.
No constraint counts. Materialize the count into one row.
“At most 20 Deluxe a night” becomes sold ≤ capacity on a per-night row. The 21st booking fails the CHECK; popular nights queue on that row.
Remember
Validate in the API for friendly messages. Enforce in the database for truth, wherever a constraint fits.