> 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.
> 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.
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.
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?
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.
Postgres materialized views are pretty terrible / useless compared to other rdbms. I’ve never found a usecase for the very limited pg version.
having a dataset refresh on a timer and cache the result for future queries is pretty useful