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
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" |