refresh concurrently: limitation of diff refreshing

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: postgresql(dot)org(at)stelb(dot)de
Subject: refresh concurrently: limitation of diff refreshing
Date: 2021-08-23 15:46:17
Message-ID: 162973357793.28128.505258270982042855@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/sql-refreshmaterializedview.html
Description:

Hi,

it may seem obvious, but it took us TB of WALs and some time to identify the
problem, why a bigger materialized view created much more WALs than a normal
refresh.

A concurrent refresh needs at least one unique index
and no column of these indexes should be null: unique columns with null
cannot identified as unchanged and will be replaced.

I am not sure about the wording, but there should be a hint that this can be
a problem.
This might be ok for only a few rows but we had >14.000.000 rows and only a
few thousand had all values set for the unique index. Hourly refresh, TB of
WAL/day for just 4GB of database :-(

This is at least valid for 11 - 13

Thanks :)

Stefan

Browse pgsql-docs by date

  From Date Subject
Next Message Dave Cramer 2021-08-27 13:17:28 Background worker docs,
Previous Message David G. Johnston 2021-08-19 23:47:32 Re: Unclear\mistakable description of UPDATE behaviour in "13.2.1. Read Committed Isolation Level"