How to do an incremental refresh of a PostgreSQL Materialized View?

I'm looking at the PostgreSQL docs where:


REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded.

However, since 9.5 I believe, there is a way to update a MV without having to regenerate all the data.

There is no INCREMENTAL, it's very simple.. From the docs

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

Perhaps you're confusing it with CONCURRENTLY which is about concurrency (locking) and not minimizing updates.


Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer resources and complete more quickly, but could block other connections which are trying to read from the materialized view. This option may be faster in cases where a small number of rows are affected.

If you need an incremental refresh, use a table to store the results and join it against the source inserting only rows that NOT EXISTS,

INSERT INTO materialized_resultset
SELECT foo,bar,baz
FROM src
WHERE NOT EXISTS (
  SELECT 1
  FROM materialized_resultset AS mr
  WHERE src.foo = mr.foo
);

or use the newer ON CONFLICT UPDATE or ON CONFLICT DO NOTHING on INSERT.

StackExchange Database Administrators Q#194905, answer score: 15