A Migration Is a Production Change

We deployed an additive schema change which the database could execute without rewriting the table. It still waited indefinitely.

The statement needed an exclusive table lock. A long-running session already held a conflicting lock, so the migration queued. New application traffic then queued behind the migration. The column addition was cheap after lock acquisition; waiting to begin was the incident.

Another schema change looked cheap for the opposite reason: remove fields and tables which the application no longer read. The SQL was small. The decision would erase financial history from data sets measured in tens of gigabytes.

Neither change could be judged by reading the migration method alone.

Schema syntax is the smallest part of the rollout

A migration changes several things at once:

schema:
    which shapes the database accepts

compatibility:
    which old and new application versions can use those shapes

locks:
    which concurrent work must wait

data:
    which existing facts are transformed or removed

operations:
    how long to wait, observe, stop, recover, and continue

These concerns interact, but they are not interchangeable. A metadata-only change may have severe lock impact. A non-blocking operation may still destroy needed data. A reversible-looking down() method may recreate a column while being unable to recreate its values.

Calling all of this “run the migrations” hides the decisions that deserve review.

Fast execution does not mean fast acquisition

Database documentation often explains whether a schema operation rewrites a table or can update metadata. That answers what happens after the required lock is granted.

It does not say when the lock will be granted.

The retained PostgreSQL case added nullable pricing fields. The first version could wait indefinitely for its table lock. While it waited, production API and administrative traffic queued behind it. The correction set a five-second session-local lock timeout and made the additions idempotent so an operator could apply them separately without making the next deployment fail.

PostgreSQL documents that ALTER TABLE acquires ACCESS EXCLUSIVE unless a form states otherwise. Its client configuration reference defines lock_timeout as the maximum wait for a lock and advises against setting it globally because unrelated sessions would inherit the policy. The operational rule follows: the migration session needs a bounded acquisition budget, not a comforting estimate of execution time.

acquire within the budget
    -> execute and verify

cannot acquire within the budget
    -> fail this rollout
    -> inspect the blocker
    -> reschedule or change the operation

A timeout does not make the change safe. It prevents the migration from silently converting a busy database into an unbounded queue.

A blocked migration can block work behind it

It is tempting to think a waiting schema statement is passive. Lock queues make that assumption dangerous.

Suppose an old transaction holds a read-compatible lock. A schema change asks for an exclusive lock and waits. Later requests ask for locks which would have coexisted with the old transaction, but the database queues them behind the exclusive waiter to avoid starving it.

session A: long transaction holds table lock
session B: ALTER TABLE waits for exclusive lock
session C: ordinary request waits behind B
session D: ordinary request waits behind B

The schema statement has not changed a byte. It has changed traffic behavior.

Before execution, inspect long transactions and lock holders. During execution, observe both the migration and application waiters. Set a lock-acquisition limit short enough to preserve the service, then give actual execution its own separate statement budget.

One timeout cannot express both decisions. “Wait at most five seconds to start” and “allow sixty seconds once started” protect different risks.

Managed operations share the lock graph

The second retained lock case used a managed MySQL database. An online backup held an instance-level backup lock while a schema change waited, even though the requested DDL was described as instant and non-blocking.

MySQL’s reference manual states that LOCK INSTANCE FOR BACKUP permits data changes during an online backup while preventing operations which could make the snapshot inconsistent. The lock is independent of ordinary transactional locks. That explains why an application-only inspection can miss the blocker: maintenance performed by the platform still participates in the database’s coordination rules.

The lesson is not to avoid backups. It is to put provider maintenance, automated backups, replicas, long transactions, and schema tools on the same rollout timeline.

If the platform does not expose the blocking session directly, the deployment still needs a bounded wait and a provider-aware escalation path. “The DDL is instant” is an execution claim, not permission to wait forever.

Compatibility is a sequence, not a boolean

Lock safety says nothing about old and new application versions overlapping during a rolling deployment.

An additive change usually permits an expand-and-contract sequence:

expand:
    add nullable field or new table

deploy readers:
    old code ignores it
    new code tolerates absence or empty values

deploy writers:
    populate both old and new representations when required

backfill and verify:
    move existing data in bounded batches

switch:
    make the new representation authoritative

contract:
    remove old readers, writers, then old storage

Each arrow needs an observable condition. “Deployment finished” is not enough when old workers, queued jobs, scheduled tasks, or rollback artifacts can still execute.

The reversal point also moves. Before new writes begin, dropping the added field may be harmless. After new-only data exists, rolling the application back requires a compatibility path, not merely a migration down() method.

Deleting storage requires a retention decision

The retained cleanup case removed legacy commission fields and tables only after the replacement reporting path no longer read them. That established code compatibility. It did not, by itself, establish that the historical data could be destroyed.

Planning material described affected financial data sets in tens of gigabytes. The exact production snapshots, row counts, retention decisions, and backup restore exercise are not published here. The evidence supports the need for review, not a claim that one generic retention period was correct.

Before destructive DDL, answer separate questions:

• Does any current code read or write the storage?
• Do queued or rollback versions still depend on it?
• Does the data carry accounting, audit, support, or legal meaning?
• Has the record owner approved deletion or archival?
• Is the backup both recent and restorable within the required time?
• Will dropping the object reclaim space now, later, or only after a rewrite?
• Which dependencies, views, indexes, and foreign keys disappear with it?

PostgreSQL documents that dropping a column makes it invisible without immediately reclaiming its occupied space. A later table rewrite may be needed. That detail separates logical deletion from storage recovery.

The word “legacy” answers none of these questions.

Backups are evidence only after a restore

A backup makes destructive migration less irreversible, but it does not turn rollback into a one-line operation.

Restoring one table from a full database backup may require isolated infrastructure, point-in-time recovery, extraction, reconciliation with newer writes, and another production change. Restored values may no longer satisfy the current application schema.

A useful recovery statement names:

recovery source:
    snapshot, point in time, or archive

recovery target:
    whole database, isolated table, or selected records

new writes:
    stop, replay, merge, or discard

verification:
    row counts, invariants, financial totals, and application reads

deadline:
    how long the business can wait

For destructive changes, rehearse the recovery shape before deletion. If the rehearsal is too expensive, that is evidence for archival or a longer compatibility window.

The fixture separates three gates

The DATA-03 fixture models a rollout through three independent gates:

1. old and new application versions can coexist;
2. the lock can be acquired inside a declared budget; and
3. destructive data work has retention approval and restore evidence.

It rejects an additive change which would break old code, an execution-fast change with an unbounded lock wait, and a cleanup whose code readers are gone but whose data decision is absent. It also shows later requests accumulating behind a waiting exclusive migration.

The fixture is a deterministic scheduler and policy model. It does not run PostgreSQL or MySQL, reproduce a provider backup, measure table size, execute DDL, or prove a restore. The two retained cases and official documentation support the mechanisms; the fixture makes their separate gates visible.

A safe migration has a way forward

Not every migration needs ceremony. An empty table in a private deployment has different risk from a financial table under continuous traffic. Review depth should follow data meaning, lock behavior, version overlap, and recovery cost.

The change is ready when the operator can say:

what old and new code do during overlap
which lock is needed and how long acquisition may wait
how existing data changes and how progress is verified
what stops the rollout
what remains safe after a partial application
how to continue forward
what evidence permits destructive cleanup

Prefer forward recovery to an imaginary rollback. Idempotent additions, staged backfills, compatibility reads, bounded lock waits, and delayed contraction make a partial rollout understandable.

A migration is not safe because the SQL is short, the framework generated it, or the engine calls it instant. It is safe when schema, applications, traffic, data history, maintenance, and recovery can move through the change without contradicting one another.