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?
Does the refresh result in exclusive locking?
Regards,
Jayadevan
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?
Regards,
Jayadevan
On Wed, Jan 1, 2014 at 6:42 PM, Jayadevan M <maymala.jayadevan@gmail.com>wrote:
Show quoted text
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?
Does the refresh result in exclusive locking?
Regards,
Jayadevan
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general