Is it possible to partially refresh a materialized view in PostgreSQL?
PostgreSQL doesn't support progressive / partial updates of materialized views yet.
9.4 adds REFRESH MATERIALIZED VIEW CONCURRENTLY
but it still has to be regenerated entirely.
Hopefully we'll see support in 9.5 if someone's enthusiastic enough. It's only possible to do this without user-defined triggers/rules for simple materialized views though, and special support would be needed to even handle things like incremental update of a count(...) ... GROUP BY ...
.
The Oracle answer you refer to isn't actually incremental refresh, though. It's refresh by-partitions. For PostgreSQL to support that natively, it'd first have to support real declarative partitioning - which it doesn't, though we're discussing whether it can be done for 9.5.
I just came across a similar problem. Learning from Craig's answer that it is not possible, I used a workaround. I deconstructed the materialized view and joined and/or unioned the individual parts in a VIEW
:
- Create a
MATERIALIZED VIEW
for each row or column group in question (material_col1
,material_col2
, etc. or with more complex disjunct where conditions), using e.g. a commonid
column. - Use a regular
VIEW
(fake_materialized_view
)join
ing theMATERIALIZED VIEW
s tables on theid
column- in the case of disjunct rows one has to
union all
them
- in the case of disjunct rows one has to
REFRESH MATERIALIZED VIEW
as needed- Use your query on
fake_materialized_view
instead
The VIEW
would look somewhat like this:
CREATE VIEW fake_materialized_view AS
SELECT m1.id, m1.col1, m2.col2
FROM material_col1 as m1 LEFT JOIN
material_col2 as m2
ON m1.id = m2.id
-- in case of additional row partitioning, e.g.
-- UNION ALL SELECT m3.id, m3.col1, m3.col2
-- FROM material_col3 m3
(Upd1: Thx to Barry for his comment utilizing row partitioning, which I added to the answer.)