MEDIUMPerformancesmallPostgreSQL

Add constraints NOT VALID then VALIDATE on big tables

Add FK and CHECK constraints as NOT VALID, then VALIDATE CONSTRAINT separately to avoid a long blocking scan.

Why This Matters

Adding a constraint normally scans the whole table under a strong lock, blocking writes for the duration on large tables.

Related Rules

RLS Disabled on Public Tables

Security

CRITICAL

Unindexed Foreign Keys

Performance

HIGH

Auth Users Table Exposed to API

Security

CRITICAL

Create Indexes Concurrently

Performance

HIGH

Catch this automatically on every PR

BeforeMerge scans your pull requests against this rule and dozens more. Get actionable feedback before code ships.

Join Waitlist Browse All Rules

Why this matters

Adding a CHECK or FOREIGN KEY constraint validates every existing row under an ACCESS EXCLUSIVE lock — a long outage on a big table. Adding it NOT VALID skips the scan and only enforces new rows; a later VALIDATE CONSTRAINT checks existing rows under a weaker lock that allows concurrent reads and writes.

-- bad (full scan under strong lock)
ALTER TABLE orders ADD CONSTRAINT orders_total_chk CHECK (total >= 0);
 
-- good (two cheap steps)
ALTER TABLE orders ADD CONSTRAINT orders_total_chk CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_chk;