Constraints
ADD CONSTRAINT ... EXCLUDE
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-constraint-exclude ALTER TABLE reservations ADD CONSTRAINT no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&);What this does
EXCLUDE has no NOT VALID option and USING INDEX only accepts UNIQUE and PRIMARY KEY, so there is no concurrent-build escape hatch. This always takes the full ACCESS EXCLUSIVE lock for the validation scan, with no safe rewrite in core PostgreSQL.
Safe rewrite
No concurrent alternative exists. Minimize lock duration with lock_timeout and retry logic
SET lock_timeout = '2s';
ALTER TABLE reservations ADD CONSTRAINT no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&);
-- Retry in a loop if lock_timeout expires.