Reactive queries for PostgreSQL


#1

Over the new year’s break I implemented one idea I had for reactive queries for PostgreSQL. You can take an arbitrary SELECT query, using multiple joins, data transformations, and even custom functions, and besides the initial set of results also get real-time updates about any changes to those results. Using PostgreSQL’s to_json and jsonb data type and you can literally simulate MongoDB now with all extra features of transactions and joins. And with this package you can now easily make a Meteor publication to push data to the client, reactively.

Any feedback welcome. :slight_smile:


#2

Very cool! Unfortunately I doubt I’ll have time to play with this anytime soon but it is awesome to see new tools and techniques for live data. Thanks for making this!


#3

Nice package @mitar …reminds me of https://hasura.io/


#4

Interesting. Do you know how Hasura does reactive queries? I could not find relevant code or documentation about this. All is just about client side and how to use it.


#5

I think they are using graphql subscriptions for reactivity. I heard about it on this react native radio episode.


#6

Yes, this is how you get it from server-side to client-side. But how does it work on the query side? How does it know what has changed in PostgreSQL database?


#7

It looks like it has to do something with this and event triggers.


#8

Event triggers and DDL is about changes to tables schema/structure, not data. To my knowledge.


#9

@mitar this is simply amazing! We use Postgres heavily in our work, and have long been looking for something like this. I have a couple of questions - didn’t want to pollute the repo’s issues list so asking here.

  1. “Queries require an unique column which serves to identify rows and changes to them…”. Not a big issue, as having a unique key is good practice anyway, but out of curiosity, why not use the row OID?

  2. How would this work in the context of pooling. Say, transaction level pooling, like the one possible with pgBouncer. I’m guessing it would be impossible and one should fallback to session pooling. But actually, is any form of pooling really possible when using this package?

Thanks!


#10

True, I was confused by the terminology hasura uses but they do seem to be using regular triggers to capture crud. From then on, they seem to be using their metadata to match them to subscriptions. This is seemingly trivial for hasura because it looks like what they essentially do is parse and convert any complex graphql query to a single sql query, and having done that, generate triggers to “listen” to events. They use this for calling web hooks too.


#11

User-exposed OIDs are being removed in PostgreSQL 11, BTW.

And unique index has to be able to be made on results, not source tables. So if you join two tables, do joined results still have row OIDs? Also, what happens if you reorder the results. Do row OIDs follow? Or are they more like index in the array?

Anyway, you can specify as index column any column. Probably picking “oid column” could work as well.

How would this work in the context of pooling. Say, transaction level pooling, like the one possible with pgBouncer. I’m guessing it would be impossible and one should fallback to session pooling. But actually, is any form of pooling really possible when using this package?

This package does its own pooling internally. You can control how many connections you want for this package to do and then it multiplexes things over those. So it users temporary objects internally, not sure how pgBouncer deals with that. So how it assures that same client can access its temporary objects between queries.


#12

So I would like to see how they generate those triggers. Do you know where this is? Because you cannot have triggers on queries. So this is the main tricky question how to do (and what my package tries to solve). How do you know when and how did query results change. So I am curious how do they do that.


#13

I’ve never used PostgresQL (from a developer’s perspective). I wonder how this differs from numtel:pg, which uses triggers to get reactive PG…?


#14

I don’t know any haskell but I tried to follow the code which kind of led me to believe that they are basically requerying per each crud event that is relevant to that query, not necessarily tracking a cursor for the query itself.


#15

numtel:pg uses pg-live-select and was a great inspiration for my package. You can see my biased comparison with other projects. In short, I believe my package is fastest (lowest latency) and has what I believe a better design (does diffing in the database, close to data).

But all those packages still do full refresh (similar to Meteor’s polling strategy). We would need an oplog-like approach as well. I described ideas how to achieve that here.


#16

Care to link to code?

So are they requerying per every CRUD event on any of sources? This is similar to what I am doing as well. But in my package I do all that work in the database.

The question is if they are doing anything smarter. For example, they could know in a smart way which changes to source tables does influence a particular query results and trigger event only then.