4 ms·
It's not as simple as "subscribe to this SQL query", but you can do this relatively easy with PostgreSQL LISTEN / NOTIFY: https://gist.github.com/kissgyorgy/bec
by kissgyorgy 6y ago
It's not as simple as "subscribe to this SQL query", but you can do this relatively easy with PostgreSQL LISTEN / NOTIFY:
https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237a900c79 https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237...
You just notify the clients with the primary keys of what changed, so you only need to run the query if something changed. If you implement it correctly, you only have to run the query once and push the same result to every client.
I have a small project where I used this and wherever you change anything and send the NOTIFY, every client updates real-time.
- rockwotj 6y agoFwiw this approach doesn't work for something like: SELECT * FROM Products WHERE price > 5; If a new product comes in with price 20 you won't know to add it to your result set and send it to the clients.
- touisteur 6y agoEven from a view? Materialized or not?
- rockwotj 6y agoOnly if you keep the whole table in memory or re-read every row that is written
- kissgyorgy 6y agoYou would send the new id in NOTIFY when something inserts the new product.
- rockwotj 6y agoYou'd have to send the full row otherwise you wouldn't know if it matches the query or not