How We Migrated 2 Billion Rows Without Downtime

Dual writes, a slow backfill, and a lot of checksums got us to a new schema with zero customer-facing errors.

Six slow boring phases moved the table with zero downtime.

Our order_events table hit 2.1 billion rows and 1.8TB on a Tuesday. On Wednesday, a simple ALTER TABLE to add a column locked it for 11 seconds in staging. Production would have been much worse.

We needed a new schema. Partitioned by month, a bigint key instead of a UUID string, and three columns split out of a JSON blob. And we couldn't take the service down, because checkout writes to that table about 3,000 times a second at peak.

It took us seven weeks. Here's exactly how we did it, including the part where I almost corrupted 40 million rows.

Why Not Just Run a Migration Script

The tempting plan is a big INSERT INTO new SELECT FROM old over a weekend. I've seen that plan fail three times at other jobs.

On a table this size it takes days, holds locks or bloats WAL, and if it fails at 70%, you start over. Meanwhile new writes keep landing in the old table, so you're chasing a moving target.

So we used the pattern that always works, even though it's slow and boring. Expand, migrate, contract.

Phase 1 Create the New Table Next to the Old One

First, the new table. Partitioned by created_at, one partition per month, created ahead of time with a small scheduled job.

CREATE TABLE order_events_v2 (
  id          bigint NOT NULL,
  order_id    bigint NOT NULL,
  event_type  text   NOT NULL,
  amount      numeric(12,2),
  payload     jsonb,
  created_at  timestamptz NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

No traffic touches it yet. That's the point. Nothing can break.

Phase 2 Dual Writes

Next, every new write goes to both tables. We did this in the application, not with triggers. Triggers would have doubled write latency inside the same transaction, and debugging them in production is miserable.

@Transactional
public void record(OrderEvent e) {
    legacyRepo.insert(e);
    if (flags.isOn(Flag.DUAL_WRITE_EVENTS)) {
        try {
            v2Repo.insert(EventMapper.toV2(e));
        } catch (DataAccessException ex) {
            dualWriteFailures.increment();
            reconcileQueue.offer(e.id());
        }
    }
}

Two decisions matter here.

The old table stays the source of truth. If the v2 write fails, we don't fail the request. We count it and queue the id for repair.

And it's behind a flag. When the first deploy showed v2 inserts adding 4ms to p99, we turned it off in seconds, added a missing index, and turned it back on.

Phase 3 The Backfill

Now the 2 billion old rows. We wrote a backfill worker that copies in small batches by primary key range.

long cursor = checkpoint.load();
while (cursor < maxId) {
    long next = cursor + 5_000;
    jdbc.update(COPY_RANGE_SQL, cursor, next);
    checkpoint.save(next);
    cursor = next;
    throttle.awaitIfReplicaLagAbove(Duration.ofSeconds(2));
}

A few things made this survivable.

• Small batches. 5,000 rows per batch kept each transaction under 200ms.
• Checkpoints. The worker saved its position after every batch. It crashed four times during the migration and each time picked up where it left off.
• Throttling on replica lag. If read replicas fell more than 2 seconds behind, the worker paused. Our read traffic never noticed.
• Idempotent copies. We used INSERT ... ON CONFLICT DO NOTHING so overlap with dual writes was harmless.

At full speed it moved about 9,000 rows a second. The full backfill took just under three days of runtime, spread over a week because we paused it during peak hours.

Row counts, checksums and shadow reads each caught different bugs.

The Part Where I Almost Broke Everything

Our mapper converted the old UUID string ids to bigint using a lookup table. During the backfill, I noticed the v2 row count was 40 million higher than I expected for one range.

Get Naveen Metta’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The mapper had a bug. For rows created before 2021, a legacy id format was being parsed twice, and those rows were being inserted with two different new ids. Dual writes didn't hit it because new rows never used the old format.

If we had cut over without checking, reports would have double-counted every order event from 2020. Finance would have found it before we did.

Row counts are the cheapest test you can run during a migration, and they caught the worst bug we had.

We truncated the affected partitions, fixed the mapper, and re-ran that range. Cost us four days.

Phase 4 Verify Like You Don't Trust Yourself

After that scare, I stopped trusting anything. We built three layers of checks.

1. Row counts per day. Cheap, runs every hour, catches gross errors.
2. Checksums per range. For each block of 100,000 ids, we hashed key columns in both tables and compared. This found 1,200 rows where amount was rounded differently.
3. Shadow reads. For a week, 5% of read requests queried both tables and logged any difference. We did this off the request thread so users didn't wait.

SELECT md5(string_agg(id || ':' || amount, ',' ORDER BY id))
FROM order_events_v2
WHERE id BETWEEN $1 AND $2;

When shadow reads ran for five straight days with zero mismatches, I finally relaxed a little.

Phase 5 Cut Over Reads, Then Writes

We moved reads first, behind a percentage flag. 1%, 10%, 50%, 100%, over about four days. We watched latency and error rates at every step.

Reads on v2 were actually faster. Monthly partitions meant most queries only touched one or two partitions, and p95 for the order history page dropped from 140ms to 45ms.

Then we flipped the source of truth. v2 became primary, and the legacy table became the secondary dual write, just in case we had to go back. We kept that for two weeks.

Phase 6 Contract

Finally, we stopped writing to the old table, waited another week, took a final backup, and dropped it. That freed 1.8TB and a surprising amount of autovacuum CPU.

The whole thing had zero customer-facing errors and zero downtime. It was not fast. It was not exciting. That's exactly what you want from a migration.

Key Takeaways

• Use expand, migrate, contract. Never one giant copy.
• Dual write in the application, behind a flag, with the old table as the source of truth.
• Backfill in small batches with checkpoints and throttling on replica lag.
• Make every copy idempotent so overlaps don't matter.
• Verify with row counts, checksums, and shadow reads before trusting the new table.
• Cut over reads gradually, then writes, and keep a way back.

What's the biggest table you've had to migrate live? Tell me how it went in the comments.