Two billion rows cannot be eyeballed, and a green dashboard is not evidence. Verification is where a migration either earns the right to delete the old system or quietly accumulates wrong rows that surface as customer tickets six months later. This chapter treats verification as an engineering system in its own right: an equivalence function that is really the migration's spec, chunked hashing that runs where the data lives, comparisons that stay honest while both sides keep moving, sampling that knows what it cannot prove, invariants for when rows do not line up, a reconciliation report whose most important number is not the one people watch, and the evidence file that justifies the one step you can never undo.
Nobody compares bytes across two different engines. You compare canonical forms under an equivalence function: decode each side, map it into a common typed representation, then compare. Teams usually write that function late, in a hurry, as a helper inside the diff job. That is a mistake of category. The equivalence function is the only place where every implicit behaviour of the old system is forced into an explicit rule, and so it is the most accurate specification of the migration that will ever exist. Most "verification found a bug" moments are really "the equivalence function found an undocumented behaviour".
The function can fail in two directions, and they fail differently. Too strict and it floods the report with representation noise; the team learns to ignore the report within a fortnight, and after that the verifier might as well not exist. Too lax and it launders real bugs. The laundering is the dangerous one because it looks like progress. Somebody trims trailing whitespace to silence 40,000 noisy diffs from a MySQL source whose pre-8.0 collations are PAD SPACE, where 'acme ' and 'acme' compare equal. The diffs disappear. So does the fact that the new Postgres unique index on login name now admits both values as distinct rows, so the old system's single account can become two in the new one. The rule did not fix a representation difference. It hid a behaviour change.
A representation-only rule makes two encodings of the same fact compare equal. A semantic rule hides a case where users would see different behaviour. Semantic rules need a named approver, because each one is a product change that the verifier has been told to stop reporting.
So classify every canonicalization rule as either representation-only (binary floats compared by bit pattern rather than decimal text, NFC-normalizing strings that are semantically identical) or semantic (anything where a user, a query or a constraint would behave differently). Semantic rules go through review with a named approver, exactly like a product change, because that is what they are. Two more construction rules save weeks:
CONCAT_WS row hash skips NULLs, so (x, NULL, y) and (x, y, NULL) hash identically; plain concatenation without separators makes ('ab', 'c') equal ('a', 'bc'). Use a typed, length-prefixed encoding with an explicit null marker per column. Mature tools do this; home-grown diff jobs frequently do not.When the schema changes shape (one table split into three, a document store flattened into relations), the function compares projections. Write both directions: old projected forward into the new shape, and new projected back into the old shape. The reverse projection is not extra work. It is the same code that reverse replication needs for rollback, which chapter 06, Cutover, Point of No Return & Rollback, depends on. If the reverse projection cannot be written, the migration is lossy, and you want to know that before the cutover rather than during a rollback.
Pitfall · the time value that changes date
DATETIME(0) written from 23:59:59.7 stores the next day. If the new store keeps microseconds, the two sides disagree on the calendar date of that row, and so on every partition key, daily aggregate and "orders placed today" query derived from it. The rule cannot be "truncate both sides to seconds"; it has to reproduce the old rounding, and someone has to decide which answer the business treats as correct.Start with the cost. A 20 TB table pulled through a comparator at a throttled 200 MB/s per side takes about 28 hours per side, moves 40 TB across the network and competes with production for buffer cache the whole time. Push the hashing into each database instead and only chunk digests cross the wire: 2 billion rows in 1,000-row chunks is 2 million digests, tens of megabytes. That is the argument for push-down, and it is decisive for the first full sweep.
The catch is that push-down means implementing the canonicalizer twice, once in each engine's SQL dialect, and those two implementations will disagree precisely on the edge cases from 5.1 (float formatting, collation, time precision). The pragmatic split is push-down for the sweep, pull-and-canonicalize in one process for the drill-down. When the mismatched-chunk list is dominated by chunks where the pulled comparison finds nothing wrong, you have a canonicalizer disagreement, not data divergence, and you fix that before trusting any other number.
sweep ≠ · pull ≠
real divergence
Both canonicalizers agree the data differs. Row-diff the chunk, file by signature.
sweep ≠ · pull =
dialect disagreement
The two SQL canonicalizers differ (float text, collation, precision). Fix before trusting any other count.
sweep = · pull ≠
blind sweep
Push-down hides a difference: shared-lossy cast, CONCAT_WS NULL skip, truncating CAST. The dangerous quadrant.
sweep = · pull =
clean
Expected steady state. Audit-sample 1 in N clean chunks by pull to keep the bottom-left quadrant honest.
The top-right share is a health metric for the verifier itself: above a few percent of mismatched chunks, the sweep is measuring its own dialect bugs. The bottom-left can only be found by auditing clean chunks; illustratively, pulling 1 in 1,000 clean chunks costs 0.1% of a full pull and bounds a systematic blind spot quickly, because such bugs affect every chunk that holds the value class.
The sweep is itself a production workload, and its failure mode is not load. Teams throttle the sweep's I/O and then take the database down another way: by running each table's checksum inside one long consistent snapshot so the numbers are "coherent". On Postgres a snapshot held for the 28 hours above pins the xmin horizon, so vacuum cannot reclaim any tuple that dies during those hours on any table in the database. At 40,000 updates/s (illustrative) that is about 4 billion dead tuples retained, and the symptoms (bloat, index scans slowing, then wraparound warnings on a busy cluster) appear on tables the verifier never touched, so on-call looks everywhere but the verifier. On InnoDB the same snapshot holds back purge and the history list length grows until every read of a hot row walks a long undo chain. The rule: one short transaction per chunk, a statement timeout on every verifier query, and a verifier role that the database's idle-transaction and long-query killers are allowed to kill. Per-chunk snapshots mean the sweep is not globally consistent, and that is fine, because 5.3 has to handle the moving target anyway. If the sweep runs on a replica instead, check the replica's feedback settings: hot_standby_feedback sends the replica's xmin to the primary and brings the same bloat back.
Chunk by key range, never by offset. This sounds obvious until someone implements chunks as ORDER BY id LIMIT 1000 OFFSET n because the ids are sparse. One missing row shifts every subsequent chunk by one, every later chunk mismatches, and the drill-down re-reads the remainder of the table. Worse, the boundaries of a key range must mean the same thing on both sides. On string keys they often do not: a range predicate evaluated under a binary collation and under a case-insensitive collation selects different rows. Chunk on a numeric surrogate if one exists on both sides, otherwise on hash(key) mod N computed with the same function on both sides.
one row missing from chunk 3 in new
Offset chunks shift by one row after the gap, so every later chunk mismatches and the drill-down re-reads the rest of the table. Key-range chunks isolate the defect.
string keys across engines
Range ['M', 'a') under a binary collation contains 'Zeta'; under a case-insensitive one the range is empty, because 'M' sorts after 'a'. Same predicate, different rows. Chunk on a numeric surrogate or on hash(key) mod N, computed identically on both sides.
drill-down, fan-out 64 (2e9 rows)
With k = 300 scattered bad rows: 64 + 2 × 64 × 300 + 8 × 300 ≈ 41k hash comparisons and 300k rows re-read, instead of 2 billion rows pulled over the wire twice.
Make the comparison symmetric. A very common verifier bug is a loop that iterates the old side and looks each key up in the new side. It never sees rows that exist only in new: resurrected deletes, duplicates from a retried batch, rows written by a code path that only writes to new. Deletes are the defect class verifiers most reliably miss, because CDC snapshots, soft-delete flags and TTLs all treat deletion differently. Every comparison is a full outer join on the key, and "present in new, absent in old" is a first-class mismatch category.
Choose the chunk aggregate for migration defects, not replica drift. The classic MySQL checksum shape, popularized by pt-table-checksum, stores a COUNT and a BIT_XOR of per-row checksums per chunk. It was built to catch replica drift, where divergence is effectively random. Migration defects are structured. XOR sees only the parity of each row's multiplicity: a row written three times by two non-idempotent retries has the same XOR as the row written once, so COUNT is the only detector left. When the old side is uniquely keyed and the key is inside the hashed bytes, COUNT is enough, because every multiplicity starts at one and can only rise. The trouble is how often a migration compares rows that are not uniquely keyed: child rows compared by content (two identical line items on one order are legitimate), projections that drop the surrogate key because the two sides assign different ones, legacy tables with no primary key. There, a lost pair of identical line items plus a duplicated pair elsewhere in the same chunk leaves both COUNT and XOR unchanged. An additive multiset hash (the sum of 64-bit row hashes modulo 2⁶⁴, plus COUNT) sees multiplicity itself rather than its parity, costs the same, and updates incrementally from a CDC event by subtracting the old row's hash and adding the new one. There is no reason to accept the weaker aggregate in new code. And do not reuse a 32-bit CRC as the row hash: it is linear over XOR, so structured bit-level changes can cancel across rows, and it leaves too little margin for a verifier whose only output is "equal".
XOR plus COUNT is the classic MySQL checksum shape, and it is fine for replica drift, where defects are random bit-level divergence. Migration defects are not random: non-idempotent retries change multiplicities, and XOR sees only the parity of each row's multiplicity. Use an additive multiset hash with a 64-bit or wider row hash.
That incremental property is what makes continuous verification affordable. Keep additive leaf digests per key range, arrange them in a tree (the anti-entropy structure the Dynamo paper described and Cassandra's repair uses), and update leaves from both sides' change streams. Re-verification becomes a comparison of roots, and a mismatch is localized in log-fan-out steps rather than by rescanning. The first sweep costs a full scan per side. Every later check costs close to nothing, which matters because the verification you need most is the one after cutover, when writes flow only through code nobody has watched in production before.
~28 h
20 TB pulled at 200 MB/s, per side
~2M
digests for 2e9 rows / 1k chunks
4 levels
fan-out 64 over 2M leaves
even count
what XOR cannot see
The verifier reads old at one instant and new at another, and between them sits a replication pipe with lag. Any row written in that window compares as a mismatch that is not one. How many? Take 2 billion rows and 40,000 writes/s (illustrative). If writes were uniform, a 1,000-row chunk would receive 0.02 writes/s; with a 3-second window (2 s of p99 lag plus 1 s of read skew) about 6% of chunks false-mismatch on each pass. That is survivable: re-check them after the lag and most go quiet.
Writes are not uniform. Suppose the newest 1% of rows take 80% of writes, a typical shape for anything order- or event-like. Those chunks receive about 1.6 writes/s each, and the chance one of them is touched inside a 3-second window is around 99%. On every pass, the hot chunks mismatch. The naive fix is "re-check until stable, exclude what never stabilizes", and it has a nasty property: the rows it quietly excludes are the most recently written and most frequently read, which are exactly the rows that most customers care about and that the newest code paths produce.
Four techniques, in rising order of cost and certainty:
recovery_target_lsn with a pause action; MySQL START REPLICA UNTIL a GTID set), stop a clone of the target's applier at the same position, and compare the two frozen copies. Zero false positives, every hot row included. It costs a dedicated replica and a target clone, which is why it is the final exam before an irreversible step rather than the daily check.The frozen-point technique also explains why pt-table-checksum works so well for its original job. It runs the checksum query through statement-based replication, so each replica computes the chunk digest over its own data at the same logical point in the stream. That trick relies on both sides executing the same statement, which is exactly what a cross-engine migration cannot do; you rebuild it with explicit log positions instead.
Architect's note · report the exclusion set
The sampling-versus-full debate usually gets framed as cost against confidence. The sharper framing is that they answer different questions. Sampling tells you whether a defect of some rate exists. Full comparison tells you which rows are wrong. For a migration, the list is almost always what you need, because every wrong row in a billing, identity or permissions table is a specific customer, and knowing that "about 0.0001% of rows diverge" leaves you unable to fix any of them. Chapter 03 applied the rule of three to shadow traffic; the arithmetic is the same here, and it is sobering. To detect with 95% probability a defect affecting 2,000 rows out of 2 billion (rate 1e-6) you need about 3 million uniformly sampled rows.
Uniformly is doing a lot of work in that sentence. Migration defects cluster by provenance, not at random. They cluster in rows written before a 2019 schema change, rows imported from an acquisition, rows written by one legacy service, rows for tenants with custom fields. Provenance correlates with insertion time, and insertion time correlates with physical location on disk. So a sampler that selects blocks rather than rows inherits the clustering. Postgres TABLESAMPLE SYSTEM picks whole pages; BERNOULLI picks rows but has to scan the full table to do it. With 100 rows per page, a 100,000-row page sample touches only 1,000 pages and has about a 0.1% chance of hitting a defect confined to 20 adjacent pages. A row sample of the same size has about 9.5%. Same sample size, a hundredfold difference in evidence.
The fix is not a larger sample but a smarter one: stratify by provenance. If the defect lives only in the pre-2019 stratum (1% of rows) at a rate of 1e-4 within it, 30,000 rows sampled from that stratum detect it with 95% probability, a hundred times fewer than uniform sampling needs. That requires knowing which provenance boundaries exist, and the best source is the old system's own history: schema migrations, writer services, import jobs, the dates of past incidents and data fixes.
When sampling is enough. For data that can be rebuilt from an authoritative source (search indexes, caches, analytical copies), for early smoke checks while a backfill is still running, and for continuous low-cost monitoring after a full pass has established a clean baseline. When it is not. Anything with money, identity, permissions or legal retention, and any step you cannot undo. The full comparison is also usually cheaper than people fear: it is one scan per side, once, plus an incremental tree afterwards, and it is weighed against the cost of a single customer-visible wrong balance found by the customer.
Pitfall · verify-on-write is not verification
Row-level comparison needs a one-to-one mapping between old and new rows. Many of the migrations worth doing destroy that mapping on purpose: a mutable balances table becomes an append-only ledger, a denormalized document becomes five normalized tables, a relational model becomes an event stream. Then you verify through invariants: properties that must hold on both sides regardless of shape.
Conservation. Some quantity must be equal when summed over a partition that both sides can compute: total balance per currency per account class per day, number of line items per order, number of active entitlements per tenant. Partition finely. A global sum across 2 billion rows can match with two errors that cancel; a sum per account per day localizes an error to the rows that can explain it. For money, the double-entry invariants of the Money & Ledger Systems track are the strongest check available, because the new ledger has to balance independently of anything in the old system.
Two mechanics decide whether a conservation check produces signal or noise. First, sum in exact arithmetic. If either side stores amounts as binary floats, a SUM depends on evaluation order, and two engines (or one engine across a parallel and a serial plan) will produce totals that differ in the last digits on every partition. Teams then add a tolerance, and a tolerance of one cent per partition across a million account-days is a ten-thousand-dollar hole the check can no longer see. Convert to integer minor units or a DECIMAL of fixed scale before summing, on both sides, and compare exactly. Second, agree on the partition boundary before comparing the partition. "Per account per day" means nothing until both sides assign each event to the same day: the same time zone for the day boundary, the same rounding from 5.1, the same answer for a transaction whose authorization and settlement fall on different days. A per-day sum that mismatches on day D by +x and on day D+1 by −x is almost always a boundary rule, not lost money, and the checker should detect that pattern automatically (pairwise adjacent residuals that cancel) and classify it as a canonicalizer defect rather than paging someone.
Distributions as signatures. Histograms of lengths, values and timestamps per column per tenant are cheap, and specific bugs leave recognizable shapes. A spike of string lengths at exactly the new column's limit is truncation. The whole timestamp histogram shifted by a whole number of hours is a time-zone conversion applied once too often or not at all. A new mode at a single value that did not exist in old is a default leaking in where NULL used to be. None of these needs a row mapping, and all of them show up in minutes.
Run the bottom rungs continuously (they are nearly free), the middle rungs per slice and nightly, and the top rungs as the evidence for irreversible steps. No single rung is sufficient; the blind-to column of each is covered by some other rung.
Semantic replay. The top rung compares what users would see, not what is stored. Recompute a month of invoices, permission decisions or search results from both stores and diff the outputs. This catches the defects that every storage-level check passes: two stores holding identical data that the new code interprets differently. It is expensive and its coverage is limited to the outputs you choose to replay, which is why it sits on top of, not instead of, the rungs below.
Every invariant check on real data meets the same surprise: the old system already violates it. Orphaned rows, duplicated rows, balances that do not sum, from a decade of hand-run data fixes and incidents. The naive response is to call the check broken and turn it off. The right one is to capture the old system's violations as a baseline and verify that the new system's violation set equals it, row for row. The new system should inherit exactly the old corruption, no more and no less, until someone decides to repair it as a separate, announced change, for the same reason chapter 03 gave for reproducing the old system's bugs during shadowing.
Verify the state that is not rows. Every check above compares data. The incidents in the first hour after cutover usually come from state that no row comparison can see, and they fail in a predictable order:
next value on new > max(id on old) + headroom, where the headroom covers the writes that land between the check and the flip. Run it inside the cutover runbook, not the day before.NOT VALID, or run with session_replication_role = replica to skip triggers, which also skips Postgres's foreign-key enforcement triggers. Both are sound for speed. Both leave the catalog saying a constraint exists over rows it never checked. Assert that every constraint in the target catalog is validated, and diff the set of constraints, triggers and check expressions against a list derived from the old system's schema, not against the new system's own migration files.A verifier produces rows; a reconciliation report produces decisions. Build it as a product with a user (the migration lead and, eventually, whoever signs the deletion), a daily cadence and a fixed taxonomy. Every mismatch lands in exactly one class: lag (resolved on recheck), canonicalizer (the rule is wrong, not the data), known defect (bug filed, fix pending), inherited (matches the old system's baseline violations), accepted (a named owner has approved the difference, with a rationale and an expiry date) and unexplained. The migration is not verified until unexplained is zero and stays zero.
But the headline number is the wrong one to watch. A backlog of unexplained mismatches is the sum of two different things: history (rows that diverged before a bug was fixed) and live divergence (rows diverging now). History falls to zero with repair work. Live divergence does not, and it is fully hidden by a falling backlog.
On day 18 both teams can report the backlog down 90% or more. Only the green one is converging. Track arrivals by write date: any mismatch on a row written after the last fix means a live path is still diverging, and no amount of backlog repair will finish the job.
So the report leads with arrivals by write date: mismatches on rows whose last write happened after the most recent fix was deployed. Any nonzero arrival rate means some write path is still producing divergence, and it is the only number that predicts whether the backlog can ever reach zero. Next to it: the age of the oldest unclassified mismatch (an ageing unexplained row is an investigation nobody owns), the exclusion set from 5.3, and coverage, meaning the fraction of rows compared at least once in the past N days.
Computing arrivals needs a per-row "last written at" that both sides agree on. Old systems often have no trustworthy updated_at (application-maintained, skipped by bulk fixes, absent on half the tables), so do not use it. Use the source commit position carried with the row version from 5.3: the mismatch is an arrival if that position is later than the log position at which the last fix was deployed. That also makes the rule precise at the boundary, where a wall-clock comparison would misclassify the rows written during the deploy itself.
The unit of triage is the signature, not the row. Thirty-eight thousand unexplained rows are not thirty-eight thousand investigations. Group mismatches by a signature: table, the set of fields that differ, the shape of the difference (old value NULL and new value empty string; new timestamp exactly 3,600 s later; row present only in new), and, where it can be recovered, the writer service or backfill range. In practice a large backlog usually collapses into a few dozen signatures, each one a single bug, and the report's real backlog is that list. Two corollaries. A new signature appearing is a more urgent event than any change in row counts, because it is a new bug. And a signature containing a single row is often the most interesting one on the page: it is either a hand-edited row in old, which belongs in the inherited class, or a race that fires rarely and will fire again at scale after cutover.
Repair through the pipeline, never by hand. A wrong row in new should be fixed by re-emitting the authoritative row from old through the same replication path (touch the row, re-publish its CDC event, or send the key range back through the backfill's range re-run command from 4.4), not by an UPDATE on new. A hand patch fixes the symptom and destroys the evidence: the next verification pass says "match", and the bug that caused the divergence is still in the apply path, ready to produce the next one.
Architect's note · verify the verifier
Every other step in a migration can be undone at some cost. Deleting the old system cannot, and chapter 01 showed that almost all of the migration's savings arrive only at decommission, so the pressure to delete is real and justified. The way to resist the pressure without stalling is to make the decision a signature on an evidence file rather than a feeling in a meeting. The file has specific contents:
A full sweep during the backfill measures the copier's progress, not correctness. Post-cutover, the incremental tree runs on the reverse stream (new → old) for as long as rollback is possible, because that is when new-only code paths first write production data.
How long to dwell. The observation window is set by the rarest write path, not by a round number. List the writers that run on a calendar (daily batch, month-end close, quarterly reporting, annual renewals, tax year-end) and ask which have executed at least once against the new system with zero arrivals afterwards. A useful way to size the remaining risk: if a path runs once a year and has not run yet, the window has zero evidence for it however many clean days it shows. The options are to wait, to force a run of that job against production data in a non-committing mode and diff its outputs (semantic replay again), or to accept the risk in writing. The third is legitimate. Leaving it unstated is not.
Then walk down the ladder, one irreversibility step at a time, and dwell at each until its evidence arrives. The step people skip is revoking reads. Revoking the old system's read credentials before stopping it is how the undiscovered consumers (the quarterly finance query, the partner export, the notebook reading a replica) announce themselves as authentication failures, which are cheap to undo, instead of as an outage, which is not.
step 1
writes revoked
old is read-only; reverse replication off
gate
zero write attempts from any principal
undo cost
minutes
step 2
reads revoked
credentials rotated, not reissued
gate
zero auth failures from unknown callers through a month-end
undo cost
minutes
step 3
compute stopped
instances down, volumes retained
gate
no pages, no tickets, no finance questions
undo cost
hours
step 4
archived + restore-tested
open format + native dump in object storage
gate
restored into a scratch env and queried
undo cost
hours to days
step 5
deleted
volumes, snapshots, replicas, backups
gate
named owner signs the evidence file
undo cost
impossible
Revoking reads is the step that finds the callers the inventory missed, and it costs a credential rotation to undo. Stopping compute before that turns an unknown reader into an outage. Deleting before a restore test means the archive is a hope, not a fallback.
The archive costs almost nothing; skipping it can cost a lot. A 20 TB database might compress to 4–5 TB. At standard object-storage list prices, on the order of $20–25 per TB-month, that is roughly a thousand dollars a year, and a small fraction of that on deep-archive tiers (illustrative; check your provider). Against that sits the day a regulator, an auditor or a litigation hold asks what a record said in 2023. Two details decide whether the archive is usable. Store it in an open format (Parquet or CSV plus a schema) alongside the native dump, because in five years nobody will have the old engine version at hand. And archive the equivalence function and its version with the data: it is the only documentation of how old values map to new ones.
The archive has a second-order cost that the storage bill hides. It is a full copy of every record the old system held, including personal data, and it is now outside every pipeline that enforces retention and erasure. When a GDPR erasure request or a retention-period expiry deletes a customer from the new system, the archive still holds them. Either the archive has a documented retention period and a legal basis that the privacy owner has signed off, or erasure has to reach it, which in practice means encrypting it per tenant or per partition so that a record can be made unrecoverable by destroying a key rather than rewriting a Parquet file. Decide this before writing the archive; retrofitting crypto-shredding onto a 5 TB blob is a migration of its own.
0
unexplained & zero arrivals
≥ 1
month-end (quarter-end if finance)
daily
seeded canaries caught
restored
archive, not just written
Finally, sign it. Deletion is a risk acceptance, and like every risk acceptance it needs one named owner who has read the evidence file and accepts what it does not cover. That is not bureaucracy. The alternative is a deletion that happened because a cost dashboard turned red, performed by someone who assumed somebody else had checked.
Questions to ask in a design review
updated_at?Where next