The counter behind a Postgres orders table is a 64-bit integer that owns no rows. It sits in a system catalog, advances on every attempted insert whether or not the transaction commits, and does not travel through logical replication. A team can copy four billion order rows into a distributed cluster with byte perfect fidelity, move traffic over in under two seconds, and then mint duplicate primary keys before lunch, because the sequence arrived at its destination as a fresh counter starting at one. Nothing in the row data flags this. It appears as a unique violation on the first insert of the new business day, inside a service that tested clean at cutover.
The maintenance window is not where migrations fail
Most of the planning energy in a database move goes into the window everybody negotiates, the eight to twelve hours of read only time, the Sunday night, the note to customer support. That window is a scheduling problem with a known price, and in 2026 it is largely optional. The part that isn't optional is the state of your data three weeks after go live, which is the portion of the migration nobody schedules a review for.
The mechanical shape of a modern move has four phases. Schema goes first, converted and tested against a copy. Then a bulk copy of the existing rows. Then continuous change capture, where a tool like Debezium reads the source's logical replication slot, decodes the write ahead log into ordered change events, parks them in Kafka, and a consumer applies them to the target. Then cutover, which should be measured in seconds. The ordering detail that sinks more projects than any other sits between phases two and three. You must record the starting position of the change stream before the bulk copy begins, not after it. Snapshot first and stream second, and every update committed during the copy gets overwritten by stale row data, with no error and no alert, just rows that are wrong in a way that looks plausible.
The alternative pattern, dual writes, is what teams reach for when they assume their application code is easier to change than their replication setup. Write to both databases on every request, keep them aligned by construction, and skip the log plumbing. It holds until one request fails halfway. One write lands, the other doesn't, the client retries, and one side now has two rows where the other has one. Without a two phase commit protocol, which most application stacks don't run and most teams quietly shouldn't, dual writes trade a controlled problem for an unbounded one. The safer architecture is one authoritative write and a derived stream, so the second copy of the data is always a consequence of the first rather than a parallel promise to keep up.
Drift has a taxonomy, and none of it is random
Data drift sounds like entropy, but it clusters into a short list of causes you can enumerate before you start. The first is state that lives outside rows, meaning sequences, identity counters and the top of a key range. The second is invariants a single node enforced for free, meaning unique indexes, exclusion constraints, foreign keys checked at commit, and check constraints involving subqueries. The third is values the engine produced on your behalf, meaning `now()` defaults, trigger populated audit columns, generated columns, and sort orders that depended on the database's collation and encoding settings. The fourth is application semantics, particularly transactions that bundled three business actions into one commit because they all lived in one place.
Each of those breaks differently once data is spread across nodes, and each needs a different check. A practical pre cutover list looks like this:
- Diff every sequence and identity counter against the target's next value, then bump the target above the source's high water mark manually rather than trusting a schema conversion tool.
- Rebuild unique constraints as explicit indexes or global lookups, and test the contention case with concurrent writes to the same key.
- For every foreign key, verify that the row copy order or the constraint's deferral actually holds across partitions.
- Compare sort order for text columns between source and target collations, not just the stored values.
- Replay one hour of production writes against both systems and diff the outputs rather than comparing row counts.
| Drift class | Where it hides | Cheap detection |
|---|---|---|
| Key counters | Sequences, identity columns, shard high water marks | Insert probe after cutover, compare next value to source max |
| Unique invariants | Local B-trees that became cross node lookups | Concurrency test on one hot key |
| Engine generated values | `now()`, triggers, generated columns, session timezone | Replay an hour of writes, diff the outputs |
| Transaction boundaries | Commits that covered several business actions | Trace one request end to end in both systems |
| Type and collation | Decimals, character padding, text ordering | Ordered hash per column, not just per row |
| Reverse path | Schema changes made after cutover | Rehearse a rollback while live writes still arrive |
A checksum over whole tables catches none of the first four. It compares what landed, and what landed is usually correct. What's missing is the behavior of the system that produced the rows.
The constraint that stops being a constraint
Consider the unique index on a customer email address. On a single Postgres primary, that constraint is a lookup in a local B-tree inside a transaction that already holds the row locks. On a distributed cluster, enforcing the same address globally means either routing every write for that key through a consensus group or maintaining a global index that has to agree across regions. The insert gets more expensive, and the price shows up unevenly, because writes touching the same key serialize while writes touching different keys run in parallel. A migration that changes the cost profile of a constraint without changing the application's retry behavior is a migration that will page someone.
The isolation default matters more than teams expect. CockroachDB ships serializable isolation as the default, so write conflicts surface as retryable errors the application has to handle. YugabyteDB defaults to read committed or snapshot, which cuts retries at the cost of weaker guarantees. Spanner offers external consistency on Google Cloud only. Amazon Aurora DSQL uses optimistic concurrency control and detects collisions across regions, returning errors for the client to retry. In every case, code paths that treated a serialization failure as something that happens to other people get exercised for the first time under production load, and that is frequently the real cutover failure.
| Engine | Default isolation | Where it can run | Migration note |
|---|---|---|---|
| CockroachDB | Serializable | Any cloud, on premises | Retry logic becomes load bearing on day one |
| YugabyteDB | Read committed or snapshot | Any cloud, on premises | Fewer retries, weaker cross row guarantees |
| Google Spanner | External consistency | Google Cloud | Multi region latency tied to Google's network |
| Amazon Aurora DSQL | Optimistic concurrency control | AWS, multi region within a continent | Retries on collision, no cross continent clusters yet |
The last row is the detail that changes architecture decisions. AWS extended Aurora DSQL multi region clusters to Stockholm, Spain, Mumbai and Singapore on July 31, 2026, and cross continent clusters are still not supported. If your residency plan assumes European and Indian customer data sitting in one logical database, the multi region label stops at the coastline.
Copy time sets your drift budget
Here is the number that rarely makes the slide deck. An 800 GB database copying at a sustained 60 MB/s over a replication link takes about 3.7 hours. If the source emits 1,800 row changes per second, those 3.7 hours generate roughly 24 million changes that the capture pipeline must replay before it catches up. Your change capture layer is therefore not a background sync. It's a queue that has to outrun the production write rate, and the cutover window is set by the gap between those two speeds rather than by the size of the copy.
Run the second half of that calculation before you pick a weekend. If the target applies changes at 1.4 times the source's write rate, a 24 million event backlog drains in roughly nine hours of steady traffic. If it applies at 1.05 times, the backlog never fully clears during business hours, so the migration drags on for weeks and you never get a clean window at all. Apply throughput has to be measured under realistic conflict rates rather than in a benchmark built on synthetic sequential keys.
The monitoring follows from the same math. Track replication lag in bytes and in seconds, track apply rate, and track conflict and retry rates separately. Lag in seconds looks calm during a burst because the timestamp of the newest applied event can be fresh while a long tail of older changes sits in the queue. Byte offsets and event counts don't lie about that.
The comparison job is the deliverable
Validation that catches drift isn't a row count. Counts match when rows are present and wrong. Partition the tables into chunks of a few hundred thousand rows, hash an ordered column list per chunk on both sides, and compare chunk by chunk, which tells you where divergence lives instead of merely that it exists. Then add a second layer by routing a small slice of production read traffic to both systems for a week and logging the differences. Checksums verify stored data, and shadow reads verify the answers the database gives, which is where collation, rounding and timezone drift actually surface.
Rollback deserves the same engineering. Reversibility isn't a backup, it's a schema discipline. Expand the target with new columns and structures, keep the old ones writable, and contract only after a month of clean comparison. Keep the reverse replication path warm rather than theoretical, because a rollback executed under pressure is itself a migration with the same drift risks.
Licensing feeds the same decision. CockroachDB's move to a source available licence changed the calculus for teams that had assumed open source permanence, while YugabyteDB keeps an Apache 2.0 core, a split that echoes the wider fight over proprietary moats in infrastructure software. The choice between engines now turns on governance and staffing more than throughput claims.
What separates the migrations that hold from the ones that leak is rarely tool selection. It's whether the team accepts that for some number of weeks they own two live systems, a reconciliation job and a rollback path at the same time they own the product. Teams that treat the work as a project with a launch date produce drift, because the project ends and the drift doesn't. Teams that treat it as a change in the data ownership contract, with a permanent comparison service and one named authority per table, don't need a launch date at all. The unresolved commercial question is who keeps paying for that comparison service once the migration deck stops being interesting to the people who funded it.
Strategic Takeaways
- ✓A zero downtime migration is a replication problem you can rehearse, not a maintenance window you negotiate with the business.
- ✓Drift rarely comes from the rows you copied, it comes from counters, constraints, clock defaults and transaction boundaries that lived outside the table.
- ✓Budget for the reconcile loop rather than the cutover, because if the comparison job stops after go live the migration was never finished.

Anthony Dami
Technology & Culture writer at Largern, focusing on AI, consumer hardware, film, internet culture, and the routines and tools behind everyday life.
Comments (0)
No comments yet
Be the first reader to share your perspective on this story.



