Is it possible to partially refresh a materialized view in PostgreSQL?

Question

In Oracle, it is possible to refresh just part of the data. But in PostgreSQL, materialized views are supported since 9.3 (the current version now), which is not so long. So I wonder: is it possible to refresh just part of the data in the materialized view in PostgreSQL 9.3? If yes, how to do it?

Answer 1

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.

Answer 2

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:

1. 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 common id column.
2. Use a regular VIEW (fake_materialized_view) joining the MATERIALIZED VIEWs tables on the id column
   • in the case of disjunct rows one has to union all them
3. REFRESH MATERIALIZED VIEW as needed
4. 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.)