Refresh materialized views with concurrency

20
votes

I have a PostgreSQL DB, where I use materialized views. The problem occurs when I try to refresh these materialized views. 

REFRESH MATERIALIZED VIEW product_cat_mview;
REFRESH MATERIALIZED VIEW productsforproject;

My solution is, when the user want to see updated data, he should click a "refresh button" on the web page, but this takes about 50s (on a local connection and about 2 minutes from the application server) and all this time the user has to wait, which is not good.

Now I should create a solution to automatically refresh these materialized views every 10 minutes. I have created a Java solution with multithreading. But I have one problem.

The first query

REFRESH MATERIALIZED VIEW CONCURRENTLY product_cat_mview;

works correct, but the second

REFRESH MATERIALIZED VIEW CONCURRENTLY productsforproject;

complains that I need to create a unique index. I tried create index, unique index etc. that I found in google, but I still get the message to "Create unique index".

1
postgresqlmaterialized-views
Does this error get raised when you run the refreshes one by one? – David Aldridge
I run them one by one with delay of 30 seconds. – Volodymyr Zavada

1 Answers

26
votes

You will have to create a unique index on the materialized view itself.

This would look like this:

CREATE UNIQUE INDEX ON productsforproject (id);

Replace id with a suitable unique key column or a (comma separated) combination of such columns.