postgresql-contrib VS pg_ivm

You can use https://github.com/twosigma/postgresql-contrib/blob/master/m... and write triggers to update your views or mark them as needing asynchronous refreshes. It's not as automatic as what you'd like, but it's a lot better than having to keep waiting.

I use PG with an alternative materialized views implementation[0] that is pure PlPgSQL and that exposes real tables that can be used to write to in triggers, and where the views can be marked stale too.

This means hand-coding triggers to keep the materializations up to date, or else to mark them as out of date (because maybe some operations would be slow or hard to hand-code triggers for), but this works remarkably well.

As a bonus, I get an update history table that can be used to generate updates to external systems.

In principle one can get the AST for a VIEW's query from the PG catalog and use that generate triggers on the tables it queries to keep it up to date. In practice that's only trivial for some kinds of queries, and I've not written such a tool yet.

[0] https://github.com/twosigma/postgresql-contrib/blob/master/m...

FTA, because I don't like Jeopardy questions in headlines:

“Incremental View Maintenance (IVM) provides a method for keeping materialized views current by calculating and applying only the incremental changes, as opposed to the complete recomputation of contents performed by the REFRESH MATERIALIZED VIEW command.”

Article shows using the pg_ivm Postgres extension available here: https://github.com/sraoss/pg_ivm

There is an extension that provides some limited incremental MVIEW refresh: https://github.com/sraoss/pg_ivm

There’s awesome work being done on incremental view maintenance in postgres:

https://github.com/sraoss/pg_ivm

It makes sense to perform managed denormalization - use a materialized view or automatically refresh a table or foreign server (via FDW) using common triggers (like pg_ivm does). And it's fine to add a TTL to it and use as a read store... update on user login and make a partial index just for that. And that's how you could get CQRS...