Zero-Downtime Data Migrations: Moving 100M Rows While Live

Zero-Downtime Data Migrations: Moving 100M Rows While Live

The Context: "The 4-Hour Maintenance Window Myth"

It was mid-2024. Our core "Transactions" table, which holds 100 million rows, was on an old Oracle 11g schema and needed to be migrated to a new, partitioned 19c schema to support our "Global Scaling" initiative. The business team gave us a "4-hour maintenance window" on a Sunday night.

But our initial testing showed that a simple INSERT INTO ... SELECT FROM for 100 million rows took 9 hours. And if it failed halfway? We'd have to rollback and leave the bank "Down" for another 4 hours. We didn't have a "Migration" problem; we had an "Archaic Thinking" problem. This article explores the architecture of Zero-Downtime Migrations and how we moved 100 million rows while users were still swiping their cards.

The Problem Statement

Moving massive datasets in a live 24/7 bank is a "High-Wire Act."

• Goal: Zero Downtime (RTO=0) for the User.
• Metric: Zero Missing Records & Zero Duplicate Records.
• Challenge: The "Catch-Up" phase. While you're copying the old 100M rows, new transactions are arriving. How do you "Sync" the data that was added during the copy?

Visual Architecture: The Dual-Write Migration Bridge

+-RESETFULLSCREEN

The Core Logic: The 3-Phase Migration

Phase 1: The Dual-Write

Before you copy a single old row, you must modify your application to write to both the old and new schemas for every new transaction.

• The Trick: The write to the "Old" schema is the primary. The write to the "New" schema is asynchronous (so it doesn't slow down the user).
• Result: From this moment forward, the old and new databases are "Synchronized" for all NEW data.

Phase 2: The Historical Backfill

Now, you can copy the 100 million "Old" rows.

• The Strategy: Run a background worker that reads the old data in batches (e.g., 50k rows at a time) and "Upserts" it into the new schema.
• Conflict Handling: If the row already exists in the new schema (because it was a "New" transaction from Phase 1), the worker "Skips" it. This is why you need an Idempotent Primary Key.

Phase 3: The "Verified" Switchover

Once the backfill is done, you run a Reconciliation Job (a simple "COUNT" or "HASH" check) to verify that both schemas are identical.

• The Result: You switch the application's "Primary" pointer from the old DB to the new DB. You keep the old DB as a "Read-Only" fallback for 24 hours just in case.

The "Baigan" Take: Insights from the Trenches

Look, people love "Database Migration Tools" that promise "Seamless Cloud Mobility". But baigan ka logic hai wo (that's nonsense) when you're moving 10TB of data over a 1Gbps link.

The 'Foreign Key' Nightmare. The biggest mistake I've seen is migrating a table without its related tables first. If you move "Transactions" but forget "Accounts," and your new DB has a "Foreign Key Constraint," every single write will fail.

• Rule: Migrate your "Reference Data" (Accounts, Customers) weeks BEFORE you move your "Transaction" data.
• Rule: Use CDC (Change Data Capture) if your volume is too high for application-level dual-writes. Oracle GoldenGate can "Tail" the redo logs and replicate the changes to the new DB with sub-second latency.

Performance & Implementation Checklist

• Locking Strategy: Ensure your migration worker uses "Row-level Locks" or "Index Scans" to find missing rows. A "Table Scan" will kill the production database's performance.
• Sequence Management: If your primary keys are from an Oracle SEQUENCE, ensure the new schema's sequence "Starts At" a value much higher than the current old sequence. You don't want a "Key Collision" on day 1.
• Network Throughput: Monitor the bandwidth between the old and new DB nodes. If you saturate the link, your "Sync" will lag, and your switchover will fail.
• Verification: Run a Checksum Comparison on 1% of random rows before flipping the switch. If even 1 row is different, the "Dual Write" logic is broken.

Conclusion

Zero-Downtime Migration is the "Holy Grail" of database architecture. It transforms a "High-Risk Night" into a "Boring Day." By mastering the Dual-Write and Historical Sync patterns, you build a data tier that is truly "Cloud-Native" – always evolving, always online, and always reliable. Migrate for the future, verify for the present, and never take the bank offline again.

Verified for Technical Nexus v1.9.0 | Part of the System Design Vault archive.