Removing a column: stop reading it, deploy; stop writing it, deploy; then drop it. In Postgres DROP COLUMN is metadata-only and instant — but the space is not reclaimed until a table rewrite, so a dropped 200-byte column keeps costing storage until pg_repack or a natural rewrite occurs.

---

The Real Cause of Migration Outages: Lock Queueing, Not the Rewrite

Here is the belief to correct, because it sends teams to the wrong solution:

"The migration took the site down because it rewrote a multi-terabyte table."

Sometimes true. Usually not. The far more common cause is lock queueing, and the mechanism is worth understanding precisely because it is counter-intuitive and because one setting prevents it.

ALTER TABLE requires an ACCESS EXCLUSIVE lock — conflicting with everything, including plain SELECTs, which hold ACCESS SHARE. So:

The DDL did not need to be slow to cause an outage. It only needed to wait.

The one setting that prevents most of this

With a short lock_timeout, the DDL either acquires the lock quickly or gives up before a queue forms behind it. Failing the migration is enormously preferable to blocking production, and the retry loop will succeed the moment there is a gap between long queries. This is the highest-value line in any migration script, and its absence explains most migration incidents.

Three companion practices:

• Check for long transactions first. Query pg_stat_activity for anything running longer than a few seconds — including idle in transaction, which holds locks while executing nothing (Module P-4) — and either wait or terminate it.
• Do not migrate during a reporting window. An analytics workload on the primary is a supply of long ACCESS SHARE holders, which is one more argument for keeping analytics off the OLTP instance (Module P-22).
• Set statement_timeout on the migration role too, so a genuinely long rewrite cannot run for an hour unnoticed, and run migrations as a separate role from the application (Module A-13).

Which operations are cheap and which rewrite

Note the pattern: on modern Postgres most schema changes are metadata-only. Which is exactly why lock queueing, not rewriting, is the dominant outage cause — the statements are fast, and the waiting is what hurts.

CREATE INDEX CONCURRENTLY has three sharp edges worth knowing before you rely on it: it takes roughly twice as long and does two passes; it cannot run inside a transaction block, which most migration frameworks wrap statements in by default and which is a recurring practical stumble; and if it fails it leaves an INVALID index behind that continues to consume write overhead and must be found and dropped. Check pg_index.indisvalid after any failed index build.

pg_repack rebuilds a bloated table or index without the sustained exclusive lock of VACUUM FULL — it needs disk space for a copy and a brief lock at the swap, and it is the standard answer for reclaiming space from a table that has accumulated dead tuples (Module F-13).

View-based expand/contract, and the MySQL tools that are not the answer

The state of the art in Postgres migration tooling is view-based versioning — pgroll and Reshape are the notable implementations. They present each application version with its own view of the schema, so version N−1 sees email while version N sees email_address, both backed by the same physical table with triggers keeping them consistent. That makes expand/contract a property of the tooling rather than six hand-managed deploys, and it makes rollback a view swap.

And the contrast worth being explicit about, because the names are famous enough to be misapplied:

gh-ost and pt-online-schema-change are MySQL tools. They exist because MySQL's online-DDL story was historically weak, so they build a shadow table, copy rows in batches, keep it synchronised via triggers or the binlog, and atomically swap it in at the end.

Understanding why they exist is useful — the shadow-table approach is how you get an online migration when the engine will not give you one. But they are not the Postgres answer, and reaching for a shadow-table pattern on Postgres means reimplementing what the engine already provides: transactional DDL (so a failed migration rolls back cleanly), metadata-only column operations, CREATE INDEX CONCURRENTLY, and NOT VALID/VALIDATE for constraints. The residual gap — presenting two schema versions simultaneously — is what pgroll fills natively. Use the MySQL tools as an explanation of the problem, not as a template for this stack.

---

Backfills That Do Not Take the Database Down

The one-liner that looks correct and is an outage:

Four separate failures, all at once:

• One transaction, one snapshot, held for hours — which blocks vacuum across the whole database and bloats every busy table (Module F-13).
• An enormous WAL burst, which is replicated to every standby (replica lag spikes, Module P-5) and to every CDC consumer (slot growth, Module P-23), and can fill disk.
• Lock contention on 400 million rows.
• No progress and no resumability — a failure at 90% redoes everything.

The correct shape is chunked, throttled, resumable and idempotent:

Four properties: small committed chunks (so no long snapshot and no large WAL transaction), a persisted cursor so it resumes, idempotent by predicate (WHERE email_address IS NULL) so re-running is harmless, and — the practice most worth adopting — throttling on replica lag as the feedback signal rather than a fixed sleep. Lag is the composite measure of whether you are outrunning the system's ability to absorb your writes, so using it as the control loop makes the backfill self-pacing across different times of day.

Watch during a backfill: replica lag, WAL generation rate, primary disk free, dead-tuple accumulation, and CDC slot retention (Module P-23).

---

Rollback Is Asymmetric

Code rollback is easy: redeploy the previous immutable artefact (Module O-3). Schema rollback usually is not — you cannot un-drop a column that had data in it, and you cannot un-write rows a new version created in a new format.

Hence the two rules:

• Never combine a schema change and the code that depends on it in one deploy. Ship the expand, then the code. Otherwise a code rollback leaves a schema the old version cannot use, or vice versa.
• Prefer rolling forward for data problems. A corrective migration you write deliberately is usually safer than attempting to reverse one, precisely because the reverse path was never designed.

And remember the deploy interacts with the stateful parts: connection draining must complete before an instance exits, or every deploy serves errors (Module A-5), and a database failover during a deploy is a genuinely unpleasant combination worth avoiding by not deploying during maintenance windows.

Why this matters in production: the migration incidents that happen are a two-millisecond ALTER TABLE that made a table unavailable for eight minutes because it queued behind a report; a CREATE INDEX without CONCURRENTLY that blocked writes for forty minutes; a CREATE INDEX CONCURRENTLY that failed inside a framework's transaction wrapper and left an invalid index nobody noticed for a month; a single-statement backfill that pushed replica lag to twenty minutes and broke read-your-own-writes across the product; and a column rename shipped in one deploy that broke every instance still running the previous version. Each has a specific preventive named above, and lock_timeout alone prevents the largest share.

---

Where This Shows Up in Your Stack

• PostgreSQL: SET lock_timeout before every DDL statement, with a retry loop — the single highest-value practice here. Check pg_stat_activity for long and idle in transaction sessions first. ADD COLUMN, DROP COLUMN, RENAME and ADD CONSTRAINT … NOT VALID are metadata-only; VALIDATE CONSTRAINT scans under a weak lock; CREATE INDEX CONCURRENTLY cannot run in a transaction and can leave an INVALID index; pg_repack reclaims bloat without VACUUM FULL's exclusive lock. Run migrations as a dedicated role with its own statement_timeout (Module A-13), and consider pgroll for genuine two-version schema presentation.
• Node.js migration frameworks: most wrap each migration in a transaction, which breaks CREATE INDEX CONCURRENTLY — check for the framework's opt-out and make sure the setting is actually applied. Set lock_timeout in the migration's session, not globally. And handle SIGTERM so a rolling deploy drains rather than dropping in-flight requests (Module A-5).
• Kubernetes: rolling updates with maxSurge/maxUnavailable, readiness gates so an unready pod receives no traffic, a preStop delay so deregistration propagates, and a terminationGracePeriodSeconds longer than your longest request (Module A-5). Run migrations as a separate Job that completes before the new version rolls out — not as an init container on every pod, which runs it N times concurrently.
• Kafka and event consumers: during a rolling deploy both consumer versions are in the group, so message formats must be forward and backward compatible and a schema registry should reject incompatible changes (Module P-23). Adding a required field is a breaking change for the consumers still running.
• Feature-flag services: locally cached with compiled-in defaults so they cannot become a hard dependency (Module A-7), with flag age tracked and release toggles removed as part of the work.
• Caches: version the key when the value's shape changes, or the new code deserialises an old entry and fails on a fraction of requests that shrinks as the TTL expires — an unusually confusing symptom (Module P-13).

---

Summary

• Deploy is not release. Separating them with flags means you ship code dark, verify it, and expose it deliberately — and your fastest rollback becomes a configuration change in seconds rather than a redeploy in minutes.
• Rolling is cheap and slow to reverse; blue-green gives the fastest rollback at double capacity and solves nothing about the database, since both environments share it; canary detects risk on 1% of users (watch for insufficient events to be significant, and for users bouncing between versions); shadow traffic validates performance before any user is affected and is only safe on read-only or genuinely idempotent paths.
• Every strategy runs two versions at once, so version N must be compatible with N−1 in both directions — additive API responses, requests that accept the old shape, tolerant event readers, versioned cache keys, and a schema both can read and write. Most "the deploy broke things for four minutes" incidents are this rule being violated.
• Feature flags are four different things with different lifetimes, and release toggles are debt with a due date — n flags give 2ⁿ untested paths, so track their age and delete them as part of the work. The flag service must be locally cached with compiled defaults so it is never a hard dependency.
• Expand/contract is the only pattern where each step is reversible: expand, migrate/backfill, switch reads, contract — with the contract as the point of no return, taken days later, not in the same release. A column rename is six steps; ALTER TABLE RENAME breaks every running old instance instantly.
• Use NOT VALID then VALIDATE for constraints, which separates the strong lock from the full scan so neither is held long and strong at once.
• The dominant cause of migration outages is lock queueing, not table rewriting. An ACCESS EXCLUSIVE request that waits behind one long-running SELECT causes every subsequent query on that table to queue behind it, because locks are granted in order — so a two-millisecond ALTER TABLE can make a table unavailable for eight minutes.
• SET lock_timeout before every DDL statement, and retry with backoff. Failing the migration is vastly better than blocking production, and this one setting prevents the largest share of migration incidents. Also check pg_stat_activity for long and idle in transaction sessions first, and do not migrate during a reporting window.
• On modern Postgres most schema changes are metadata-only — ADD COLUMN (including with a non-volatile default since PG 11), DROP COLUMN, renames, ADD CONSTRAINT … NOT VALID — which is precisely why waiting, rather than rewriting, is what hurts. Type changes, SET NOT NULL, VACUUM FULL and CLUSTER do rewrite.
• CREATE INDEX CONCURRENTLY has three sharp edges: it takes about twice as long, it cannot run inside a transaction (which most migration frameworks impose by default), and a failure leaves an INVALID index that still costs write overhead until someone finds and drops it. pg_repack reclaims bloat without VACUUM FULL's exclusive lock.
• View-based tooling (pgroll, Reshape) presents two schema versions simultaneously, making expand/contract a property of the tool and rollback a view swap. gh-ost and pt-online-schema-change are MySQL tools whose shadow-table approach exists because MySQL's online DDL was historically weak — useful as an explanation of the problem, and the wrong template for Postgres, which already provides transactional DDL, metadata-only operations, concurrent index builds, and NOT VALID/VALIDATE.
• A single-statement backfill over hundreds of millions of rows fails four ways at once: a snapshot held for hours blocking vacuum database-wide, a WAL burst that spikes replica lag and CDC slot growth and can fill disk, mass lock contention, and no resumability. Chunk it, commit each chunk, persist a cursor, make the predicate idempotent, and throttle on replica lag as the control signal so the backfill paces itself.
• Rollback is asymmetric: code rolls back by redeploying an artefact, schema usually cannot — you cannot un-drop a column with data. So never ship a schema change together with the code depending on it, and prefer rolling forward for data problems, because the reverse path was never designed.

The next module, Load Testing and Capacity Planning, is where the numbers behind every limit in this course come from. Rate limits, concurrency caps, shed thresholds, pool sizes and autoscaling targets have all been described as "derived from measured capacity" — O-5 is where that measurement happens, including how to find the knee of the curve, why a soak test finds different bugs from a spike test, and how to build a capacity model that survives a launch.

---