Zero-Downtime Database Migration: Expand-Contract and Safe Cutover

A zero-downtime database migration changes the schema of a live database without taking the application offline. Requests keep succeeding while old and new code share one Postgres (or other relational) instance, which means every migration step has to stay readable by both revisions until the cutover finishes.

This is separate from zero-downtime deployment, which covers rolling, blue-green and canary releases of compute. Here the shared state is the problem: a breaking ALTER in the same release as the code that needs it removes the rollback path and drops requests during the overlap window.

Expand-contract at a glance

What expand and contract actually require

Expand is additive: ADD COLUMN with a default or nullability the old binary accepts, new tables for a renamed concept, or a new index built concurrently where the engine allows it. PostgreSQL's CREATE INDEX CONCURRENTLY avoids a full write lock for many index builds; plain ALTER TABLE ... ALTER COLUMN TYPE often does not.

Contract is the cleanup release after metrics show no traffic on the old path and every old task is gone. Dropping a column in the expand deploy is the classic outage: the previous revision still selects that column during the rolling update.

On MySQL, online schema change tools such as gh-ost or pt-online-schema-change help when a rewrite would lock a large table for too long. On Postgres, most additive expands are ordinary migrations plus CREATE INDEX CONCURRENTLY where needed; the discipline is still compatibility across two app versions, not the tool brand.

Dual-write pitfalls

Dual-write means the application updates the old and new representations in one request path. It looks like insurance and often creates silent divergence:

1. Partial failure. The first write commits, the second fails, retries rewrite only one side, and readers disagree.
2. Missing dual-read. Writers fan out while readers still trust the old column, so bugs show up only after cutover.
3. Ordering and uniqueness. Two stores need the same uniqueness rules or you invent conflicts the single table never had.
4. Early contract. You remove the old column while a canary or a stuck task still runs the previous binary.

A safer sequence is dual-read (new code prefers the new column, falls back to the old), then dual-write with an idempotent reconciliation job, then single-write to the new shape, then contract. For renames, a generated column or a view can bridge without two writers.

Safe cutover checklist

• Ship expand migrations in a release that does not require the new columns to be populated.
• Backfill in batches with a progress marker and a way to resume.
• Deploy dual-read or dual-write code; watch error rates and row-level mismatches.
• Flip a feature flag or deploy the single-writer revision only when lag is near zero.
• Remove dual paths and old columns in a later release after the previous revision is fully drained.
• Rehearse the path in a staging database the size of production enough to expose lock and backfill time.

Amazon RDS and similar managed Postgres services do not remove this application-level choreography; they only host the engine you migrate.

How Encore runs Postgres migrations

Encore works alongside the frameworks and services you already run, and the Postgres database you declare is infrastructure from code. The platform provisions and deploys into your own AWS or GCP account: you declare a Postgres database next to the handlers that use it, keep schema changes as numbered SQL files, and Encore provisions the managed database in your own cloud when you connect the account.

Per the TypeScript database docs (and the matching Go database docs), Encore automatically applies up migrations and requires down migrations to be run manually. That matches expand-contract practice: forward steps are part of deploy; undo of schema is an explicit operation, not a side effect of redeploying an old image.

Set up the app

Declare the database and keep an expandable migration for a column rename that must not drop traffic:

// catalog/items.ts
import { api } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";

const db = new SQLDatabase("catalog", { migrations: "./migrations" });

export const getItem = api(
  { expose: true, method: "GET", path: "/items/:id" },
  async ({ id }: { id: number }): Promise<{ title: string }> => {
    // Dual-read during expand: prefer the new column, fall back to the old name.
    const row = await db.queryRow<{ title: string | null; name: string }>`
      SELECT title, name FROM items WHERE id = ${id}
    `;
    return { title: row?.title ?? row?.name ?? "" };
  },
);

Add the expand migration in the same change, without dropping name yet:

-- catalog/migrations/2_add_title.up.sql
-- Expand only: old code still selects "name".
ALTER TABLE items ADD COLUMN IF NOT EXISTS title TEXT;
-- Backfill separately in resumable batches after this migration,
-- not as one UPDATE over the whole table.

encore run starts Postgres locally and applies pending up files before your APIs serve. On deploy to AWS, Encore creates an RDS for PostgreSQL instance by default (or Aurora, imported RDS, or Neon when you choose those in the environment), runs the migrations, and gives each service a database role scoped to the databases it uses, as listed in the AWS infrastructure docs. Encore provisions an IAM role for the service. A later 3_drop_name.up.sql that drops name belongs in a contract release after dual-read is gone, not in the expand deploy.

An AI coding agent can add the expand migration and dual-read handler in one pull request, validate with encore run, and leave production cutover to a reviewed deploy. Secret values stay out of the repo via encore secret set when the service needs credentials beyond the injected database role.

Best fit: Teams that want infrastructure from code for Postgres in their own cloud, with migrations that stay backward compatible across rolling deploys, working alongside whatever HTTP framework or service layout they already use.

When to pick which approach

Related guides

• Zero-downtime deployment
• Deployment rollback
• What is Amazon RDS?
• How to add a database to a TypeScript API
• Blue-green deployment
• SQL databases in TypeScript