27

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?

Community
  • 1
  • 1
Pavel V.
  • 2,653
  • 10
  • 43
  • 74
  • 2
    Can you provide an example of what you mean? I thought the point of having a materialized view was to show only part of the data. – Lucas Sep 03 '14 at 09:54
  • That answer you linked to for Oracle is not very compelling. It says you can forcefully swap out one of the partitions for a partitioned MV. Since partitioning in Postgresql is not really a built-in feature, but something you have roll yourself, I suppose you could get the same approach working in Postgres as well. – Thilo Sep 03 '14 at 09:58

2 Answers2

30

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • worth mentioning this new feature under development: https://wiki.postgresql.org/wiki/Incremental_View_Maintenance – acristu Jan 15 '22 at 22:43
  • 4
    is this answer still applicable to 14.5? – PirateApp Sep 12 '22 at 03:31
  • 1
    Yes, unfortunately, it seems that it's still all or nothing According to the docs I link below "REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. " See https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html – Shmuel Kamensky Dec 05 '22 at 10:12
6

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.)

Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96
n1000
  • 5,058
  • 10
  • 37
  • 65
  • 2
    unfortunately that is only helpful for "partitioned columns", not for "partitioned rows" (that are not necessarily mapped to simple definable partitions and rather to individual rows) – Andreas Covidiot Mar 21 '16 at 19:20
  • 1
    @AndreasDietrich use `union all` over a bunch of materialized views, one for each partition, can work ok if the partition list is relatively fixed. It won't help in every use case, but it can ok well for materialized views which are aggregates. – Barry Kelly May 11 '20 at 12:33
  • @n1000: Hey, I just realized, that I edited your answer which I thought was mine :/ ... sorry if the edit would be not fine with you ... then let's revert it and I'll post an own version with example as a separate answer. – Andreas Covidiot May 19 '20 at 10:36