In postgres we have queries like `SELECT
In postgres we have queries like
SELECT * from some_table WHERE user_id = auth.uid() OR members ? auth.uid(). We poll that, compare the ids and versions of the rows to the ids and verisons the client says it has, and only return whatever has changed (higher version than client_data), no longer matches (in client data but not in query results), or is new (not in client data but in query results)11 Replies
@Joshua good idea, sorry for clogging up the questions channel
and sorry if this wasn't clear, but we do this all in postgres (plpgsql) to avoid an intermediate server processing the results and sending extra data to clients
Does the client send all the data it has on every request? Isn't that as inefficient as the server sending all the data?
I was also thinking - You could write this "stream processing + triggering" application "wrapping" TypeDB. If this wrapper is the one inserting data as well, wouldn't it be able to determine which queries need to be re-run each time it inserts any data?
No, the client only sends maps of row ids to version ints like
{ table: "foo", client_data: { 1: 0, 43: 4, 532: 8}} so the requests aren't huge
Doing it as a wrapper would be possible, but I think that might run into problems or unintended complexity since it would essentially have to wrap the entire DB and ensure that nothing ever modifies data without using the wrapper.
I think that's probably why supabase uses the WAL reading approach for their realtime.There have been things like what I'm describing before, but AFAIK not in a relational or strongly typed db. For example https://www.meteor.com/ meteor.js but that comes with all the problems of mongodb
The full-stack JavaScript framework for real-time apps - Meteor.js
Meteor.js is an open source platform for building Web, Mobile, and Desktop applications.
Spitballing, but that does make me wonder however if this could be achieved as a separate process that reads WAL essentially into a separate temporary database then executes the "watched" queries against this much smaller temp db. It could drop any data older than the oldest subscriber so would likely stay small enough to hold everything in memory.
That might end up more complex than implementing something direct in a database however. Since the db already naturally "knows" what data is being changed it's the most natural spot I think to basically reverse-query (go from a set of modified rows and match them back to queries clients are interested in)
definitely we can already read the wal and see what data is new or changed. the flow back up is the harder part imo
if you're doing it with clever polling you might be able to redo something like that in typedb in the data model
as a first approximation
I think I'm just not familiar enough with how the wal and internals work in postgres or typedb to know whether it would be better to read wal or try to do something directly with internal data structures of transactions.
I guess it also depends on how queries are compiled and represented internally for how hard it would be to match a set of changed data back to some queries.
Tbh the wrapper was me trying to figure out how we'd implement it. We have access to the WAL of course, but what are we looking for in the WAL? Presumably some concept that would be an answer to one of the variables in the query.
There's been talk over the years about TypeDB as an embedded database. I'm wondering whether the interface in such a case could be as simple as:
It doesn't solve your problem, but does take care of the change tracking half the problem - which you rightly point out is best done by processing changes to the WAL.
It doesn't solve your problem, but does take care of the change tracking half the problem - which you rightly point out is best done by processing changes to the WAL.
Typedb as an embedded db would be super cool. Especially combined with change detection, you could implement transparent sync to or from client side embedded dbs. This is just an area of ongoing development I think is cool haha, I think there's a lot of gains still to be made in ui/ux/dx with client side partially replicated dbs. I like what electric-sql has been doing in this space. I actually just opened a PR with proof-of-concept react-native support for pglite (which is their single user client side build of postgres) https://github.com/electric-sql/pglite/pull/774
GitHub
POC react native support by evelant · Pull Request #774 · electri...
(very) WIP react native support.
Warning: this is definitely not complete or entirely correct. It is proof-of-concept level. There is a lot of leftover verbose logging and incorrect/unused code fro...
I'd imagine given that it's a modern rust codebase it might be easier (than postgres) to compile typedb to a mobile shared library or wasm.
although I guess wasm not so easy, tokio doesn't seem to support it very well and rocksdb probabaly wouldn't work easily on wasm either
yeah rocksdb + wasm is no good yet