search

numtel / pg-live-select

Live Updating PostgreSQL SELECT statements
https://www.npmjs.com/package/pg-live-select
Other
55 stars 9 forks source link

What about Logical decoding ? #1

Open pinouchon opened 9 years ago

pinouchon commented 9 years ago

It looks like pg-live-select is currently working with manual triggers. This is a lot of work and hard to do right.

Is the live-select functionality achievable with the Logical Decoding feature of postgres (http://www.postgresql.org/docs/9.4/static/logicaldecoding.html) ?

I'm curious about whether Logical Decoding has been thought about. And if yes, what are the problems with it ?

Discussion on hacher news here: https://news.ycombinator.com/item?id=9387988

numtel commented 9 years ago

Yes, it's definitely possible but it requires Postgres 9.4. Although it may provide a slight performance improvement, manual triggers would not change: it would simply work like numtel:mysql using the binary log.

In order to automate what is currently "manual" triggers. I have started exploring ways to parse the EXPLAIN command's output but it's quite difficult as there are many possibilities. Hopefully I can get this feature working in a future version because it would be killer!

numtel commented 9 years ago

And if you're wondering if it's possible to update queries without repeatedly executing the query, see the abandoned simple_queries branch of this project.

It turns out that doing all that work in Javascript blocks the event loop significantly on higher loads, resulting in faster operation by offloading the work to Postgres.

pinouchon commented 9 years ago

Is Postgres 9.4 too big of a requirement ?

I think a possible way would be to first implement it using Logical Decoding, then add the fallback for older versions of postgres...

Slava commented 9 years ago

If one uses logical decoding, does it mean the computation would need to happen in the application process? Can it be done in the postgres process with stored procedures?

numtel commented 9 years ago

Hey Slava, Not sure what you mean by "the computation."

Thinking about it now though, if triggers were abandoned for only reading from the write-ahead log, there would have to be constant polling for new changes. In a high-traffic environment, this might result in fewer transmissions between the pg server and the app server but in low-traffic situations, the opposite.

Unless an extension to postgres is written so that asynchronous actions can be performed, the diffing of result sets is best to be initiated from the app server in order to provide a throttling mechanism.

Not sure if that helps, maybe you can elaborate more?

Slava commented 9 years ago

I guess, I meant something different. By "computation", I meant "figuring out how to incrementally recompute the query" or "re-poll the query and diff the result".

numtel commented 9 years ago

Oh okay. Yeah that would need to take place in the app server or a custom postgres extension that uses a Background Worker. As far as I know, there's no way to place a "trigger" on a write-ahead log entry using logical decoding.