Ten billion rows over the public internet: what we learned
We have kept more than ten billion rows in step across ordinary public internet links, not dedicated lines. Here is what held up, what did not, and what surprised us.
The number people ask about is the ten billion. The part that matters more is the link. Every one of those rows travelled over the same public internet that carries video calls and email: shared, congested at odd hours, dropping for a minute here and an afternoon there. Nothing leased, nothing dedicated. This is what that taught us.
Send only what changed
The first lesson is the oldest one. A full copy of a table, sent every night, is the wrong shape for a shared link. It is large, it repeats work already done, and it fails halfway through more often than you would expect.
So after one initial sync, only the rows that were added, changed or deleted leave the site. The sync service asks the database what moved since it last looked. On SQL Server that means 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. Traffic falls to a trickle that follows the real rate of work.
Compress, then encrypt, per site
Changes are queued on site, compressed, then encrypted before they go anywhere. The order matters: compression works by finding repeated patterns, and encrypted data has none, so compressing after encrypting achieves nothing.
Encryption is per site, with a separate key for each. That sounds like a small detail until one site has an incident and you need to be certain the others are untouched; a key for one site opens nothing from another, which is the property an auditor asks about first.
Assume the link is down
We stopped thinking of an outage as an exception early on. A link that is up most of the time is still down for hours across a month, and a warehouse does not pause because the internet did.
When the link drops, changes wait on site in a queue and go when it comes back. That queue is a holding area, not a read copy; applications at the site cannot read from it while cut off, and we do not pretend otherwise. What it gives you is the promise that nothing is lost.
An outage is not a failure of the design; it is one of the inputs.
Resume per table, not per site
When something restarts, the naive approach is to start the whole site over. On a site with hundreds of tables and one very large one, that is a disaster: you reload the small tables endlessly while waiting for the big one to finish.
Each table remembers its own position instead. After a restart, or after the link returns, every table picks up exactly where it stopped. A table that finished stays finished. The big table carries on from the last position the central store confirmed rather than from the beginning.
Reconcile on arrival
Delivery is at-least-once. Over a bad link, a change can be sent, arrive, and have its acknowledgement (the reply confirming it arrived) lost on the way back; the sender then sends it again. Promising that every change arrives one time and one time only is a promise you cannot keep over the public internet, and we do not make it.
Instead, the central store reconciles each change on arrival. Every row is stamped with the site it came from, and each site keeps its own space in the store, so a repeated change is recognised and applied once, and an edit or a deletion is carried through rather than dropped. The copy converges on the truth even when the transport was messy.
Hold back when the source is busy
The site database has a day job. It runs the warehouse, the depot, the plant. Sync is a guest, and a guest that hogs the machine during the afternoon peak will be switched off by whoever runs the site, and rightly so.
So the service holds back when the machines at the site are working hard, and catches up when they are not. Reports never run against the site database; they run against the central copy. That is the real benefit for the site: it sheds its reporting load and keeps its headroom for the peaks that matter.
What surprised us
Three things. First, the deletions. Everyone plans for new rows; edits and deletions are where copies drift apart quietly, and carrying them through correctly was more of the work than moving new rows ever was.
Second, silence. The hardest failure was never a loud one. It was a site that quietly stopped sending, and nobody noticed for a while. Detecting a silent site became as important as moving its data.
Third, how little the pipe mattered once the design was right. Sending only changes, compressed, meant the ordinary public internet was more than enough. The dedicated line we might once have asked for would have been paid for and barely used.
Where it landed
End to end, a change lands in the central store within roughly thirty seconds to two minutes. That is not instant, and we say so. For reporting across many sites, current to within a couple of minutes is what the business actually needed, and the store it lands in is low-cost document storage rather than a cloud relational database billed by the hour.
This is the machinery behind redfly Remote Sync: the same sync service that keeps Redis in step beside a database, pointed outward over the internet at a central store instead.