Drift, backfill and recovery: the unglamorous parts of sync
Copies of data quietly disagree, restarts lose their place, and schemas change under you. What drift is, how it happens, and how an honest sync service detects and repairs it.
Moving a change from one place to another is the easy part of sync. Anyone can write the first version in a week, and it will look fine in a demo. The work that fills the next two decades is everything that happens when the copy and the source stop agreeing and nobody has noticed yet.
What drift is
Drift is two copies of the same data that quietly disagree. The source database says an order has forty lines; the copy that reports read from says thirty-eight. Nothing crashed, no alert fired, and the report has been wrong for three weeks.
Drift does not announce itself. It is discovered by a person who happens to compare two numbers, usually at the worst moment, and by then the question is not how to fix it but how long it has been wrong.
How drift happens
- A change is missed: the sync service was down, or busy, when a row changed, and the signal that would have told it about that row has since expired.
- A change is applied twice or out of order: a retry over a bad link replays an old update on top of a newer one.
- A deletion is dropped: many systems only carry new rows and edits, so rows removed at the source live on forever in the copy.
- A restart loses its place: the service comes back up and either starts from the beginning or, worse, from an approximate point it guessed.
- The schema (the layout of the tables) changes: a column is added, renamed or given a new type at the source, and the copy keeps writing the old shape without complaint.
Every one of them is a normal Tuesday in a system with more than one site and more than one table.
Detecting it
The first job of a sync service is to notice. That starts with knowing where it is: each table keeps its own position, a marker for the last change it is certain the destination has. The mechanism that provides the marker depends on the engine. On SQL Server it is change tracking, a built-in feature that notes which rows changed, read on a short interval; on PostgreSQL it is logical replication, streamed; on MongoDB it is change streams, streamed.
Beyond position, the service has to check the copy against the source rather than trust that following changes was enough. A copy that was never compared with the truth is a copy you are hoping is right. Comparing is dull, and it is the part that turns a hope into a fact.
A sync that has never been checked against its source is a rumour with a schedule.
Repairing it
Three repairs cover almost everything, and the trick is picking the cheapest one that works.
- Resume from a known position. After a restart or an outage, each table picks up exactly where it stopped. This is the everyday case and it should cost nothing beyond the changes that piled up while the service was away.
- Reconcile on arrival. Every change is applied against what is already there, not appended blindly. A change that arrives twice is recognised and applied once; an edit replaces the row; a deletion removes it. Every row is stamped with the site it came from, so two sites cannot overwrite each other.
- Backfill the table. When the position is lost or the schema has changed under the copy, the honest move is to reload that table in full and then resume following changes from the point the reload began. This is the expensive repair, so it is done per table rather than for the whole site.
That last point matters more than it sounds. A site with hundreds of small tables and one enormous one should never reload the small ones because the big one had a problem. Position, reconciliation and backfill are all per table, so a repair stays as small as the fault.
Why at-least-once is the honest design
Every sync service has to pick a delivery promise. Over the public internet, with links that drop and acknowledgements (the replies confirming arrival) that get lost on the way back, a change will sometimes be sent twice. You can design the sender to never repeat itself, but then a lost acknowledgement means a lost change, which is worse.
So we send at least once and reconcile on arrival. Duplicates are expected and harmless because the destination knows how to apply the same change twice and end up with one result. It is a less impressive promise, and it is the one that holds up on a bad day.
Schema changes
Schemas move. A column gets added for a new regulation; a field gets renamed because the old name was embarrassing. A sync service that silently keeps writing the old shape is a drift machine.
In practice this means schema changes are part of running the service, not an incident. The copy is updated to the new shape, the affected table is backfilled if the change cannot be followed forward, and then ordinary following resumes. It is work someone does, and a person drives it; it is not a switch that flips itself.
Where redfly fits
The redfly sync service keeps Redis in step with a database beside it, and keeps a central store in step with sites over the public internet, using the same position tracking, reconciliation and per-table backfill described here. Drift, backfill and recovery are not features we added later; they are the problems our team has spent two decades on.