5 ms·

> And then by magic the results of this query will just always exist and be up-to-date. With PostgreSQL the materialized view won't be automatically updated

> And then by magic the results of this query will just always exist and be up-to-date. With PostgreSQL the materialized view won't be automatically updated though, you need to do `REFRESH MATERIALIZED VIEW` manually.

4ndrewl 1y ago
Just landed here to write this. Materialized Views are _very_ implementation specific and are definitely _not_ magic. It's important to understand how your implementation works before committing to it.
- shivasaxena 1y ago
  Curious if anyone know any implementation where they would be automatically updated? Now that would be awesome! EDIT: come to think of it, it would require going through CDC stream, and figuring out if any of the tables affected are a dependency of given materialized view. Maybe with some ast parsing as well to handle tenants/partitions. Sounds like it can work?
  - magicalhippo 1y ago
    MSSQL and Sybase SQLAnywhere has options for that, we use it a fair bit with both. At least on SQLAnywhere it seems to be implemented using triggers, ie not unlike what one would do if rolling your own.
  - cyanydeez 1y ago
    Postgesql knows when you try to drop its dependencies so shouldnt be to hard to watch traffic
  - 4ndrewl 1y ago
    Oracle will do - a couple of options, either a full rebuild or an incremental rebuild.
    - tanelpoder 1y ago
      Also, latest Oracle version (23ai) has added "concurrent on-commit fast refresh" functionality, where concurrent transactions' changes are rolled up to the MV concurrently (previously these refreshes were serialized). https://oracle-base.com/articles/23/materialized-view-concurrent-refreshes-23 https://oracle-base.com/articles/23/materialized-view-concur... From the article: "Concurrent refreshes will only happen if there are no overlaps in the materialized view rows updated by each session."
  - lsuresh 1y ago
    Do give us at Feldera a shot -- full IVM for arbitrary SQL + UDFs: https://github.com/feldera/feldera/ https://github.com/feldera/feldera/
dalyons 1y ago
Postgres materialized views are pretty terrible / useless compared to other rdbms. I’ve never found a usecase for the very limited pg version.
- thrown-0825 1y ago
  having a dataset refresh on a timer and cache the result for future queries is pretty useful
- JohnBooty 1y ago
  I've only used Postgres' and (ages ago) MSSQL's materialized views. What is pg missing compared to the others? I've found them VERY useful for a narrow range of use cases but, I probably don't realize what I'm missing.
- globular-toast 1y ago
  In Postgres a materialized view is basically a table that remembers the query used to generate it. Useful if you want to trigger the refreshes without knowing the queries.
jelder 1y ago
Did I miss in the article where OP reveals the magic database that actually does this? 3rd party solutions like https://readyset.io/ https://readyset.io/ and https://materialize.com/ https://materialize.com/ exist specifically because databases don’t actually have what we all want materialized views to be.
- sophiebits 1y ago
  These startups (and a handful of others) are what I meant!
  - jelder 1y ago
    What others do you know of?
    - jamesblonde 1y ago
      Feldera
    - sophiebits 1y ago
      Feldera, RisingWave, DeltaStream, Epsio, Decodable, Confluent all seem to have some offerings in this space. Probably others too!
- rapind 1y ago
  You can do targeted materialized view updates via triggers. It's definitely verbose but does give you a lot of control. I'm currently parking PostgREST behind Fastly (varnish) for pretty much the same benefits plus edge CDNs for my read APIs. I really just use materialized views for report generation now.
- anon84873628 1y ago
  In the analytics world, BigQuery MVs seem pretty cool. You can tune the freshness parameters, it will maintain user-specific row-level security, and even rewrite regular queries to use the pre-computed aggregates if possible. But I don't think there is anything similar in the GCP transactional db options like Spanner or CloudSQL.
oftenwrong 1y ago
There is a PostgreSQL extension that adds support for incremental updates to materialised views: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm

4ndrewl 1y ago

Just landed here to write this. Materialized Views are _very_ implementation specific and are definitely _not_ magic. It's important to understand how your implementation works before committing to it.

shivasaxena 1y ago
Curious if anyone know any implementation where they would be automatically updated? Now that would be awesome! EDIT: come to think of it, it would require going through CDC stream, and figuring out if any of the tables affected are a dependency of given materialized view. Maybe with some ast parsing as well to handle tenants/partitions. Sounds like it can work?
- magicalhippo 1y ago
  MSSQL and Sybase SQLAnywhere has options for that, we use it a fair bit with both. At least on SQLAnywhere it seems to be implemented using triggers, ie not unlike what one would do if rolling your own.
- cyanydeez 1y ago
  Postgesql knows when you try to drop its dependencies so shouldnt be to hard to watch traffic
- 4ndrewl 1y ago
  Oracle will do - a couple of options, either a full rebuild or an incremental rebuild.
  - tanelpoder 1y ago
    Also, latest Oracle version (23ai) has added "concurrent on-commit fast refresh" functionality, where concurrent transactions' changes are rolled up to the MV concurrently (previously these refreshes were serialized). https://oracle-base.com/articles/23/materialized-view-concurrent-refreshes-23 https://oracle-base.com/articles/23/materialized-view-concur... From the article: "Concurrent refreshes will only happen if there are no overlaps in the materialized view rows updated by each session."
- lsuresh 1y ago
  Do give us at Feldera a shot -- full IVM for arbitrary SQL + UDFs: https://github.com/feldera/feldera/ https://github.com/feldera/feldera/

dalyons 1y ago

Postgres materialized views are pretty terrible / useless compared to other rdbms. I’ve never found a usecase for the very limited pg version.

thrown-0825 1y ago
having a dataset refresh on a timer and cache the result for future queries is pretty useful
JohnBooty 1y ago
I've only used Postgres' and (ages ago) MSSQL's materialized views. What is pg missing compared to the others? I've found them VERY useful for a narrow range of use cases but, I probably don't realize what I'm missing.
globular-toast 1y ago
In Postgres a materialized view is basically a table that remembers the query used to generate it. Useful if you want to trigger the refreshes without knowing the queries.

jelder 1y ago

Did I miss in the article where OP reveals the magic database that actually does this? 3rd party solutions like https://readyset.io/ https://readyset.io/ and https://materialize.com/ https://materialize.com/ exist specifically because databases don’t actually have what we all want materialized views to be.

sophiebits 1y ago
These startups (and a handful of others) are what I meant!
- jelder 1y ago
  What others do you know of?
  - jamesblonde 1y ago
    Feldera
  - sophiebits 1y ago
    Feldera, RisingWave, DeltaStream, Epsio, Decodable, Confluent all seem to have some offerings in this space. Probably others too!
rapind 1y ago
You can do targeted materialized view updates via triggers. It's definitely verbose but does give you a lot of control. I'm currently parking PostgREST behind Fastly (varnish) for pretty much the same benefits plus edge CDNs for my read APIs. I really just use materialized views for report generation now.
anon84873628 1y ago
In the analytics world, BigQuery MVs seem pretty cool. You can tune the freshness parameters, it will maintain user-specific row-level security, and even rewrite regular queries to use the pre-computed aggregates if possible. But I don't think there is anything similar in the GCP transactional db options like Spanner or CloudSQL.

oftenwrong 1y ago

There is a PostgreSQL extension that adds support for incremental updates to materialised views: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm