Zero-downtime Data Migration
A zero downtime data migration moves live records to a new database while apps stay online. Teams do this by copying past data first and streaming new changes until cutover. During this work, engineers often use shared admin roles to set up sync jobs. Broad permissions make it easy for one bad query to break live tables. How a zero downtime data migration works in stages Every live move follows four clear steps to keep the app running. First, the team sets up the new database and applies the
Zero Trust Architecture + Post-Quantum Migration Planning: the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
A zero downtime data migration moves live records to a new database while apps stay online. Teams do this by copying past data first and streaming new changes until cutover. During this work, engineers often use shared admin roles to set up sync jobs. Broad permissions make it easy for one bad query to break live tables.
How a zero downtime data migration works in stages
Every live move follows four clear steps to keep the app running. First, the team sets up the new database and applies the base schema. The old database still takes all read and write traffic from the app. Second, the team starts a backfill job to copy existing rows over.
While the backfill runs, users keep changing data in the old system. To catch these updates, engineers turn on change data capture or dual writes. Change data capture reads the database transaction log and sends new events forward. This keeps the new database in sync with the old one within seconds.
Third, the team runs checks to compare row counts and sample values. Engineers query both sides to confirm the new tables match the source records. If a check finds a mismatch, the team fixes the sync logic and rechecks. Fourth, the team flips the app to read and write on the new database.
Managing schema changes with expand and contract
Moving data often happens alongside table structure changes in the same release. If you rename a column all at once, the running app will crash. Instead, teams use the expand and contract pattern to update schemas in steps. In the expand phase, you add the new column without dropping the old one.
Next, you update the app code to write to both columns at once. A background script fills in the missing values for older rows in batches. Small batches stop the script from locking tables or slowing down user requests. Once every row has the new data, the app reads from the new column.
Finally, you start the contract phase after the new code runs without errors. You remove the old write paths from the app and drop the old column. Keeping the old column around for a few days gives you an easy rollback. If the new setup fails, you can point the app back right away.
Zero Trust Architecture + Post-Quantum Migration Planning: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native migration roles solve and what they miss
Native database roles let you grant read and replication rights to sync tools. You can create a dedicated user that only reads the source transaction logs. You can also set up a separate role for the target database writes. These controls decide who can log in and which tables they can touch.
Still, native roles do not check what individual queries do during an active session. During a migration, engineers log in to both databases to check rows and fix drift. A tired engineer might run a drop or update command on the wrong host. Standard roles allow any valid command once the user is inside the database.
Also, validation scripts often pull real customer records to compare fields across both systems. Those scripts can expose emails, phone numbers, card numbers, or national IDs in logs. Native roles cannot rewrite matched fields in query results on the fly. They also fail to tie shared role actions back to a specific person.
Govern statements and responses across databases and APIs
Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.
First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.
Frequently asked questions
How do you roll back a cutover if the new database fails?
Keep reverse sync running from the new database back to the old one. If errors spike after cutover, point the app back to the original database. Because reverse sync copied the latest writes, you will not lose new user data.
Should we use dual writes or change data capture for syncing?
Change data capture is safer because it reads from the database log directly. Dual writes inside app code can fail half way if one database times out. That leaves your two databases out of sync until you run a repair script.
How do you avoid locking production tables during the initial backfill?
Read from a read replica instead of the primary database whenever you can. If you must read from the primary, copy rows in small chunks by key. Add short pauses between batches so regular app queries get enough disk and CPU.