What your trying to do is very difficult (particularly since your data views are relying upon joins and group queries.) You may need layered solutions.

I've been looking at https://deepstream.io for keeping clients in sync. You will still have to watch the db, though the client and back end sync is handled!

Very interesting, thank you. I looks like a layer on top of your database, not a standalone baas, which is great. Have you used it in production?

I am evaluating it for a project.

The maintainer has another great project, Golden Layout https://golden-layout.com/

Too bad it still depends upon jQuery.

Just very generally, event-driven and data-flow architectures have been with us forever, and I have implemented products on them. Should not be too hard to base what you want on that sort of thing.

just thinking about implementing incremental materialized views and maintaining web sockets with clients makes me nervous, but maybe I'm the only one. Have you implemented such a solution e2e? from db to frontend?

The systems I was designing/building were for trading, and years ago. Some involved push live updated recomputed instrument values into live spreadsheets. And yes it was ugly! There was not a relational 'DB' in the live path.

You're overcomplicating everything. Websocket endpoints. Simple.

...and that oversimplifies everything. Yes, websockets to the browser are likely to be a piece of the answer. But a server needs to maintain the session for the websocket and send messages. And that server needs to subscribe to data updates in the database... while also respecting auth of the current user.

It is certainly a problem with an achievable solution, but it is not simple.

Agree.

In his initial message, the original poster provided a link to Supabase, which integrates PostgreSQL with PostgREST out of the box. This setup automatically offers websocket server functionality:

1. The websocket server, database data subscriptions, and all session management are consolidated in this layer.

2. Updates to data are efficiently managed using a built-in PostgreSQL function.

3. User authentication is managed via React, employing either JWT or OAuth.

As I mentioned earlier, it's quite simple.

I'm interested in this problem also!

I think there is a large overlap with projects that market/focus on offline-first experiences.

AFAIK this problem can be solved by:

1) Considering a client-side copy of the database that gets synced with the remote DB. This is an approach [PowerSync](https://www.powersync.com/) and [ElectricSql](https://electric-sql.com/) and [rxdb](https://rxdb.info/) take! For the first two, they effectively use postgres CDC and then have a sync process that is provided for you. For rxdb I think you have to roll your own syncing but there is an interface for you to implement.

One difference between ElectricSQL and PowerSync (copied this from another [HN comment](https://news.ycombinator.com/item?id=37600917)) -> `ElectricSQL uses “shapes” (currently a WIP feature) defined on the client to partition data, while PowerSync uses server-side “sync rules”. The sync rules approach allows data to be transformed and filtered using basic SQL queries, which [PowerSync] expect to scale well (benchmarks pending). PowerSync guarantees consistency over all synced data, while it appears like ElectricSQL only guarantees consistency within each shape. PowerSync also automatically adds and removes sync buckets when changes are made to either the sync rules or data, while ElectricSQL likely requires you to subscribe and remove shapes yourself.`

^ Please note that comment faced rebuttal from ElectricSQL. On the same thread (6 months ago) , someone from ElectricSQL said this `Electric is designed to support partial sync, and so you don’t have to sync your whole dataset. (Note that this is feature is under development and not yet public)`

2) Another exciting entry is [Convex](https://www.convex.dev/) which supports reactive queries but it is not based on a well known existing DB like postgres, it's fully custom and new - but looks easy to get started with! They recently open-sourced their backend so you can give that a try.

3) [SurrealDB](https://surrealdb.com/features#connectivity) also supports realtime queries? but I haven't looked into this yet

4) [Triplit](https://www.triplit.dev/) Another option! Triplit is an open-source database that syncs data between server and browser in real-time but again, this not based on a well-known db like postgres, instead it uses another new solution. It is open-source, but I haven't tried it yet. Looks quick to set uo.

4) Yes, you can use supabase realtime/ postgres events to listen to changes in your db and refetch all your data as you said - not ideal but yep.

Other projects/technologies to look into include Debezium for CDC and also potentially using GraphQL subscriptions to keep your frontend up to date

See also: [Prisma Pulse](https://www.prisma.io/data-platform/pulse) which I looks something similar to Supabase Realtime. Supabase are in the midst of their announcement week. I have quietly hoping that they will launch an offline-first / realtime query feature soon. [This github issue](https://github.com/orgs/supabase/discussions/357) seems to tease it.

Best of luck with this! It's a super interesting problem and I'd like to know what you end up doing! :)

Just to expand on this - ElectricSQL and PowerSync (and some others like Evolu) use a simple but quite effective approach to get reactive queries:

1. Listen for updates to the table(s) used by the query, using SQLite's update hook or similar approach.

2. When a table has changed, rerun the query.

There is no incremental updating of row results, no smart detection of whether the updated rows have an effect on the query. But it works with practically any query, including joins, aggregates, and even views and CTEs in the case of PowerSync and Evolu. And in most cases, SQLite is fast enough that performance is not an issue.

Hey, Electric co-founder here.

Yup, our reactivity is quite simple at the moment, as described, and works quite well because SQLite is so fast. There are other projects doing more sophisticated reactivity algorithms, like Riffle/LiveStore, which has a reactive DAG optimised to avoid re-rendering and https://github.com/vlcn-io/materialite, which is like differential dataflow for live queries.

They're quite bleeding edge at the moment, but you can expect more efficient primitives for incremental view maintenance / efficient subscriptions to land in the embedded database layer quite soon.

Just also re: above, shape filtering for partial sync landed in v0.10 https://electric-sql.com/blog/2024/04/10/electricsql-v0.10-r... :)

Thanks! Electric looks great! though I'm wondering how it works in scale. I'm using Postgres and have some heavy queries, would be great to cache\materizlized the results instead of re-run them every time for every user, or for returning users without any change in the data.

Thank you for the detailed answer, you're awesome.

Electric-SQL, Convex, and Triplit are definitely relevant, and I'll dive deep into understanding their pros and cons. In the meantime, I found another relevant one: https://squid.cloud/, which has a slightly different approach, more like full BAAS.

On one end, it can save me some time; on another, it's another stack my R&D needs to be familiar with.

FYI, HN doesn't use Markdown: https://news.ycombinator.com/formatdoc

I've been using Hasura.io (through nhost.io) for quite some time now and I think it would suit your needs very well. It is very good in handling complex relationships with subscriptions.

You already mention Materialize -- have you looked at the SUBSCRIBE feature? https://materialize.com/docs/sql/subscribe/

It's fairly straightforward to write a websocket endpoint in whatever backend you have that issues a SUBSCRIBE query on connect and then continuously forwards the updates to the client. The complicated parts you'd need to build are on the frontend -- handling updates and merging them into whatever state you render from.

Disclaimer -- I work at Materialize -- but it does seem like the perfect fit for what you need (efficiently updated real-time materialized views).

Thanks! > It's fairly straightforward to write a websocket endpoint in whatever backend you have

It indeed solves half of the problem, but managing websockets for thousands of users is not that trivial IMO

you should try phoenix channels https://hexdocs.pm/phoenix/channels.html

they scale really really easily for millions of users.

Use convex.dev

It's really the best developer experience.

It looks very relevant to my problem! Have you used it? is it production-ready?

There's no generic solution to database problems.

They all depend on the specifics of the data and the specifics of access and the specifics of the use.

Then you make engineering tradeoffs per the CAP theorem.

Is the application launch-the-missiles critical?

Is it newest-lol-cat-pictures critical?

What's the network latency?

What is the CDN strategy?

How frequently does the data change?

How much data changes per second/minute/hour/day/week/month?

How much cache can you use?

Etc.

Good luck.

easy, add a new version on a table every time you change anything, poll to check version, update FE if version changed.

Do it the way the database wants you to do it. Don’t use HTTP. Instead use TCP sockets (WebSockets when in the browser). Then put a tigger on the tables you care about.

Every time the table changes the DB sends a message to the browser in real time. Super simple.

It's super simple for a simple demo app, not easy for a large scale.

It remains just as simple. The transmission model does not change as the size of application code changes.

The only thing to worry about is load balancing socket restoration when the server recovers from an outage so it’s not overwhelmed reopening all sockets simultaneously.

As for the database side you should only be concerned with changes to a select few tables and not the entirety of data. That could also be centrally managed with a script.

Noria sounds like what you mention with materialized views, but it’s a server side rust thing, might be pretty heavy. https://docs.rs/noria/latest/noria/

Consider Server Sent Events as an alternative to sockets because this lets you keep Client events restful instead of doing everything on sockets.

https://developer.mozilla.org/en-US/docs/Web/API/Server-sent...

Might be wise to make a “sync” service? Make sure your client has a local database in indexeddb and listen for server events with data updates, apply the event to the database, and use stale-while-revalidate to double check data (perhaps debounce revalidation substantially unless you have extremely dynamic data)

One idea would be to use redis queues to hold server events, let clients subscribe to the redis, don’t let them touch the DB behind the server, then it’s less of a push and more of a pull when client is ready

What’s the downside with sockets that makes you want to keep events RESTful?

I reckon you want some kind of publish-subscribe websocket setup, with event streams. Something like Apache Pekko, or Elixir, or maybe Redis, so the web browser subscribes to a topic they're authorised to receive updates on. And then separately, subscribe listeners on the backend to the tables, and fan out updates to the various topics. They'd get moved out to the frontend. "reactive" would be the name of the game, so event streams, managing backpressure, etc, etc.

Something like the "stale while revalidate" strategy (eg as used by react-query) is wayyyy simpler however, and gets you most of the way there. You can set a timeout for when to invalidate the frontend cache to something short if you wanted (eg 3 seconds), and if the content is still being viewed by a component after that time it just fetches itself again while keeping the stale data in place. And for the few pieces that truly are realtime, like a stock ticker or something, just use a direct websocket.

Expiration-based cache (e.g., 3 seconds) is not acceptable in this case, and more than that will reduce the load on the server + db dramatically.

Imaging subscribing to SQL query not just for a table; isn't it very complicated?

In the last stage of your data pipelines, when you are sure the data is correct and only need to be pushed to a table, maybe branch out a script that send the "hot" data to the frontend.

> Firebase offer subscription models to database changes, but falls short when dealing with complex queries involving joins or group-bys.

Firebase is the only tool here I'm familiar with, and this makes sense to me. It's real time data "for dummies", where you don't have to worry about the internals.

They can guarantee that simple gets return events quickly, but if you're asking for complex queries, you actually have to engineer the tradeoffs.

Like, what happens if you are frequently writing to 2 tables, and are subscribed to a view that joins both?

websockets and LISTEN/NOTIFY (if using Postgres)

Also, consider using server-sent events over HTTP/2 (instead of websockets) if the data stream is unidirectional (server -> frontend).

I'd use PowerSync for this:

https://powersync.com/

People are often blown away by the real time reactivity.

Maybe SignalDB?: https://signaldb.js.org/core-concepts/#signals-and-reactivit...

I swear I read that MeteorJS and/or MongoDB implemented signals for the DB layer. While searching for the source, I found SignalDB.

---

edit: Found the source:

https://dev.to/this-is-learning/the-evolution-of-signals-in-...

> ...Meteor Tracker is a good prior art on signals and effects history in JS (although not using the same terminology). The really magical thing about Meteor is that their "signals" and "effects" are database connected: you use variables in your frontend template, and those values can come directly from a backend DB...

SWR

You listed Hasura as a footnote but not in your message. Did you try it out? I wrote these guides a while back:

https://hasura.io/blog/getting-started-with-react-query-and-... https://hasura.io/blog/a-simple-real-time-event-driven-archi...

And did this YouTube series about building realtime apps with Hasura and React: https://www.youtube.com/playlist?list=PLTRTpHrUcSB8elpwJKDIQ...

I edited my first comment, but perhaps this deserves its own comment:

https://docs.meteor.com/api/tracker.html

> Meteor has a simple dependency tracking system which allows it to automatically rerun templates and other computations whenever Session variables, database queries, and other data sources change.

> Unlike most other systems, you don’t have to manually declare these dependencies — it “just works”. The mechanism is simple and efficient. When you call a function that supports reactive updates (such as a database query), it automatically saves the current Computation object, if any (representing, for example, the current template being rendered). Later, when the data changes, the function can “invalidate” the Computation, causing it to rerun (rerendering the template).

I enjoyed using Meteor. It is magic at first, and still rather elegant when you get into advanced stuff. The main downside for me was that it uses Mongodb. We had to denormalize heavily to get it working acceptably fast. There was talk about Postgres support, so maybe that is solved.

Throwing RethinkDB in the mix as well.

https://rethinkdb.com/

This is the answer for what OP wants. It literally is built to do what he wants and shows a demo doing it on the front page of the site. Too bad they couldn't make it into a business, but the project seems to be alive and well.

Pocketbase [0] is a possibility. It offers a way to subscribe to collections, meaning the client will be notified if any of the records in that collection change. [1] Should be quite efficient too, the FAQ claims that 10k realtime connections on a small hetzner VPS is no problem [2]

[0] https://pocketbase.io/ [1] https://pocketbase.io/docs/api-realtime/ [2] https://pocketbase.io/faq

Currently my preference as well. I like the auth part and admin UI. Combined with htmx it makes building reactive apps (without React) relatively easy.

I would suggest checking out PowerSync (https://www.powersync.com/) for this.

I've built various React apps (and iOS apps before that) in the past on a bunch of stacks.

My experience with the following tools were as follows:

- Pusher: love the tool, but required lots of layers on top of it to be used for keeping DB's in sync. It does make sockets trivially easy to get going and use.

- MeteorJS: lots of cool "realtime magic", but hard to break out (it's too opinionated and constrained). We ended up breaking out and running ExpressJS + React + Pusher and ditching MeteorJS.

- Firebase: used it for two projects. Platform lock-in, like weird Firebase-specific nuances to workaround and solve for, felt like quite a lot. You basically have to park your experience with other stacks and databases and become an expert in Firebase. A big one here was around access policies for the data in the database → limiting what data a user can access.

My current stack is now mostly Cloudflare and keeping real-time requirements in the product design to a minimum, to reduce the overhead.

Thanks, super helpful. re Cloudflare, are you using them to reduce latency? what about a centralized database?

I did something akin to that with the following monstrosity of DevOps:

- set up Kafka

- set up Flink

- CDC on your DB, into Kafka

- have your server maintain a websocket to clients, read Kafka from it, and dispatch messages according to a field from Kafka

- put your reaction logic into Flink, insert into the Kafka topic read by the server

I even went to opening JIRA tickets in Apache Flink, so that you may choose the Kafa topic to insert to dynamically. In case you have multiple servers to dispatch messages to.

It was very clunky, and a pain to operate. However it was short-lived and is no longer a concern of mine. What I got away from that: adding a time to data serving is just multiplying the complexity of data bugs you can have.

Maybe I'm misunderstanding, but you said anything goes, so here goes:

If rerunning entire queries is the problem, can you implement a changes table (could potentially double as an audit table if desired) which on a basic level just has time and rowid. Then you poll that table for anything newer than your current timestamp. Say for a report with 1000 rows, only 2 have changed in the last second. Then you can just do a query of the big table on those exact rowids.

The benefit over having timestamp in the big table is the changes table can be kept to a minimal size. You don't need to store any more than, say, a minute's worth of changes. Anyone visiting the page for the first time or that far out of sync will poll the whole report.

I don’t think I understand what you are after. Do you want a frontend framework that takes care of table and row based state management? A notification system that pushes changes made to the backend database to the browsers? A database that can push out results of complex queries to browsers using some sort of magical scalability?

Your requirements aren’t really clear, but I suspect you are asking the wrong questions.

You might be interested in checking out perspective[0] they server side `tables` which propagate changes to frontend charts by sending row diffs.

[0] https://perspective.finos.org/

Ably’s LiveSync (https://ably.com/livesync) is conceptually designed to solve exactly this problem by providing a database connector that subscribes to changes committed using the outbox pattern, and a models SDK to subscribe to those changes in your app. It’s early days as it’s just been released, but at the core the team’s goal in designing that was to solve this exact problem. Interested if this is meets your needs? Disclaimer. I am the co-founder of Ably.

sure, I would be happy to hear more about it. Let me know how to contact you.

Hey, I'm the PM for LiveSync. Happy to speak with you about the product — could you please send an email to [srushtika][dot][neelakantam][at][ably][dot][com]?