postgres materialized view refresh performance

From: Ayub M <hiayub(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: postgres materialized view refresh performance
Date: 2020-10-22 19:53:59
Message-ID: CAOS0qEs1FJ6o=kPh80Vk0+i22behSY9VkSHRCknEa2jgGXyJ8Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

There is a table t which is used in a mview mv, this is the only table in
the mview definition.

create table t (c1 int, ..., c10 int);-- there is a pk on say c1
columncreate materialized view mv as select c1, c2...c10 from
t;---there is a unique index on say c5 and bunch of other indexes on
the mview.

The reason there is a mview created instead of using table t, is that that
the table gets truncated and reloaded every couple of hours and we don't
want users to see an empty table at any point of time that's why mview is
being used.

Using "refresh materialized view concurrently", this mview is being used by
APIs and end users.

Couple of questions I have -

1. Whenever mview refresh concurrently happens, does pg create another
set of table and indexes and switch it with the orig? If no, then does it
update the existing data?
2. If the usage of mview is pretty heavy does it impact the performance
of the refresh process? Vice-versa, if the refresh is going on does the
performance of mview by users take a hit?
3. The mview gets refreshed in a couple of mins sometimes and sometimes
it takes hours. When it runs for longer, there are no locks and no resource
shortage, the number of recs in the base table is 6m (7.5gb) which is not
huge so why does it take so long to refresh the mview?
4. Does mview need vacuum/analyze/reindex?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark Johnson 2020-10-22 20:21:10 Re: Hot backup in PostgreSQL
Previous Message Adam Brusselback 2020-10-22 15:13:39 Re: Hot backup in PostgreSQL