Schema Migrations with Alembic

Mental model

Alembic provides a version-controlled database migration framework for SQLAlchemy, tracking schema revisions in a dedicated alembic_version table.

Theory

Production DDL statements (ALTER TABLE, ADD COLUMN) acquire table locks. If a migration waits for an exclusive lock behind long-running queries, subsequent SELECT queries queue up behind it, exhausting connection pools. Safe migrations configure strict lock_timeout values prior to DDL execution.

Alternatives and trade-offs

• Manual DDL SQL Scripts: Error-prone, hard to track in CI/CD, no automated rollback verification.
• Alembic Versioned Migrations: Audited, repeatable, auto-generates delta scripts from SQLAlchemy model changes.

Failure modes and misconceptions

1. Blocking Default Values: Adding a column with a non-constant default (ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL) in PostgreSQL < 11 rewrote the entire table, holding an ACCESS EXCLUSIVE lock.
2. Dropping Columns in Single Deployment: Dropping a column while old application pods are still running causes HTTP 500 crashes. Always use multi-phase expand/contract migrations.

Decision scenario

Configure SET lock_timeout = '5s' in Alembic env.py so that production schema migration deployment scripts fail fast rather than blocking user queries if a lock cannot be acquired immediately.

Learning outcomes

• Structure version-controlled database migrations using Alembic.
• Enforce lock timeout safety to prevent DDL migration deadlocks.
• Execute zero-downtime expand/contract schema migrations.

Trade-offs

Alembic automates database schema evolution, but auto-generated revision scripts must be manually audited before applying DDL changes in production.