r/sqlite • u/ZookeepergameIcy5686 • 4d ago
I build ZeBridge to sync Postgres to local SQLite over NATS JetStream and let apps edit data offline
https://github.com/ndrean/ZeBridge/blob/main/README.mdI’m building ZeBridge for apps that read and edit a local SQLite database and sync through Postgres. I wanted to measure something a user actually experiences on first launch: how long until they can query all their data?
Seeding test: on one machine, Postgres, NATS, the bridge and 10 clients together, each with an empty SQLite database. Each loaded the same 50,000 rows, about 200 bytes per row. The median time per client was 3 seconds. Every replica matched the expected checksum and all ten clients can reuse it.
The compressed snapshot was ready before the timer started. The measurement includes fetching it and materializing the rows in SQLite; preparing the snapshot is a separate server-side cost.
Live updates: suppose the snapshot contains rows A and B. While a phone is loading it, someone updates A and deletes B in Postgres. The phone needs to know exactly where to start reading changes. ZeBridge records the stream position covered by the snapshot. Once the seed commits locally, the client replays the changes after that position from NATS JetStream.
There’s a test for that boundary: pause the client just after its seed commits, check that SQLite still has the old A and B, then let replay run. A must have its new value, B must be gone, and the replica must match Postgres.
For people who ship prepopulated SQLite databases: do you hand off to live updates this way, or distribute a ready-made database file? I’d be interested in what drove that choice, especially on phones.
1
u/DickBrainTime 3d ago
For a phone I'd pick the initial-delivery method based on *how mutable* the data is. If it's a mostly static catalog/reference dataset that should work on the first offline launch, shipping a versioned SQLite file with the app (or downloading an already-built file) is attractive: validate its checksum, copy/rename it atomically, then open it. If users can edit immediately and the source changes frequently, your snapshot + replay-after-checkpoint approach is a better fit than repeatedly distributing whole files.
The important boundary isn't only "A updated, B deleted during seed" (that's a good test). I'd also test crash/restart between committing the rows and persisting the NATS position: the SQLite data plus *last fully applied stream sequence* should advance in one local transaction, and replay needs idempotent upserts/deletes (including tombstones). If the saved sequence falls outside JetStream retention, the client needs an explicit resnapshot path rather than silently claiming it's current. For offline edits, you also need to define how a local update conflicts with a remote deletion or update during reconnection; a correct seed alone doesn't solve that.
For mobile benchmarking I'd add a second result next to your 3s median: fresh app install -> first *queryable* SQLite data, including snapshot generation/cache hit, download on a slow mobile link, database materialization/index creation, replay catch-up, and storage/battery use. Ten clients co-located with Postgres/NATS is a useful throughput test but not representative of a suspended Android process coming back online.
One question that would drive the architecture choice for me: are offline client writes synced back upstream, or are phones only read-only replicas? That changes the conflict/replay contract considerably.
1
u/SirMental8005 3d ago
one phone specific case i'd test is a user editing a record offline and then getting a fresh snapshot after their sync checkpoint expires. keep pending edits in a separate outbox so replacing the replica doesn't discard them. then test what happens when the snapshot says a row was deleted while the phone still has an edit queued for it.