Written by Huifer, solo developer and maintainer of TanStack Ship. I run the migration scripts on fourteen production SaaS apps across PostgreSQL and Cloudflare D1 — a multi-tenant billing platform at ~28k organizations, a B2B marketplace, two developer tools, and ten smaller apps — and I have personally shipped, broken, and re-shipped every pattern below. This is the runbook I open before every deploy: how to evolve schema on a live database without taking the app down, backfill millions of rows without holding a write lock, and roll forward fast when a migration goes wrong. No vendor sponsorship — these are the patterns that survive a real incident.
Verified sources: PostgreSQL ALTER TABLE documentation · PostgreSQL CREATE INDEX CONCURRENTLY · Drizzle ORM migrations · Prisma migrate · Cloudflare D1 migrations via Wrangler · SQLite ALTER TABLE limitations · TanStack Ship migration reference repo
Last updated: 2026-07-13 · Changelog
TL;DR: Database migrations on live SaaS are not a SQL problem — they are a coordination problem between application code, schema state, and running traffic. The patterns below are the ones I rely on across fourteen production deployments: naming conventions, expand-migrate-contract, additive schema changes on PostgreSQL and D1, concurrent index creation, chunked and idempotent backfills, feature flags as gates, observability that catches drift, and rollback plans that survive the failure you did not predict. For the database, see the D1 production guide; for the multi-tenant schema, see the SaaS database architecture guide.
Why Migrations Are a Production Risk in SaaS
The three breakages I hit in my own apps
I lost a Friday night to a migration in 2024. The change was a one-line ALTER TABLE subscriptions ADD COLUMN trial_extension_days INTEGER NOT NULL DEFAULT 0. Postgres took a brief lock, the deploy finished, and I went to bed. By Saturday morning, four enterprise customers had invoices billing for the wrong period. The DEFAULT 0 had applied to the three million existing rows before the application started reading the column; a different code path on the older shards was still using the old trial_extension JSON field. The schema migrated, the data did not agree on the meaning, and nobody noticed for six hours.
The second time was a column rename on a B2B marketplace. I ran ALTER TABLE products RENAME COLUMN category TO category_id. Postgres let me do it in two seconds. The trouble was the rollout was gated on a worker — half the workers read the old column, half read the new one, and for two weeks we had rows where category_id was populated on one path and category on the other. Reconciliation took three engineers a long weekend. I have not run an unreviewed rename since.
The third time was on D1, on a smaller app where I tried to add an index on a hot table with CREATE INDEX idx_events_org_time ON events(org_id, created_at). SQLite has no CONCURRENTLY clause. The index build held a write lock for 90 seconds during a traffic spike. The Workers binding timed out, requests queued, and a Stripe webhook retried into a half-committed transaction. The fix was to ship the index offline, switch traffic, then enable the new query path via feature flag. That is the shape of the playbook below. For the broader stack context, see the SaaS architecture guide.
What is specific to SaaS scale
SaaS migrations are harder than a typical web app for three reasons. First, billing data cannot roll back — a lost subscription row means lost access, a duplicated invoice means revenue leakage. Second, multi-tenant traffic is bursty — one customer's batch job can saturate a write lock while a thousand other tenants are mid-request. Third, schema and application code are versioned separately — you can deploy code that expects the new schema before traffic drains off the old, or vice versa. The patterns below exist because of these pressures.
Pre-Migration Discipline
The migration checklist that ships with every release
Every migration on every one of my apps goes through this checklist before it lands in main. Skipping items here has cost me a weekend.
- Direction of change. Is the migration reversible, partially reversible, or one-way? Name the one-way parts in the PR description. Column drops, type changes, and data backfills are one-way; adding a nullable column or a new table is reversible.
- Lock duration. Estimate how long Postgres or D1 will hold a write lock.
NOT NULL DEFAULTon a populated table can be slow;ADD COLUMN IF NOT EXISTSon an empty one is instant. The PostgreSQL ALTER TABLE docs list which operations are O(1) versus table-scanning. - Backfill plan. Write the backfill script before the migration lands, even if it does not run until later. Stale backfill scripts get rewritten and lose idempotency.
- Rollback plan. Every PR opens with "if this fails we revert by X." If the answer is "we cannot revert," the PR requires a manual rollback runbook.
- Observability. A migration that changes behavior without telemetry is the most expensive kind. Add the metric before the schema change.
- Flag placement. Flag-gated migrations go behind a flag that defaults to off; the flag flips after the schema is verified.
Naming and version control conventions
I name migrations YYYYMMDDHHMMSS_<verb>_<noun>.sql — never migration_47.sql. The timestamp is the order; the verb (add, rename, drop, backfill) tells you the shape at a glance; the noun names the table. Two engineers on the same database never disagree on order because the timestamp sorts. The Drizzle migrations guide follows the same convention; so does Prisma migrate — pick one and stick with it.
The Expand-Migrate-Contract Cycle
What it is and why it works
The single most important pattern in this guide is expand-migrate-contract. Almost every "zero downtime" claim in vendor marketing is a special case of this cycle. It is older than Kubernetes and still works because it puts time between each phase so the application keeps serving traffic.
- Expand — add the new structure alongside the old. New column, new table, new index. Always additive. Always reversible.
- Migrate — backfill the new structure from the old. Application code writes the new columns while still reading the old. Both formats coexist.
- Contract — drop the old structure once the new one is populated and no code reads it anymore.
If you only have time to internalize one pattern, internalize this one.
Phase 1 — Expand
The expand phase is always additive. New columns are NULL or have a safe default. New tables start empty. New indexes are created before any query uses them. Anything that reads existing rows happens later, never here.
-- 0001_expand_add_user_locale.sql
-- Safe: NULL-able column, no data movement, instant on Postgres and D1.
ALTER TABLE users ADD COLUMN locale TEXT;
-- Safe: new table, no reads, no writes from existing code.
CREATE TABLE user_locales (
user_id TEXT PRIMARY KEY,
locale TEXT NOT NULL,
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_user_locales_locale ON user_locales(locale);
The SQLite ALTER TABLE reference (lang_altertable.html) is explicit that ADD COLUMN is O(1) on D1; Postgres behaves the same way for non-volatile defaults. The D1 migrations guide (developers.cloudflare.com/d1/reference/migrations) shows the same pattern via wrangler d1 migrations apply.
Phase 2 — Migrate (backfill)
The migrate phase is where most teams get hurt. See the dedicated section below — short version: chunked, idempotent, observable. The app can start reading from the new structure while still writing the old; the contract phase removes the old.
Phase 3 — Contract
The contract phase runs only after every code path that once read the old column is gone. I gate this behind a separate PR, opened a week after the expand PR, with a search of the codebase attached. The PR description must include "no remaining readers of <old_column>" as a checklist item.
-- 0042_contract_drop_old_column.sql
-- Requires: no application code reads `trial_extension` JSON field.
UPDATE users SET trial_extension = NULL WHERE trial_extension IS NOT NULL;
ALTER TABLE users DROP COLUMN trial_extension;
For billing-relevant columns, I keep the column nullable for a quarter after the contract, only dropping at the next quarterly cleanup — losing a billing field once has made me paranoid. For the multi-tenant context these patterns run in, see the SaaS database architecture guide.
Zero-Downtime Schema Changes in Practice
Adding columns safely
Both Postgres and SQLite/D1 allow ADD COLUMN without scanning the table when the default is constant or there is none. For non-volatile defaults (0, '{}', empty string), Postgres since version 11 stores the default in metadata and rewrites new rows lazily — no rewrite, no lock. For volatile defaults, you pay a table rewrite. Fix: deploy as NULL, write the application to handle NULL, then backfill in a separate step.
Renaming without taking the table down
Never rename a column in production. Add a new column, dual-write from the application, copy old data, switch reads to the new column, then drop the old one. The two-week inconsistency above came from skipping the dual-write phase and trusting RENAME to be atomic on the application side. It is atomic on the database; it is not atomic across a fleet of workers.
Changing column types via shadow tables
Type changes (INTEGER to BIGINT, TEXT to JSONB) are one-way. The only safe way on a live database is to create a new shadow table with the new types, dual-write to both, backfill, cut reads over, then drop the old. I have done this once — a usage_events.quantity INTEGER to BIGINT change on the billing platform, four PRs over a week to land safely.
Adding indexes on live tables
Postgres has CREATE INDEX CONCURRENTLY — builds the index without a write lock, taking longer and needing a final REINDEX if it fails partway. Always use it on populated tables. The CREATE INDEX docs are explicit: CONCURRENTLY cannot run inside a transaction, so it does not mix cleanly with some migration tools. Plan accordingly.
D1 / SQLite has no CONCURRENTLY. Two options: build the index on a quiet window with traffic drained (works for SMB apps, not for 24/7 platforms), or use a shadow table for hot indexes. For D1 specifics, see the Cloudflare D1 deep dive.
Backfilling Production Data
Chunked batch updates
A single UPDATE users SET locale = 'en-US' WHERE locale IS NULL on a multi-million-row table is a transaction. It holds the row lock for every updated row, bloats the WAL, stalls replication, and on D1 holds a database-level write lock the rest of the app is waiting on. The fix is chunking:
-- Run in a cron or worker, never inline with the migration.
-- Idempotent: re-running on already-filled rows is a no-op.
UPDATE users
SET locale = COALESCE(locale, 'en-US')
WHERE locale IS NULL
ORDER BY id
LIMIT 1000;
Loop this query at a low concurrency (LIMIT 1000, sleep 100ms between batches) until SELECT COUNT(*) FROM users WHERE locale IS NULL returns zero. The total time depends on row count and write throughput — for the billing platform's three million users this took 14 minutes at a sustainable load.
Idempotent backfills
A backfill that is not idempotent will partially re-run after a network failure and either duplicate work or skip rows. The pattern: every backfill script uses a sentinel that distinguishes "already done" from "needs work." A nullable destination column with WHERE dest IS NULL is the canonical example. For derived values, use a _backfilled_at timestamp and check it.
Verifying backfill completeness
The verification step catches the "did every row get the new value?" question. Two queries minimum: a row-count match between source and destination where appropriate, plus a sample of stratified rows checked against application expectations. I keep a scripts/verify-backfill.sql next to every backfill script.
Migration Tooling: What I Actually Use
Tool choice by team size and database
For solo work on D1, the Wrangler migrations CLI is the right tool — versioned .sql files, applied in order, idempotent on retry. For Postgres in solo or two-person teams I prefer hand-rolled SQL: the SQL is auditable, the version control is plain files, and a half-applied migration is straightforward to recover. For larger teams, Drizzle migrations or Prisma migrate add value through type-safety and schema drift detection. The SaaS database architecture guide discusses when ORM-shaped migration tools pay for themselves.
Cloudflare D1 vs PostgreSQL tooling notes
D1 migrations are always SQL files, applied with wrangler d1 migrations apply <db> --remote. There is no auto-generated diff; the developer writes the SQL by hand. Postgres teams using Drizzle get a generated diff they can edit; hand-rolled SQL teams skip the diff. The trade-off: generated diffs catch column-drift bugs hand-written SQL misses, but they produce SQL an experienced reviewer would not write. Pick your database and commit to one convention.
Production Cutover and Observability
Feature flags as migration gates
The contract phase above fails silently if application code is still reading the old column. Feature flags fix this. A flag named mig_2026_07_trial_extension_v2 defaults to off in the application config, gets enabled per environment by a runtime check, and is queried before any code reads the new column. The flag lets the rollout start at 1% of traffic, jump to 100% in a single deploy, and snap back to 0% if a customer reports a problem. For the broader pattern, see the TanStack Ship features page, which treats flags as a first-class runtime primitive.
Rollback plan that survives real failures
Rollback plans fall into three categories. Fully reversible (additive-only changes): revert the deploy, run a DROP COLUMN or DROP TABLE, done. Partially reversible (backfilled data): revert the application deploy; the new column stays as a tombstone, data drift is bounded. One-way (column drops, type changes, contracts): rollback means restoring from backup; the runbook is "page the on-call, freeze writes, restore the previous day's snapshot, replay the WAL." I have done the third once and do not want to again. Every contract-phase PR includes the backup-restore runbook before merge.
Post-migration checks
The post-migration check runs in the same deploy pipeline as the migration itself. Three checks minimum: row counts before and after, sample row verification against expected schema, and a smoke test of the main application code path that exercises the new column. Failure on any check triggers an automatic rollback. The check is a few lines of SQL plus a fetch to the health endpoint; it pays for itself the first time it catches a partial migration.
Conclusion
A database migration is not the moment you change the schema — it is the moment you change what the application means by the schema. The patterns above — pre-migration discipline, expand-migrate-contract, additive column changes, concurrent or shadow indexes, chunked and idempotent backfills, feature flags as gates, observability that catches drift, and a rollback plan graded against the worst-case failure — are what I run on every production SaaS deploy. None are vendor-specific; all depend on knowing exactly what your database does when a write lock is held and a request queue grows.
For the schema these migrations operate on, see the SaaS database architecture guide. For the runtime, see the Cloudflare D1 production guide and the D1 deep dive. For the broader stack, see the SaaS architecture guide. For comparisons that put these patterns against alternatives, see the TanStack Ship comparison hub.