Production Migrations — Zero-Downtime Patterns

Schema changes that don't break the running app

Most schema changes (ADD COLUMN, CREATE INDEX) are safe to apply to a running SQLite database. The risky ones — DROP COLUMN, type changes, large table rewrites — need more care.

Additive changes (ADD COLUMN with DEFAULT, CREATE INDEX, CREATE TABLE) — apply at startup, hold a brief lock, done.
Renames (RENAME TABLE / RENAME COLUMN) — fast metadata change in modern SQLite.
Destructive changes (DROP COLUMN, type change via CTAS) — write a migration that's safe to run while old code is still alive: ADD the new column, deploy code that writes both, backfill, deploy code that reads new, drop the old.
Index creation on huge tables — locks for the duration. Schedule for low-traffic windows or use SQLite's indexed_columns on STRICT tables to keep work small.

Warning: Unlike Postgres, SQLite has no concurrent index creation. A CREATE INDEX on a 100M-row table will hold a write lock for the duration. Plan accordingly — measure on a copy first, deploy in a maintenance window if needed.

Code

Safe migration pattern — phased deploy·sql

-- Phase 1: additive change, deploy migration, deploy code that writes both
ALTER TABLE users ADD COLUMN email_lc TEXT;
UPDATE users SET email_lc = lower(email);   -- backfill
CREATE INDEX idx_users_email_lc ON users(email_lc);
PRAGMA user_version = 7;

-- Phase 2 (next deploy): code reads from email_lc
-- Phase 3 (later deploy): drop old column or trigger
--   ALTER TABLE users DROP COLUMN email;

Production Migrations — Zero-Downtime Patterns

Schema changes that don't break the running app

Code

External links

Exercise

Progress

Comments 0