| ▲ | quectophoton 9 days ago |
| > 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. |
|
| ▲ | jelder 8 days ago | parent | next [-] |
| Did I miss in the article where OP reveals the magic database that actually does this? 3rd party solutions like https://readyset.io/ and https://materialize.com/ exist specifically because databases don’t actually have what we all want materialized views to be. |
| |
| ▲ | sophiebits 8 days ago | parent | next [-] | | These startups (and a handful of others) are what I meant! | | | |
| ▲ | anon84873628 8 days ago | parent | prev | next [-] | | 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. | |
| ▲ | rapind 8 days ago | parent | prev [-] | | 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. |
|
|
| ▲ | 4ndrewl 9 days ago | parent | prev | next [-] |
| 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 9 days ago | parent [-] | | 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 8 days ago | parent | next [-] | | 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. | |
| ▲ | lsuresh 8 days ago | parent | prev | next [-] | | Do give us at Feldera a shot -- full IVM for arbitrary SQL + UDFs: https://github.com/feldera/feldera/ | |
| ▲ | 4ndrewl 8 days ago | parent | prev | next [-] | | Oracle will do - a couple of options, either a full rebuild or an incremental rebuild. | | |
| ▲ | tanelpoder 8 days ago | parent [-] | | 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-concur... From the article: "Concurrent refreshes will only happen if there are no overlaps in the materialized view rows updated by each session." |
| |
| ▲ | cyanydeez 8 days ago | parent | prev [-] | | Postgesql knows when you try to drop its dependencies so shouldnt be to hard to watch traffic |
|
|
|
| ▲ | oftenwrong 8 days ago | parent | prev | next [-] |
| There is a PostgreSQL extension that adds support for incremental updates to materialised views: https://github.com/sraoss/pg_ivm |
|
| ▲ | dalyons 9 days ago | parent | prev [-] |
| Postgres materialized views are pretty terrible / useless compared to other rdbms. I’ve never found a usecase for the very limited pg version. |
| |
| ▲ | globular-toast 8 days ago | parent | next [-] | | 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. | |
| ▲ | JohnBooty 8 days ago | parent | prev | next [-] | | 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. | |
| ▲ | thrown-0825 8 days ago | parent | prev [-] | | having a dataset refresh on a timer and cache the result for future queries is pretty useful |
|
