- Traditional methods like polling are resource-intensive and rerun entire queries, which isn't efficient. - I’m familiar with some libraries and companies are doing this by building materialized views close to the DB [1][2], but I want to update the frontend, not just the backend. - Some platforms like Supabase Realtime [3] and Firebase offer subscription models to database changes, but these solutions fall short when dealing with complex queries involving joins or group-bys.
My vision is that the modern frontend to behave like a series of materialized views that dynamically update as the underlying data changes. Current state management libraries handle state trees well but don't seamlessly integrate with relational or graph-like database structures.
The only thing I can think of is to implement it by myself, which sounds like a big PITA.
Anything goes, Brainstorm with me. Is it causing you headaches as well? Are you familiar with an efficient solution? how are you all tackling it?
[1] https://readyset.io/ [2] https://materialize.com/ [3] https://supabase.com/docs/guides/realtime/postgres-changes [4] https://github.com/hasura/graphql-engine/blob/master/architecture/live-queries.md
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.
Imaging subscribing to SQL query not just for a table; isn't it very complicated?
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.
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...
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).
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).
It indeed solves half of the problem, but managing websockets for thousands of users is not that trivial IMO
they scale really really easily for millions of users.
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
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!
The maintainer has another great project, Golden Layout https://golden-layout.com/
Too bad it still depends upon jQuery.
https://rethinkdb.com/
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...
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.
[0] https://perspective.finos.org/
Every time the table changes the DB sends a message to the browser in real time. Super simple.
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.
[0] https://pocketbase.io/ [1] https://pocketbase.io/docs/api-realtime/ [2] https://pocketbase.io/faq
- 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.
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?
It is certainly a problem with an achievable solution, but it is not simple.
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.
Your requirements aren’t really clear, but I suspect you are asking the wrong questions.
It's really the best developer experience.
https://powersync.com/
People are often blown away by the real time reactivity.
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! :)
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.
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... :)
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.
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.