Thread: Refresh Materialized View
Hello all,
A few questions about materialized views.When I refresh a materialized view, is it a DELETE/INSERT behind the scenes?
Do we need to vacuum to reclaim space?
If a query is executed against the view when the refresh is happening, will the query see the data before the refresh started?
Regards,
To answer my own question, I saw this thread -
http://postgresql.1045698.n5.nabble.com/Efficiency-of-materialized-views-refresh-in-9-3-td5762618.html
I think that does answer my questions. Nothing has changed?http://postgresql.1045698.n5.nabble.com/Efficiency-of-materialized-views-refresh-in-9-3-td5762618.html
On Wed, Jan 1, 2014 at 6:42 PM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
JayadevanDoes the refresh result in exclusive locking?Hello all,A few questions about materialized views.
When I refresh a materialized view, is it a DELETE/INSERT behind the scenes?
Do we need to vacuum to reclaim space?
If a query is executed against the view when the refresh is happening, will the query see the data before the refresh started?
Regards,
Jayadevan M <maymala.jayadevan@gmail.com> wrote: > Jayadevan M <maymala.jayadevan@gmail.com> wrote: >> A few questions about materialized views. >> When I refresh a materialized view, is it a DELETE/INSERT behind >> the scenes? With 9.3 it is closer to TRUNCATE/SELECT INTO behind the scenes. In 9.4 (expected to be released next year) the CONCURRENTLY option will cause DELETE and INSERT of a minimal set of rows, not the entire matview. >> Do we need to vacuum to reclaim space? Not with 9.3, although an initial vacuum/analyze after CREATE or REFRESH will set visibility information and statistics. With 9.4 matviews REFRESHed with the CONCURRENTLY option will need normal vacuum maintenance. >> If a query is executed against the view when the refresh is >> happening, will the query see the data before the refresh >> started? With 9.3 it will block, and after the REFRESH completes it will see data as of the start of the REFRESH. With 9.4 a query reading the matview will not be blocked by REFRESH CONCURRENTLY and visibility will be the same as if the refreshing transaction had been a set of DELETE and INSERT statements committed in a single transaction. >> Does the refresh result in exclusive locking? Non-concurrent refresh does. > To answer my own question, I saw this thread - > http://postgresql.1045698.n5.nabble.com/Efficiency-of-materialized-views-refresh-in-9-3-td5762618.html > I think that does answer my questions. Nothing has changed? There have been some bug fixes recently, but other than that new behaviors are only allowed in major releases (where the number changes to the left of the second dot). -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company