In order to keep it up to date, the developer has to tell postgres to refresh the data and postgres will do all the work from scratch.
Incremental Materialized views are _hard_. This^2 article goes through how Materialize does it.
MSSQL does it really well from what I understand. They only have a few restrictions, though I've never used a MSSQL materialized view in production.^3
[1]: https://www.postgresql.org/docs/current/rules-materializedvi... [2]: https://www.scattered-thoughts.net/writing/materialize-decor... [3]: https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...
https://github.com/sraoss/pg_ivm
Though I don't know how well it works on a write-heavy production db.
In order to keep it up to date, the developer has to tell postgres to refresh the data and postgres will do all the work from scratch.
Incremental Materialized views are _hard_. This^2 article goes through how Materialize does it.
MSSQL does it really well from what I understand. They only have a few restrictions, though I've never used a MSSQL materialized view in production.^3
[1]: https://www.postgresql.org/docs/current/rules-materializedvi... [2]: https://www.scattered-thoughts.net/writing/materialize-decor... [3]: https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...