[debaserab2]

You can do effectively the same thing using a physical table and triggers [1] if you want a pure postgres solution. I ended up with the same problem and just ended up using a physical table and implemented the updating logic at our app layer. Not exactly fun or easy to implement all the updating logic yourself though...

[1] https://hashrocket.com/blog/posts/materialized-view-strategi...

[BenoitP]

I'm very hopeful for approaches that model around this need from the ground up. Big Data distributed stream processing frameworks like Spark or Flink allow for standing SQL queries that are incrementally build; and you can even subscribe to react to the results.

There are other initiatives for plugging it on existing RDBMSs, like Noria [1] for MySQL. It allows for subscribing to changes; and also for lazy evaluation of the materializing view rows, keeping them in cache for later.

[1] https://github.com/mit-pdos/noria/blob/master/README.md

[fulafel]

Like you said, not easy: Recreating the functionality of views using triggers is error prone and a heavy maintenance burden, and the triggers have to be implemented differently based on what each query is like... In a lot of cases it's better to just optimize the query and indexing.

In PG you can make a lot of queries run straight from one index if you tailor an index to a query, since you can index your own plpgsql functions.