Zero-Downtime Database Schema Migrations on Live Kubernetes Clusters

-- Step 1: NEVER run standard CREATE INDEX on a live production table
-- It locks writes for the entire duration of index creation.
-- INSTEAD: Always use CONCURRENTLY in PostgreSQL
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_invoices_customer_status
ON invoices (customer_id, payment_status)
WHERE payment_status != 'archived';

-- Step 2: Adding a new required column without downtime
-- NEVER do: ALTER TABLE users ADD COLUMN phone_e164 TEXT NOT NULL;
-- (This locks the entire table while rewriting all rows with default values)

-- INSTEAD: Add as nullable first (instant catalog update)
ALTER TABLE users ADD COLUMN phone_e164 TEXT;

-- Step 3: Add validation constraint without holding exclusive table locks
ALTER TABLE users 
ADD CONSTRAINT check_phone_format 
CHECK (phone_e164 ~ '^+[1-9]d{1,14}$') 
NOT VALID; -- NOT VALID applies check only to new writes immediately

-- Step 4: Validate existing historical data in background without locking writes
ALTER TABLE users VALIDATE CONSTRAINT check_phone_format;