Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?
Consider this table & materialized view:
CREATE TABLE graph ( xaxis integer NOT NULL, value integer NOT NULL, ); CREATE MATERIALIZED VIEW graph_avg AS SELECT xaxis, AVG(value) FROM graph GROUP BY xaxis
Periodically, new values are added to
graph or an existing value is updated. I want to refresh the view
graph_avg every couple of hours only for the values that have updated. However in PostgreSQL 9.3, the whole table is refreshed. This is quite time consuming. The next version 9.4 allows
CONCURRENT update but it still refreshes the entire view. With 100s of millions of rows, this takes a few minutes.
What's a good way to keep track of updated & new values and only refresh the view partially?