Alembic Migrations Without Downtime Surprises

A database migration is production code that transforms shared durable state while old and new application versions may run together. Alembic records ordered revisions, but autogenerated output is only a draft. Safe changes are reviewed, tested on representative data, monitored, and often split into expand, backfill, switch, and contract phases.

What will you be able to do?

• initialize Alembic and generate a revision;
• inspect SQL before execution;
• add a required field through compatible phases;
• plan roll-forward and restore instead of assuming downgrade is safe.

How do you create the migration environment?

Configure the migration environment to import your SQLAlchemy metadata, but inject the database URL securely. Review table names, constraints, defaults, index order, and destructive operations in every generated revision.

How do you add a required category safely?

1. Expand: add nullable category; deploy code that writes it but tolerates null.
2. Backfill: update old rows in bounded batches with progress, locks, and replication impact monitored.
3. Validate: prove no null remains and new writes always provide a category.
4. Constrain: add or validate the non-null/check constraint using a database-appropriate low-lock method.
5. Contract: remove compatibility code after all old application versions are gone.

Do not put a huge data rewrite in one startup migration. Use a resumable operational job with checkpoints when volume warrants it.

How are migrations deployed?

Run migrations as one controlled release job, not independently in every web replica. Use a dedicated migration role, an advisory lock or platform singleton, statement/lock timeouts, and alerts. Application startup should fail clearly when schema compatibility is missing, but it should not race to mutate production.

Why does this matter in AI-native systems?

Prompt version, output schema, embedding model, chunker, and vector dimensions evolve. Keep old and new representations distinguishable. Re-embedding is a backfill with cost and quality consequences; use versioned collections, shadow evaluation, and an atomic traffic switch.

Common mistakes

• Trusting autogenerate to detect semantic renames; it may propose drop-and-create.
• Adding a defaulted non-null column in a way that locks or rewrites a large table unexpectedly.
• Editing a revision already executed in another environment.
• Depending on downgrades that would discard new data.
• Deploying code that only understands the new schema before expansion.

AI Pair-Programmer Prompt

Exercise

Write an expand-contract plan for replacing tickets.priority text with a foreign-keyed priority policy, while two app versions run and existing values may be invalid.

Acceptance criteria: every phase is reversible or has a recovery path, invalid data is measured before constraints, and no version observes an impossible schema.

Knowledge check

1. Is autogenerated migration code ready to run unreviewed?
2. Why separate backfill from schema change?
3. Should every replica run migrations at startup?

---

Answers

1. No; it is a draft requiring semantic and operational review.
2. Large data work needs batching, monitoring, resumption, and different lock behavior.
3. No; use one controlled migration job.

Completion checklist

• Fresh install and upgrade path both work.
• Migration SQL and locks were reviewed.
• Recovery and compatibility are documented.

Primary references

• Alembic tutorial
• PostgreSQL ALTER TABLE