What about Logical decoding ?

[pinouchon]

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]

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]

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]

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]

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]

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]

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]

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.