Re: Refresh Materialized View

Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)com> wrote:
> Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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