How I Ran a Zero-Downtime Postgres Migration on a 180M-Row Table With Claude Code

TL;DR

Our events table was about to hit the 2,147,483,647 ceiling on its int4 primary key, and it had 180 million rows and zero tolerance for downtime. I used Claude Code to plan and write an expand/contract migration to bigint that ran over 9 days with no locked writes. The agent was a fantastic co-pilot for the boring 80%, and confidently wrong in two places that would have taken production down.

The Problem

Here's the alert that started it:

events_id_seq: 1,931,204,118 / 2,147,483,647 (89.9%)
projected exhaustion: ~41 days

The events table is the busiest table in the app. It takes roughly 1,400 inserts per second at peak, it's referenced by three other tables via foreign keys, and our API reads from it on almost every request. It was created years ago with serial, which means int4. Once the sequence hits the max, every insert fails. Not "slows down". Fails.

The textbook fix is one line:

ALTER TABLE events ALTER COLUMN id TYPE bigint;

And that one line is a trap. It takes an ACCESS EXCLUSIVE lock and rewrites the whole table plus every index on it. On 180M rows (~210 GB with indexes), our staging rehearsal of that command ran for 3 hours and 12 minutes. Three hours of no reads and no writes on the core table is not a migration, it's an outage with extra steps.

The constraints I set for myself:

• No statement may hold a heavy lock for more than ~2 seconds
• Replication lag on the read replicas must stay under 5 seconds
• Every step must be reversible until the final swap
• It has to be done well before the 41-day deadline, with buffer for surprises

Setup for context: PostgreSQL 16.4 on a managed service, a Node.js 22.x API, and Claude Code (v2.x at the time) running in my terminal with read access to the schema and a staging database.

How I Solved It

Step 0: Make the agent understand the blast radius first

Before writing any SQL, I asked Claude Code to map everything that touched events.id. Not just the table, but the whole graph:

> Find every foreign key, index, view, trigger, and application query
> that depends on events.id. Output a table. Do not propose a fix yet.

That "do not propose a fix yet" line matters. Without it, the agent jumps straight to a plan and the inventory gets sloppy. With it, I got a clean list: 3 FK columns in other tables (also int4, also need migrating), 4 indexes, 1 materialized view, and 11 ORM queries that cast the id in application code. Two of those casts I didn't know existed.

Step 1: Expand — add the new column and keep it in sync

The plan we landed on is the classic expand/contract pattern:

flowchart LR
  A[Add id_new bigint] --> B[Trigger syncs new writes]
  B --> C[Batched backfill of old rows]
  C --> D[Build unique index CONCURRENTLY]
  D --> E[Validate NOT NULL via CHECK]
  E --> F[Swap in one short transaction]
  F --> G[Contract: drop old column]

Adding a nullable column with no default is metadata-only in modern Postgres, so it's instant:

SET lock_timeout = '2s';
ALTER TABLE events ADD COLUMN id_new bigint;

Then a trigger so that every new or updated row gets id_new populated automatically:

CREATE OR REPLACE FUNCTION events_sync_id_new() RETURNS trigger AS $$
BEGIN
  NEW.id_new := NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER events_sync_id_new
BEFORE INSERT OR UPDATE ON events
FOR EACH ROW EXECUTE FUNCTION events_sync_id_new();

The SET lock_timeout = '2s' line became a rule for every DDL statement in this project. If the ALTER can't get its lock within 2 seconds (because a long-running query is holding something), it gives up instead of queueing. That matters because a queued ACCESS EXCLUSIVE request blocks every query behind it, even plain reads. A failed DDL you can retry. A lock queue pile-up you can't undo.

Step 2: Backfill in small, throttled batches

This is where Claude Code saved me the most typing. I asked for a backfill script with explicit requirements: batches by primary key range, a pause between batches, and a check on replica lag that slows down automatically.

Here's the load-bearing part of what we ended up with (Node.js, pg client):

const BATCH = 20_000;
const MAX_LAG_SECONDS = 5;

async function replicaLagSeconds(replica) {
  const { rows } = await replica.query(
    `SELECT COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()), 0) AS lag`
  );
  return Number(rows[0].lag);
}

async function backfill(primary, replica, startId, endId) {
  for (let lo = startId; lo <= endId; lo += BATCH) {
    const hi = lo + BATCH - 1;
    await primary.query(
      `UPDATE events SET id_new = id
       WHERE id BETWEEN $1 AND $2 AND id_new IS NULL`,
      [lo, hi]
    );

    let lag = await replicaLagSeconds(replica);
    while (lag > MAX_LAG_SECONDS) {
      await sleep(2_000);            // back off until replicas catch up
      lag = await replicaLagSeconds(replica);
    }
    await saveCheckpoint(hi);         // resumable after a crash or deploy
  }
}

A few details that made a real difference:

• Range-based batches, not LIMIT/OFFSET. Offsets get slower as you go. Ranges on the primary key stay constant-time.
• AND id_new IS NULL makes each batch idempotent. Re-running a range is harmless.
• A checkpoint after every batch. The script got killed twice (once by a deploy, once by me), and both times it resumed from the last saved id.

The backfill ran for about 5.5 days at an average of ~380 rows/second during peak hours and much faster at night, when the lag check rarely kicked in. Peak replica lag during the whole run: 4.1 seconds.

Step 3: Build the index without blocking writes

You can't make id_new a primary key without a unique index, and a normal CREATE INDEX blocks writes for the whole build. So:

CREATE UNIQUE INDEX CONCURRENTLY events_id_new_idx ON events (id_new);

That took 47 minutes, with writes flowing normally the whole time.

Step 4: Prove NOT NULL without a full-table lock

A primary key requires NOT NULL. ALTER COLUMN ... SET NOT NULL normally scans the entire table under a heavy lock. The trick (Postgres 12+) is to add a CHECK constraint as NOT VALID, validate it separately with a weaker lock, and then let SET NOT NULL reuse that proof:

SET lock_timeout = '2s';
ALTER TABLE events
  ADD CONSTRAINT events_id_new_not_null CHECK (id_new IS NOT NULL) NOT VALID;

-- Takes SHARE UPDATE EXCLUSIVE: reads and writes keep working
ALTER TABLE events VALIDATE CONSTRAINT events_id_new_not_null;

Step 5: The swap — one short, rehearsed transaction

Everything above was preparation for about 1.4 seconds of actual lock time:

BEGIN;
SET LOCAL lock_timeout = '2s';

ALTER TABLE events ALTER COLUMN id_new SET NOT NULL;  -- instant, uses the CHECK
ALTER TABLE events DROP CONSTRAINT events_pkey;
ALTER TABLE events
  ADD CONSTRAINT events_pkey PRIMARY KEY USING INDEX events_id_new_idx;

ALTER SEQUENCE events_id_seq AS bigint;
ALTER TABLE events ALTER COLUMN id_new SET DEFAULT nextval('events_id_seq');
ALTER SEQUENCE events_id_seq OWNED BY events.id_new;
ALTER TABLE events ALTER COLUMN id DROP DEFAULT;

ALTER TABLE events RENAME COLUMN id TO id_old;
ALTER TABLE events RENAME COLUMN id_new TO id;

DROP TRIGGER events_sync_id_new ON events;
COMMIT;

We rehearsed this on a staging snapshot 6 times. In production it ran in 1.4 seconds. The first attempt actually hit the lock_timeout because of a long analytics query, rolled back cleanly, and the second attempt 30 seconds later went through. That's exactly the behavior you want.

The 3 foreign-key tables went through the same dance afterwards (smaller tables, so faster), and a week later we dropped id_old once we were confident nothing read it.

Where the Agent Was Confidently Wrong

I want to be honest here, because this is the part most "I did X with AI" posts skip.

❌ Mistake 1: CONCURRENTLY inside a migration transaction

The first migration file Claude Code generated wrapped CREATE UNIQUE INDEX CONCURRENTLY in our migration tool's default transaction. Postgres rejects that outright (CREATE INDEX CONCURRENTLY cannot run inside a transaction block), so it would have failed loudly, not silently. Annoying, but safe. The fix was to mark that migration as non-transactional.

⚠️ Mistake 2: The "simpler" swap that would have caused an outage

This one scared me. Midway through, I asked the agent to "simplify the swap step". Its suggestion dropped the NOT VALID / VALIDATE dance and went straight to SET NOT NULL inside the swap transaction, with the comment "the column is fully backfilled, so this is fast."

The column was fully backfilled. But SET NOT NULL without a validated CHECK still scans all 180M rows to prove it, while holding ACCESS EXCLUSIVE. On staging, that "simplified" swap held the lock for 4 minutes and 38 seconds. In production, that's every API request timing out at once.

The agent's reasoning was correct about the data and wrong about the locking behavior. That's the pattern I now watch for: AI agents reason well about what the data looks like and badly about what the database does to get there.

Lessons Learned

1. Make the agent inventory before it plans. "Find every dependency, don't propose a fix yet" produced a far better map than "how do I migrate this column?" The two hidden casts in app code would have been production bugs.

2. Encode your safety rules as constraints, not hopes. lock_timeout = '2s' on every DDL and a replica-lag guard in the backfill turned "be careful" into something the database enforces. The agent followed these rules perfectly once they were written down in the task.

3. Never accept "simplify" on a lock-sensitive step without a staging rehearsal. Ask the agent to state which lock each statement takes. When I started asking "what lock does this acquire, and for how long?", the answers got much more careful.

4. Rehearse the swap until it's boring. Six rehearsals felt excessive. Then the first production attempt hit a lock timeout, and nobody panicked because we'd already seen that exact rollback.

5. Let the agent write the boring code, but you own the timeline. Claude Code wrote ~90% of the scripts and SQL. I owned the order of operations, the go/no-go calls, and the rollback plan. That split felt right.

What's Next

We have 4 more int4 tables on the "within 2 years" list. My plan is to turn this whole flow into a reusable runbook with the agent: a checklist template, a parameterized backfill script, and a staging rehearsal step that measures lock duration automatically and fails the run if any statement exceeds 2 seconds. The goal is that the next migration is mostly a review job, not a 9-day project.

Wrap-up

If you're sitting on a serial column on a busy table, check your sequence today:

SELECT last_value FROM events_id_seq;

Then compare it to 2,147,483,647. Future you will be grateful.

👉 Follow me on Dev.to for more build logs on shipping real systems with AI coding agents, and drop a comment with your own migration horror story. I'm especially curious whether anyone has a cleaner way to handle the foreign-key tables. 🚀