top | item 45421232 (no title) johnthescott | 5 months ago could you elaborate on pg not really having matviews? discuss order hn newest ethanseal|5 months ago Materialized views in Postgres don't update incrementally as the data in the relevant tables updates.^1In 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... timbowhite|5 months ago The pg_ivm plugin adds incremental updates to Postgresql materialized views:https://github.com/sraoss/pg_ivmThough I don't know how well it works on a write-heavy production db. Tostino|5 months ago As somebody who implemented manual incremental materialized tables using triggers, yeah it's pretty dang hard to make sure you get all the edge cases in which the data can mutate.
ethanseal|5 months ago Materialized views in Postgres don't update incrementally as the data in the relevant tables updates.^1In 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... timbowhite|5 months ago The pg_ivm plugin adds incremental updates to Postgresql materialized views:https://github.com/sraoss/pg_ivmThough I don't know how well it works on a write-heavy production db. Tostino|5 months ago As somebody who implemented manual incremental materialized tables using triggers, yeah it's pretty dang hard to make sure you get all the edge cases in which the data can mutate.
timbowhite|5 months ago The pg_ivm plugin adds incremental updates to Postgresql materialized views:https://github.com/sraoss/pg_ivmThough I don't know how well it works on a write-heavy production db.
Tostino|5 months ago As somebody who implemented manual incremental materialized tables using triggers, yeah it's pretty dang hard to make sure you get all the edge cases in which the data can mutate.
ethanseal|5 months ago
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...
timbowhite|5 months ago
https://github.com/sraoss/pg_ivm
Though I don't know how well it works on a write-heavy production db.
Tostino|5 months ago