Database Migration Fundamentals & Tool Selection

A schema migration is the riskiest line of code most teams ship, because it runs once, against live state, and a mistake is rarely a clean exception — it is a held lock, a half-rewritten table, or a replica drifting away from its primary. This section establishes the foundations every later technique depends on: what makes a migration safe, how PostgreSQL and MySQL 8.0 differ in the guarantees they offer, and which tooling enforces those guarantees instead of hiding them. It serves backend and platform engineers who write the DDL, DBAs who must keep the production database available through every release, and DevOps teams wiring migration steps into a deployment pipeline.

The discipline here is foundational rather than advanced. Before you can run an expand-and-contract methodology on a high-traffic table or wire a migration pipeline gate that blocks an unsafe deploy, you need migrations that are deterministic, re-runnable, and lock-aware. Get the fundamentals right and the harder patterns become mechanical; get them wrong and no amount of automation downstream will save a deploy that rewrites a 200-million-row table under an ACCESS EXCLUSIVE lock at peak traffic.

Core Principles

Four invariants hold across every safe migration, regardless of engine or tool. The rest of this section is application of them.

Operational safety supersedes deployment velocity. A migration that ships an hour late and holds no exclusive lock is a success; one that ships on time and rewrites a hot table is an outage. Every schema change must remain backward and forward compatible during the rolling window where application version N and version N+1 both run against the live database.

State is tracked deterministically, never inferred. Every node must apply an identical, ordered sequence of DDL, recorded in a version ledger with checksums. This is the contract that schema version control basics establishes — without it, a migration that “looks applied” can silently diverge between primary and replica.

Every script is idempotent and re-runnable. Pipelines retry, and a step that fails after a partial apply must be safe to run again. Following idempotent script design with IF NOT EXISTS guards and conflict-tolerant writes means a half-applied migration converges to the correct state on its next run instead of throwing duplicate column and blocking the deploy.

Rollback is a forward contract, not a DROP. Define the reversal path before execution begins. Destructive operations — DROP COLUMN, a restrictive ALTER TABLE ... SET NOT NULL — never run under load without a verified, non-destructive reversal. Reversal disables the new code path and leaves the expanded schema in place; it does not destroy data the backfill produced.

Phase-by-phase Overview

Every migration moves through four forward phases, each with a single job and a gate that must be green before the next begins.

Prepare — generate the migration, lint it, and assert backward compatibility against the live schema on an environment that mirrors production. Surfacing lock contention and index build time here, before merge, is the entire purpose of environment parity strategies.

# Shell · CI dry-run gate · read-only DB role, no production writes
# Fails the PR check if the generated DDL diverges from the expected diff.
flyway -url=jdbc:postgresql://staging-db:5432/app \
  -user=deploy -password="$DB_PASS" \
  -dryRunOutput=/tmp/migration-dry-run.sql migrate
./scripts/assert-no-destructive-ddl.sh /tmp/migration-dry-run.sql

Deploy — apply the migration as a discrete, additive, forward-only step that runs before the new application image rolls out. Cap how long any statement may block with an explicit lock_timeout.

-- PostgreSQL · run as the migration role · must run at a low-write window
-- lock_timeout caps how long the ALTER may wait before failing fast.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS fulfillment_status VARCHAR(50) DEFAULT 'pending';
COMMIT;
-- CREATE INDEX CONCURRENTLY must run OUTSIDE this transaction, as its own step.

Backfill — populate the new column in throttled, idempotent batches after the schema is live, halting if the slowest replica falls behind. Long transactions cause replication lag and lock escalation, so each batch commits independently.

# Python · post-deploy worker · safe to re-run · halts if replica lag is high
# Run as a separate job, NOT inline with the deploy step.
import psycopg2
from psycopg2.extras import execute_batch

def backfill_chunk(cur, last_id, batch_size=1000):
    cur.execute(
        "SELECT id FROM orders WHERE fulfillment_status IS NULL "
        "AND id > %s ORDER BY id LIMIT %s",
        (last_id, batch_size),
    )
    rows = cur.fetchall()
    if not rows:
        return None
    execute_batch(
        cur,
        "UPDATE orders SET fulfillment_status = 'legacy_pending' WHERE id = %s",
        [(r[0],) for r in rows],
    )
    return rows[-1][0]  # cursor: last processed id

conn = psycopg2.connect(dsn)
last_id = 0
with conn.cursor() as cur:
    while last_id is not None:
        last_id = backfill_chunk(cur, last_id)
        conn.commit()  # commit per chunk to bound replication lag
conn.close()

Validate — gate promotion on schema-aware health checks and a zero-drift assertion; only after convergence do you schedule the contract phase. Reversal here is forward-only, renaming for audit rather than dropping.

-- PostgreSQL · run as the migration role · safe under load (metadata-only rename)
-- Application code must already have stopped writing this column.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders RENAME COLUMN old_status TO _deprecated_old_status;
COMMIT;
-- Schedule the DROP for a later release, after confirming zero references.

Tool & Database Matrix

The engine decides what your tooling can promise. The matrix below drives how aggressive each phase can be, and the deeper split lives in transactional vs non-transactional databases.

The practical takeaway: a pipeline that assumes PostgreSQL’s transactional DDL will leave a MySQL database half-migrated on the first failed step. Pick the tool that exposes lock behavior rather than the one that hides it — the trade-offs are laid out in the migration tool comparison.

CI/CD Integration Pattern

The cheapest gate catches the most outages: a required, blocking status check that refuses to merge a destructive or non-online migration. Everything downstream assumes this check passed.

# .gitlab-ci.yml — migration safety as a required, blocking stage
# Context: runs against a throwaway snapshot DB; never touches production.
migration_safety:
  stage: verify
  rules:
    - changes: [ "migrations/**/*" ]   # only when a migration changed
  script:
    - ./bin/restore-snapshot --into ci_db            # production-like state
    - ./bin/migrate up --database ci_db              # applies cleanly?
    - ./bin/assert-lock-budget --database ci_db --max-exclusive-ms 200
    - ./bin/assert-safe-down --dir migrations/       # no DROP/TRUNCATE/DELETE
  allow_failure: false   # blocks the merge train

Wire this so a red result cannot be overridden by a merge. The full set of pull-request and pre-deploy checks — checksum verification, backward-compatibility diffs, lock-budget assertions — is built out in migration pipeline gating.

Failure Modes & Rollback Contract

Migrations fail in a small set of characteristic ways. Naming each is how you build the gate that catches it.

• Checksum drift — an applied migration differs from the repository version. Root cause: a hotfix run by hand outside the pipeline.
• Lock timeout — the lock_timeout fires because a long-running query holds the table. Root cause: the migration ran during peak traffic, not a low-write window.
• Full-table rewrite — an “add column” silently copies the whole table. Root cause: a non-INSTANT default on MySQL, or a volatile default on older PostgreSQL.
• Replication lag spike — an unthrottled backfill outruns the replica’s apply rate. Root cause: batch size tuned for the primary, not the slowest follower.
• Backward-incompatible deploy — new code ships expecting a column the gate failed to flag. Root cause: a rename disguised as an add-plus-drop across two migrations.
• Destructive rollback — an automated down runs DROP COLUMN and loses data the backfill produced. Root cause: treating rollback as schema reversal instead of path disablement.

The contract that prevents the last two: deploys are additive and forward-only, and reversal restores the previous application image while leaving the schema expanded. Prefer a forward migration over a destructive REVERT so the audit trail stays intact.

The Anatomy of a Safe Migration Script

A migration script that survives contact with production shares four properties, and each one is a habit rather than a feature of any particular tool. The first is that it is idempotent — running it twice leaves the database in the same state as running it once. Pipelines retry; a network blip after the DDL committed but before the runner recorded success will re-run your script, and a bare CREATE TABLE or ALTER TABLE ADD COLUMN fails the second time with “already exists”, turning a successful change into a red build. Guarding every statement with IF NOT EXISTS, IF EXISTS, or a catalog check makes the script converge instead of collide. The second property is that it makes one logical change. A migration that adds a column, backfills it, and adds a constraint in a single file cannot be reasoned about, partially rolled back, or throttled — and if the backfill is slow, it holds whatever lock the first statement took for the entire duration. Splitting the change into an additive DDL migration, a separate throttled backfill, and a later constraint-tightening migration keeps each step short and independently recoverable.

The third property is a timeout budget on every statement that can block. Before any ALTER, set lock_timeout (PostgreSQL) or lock_wait_timeout (MySQL) so the statement abandons a contended lock rather than queueing production behind it, and set a statement_timeout so a runaway backfill cannot run for an hour unnoticed. A migration without timeouts is a migration that trusts production to be quiet at the exact moment it runs, which it will not be. The fourth property is transactional wrapping where the engine allows it. In PostgreSQL, most DDL is transactional, so a multi-statement migration either fully applies or fully rolls back, and you should keep related statements in one transaction to get that guarantee. In MySQL 8.0 the calculus is different — the next section explains why — but the principle holds: know exactly what your engine commits implicitly, and structure the script so a failure leaves a state you can recover from without a restore.

PostgreSQL vs MySQL: The Differences That Bite

The single largest portability trap is transactional DDL. PostgreSQL treats schema changes as ordinary transactional statements: wrap three ALTERs in a BEGIN … COMMIT and a failure on the third rolls back the first two, leaving the table exactly as it started. MySQL 8.0 does not offer this — every DDL statement performs an implicit commit before and after it runs, so there is no such thing as a multi-statement DDL transaction. A migration that adds two columns and fails on the second leaves the first column committed and the migration half-applied, and there is no ROLLBACK to undo it. This is why MySQL migrations must be authored as independently idempotent steps: each statement has to be safe to re-run, because the recovery path after a mid-migration failure is “run the remaining statements”, not “roll back and retry the whole thing”. Assuming Postgres semantics on MySQL is one of the most common ways a cross-database team ships a migration that cannot cleanly recover.

Lock behavior differs too, and in ways that change how you sequence a change. PostgreSQL’s CREATE INDEX CONCURRENTLY builds an index without blocking writes but cannot run inside a transaction block and leaves an INVALID index behind if it fails, which you must detect and drop before retrying. MySQL 8.0’s online DDL uses ALGORITHM=INPLACE, LOCK=NONE for many operations, but silently falls back to ALGORITHM=COPY — which rebuilds the whole table under a lock — for the operations it cannot do in place, so you specify the algorithm explicitly and let the statement error rather than let it choose a table-copy for you. The two engines also disagree on what a “fast” ADD COLUMN is: adding a nullable column is instant metadata on both, but adding a column with a volatile default, or setting NOT NULL on an existing column, can force a rewrite on one engine and not the other. The fundamentals in this section are written to be true on both, but the moment you touch a specific operation you check that operation’s cost on your engine and version, because the difference between an instant metadata change and a full-table rewrite is often a single keyword.

Why Tool Selection Is a Safety Decision, Not a Preference

Teams often treat the choice between Flyway, Liquibase, Alembic, or a framework’s built-in runner as a matter of taste, but the properties that matter are all safety properties. The first is checksum verification: a good runner records a hash of each migration when it applies it and refuses to proceed if a previously-applied file has been edited, which catches the single most dangerous mistake in schema version control — someone changing history that production already ran. The second is honest transactional behavior: the runner should wrap a migration in a transaction where the engine supports it and be explicit where it does not, rather than papering over MySQL’s implicit commits with a promise of atomicity it cannot keep. The third is lock visibility: the best runners surface the lock a migration will take, or at least do not obscure it, so you can wire a pipeline gate that estimates lock duration before the deploy rather than discovering it in production.

What a runner deliberately should not do is decide your sequencing for you. No tool knows whether your change is backward-compatible with the code currently in flight; that is a design decision you make and a gate you enforce. The right mental model is that the runner is a disciplined executor — it guarantees migrations run once, in order, with their integrity checked — while the safety of what runs remains the author’s responsibility. Choosing a runner is therefore about which guarantees it makes cheap to rely on, and the guarantees worth paying for are exactly the ones that turn a silent corruption into a loud, early failure.

A final foundational habit ties the rest together: treat the migration as code that gets reviewed, tested, and gated like any other change, not as an operational afterthought pasted into a deploy. That means the migration lives in version control beside the application change that needs it, it runs against a production-like database in CI so its lock and runtime surface before merge, and a reviewer checks it for the four safety properties above the way they would check any risky function. The teams that ship schema changes without drama are not the ones with the cleverest tooling; they are the ones for whom a migration is an ordinary, reviewable, testable artifact whose behavior is understood before it ever touches production. Everything in the guides that follow is an elaboration of that stance applied to a specific fundamental.

What This Section Covers

This section is six guides, one per fundamental. Environment parity strategies covers making staging mirror production closely enough that lock contention and index-build time surface before merge, including seeding anonymized production data so volume distributions match. Idempotent script design covers writing DDL and data backfills that converge to the same state no matter how many times a retrying pipeline runs them. Migration tool comparison weighs runners like Flyway and Liquibase on checksum verification, transactional behavior, and how much lock detail they expose. Online schema change tools covers the copy-and-swap utilities — gh-ost, pt-online-schema-change, and their triggers-or-binlog trade-offs — that rewrite a large table without holding the blocking lock a native ALTER would. Schema version control basics covers ordering, branching, and conflict resolution so two engineers merging migrations never produce a non-deterministic apply order. Transactional vs non-transactional databases covers the implicit-commit behavior of MySQL DDL and how to make each step independently recoverable when atomic rollback is unavailable.

Frequently Asked Questions

Should structural schema changes go through an ORM’s auto-generated migrations or raw SQL? Use the ORM for application data access, but manage structural DDL as versioned, reviewed raw SQL. Auto-generated migrations accelerate development at the cost of hiding the two things that decide whether a deploy is safe: the lock class of each statement (ALGORITHM=INPLACE versus a COPY rewrite) and whether the engine commits implicitly. When the lock behavior is invisible, you cannot gate on it.

Why prefer a forward migration over a down/REVERT script for rollback? Because a forward, additive reversal preserves data and audit history, while a destructive down can lose data the backfill produced and leaves a gap in the version ledger. Rename a deprecated column to a _deprecated_ prefix and schedule the DROP for a later release once you have confirmed zero references, rather than dropping it inline during an incident.

How long must a migration stay backward compatible? For the entire rolling-deploy window where the previous application version is still serving traffic — in practice at least one full release cycle, often two to four weeks if you run canaries. During that window both the old and new schema shapes must be queryable, which is why additive changes deploy first and destructive contraction comes last.