Materialized views are one of the few places I feel Oracle DB still leads, and that’s a scary comment. I would really love Postgresql adopt something similar to the REFRESH ON COMMIT you can utilize.
TIL! This will be a powerful feature if Postgresql adopts it. More people would quickly adopt materialized views. Most folks use it sparingly because of the complexities of stale data.
How does the REFRESH ON COMMIT work in Oracle DB? Does it just reload the delta, or the entire materialized view is refreshed?
It’s been ~20year since I worked with an Oracle system, so I’m mostly working off of documentation. But there are several options to do incremental and partial rebuilds. They all have different requirements of the underlying materialized view, indices, change tracking… But you can have it just update the effected values in the view once you understand those requirements.
If you meet the DDL requirements you should be able to just use the REFRESH FAST ON COMMIT clause
I’ve had good luck so far with using generated columns for cases where I want an always fresh materialized view. It wouldn’t work across tables though.
When and how do you REFRESH the materialized view? I was thinking this could be done in a trigger function on the dependent tables. But since in Postgres a transaction does not end until triggers are done executing I am not sure it would a good idea. It could make an operation like changing an image subject take longer.
We do not refresh on every update, which is not recommended as default materialized view refresh locks it by default.
Since, in our use case, stale data could be tolerated for up to a few hours. We used to refresh the materialized view through application crons daily twice. Since the application was on rails, we used sidekiq for crons, and if you are on python, you could try celery crons.
I think Vitess (MySQL with horizontal scaling) has materialized views with real time updates. It works via replication, which makes sense after being introduced to the concept.
Materialized views are one of the few places I feel Oracle DB still leads, and that’s a scary comment. I would really love Postgresql adopt something similar to the
REFRESH ON COMMITyou can utilize.https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-MATERIALIZED-VIEW.html#GUID-EE262CA4-01E5-4618-B659-6165D993CA1B
TIL! This will be a powerful feature if Postgresql adopts it. More people would quickly adopt materialized views. Most folks use it sparingly because of the complexities of stale data.
How does the
REFRESH ON COMMITwork in Oracle DB? Does it just reload the delta, or the entire materialized view is refreshed?There is ongoing work on incrementally maintained views in Postgres (see pg_ivm on GitHub). It uses triggers and “for each statement” delta tables.
It’s been ~20year since I worked with an Oracle system, so I’m mostly working off of documentation. But there are several options to do incremental and partial rebuilds. They all have different requirements of the underlying materialized view, indices, change tracking… But you can have it just update the effected values in the view once you understand those requirements.
If you meet the DDL requirements you should be able to just use the
REFRESH FAST ON COMMITclauseUnderstood, thanks
I’ve had good luck so far with using generated columns for cases where I want an always fresh materialized view. It wouldn’t work across tables though.
What was the use case?
Maintaining FTS indexes, extracting properties from a JSONB blob.
When and how do you REFRESH the materialized view? I was thinking this could be done in a trigger function on the dependent tables. But since in Postgres a transaction does not end until triggers are done executing I am not sure it would a good idea. It could make an operation like changing an image subject take longer.
We do not refresh on every update, which is not recommended as default materialized view refresh locks it by default. Since, in our use case, stale data could be tolerated for up to a few hours. We used to refresh the materialized view through application crons daily twice. Since the application was on rails, we used sidekiq for crons, and if you are on python, you could try celery crons.
I think Vitess (MySQL with horizontal scaling) has materialized views with real time updates. It works via replication, which makes sense after being introduced to the concept.
Let me give it a read. Thanks for the link.