[kleebeesh]

I've personally had some painful experiences with refreshing materialized views in Postgres. In particular, highly variable performance on read replicas that were receiving a refreshed matview every few minutes. Maybe we were just doing it wrong, but I tend to avoid it if I can. Plus the eventual consistency can introduce confusion.

In any case, there's an interesting feature called Incremental View Maintenance that is being worked on by some Postgres developers: https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...

This would let us define a materialized view that gets automatically updated as the source tables change. When I last checked (late 2021), they were saying it might land in PG15.

[thom]

This is a really interesting area, would love it if Postgres provided a strong option here. In the meantime, Materialize has very good Postgres integration if it might work for you:

https://materialize.com/