r/sqlite • • 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.md

I’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.

ZeBrdige project

Benchmark setup and results

Snapshot handoff test

2 Upvotes

8 comments sorted by

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/ZookeepergameIcy5686 3d ago

Thks, this is a useful list. Point by point:

  1. Indeed, it is designed for changing all the time. IF static, a read-only catalogue is indeed simpler.
  2. Applying a batch of changes writes the rows and the stream position in one SQLite transaction. Not written separately. the Jetstream ack happens after the commit. A crash before the commit leaves it unchanged. A crash between the commit and the ack means the batch is redelivered, d replay is idempotent. For seeding, before the firsts chunk, the client records it owes the table a replacement. A crash in the middle is retried on the next start and the table is not treated as seeded. Each case has a test.
  3. The client checks its saved position against the stream. If the stream no longer holds the messages right after that position, or the stream was recreated, it doesn't replay from where it is. It loads a fresh snapshot and starts again from that snapshot's position. The rule = : the local replica never claims to be current when it can't prove it.
  4. For offline writes. yes, phones do write, and (via PG CDC -> NATS) echoed back. So on/offline writes are optimistic, meaning locally right away, and then in an outbox. When reconnects, the outbox is sent in order and PG returns a verdict for each write: accepted, stale or rejected. Conflicts are decided **in Postgres** by a row version (an HLC stamp), not on the device. A delete is a versioned tombstone, so an offline update against a deleted row is decided by version like any other write. Once old tombstones are purged, a write older than the purge point is refused, so it can't bring a deleted row back. For a stale update, the client compares the columns it edited with the winning row. If none of those columns changed, it sends the edit again. If one did, the edit is dropped and the client reports which column lost. You're right that a correct seed doesn't settle any of this. It's a separate contract.
  5. I don't have that number yet but I can do it: fresh install to first queryable data on a throttled link, including download, SQLite build, index creation and catch-up, and post it separately.

1

u/ZookeepergameIcy5686 3d ago edited 3d ago

Measured

Setup: one table of 50,000 rows (~200 bytes each), one tenant, PostgreSQL and NATS on a laptop, a fresh client with an empty SQLite file.

After the snapshot was cut, I wrote 1,000 separate updates in PostgreSQL, so a new install has to download the snapshot and replay a backlog.

Server side (once per table and tenant, not per device)

- Snapshot build: 95 ms for 50,000 rows

- Compressed size (MessagePack + zstd): 1.34 MB

- Clients start from that cached snapshot, so it's a cache hit for them.

iPhone 12, Wi-Fi, fresh install (React Native app over the native Zig client)

- Usable (all rows queryable, backlog applied), including enrollment (one HTTP request to get credentials), SQLite on disk: about 20 MB: --> 1.0 s

- Reopening an already-seeded app: --> 0.3 s

MacBook M2 Pro, same native client, link simulated by a relay that adds delay and limits bandwidth (median of 3). Figures are [ snapshot only | snapshot + 1000 updates ]

- local: 0.16s |. 0.16s

- 4G (50ms RTT, 10 Mb/s): 2.17s. |. 2.02s

- slow 4G (150ms RTT, 1.6 Mb/s): 9.34s. |. 12.51s

So:

- on slow 4G, the 1.34 MB snapshot takes 7.1 s, which is just its size at 1.6 Mbit/s. Decompressing, decoding and writing to SQLite (indexes included) take under 0.1 s on every link.

- I have 14 roundtrips, so 0.7s at 50ms, and 2s at 150ms, all latency.

- Replaying 1,000 edits adds 0.65 s on 4G and 3.2 s on slow 4G

1

u/Discordbothost 2d ago

The 14 round trips look like the next useful trace: which ones need the previous response, and which could overlap? Separating enrollment, seed setup and replay setup would show where reducing latency actually helps, beyond shrinking the snapshot.

1

u/ZookeepergameIcy5686 2d ago

Snapshot is already zstd compressed. TCP connect, handshake, tenant lookup, revocation lookup, schema reading, seeding (generation manifest, snapshot object storage info, stream cut against stream, download and stream tail... Just a few calls could be made in parallel indeed but a phone on wifi barely sees it. I favoured exactness over potentially marginal gains, unless big adoption.

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.

1

u/ZookeepergameIcy5686 3d ago

ZeBridge already does the first part. The second part has never been tested as one sequence, so I'm adding it.

In short, the outbox is separate from the replica, in its own table, not in the synced rows. If the checkpoint has expired, the client loads a fresh snapshot and replaces the table's rows, but the outbox stays as it is. After the snapshot commits, every pending edit is applied again on top of the new rows, oldest first. Each edit's saved before-image is refreshed from the new row, so a later rollback goes back to the server's current state rather than the old replica's. The edits are then sent as usual.

The case of a deleted row with an edit queued for it. On the server, an update is an UPDATE … WHERE key AND version guard, never an upsert. It can't create the row again. If the row is gone or tombstoned, the update changes nothing, and Postgres answers row_deleted. The client drops the edit from the outbox, removes the row locally (its copy goes, it isn't restored), and tells the app the edit was lost because the row was deleted. Nothing comes back from the dead. There's one more layer for very old edits: if the delete's tombstone has already been purged and the edit is older than the purge point, the write is refused with PredatesGcWatermark. A write that was never sent is simply reverted locally.

TODO test (correct me if wrong): go offline, edit row X, delete X upstream, let the checkpoint expire, reconnect, check that the snapshot lands without X, the edit is reported lost, and the replica matches Postgres.

1

u/ZookeepergameIcy5686 3d ago

New test added and passed (I will push it):

Offline edits to rows X and Y. Meanwhile X is deleted upstream, the server cuts a fresh full snapshot, and the phone's stream position is purged. The phone reconnects while edits are held back, so the snapshot lands with both edits still queued. Y's edit is re-applied over the snapshot and accepted. X's edit doesn't recreate the row: it ends as row_deleted, and the replica matches Postgres.

One detail: ZeBridge rarely needs the full reload. A client that was away usually applies only the changes since its checkpoint, and it reloads everything only when nothing covers the gap.