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

postgresql postgresql-9.3 materialized-views
Pavel V. picture Pavel V. · Sep 3, 2014 · Viewed 9.1k times · Source

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

Craig Ringer picture Craig Ringer · Sep 3, 2014

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.

Related questions

Refresh a materialized view automatically using a rule or notify

I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I'd like to update its data promptly. Here is what I was thinking about so far: There is a materialized …

Read More
How to refresh all materialized views in Postgresql 9.3 at once?

I am loading a bunch of data into a PostgresQL 9.3 database and then I want to refresh all materialized views that depend on the updated tables. Is there a way to do it automatically instead of going through each view …

Read More
How do I modify fields inside the new PostgreSQL JSON datatype?

With postgresql 9.3 I can SELECT specific fields of a JSON data type, but how do you modify them using UPDATE? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious: postgres=# …

Read More