1. The ACCESS EXCLUSIVE Lock Threat
Executing python manage.py migrate on production tables with millions of active rows can bring down an entire API platform if an ALTER TABLE statement acquires an ACCESS EXCLUSIVE lock. This lock prevents even basic SELECT queries from executing.
Catastrophic Queue Behavior
Even a fast ALTER TABLE will queue behind long-running queries, subsequently blocking all incoming web traffic and exhausting the Gunicorn worker thread pool.
2. Safe 3-Step Procedure for NOT NULL Constraints
Instead of adding a NOT NULL constraint directly with a default value, PostgreSQL allows adding a CHECK constraint as NOT VALID instantaneously, backfilling data in small batches, and then validating the constraint asynchronously without holding destructive locks.
safe_not_null_migration.sql
SQL
-- Step 1: Add column without default or constraint (Instant)
ALTER TABLE orders ADD COLUMN loyalty_points INT;
-- Step 2: Add CHECK constraint as NOT VALID (Zero lock time)
ALTER TABLE orders
ADD CONSTRAINT check_loyalty_not_null
CHECK (loyalty_points IS NOT NULL) NOT VALID;
-- Step 3: Backfill data in micro-batches
-- UPDATE orders SET loyalty_points = 0 WHERE id BETWEEN ...
-- Step 4: Validate constraint concurrently without blocking writes
ALTER TABLE orders VALIDATE CONSTRAINT check_loyalty_not_null;Safe NOT NULL constraint validation in PostgreSQL with zero lock duration.
<production-migration-impact />
0.00 seconds
Downtime Duration
< 2 ms
Lock Wait Time
0 blocked
Connection Pool Spikes
