Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 37 ยท Migrations

Safe Schema Changes in Production

Change a live database without breaking running code: the expand and contract pattern, backfills in batches, safe renames, NOT NULL columns and index builds.

On your laptop, a migration runs against an empty database while nothing else is happening. In production, the table has millions of rows, requests keep arriving, and for a few minutes during a deploy the old version of your code and the new one run side by side. A migration that is correct can still cause an outage if it locks a table for too long or removes a column the old code still reads.

This lesson gives you a small set of habits that work the same with TypeORM and Sequelize, because they are about the database, not the library.

The two-version problem

During a rolling deploy there is a moment when some servers run version N and others run N+1. The database must work for both. So a change is safe only if the old code still works against the new schema, and the new code works until the migration has finished.

That leads to the central pattern: expand, migrate, contract.

flowchart LR
  A["Expand<br/>add new column<br/>nullable"] --> B["Deploy code<br/>writes both"]
  B --> C["Backfill<br/>copy old data"]
  C --> D["Deploy code<br/>reads new only"]
  D --> E["Contract<br/>drop old column"]

Each arrow is a separate release, so no single step breaks running code.

Renaming a column the safe way

A plain rename column is instant but breaks every old server that still selects the old name. The safe route takes several releases:

  1. Migration: add full_name (nullable).
  2. Code: write to both name and full_name, read name.
  3. Migration or script: backfill full_name from name.
  4. Code: read full_name.
  5. Migration: drop name.

Here is the middle of that story with real SQL. The "old code" keeps working through every step.

Node.js Expand, backfill, contract

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Backfill in batches

Updating ten million rows in one statement holds locks and fills the transaction log. Process them in slices and pause between slices.

Node.js A batched backfill loop

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Note the where total_cents is null filter inside the batch. It makes the loop resumable: if it crashes, running it again continues where it stopped.

Other risky changes and their safer forms

Risky change Why it hurts Safer approach
Add NOT NULL column without default Old code inserts rows without it and fails Add nullable, backfill, then add the constraint
Add column with default on a huge table Some engines rewrite the whole table Check your engine's version; add nullable then set the default
CREATE INDEX on a busy table Blocks writes PostgreSQL CREATE INDEX CONCURRENTLY; MySQL online DDL
Change a column type Rewrites the table, may fail on bad data Add a new column, copy, switch, drop
Drop a column Old code still selects it Stop reading it first, drop in a later release
Add a foreign key Validates every row while locking PostgreSQL NOT VALID then VALIDATE CONSTRAINT

PostgreSQL cannot run CREATE INDEX CONCURRENTLY inside a transaction. In TypeORM set transaction = false on that migration class, and in Sequelize run it without wrapping a transaction.

TypeScript
export class IndexOrdersCreatedAt1735000000000 implements MigrationInterface {
  transaction = false; // required for CONCURRENTLY
  async up(q: QueryRunner) {
    await q.query('CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at)');
  }
  async down(q: QueryRunner) {
    await q.query('DROP INDEX CONCURRENTLY idx_orders_created');
  }
}

(This snippet is read-only: it needs TypeORM and PostgreSQL.)

Practical checklist before you deploy

  • Can the previous version of the code run against the new schema? If not, split the change.
  • Is there a lock_timeout so a migration fails fast instead of queueing behind a long query and blocking everyone?
  • Did you test the migration on a copy of production-sized data?
  • Is there a rollback plan that does not depend on down restoring dropped data? Take a backup first.
  • Are data changes separate from structure changes?

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What are the three phases of the safe pattern for incompatible changes?

    Add the new thing, move the data and code over, then remove the old thing in a later release.
  2. 2. Why backfill in batches?

    Small slices keep locks short and let normal traffic continue.
  3. 3. Why does the batch update filter on where total_cents is null?

    Rows already done are skipped, so a crashed backfill can simply be restarted.
  4. 4. How should you add a NOT NULL column to a busy table?

    Old code that does not know the column would fail on insert if it were NOT NULL immediately.

Summary

  • During a deploy two code versions share one database, so every step must work for both.
  • Use expand, backfill, contract for renames, type changes and removals.
  • Backfill in resumable batches.
  • Build indexes without blocking writes and set lock timeouts.
  • Test on production-sized data and keep a backup.

Next: Transaction Basics