Zero-Downtime Database Migrations for High-Traffic Checkout Services

Your checkout service can survive a bad deploy. It cannot survive a bad migration. A migration that locks the orders table for thirty seconds during a lunch rush is an incident, not a maintenance window — and for digital storefronts selling game codes, gift cards, and mobile top-ups around the clock, there is no such thing as a quiet hour to run one.

I work on backend systems behind high-volume digital delivery, including online marketplaces for digital goods like Kadokey, where a single bad migration can hold up thousands of orders. Here is the playbook that keeps migrations boring.

Rule 1: Migrations must be expand-only

The expand-contract pattern is the foundation of zero-downtime schema change:

1. Expand — add the new column or table, nullable or with a default, in one deploy.
2. Migrate — dual-write or backfill in the application layer, possibly across several deploys.
3. Contract — drop the old column only after every node runs the new code.

Nothing in the database is ever renamed in a single step. Renames are a delete-plus-add that breaks the old code the moment it runs — and with a rolling deploy, old code and new schema will overlap for minutes. Treat every rename as two separate, boring migrations.

Rule 2: Never lock what you cannot afford to hold

The usual suspects:

• ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT ... on Postgres holds an ACCESS EXCLUSIVE lock while rewriting the table on older versions. Even on newer versions, adding a NOT NULL constraint afterward still scans the table. Do it in phases: add the column nullable, backfill in batches, add the constraint with NOT VALID, then VALIDATE CONSTRAINT — validation takes a weaker lock.
• Adding a unique index: use CREATE INDEX CONCURRENTLY. It takes longer but never blocks writes.
• Changing a column type: don't. Add a new column, backfill it, swap reads over, drop the old one.

Backfills deserve their own caution. Updating ten million order rows in one statement holds locks and bloats the write-ahead log. Batch the backfill — a few thousand rows per transaction with short sleeps between batches — and make the job resumable and idempotent so a crash just continues where it stopped.

Rule 3: Make the application tolerant of both schemas

During the window between "migration ran" and "new code deployed everywhere," the app must work with the old schema, the new schema, and every half-migrated state in between. That means:

• Read from the new column with a fallback to the old one.
• Write to both during the transition.
• Feature-flag the cutover so a bad read path can be flipped back without a deploy.

This dual-read/dual-write dance feels like extra work because it is. It is also the difference between a Tuesday deploy and a Saturday incident.

Rule 4: Test the migration, not just the schema

db:migrate passing locally proves nothing. What you want to verify:

• Lock timing: run the migration against a production-sized staging database and measure lock acquisition time, not just total runtime.
• Rollback safety: every migration should have a tested reverse path. If the contract step goes wrong at 2 a.m., you want a button, not a whiteboard session.
• Application compatibility: run the old application code against the new schema in CI. Rolling deploys guarantee this combination exists in production; your tests should too.

Rule 5: Instrument the deploy window

Migrations should be first-class deploy events in your monitoring: annotate the timeline, alert on lock waits (pg_locks, pg_stat_activity), and watch p99 latency on the orders table's write path for an hour afterward. Most migration damage shows up as a slow latency creep, not an error spike — the lock is held just long enough to queue writes, and queues amplify.

The checklist

1. Expand-only change; no renames in one step.
2. Backfill batched, resumable, idempotent.
3. Indexes built concurrently; constraints validated in two phases.
4. App reads and writes both schemas behind a flag.
5. Tested against production-scale data with lock timing measured.
6. Old code tested against new schema.
7. Rollback path verified.
8. Monitoring annotated and lock waits alerted.

Done right, nobody notices a migration happened — which is the highest praise infrastructure work can get. Teams running always-on digital delivery platforms treat this discipline as table stakes: when customers expect their codes delivered in seconds at any hour, the database is never allowed a maintenance window.