mardi 23 décembre 2014

Refresh materalized view incrementally in PostgreSQL


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?





Aucun commentaire:

Enregistrer un commentaire