I've done some work in that space, which was mainly to build an alternative view materialization scheme[0] in PlPgSQL that reifies the internals of CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW but with the following properties:
- the materialized view is a plain table, so
- you can write to it from triggers
- you can have triggers on it
- refreshing a view records the deltas in a
history table (which is useful as a poor
person's logical replication scheme)
- you can mark a view as needing a refresh
Then in the application I have hand-coded triggers to either update the view's materialization directly or to mark the view as needing a refresh. A background job can asynchronously refresh views as needed.
I've also spent some time thinking about the AST form of view queries that PG stores and how one might automatically generate triggers on source tables that update the materialization or mark it as needing a refresh.
As you note, many queries can be very difficult to transform into queries that compute incremental deltas. Moreover, even where it's possible to do that, the time it takes to execute the delta computation might be unacceptably long. For example, if you have a recursively nested grouping schema and you want to maintain a view of the expanded transitive closure of that data, then removing a large from from another might require thousands of row deletions from the materialized view, and that might make the transaction take much too long in a UI -- the obvious thing to do here is to say "sorry, that kind of update takes a while to propagate, but your transaction will complete quickly", so just mark the view as needing a refresh and refresh it asynchronously.
