5

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.

5

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.

  • Oh, so in this case using a MV has not advantage over a simple Table. – Gabriel A. Zorrilla Jan 8 '18 at 20:29
  • 1
    Has a major disadvantage if the benefit you're seeking is an "incremental refresh", namely that you can't do it at all. ;) – Evan Carroll Jan 8 '18 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.