Zero-downtime database migrations: strategies for production systems

Zero-downtime migrations follow a strict sequence: add new column, deploy application code that writes to both old and new columns, migrate existing data, deploy application code that reads from the new column, drop the old column.

Never lock the entire table during migrations. Use online migration tools for large tables, or batch updates with scheduled maintenance windows. Supabase provides pg_partman for table partitioning, which enables large updates without full locks.

Test migrations on production-like data before deploying. Use database staging environments with realistic data volumes and run the exact migration command you'll use in production.

Coordinate application deployments with database changes. Deploy application code that's compatible with both old and new schema first, then run the migration, then deploy code that uses the new schema exclusively.