python-migration

Zero-downtime PostgreSQL schema migrations use the Expand/Contract pattern via Alembic to apply hand-written SQL. This ensures backward compatibility by breaking changes into nullable-add, backfill, and constraint-validation sequences without locking tables.

Adding a required column to a large PostgreSQL table safely requires a multi-deploy sequence. You first add the column as nullable, backfill existing rows, and finally validate and add the constraint to ensure zero-downtime deployment.

Yes, Alembic can be used strictly as a migration runner for hand-written SQL. This bypasses ORM-based autogeneration, providing forward-only production discipline and ensuring precise control over iterative database evolution.

asyncpg works with Alembic and SQLAlchemy as underlying dependencies for backend database evolution. This stack supports CI-verified round-trip testing and decoupled migration-deployment cycles for production-safe schema updates.

The Expand/Contract pattern is needed for breaking schema modifications like column renaming or adding constraints. It enables safe, multi-deploy schema changes by ensuring old and new application versions can coexist during the migration window.

Alembic autogeneration lacks the forward-only production discipline required for complex schemas. It often misses multi-step transition logic needed for zero-downtime deployments, making hand-written SQL necessary for safe, backward-compatible database evolution.