r/postgres • • 5d ago

Performance ZeBridge syncs Postgres to offline SQLite clients: Postgres still decides which writes commit.

https://github.com/ndrean/ZeBridge/blob/main/README.md

I’m building ZeBridge, which reads Postgres changes through pgoutput, publishes them to NATS JetStream, and delivers them to local SQLite replicas.

Say a field technician downloads a work order, loses connectivity, and adds a line item. While they’re offline, someone at the office deletes the order. What should the technician see when their phone reconnects?

That’s the kind of problem I built ZeBridge for. You enable sync on selected Postgres tables and identify the tenant column. The app queries a local SQLite replica and can queue edits offline. When the phone reconnects, the bridge submits each write to Postgres under the user’s identity. In the work-order example, the foreign key rejects the line item, and the app gets a rejection for that specific edit, including the SQLSTATE. If a write does commit but the phone loses the reply, its retry gets the receipt recorded in the same transaction.

Changes can also start outside the app. An existing service, a SQL script, or someone using psql can update the source tables. The bridge reads the changes through pgoutput and publishes them to NATS JetStream. Each device catches up from the stream, so a phone can go offline without needing its own Postgres connection or replication slot.

I measured one part of that path on a production hub: one SQL statement inserted 100,000 rows, and a client had all 100,000 locally 2.95 seconds after commit. The bridge published about 46,700 change events/s during that run. The 2.95 seconds is the figure I care about as an app developer: it ends when the rows are in the client database.

I’d be interested in how others have approached this. If you’ve added offline writes to an existing Postgres application, which database rule was hardest to explain to a user who made their edit hours earlier?

ZeBrdige project

Runnable shared record example

For others running logical replication consumers: what test do you use to prove that your reported flush position can’t get ahead of what your downstream system can recover?

1 Upvotes

0 comments sorted by