Zero-Downtime Database Migrations with Antigravity: The Expand-Contract Pattern in Production

I once ran an ALTER TABLE on a side-project SaaS in the quiet hours of the night. The command never came back. In another terminal, pg_stat_activity showed waiting queries stacking up, one after another.

The ALTER itself wasn't heavy. It was waiting for a long transaction ahead of it to finish, and every SELECT that arrived afterwards queued up behind the ALTER. My stomach hurt for a few minutes. It took me until things calmed down to see the real cause: the waiting line, not the work.

So here is the first thing I'd like to say: the dangerous change isn't the heavy one, it's the one that has to wait. What follows is the Expand-Contract pattern built on that realization, with code you can run and queries that let you check each step.

Why a Plain ALTER TABLE Stops Traffic

Most ALTER TABLE forms in PostgreSQL ask for an ACCESS EXCLUSIVE lock on the whole table. That lock conflicts with every other lock mode, including the ACCESS SHARE that a plain SELECT takes.

The trap is that the queue forms while the lock is being requested, not only while it is held. If an earlier transaction hasn't finished, the ALTER waits, and everything that arrives after it waits behind it. Even an operation that would take a millisecond can stop the service once it is standing in line.

This table sorts the common operations by what they lock and how long they run.

Row two is the one people miss. ADD COLUMN ... DEFAULT gen_random_uuid() looks like a simple column add, but because the default differs per row, every existing row gets rewritten. The Expand phase below starts by avoiding exactly that.

Decide on lock_timeout First

Put these two lines at the top of every DDL you run:

-- Give up after 3 seconds instead of building a queue
SET lock_timeout = '3s';
-- Cap the statement itself (for DDL, not for the backfill)
SET statement_timeout = '60s';

When lock_timeout fires, the ALTER fails and the queries behind it keep flowing as if nothing happened. You wait a moment and try again. It is the smallest change I know that turns a migration from something you must get right on the first try into something you can safely retry until it lands.

A runner that includes the retry:

// scripts/run-ddl.ts
import { sql } from "kysely";
import { db } from "@/lib/db";
 
export async function runDdl(statement: string, maxAttempts = 8) {
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      await db.transaction().execute(async (trx) => {
        await sql`SET LOCAL lock_timeout = '3s'`.execute(trx);
        await sql.raw(statement).execute(trx);
      });
      console.log(`OK (attempt ${attempt})`);
      return;
    } catch (err: any) {
      // 55P03 = lock_not_available
      if (err?.code !== "55P03") throw err;
      const wait = Math.min(1000 * 2 ** attempt, 30_000);
      console.warn(`lock busy, retry in ${wait}ms (attempt ${attempt})`);
      await new Promise((r) => setTimeout(r, wait));
    }
  }
  throw new Error("could not acquire lock within the retry budget");
}

Only the lock error is retried. A syntax error should fail immediately, or you will repeat a real bug eight times.

The Three Phases

Expand-Contract splits a breaking change into Expand, Migrate, and Contract.

• Phase 1 Expand — add the new schema and dual-write to both
• Phase 2 Migrate — backfill existing data, then shift reads gradually
• Phase 3 Contract — confirm zero references, rename, then drop

Ship and observe at each boundary. One schema change at a time, stopping only where you can walk back. That single line is the backbone of the whole pattern.

Phase 1: Expand — Add the Parallel Schema

The running example is moving users.id from BIGINT to UUID.

-- migration_001_expand.sql
SET lock_timeout = '3s';
 
-- Add without a default (a volatile default would rewrite every row)
ALTER TABLE users ADD COLUMN id_new uuid;
 
-- Setting a default is metadata only; it applies to rows inserted from now on
ALTER TABLE users ALTER COLUMN id_new SET DEFAULT gen_random_uuid();

gen_random_uuid() is built in from PostgreSQL 13. On older versions you need the pgcrypto extension.

Next, the unique index, built with CONCURRENTLY. This command cannot run inside a transaction.

-- migration_002_index.sql (run outside a transaction)
CREATE UNIQUE INDEX CONCURRENTLY users_id_new_key ON users (id_new);

If the build fails midway, an INVALID index stays behind. Moving on with it means uniqueness isn't actually enforced, so check:

SELECT c.relname, i.indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'users_id_new_key';
-- If indisvalid is false: DROP INDEX CONCURRENTLY and rebuild

Then add the dual-write in the application. I ask Antigravity's inline command (Cmd+I) something like "make createUser also write a UUID to id_new, and don't change the return shape," and I read the diff line by line before accepting it.

// app/server/users/create.ts
import { randomUUID } from "node:crypto";
import { db } from "@/lib/db";
 
interface CreateUserInput {
  email: string;
  name: string;
}
 
export async function createUser(input: CreateUserInput) {
  // Phase 1: the old id comes from a serial; set id_new explicitly as well
  const [user] = await db
    .insertInto("users")
    .values({
      email: input.email,
      name: input.name,
      id_new: randomUUID(),
    })
    .returning(["id", "id_new", "email", "name"])
    .execute();
 
  return user;
}

The database has a default too, but keeping an explicit write path in the app means nothing changes when you drop that default later.

Phase 2: Migrate — Chunked Backfill

Existing rows still have id_new as NULL. Running UPDATE users SET id_new = gen_random_uuid() in one statement creates a long write transaction and undoes the work of Phase 1. Cut it into chunks.

// scripts/backfill_user_uuid.ts
import { sql } from "kysely";
import { db } from "@/lib/db";
 
const CHUNK_SIZE = 1000;
const SLEEP_MS = 200;
 
async function backfillUuid(startFrom = 0) {
  let lastId = startFrom;
  let processed = 0;
 
  while (true) {
    const updated = await db
      .updateTable("users")
      .set({ id_new: sql<string>`gen_random_uuid()` })
      .where("id", "in",
        db.selectFrom("users")
          .select("id")
          .where("id", ">", lastId)
          .where("id_new", "is", null)
          .orderBy("id", "asc")
          .limit(CHUNK_SIZE)
      )
      .returning(["id"])
      .execute();
 
    if (updated.length === 0) break;
 
    lastId = Math.max(...updated.map((u) => Number(u.id)));
    processed += updated.length;
    console.log(`processed=${processed} lastId=${lastId}`);
 
    await new Promise((r) => setTimeout(r, SLEEP_MS));
  }
}
 
backfillUuid(Number(process.argv[2] ?? 0)).catch((err) => {
  console.error("backfill failed:", err);
  process.exit(1);
});

Three details matter. lastId is a cursor, so a crash resumes from process.argv. The sleep between chunks leaves I/O headroom for production queries. And progress is logged per chunk, so you can tell running from stuck.

Check completion with a query, not a feeling:

SELECT count(*) AS remaining FROM users WHERE id_new IS NULL;
-- Rows inserted after the Phase 1 deploy are already filled

Shifting Reads Gradually

Once the backfill is done, move reads to the new column behind a feature flag.

// app/server/users/get.ts
import { db } from "@/lib/db";
import { isFeatureEnabled } from "@/lib/feature-flags";
 
export async function getUserById(idOrUuid: string | number) {
  const useUuid = await isFeatureEnabled("users.read.use_uuid_id");
 
  if (useUuid && typeof idOrUuid === "string") {
    return db.selectFrom("users").selectAll()
      .where("id_new", "=", idOrUuid)
      .executeTakeFirst();
  }
 
  const numericId = typeof idOrUuid === "string" ? Number(idOrUuid) : idOrUuid;
  return db.selectFrom("users").selectAll()
    .where("id", "=", numericId)
    .executeTakeFirst();
}

Raise the flag 1% → 10% → 50% → 100%, watching error rate and latency at each step. If you don't have a flag system yet, set one up first with implementing feature flags in Antigravity.

Before flipping, check that the dual-write hasn't drifted:

SELECT
  count(*)                               AS total,
  count(*) FILTER (WHERE id_new IS NULL) AS missing_new,
  count(*) - count(DISTINCT id_new)      AS duplicated_new
FROM users;

Phase 3: Contract — Swap the Primary Key and Remove the Old Column

When reads have sat at 100% on the new column for 24 to 48 hours, you can contract. This is the part many write-ups skip: if id is the primary key, you can't just DROP NOT NULL on it. The primary key has to be swapped first.

Step 1: Add NOT NULL Without a Scan

-- migration_003_notnull.sql
SET lock_timeout = '3s';
 
-- Add as NOT VALID first (existing rows aren't inspected, so it's instant)
ALTER TABLE users
  ADD CONSTRAINT users_id_new_not_null CHECK (id_new IS NOT NULL) NOT VALID;
 
-- Validate in a separate statement (scans all rows without stopping traffic)
ALTER TABLE users VALIDATE CONSTRAINT users_id_new_not_null;
 
-- With a validated CHECK, SET NOT NULL skips the full scan (PostgreSQL 12+)
ALTER TABLE users ALTER COLUMN id_new SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_id_new_not_null;

Step 2: Swap the Primary Key in One Transaction

-- migration_004_swap_pk.sql
SET lock_timeout = '3s';
BEGIN;
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users
  ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_new_key;
COMMIT;

This adopts the existing unique index as the primary key, so nothing is rebuilt. It does take a brief ACCESS EXCLUSIVE lock, which is where the lock_timeout and retry earn their keep.

One caveat. If child tables reference users.id through foreign keys, DROP CONSTRAINT users_pkey fails on the dependency. Run the same Expand-Contract on those tables first and point them at id_new. This article stays within a single table.

Step 3: Rename to Something That Warns

-- migration_005_rename.sql
SET lock_timeout = '3s';
BEGIN;
ALTER TABLE users RENAME COLUMN id TO id_old_deprecated;
ALTER TABLE users RENAME COLUMN id_new TO id;
COMMIT;

Renames are instant. Any code still reading the old column fails here, for the first time. That's the reason to rename before dropping: you find what breaks while you can still undo it.

Step 4: Drop After the Observation Window

-- migration_006_drop.sql (after one to two weeks)
SET lock_timeout = '3s';
ALTER TABLE users DROP COLUMN id_old_deprecated;

DROP COLUMN is a metadata operation and finishes quickly. The physical space doesn't come back right away, though. It is reclaimed gradually as rows are rewritten, or with something like VACUUM FULL. If you need disk back in a hurry, remember that.

Three Traps I Stepped In

Trap 1: The Trigger Temptation

Syncing the dual-write with a database trigger looks easy because the app doesn't change. Many online schema-change tools do use triggers internally.

The cost is visibility. Writes happen inside the database without appearing in the app logs, and investigating a mismatch gets harder. I prefer writing from the app layer, but if your writes are scattered across several services, a trigger may catch what you'd otherwise miss. Whichever you choose, the condition is the same: write down, in one place, where the sync happens.

Trap 2: Long Transactions Stall CONCURRENTLY

CREATE INDEX CONCURRENTLY waits for every transaction that started before the build began. Look before you run:

SELECT pid, now() - xact_start AS duration, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC
LIMIT 10;

Batch jobs and report generators sometimes leave a transaction open without anyone noticing. Rows in idle in transaction are the first place to look.

Trap 3: The ORM Schema Goes Stale

After the rename, an app running with the old schema definition will fail forever on a column that no longer exists. Merge the rename PR before running the SQL, and put prisma db pull && prisma generate (or kysely-codegen) into CI.

Where I Use Antigravity

In this workflow I hand Antigravity two jobs: a first draft, and a second pair of eyes.

First, I ask Planning mode: "Draft an Expand-Contract plan for moving user IDs from BIGINT to UUID, with SQL per phase, app changes, and rollback." For each generated statement I follow up with: "List the locks this takes and a lock-free alternative." That's a good way to catch a forgotten CONCURRENTLY or the volatile-default trap.

I don't take the answers on faith, though. I check lock strength against PostgreSQL's Explicit Locking documentation, and finally confirm on staging by watching pg_locks.

My AGENTS.md carries a pre-change checklist:

• No long-running transactions (pg_stat_activity)
• A backup taken within the last 24 hours
• Normal and peak QPS for the table
• Rollback SQL written and run on staging
• Feature flag defaulting to OFF, with a staged rollout plan

Two More Shapes of the Same Pattern

Renaming a Column

RENAME COLUMN is instant, so what could go wrong? The application. Instances still running with the old column name error out the moment you rename, and during a rolling deploy some instances see the old name and some the new.

Expand-Contract handles it: add email next to email_address and write to both, backfill and switch reads by flag, then stop writing the old column, rename it to email_address_deprecated, and drop it after the observation window. It's slower, but no request ever sees a column vanish mid-flight.

Splitting a Table

When users has absorbed bio, avatar URL, links, and language settings, you may want users for authentication and user_profiles for the rest. Expand by creating user_profiles with a foreign key and writing to both on signup and profile update. Migrate with a chunked backfill, then move profile reads behind a flag. Contract by stopping writes of those fields to users, renaming them, and dropping later.

During Phase 2, users JOIN user_profiles returns the same data as the old single-table query, so behavior stays identical throughout. Asking Antigravity to find every read of profile fields from users and route it through the join behind the flag is the kind of mechanical edit that would take an afternoon by hand. I still review the diff myself.

Compared With Online Schema-Change Tools

In MySQL you may know pt-online-schema-change or gh-ost, which automate a similar dance: create a shadow table, keep it synced, and swap atomically. PostgreSQL has no direct equivalent, but pg_repack and pgroll take adjacent approaches. pgroll runs multiple schema versions side by side so old and new code coexist during a deploy.

I still do the manual version for two reasons. When something goes wrong, every artifact is something I wrote, so I can step through it. And doing it by hand forces me to decide things like how long to observe before contracting, which a tool can quietly decide for you. If you migrate often, pgroll deserves an evaluation.

Rollback by Phase

Rolling back from Phase 1 is easy: remove the dual-write, deploy, and keep or drop the new column. In Phase 2, set the read flag back to 0%, keep dual-writing, and investigate; the new column's data is dormant, not lost. Phase 3 is the hard one because the rename is real. Rename the columns back, then check whether any deploy in between assumed the new names.

I write the rollback SQL at the same time as the forward SQL, in two files side by side. If a clean rollback is hard to write, the migration is probably too big to ship.

Your Next Step

Pick one breaking change you have been putting off on a production database and write it on paper. Mark where Expand, Migrate, and Contract would each stop, and where you could walk back.

Then show the plan to Antigravity's Planning mode and ask for three angles you've missed. Checking those on staging, one at a time, is the surest way I know to begin—and I still feel that way every time I start a new migration.