Thread: Understanding WAL - large amount of activity from removing data
I'm encountering some surprising (to me) behaviour related to WAL, and I'm wondering if anybody can point me at an article that might help me understand what is happening, or give a brief explanation.
I'm trying to make a slimmed down version of my database for testing purposes. As part of this, I'm running a query something like this:
UPDATE table1
SET pdfcolumn = 'redacted'
WHERE pdfcolumn IS NOT NULL;
(literally 'redacted', not redacted here for your benefit)
The idea is to replace the actual contents of the column, which are PDF documents totalling 70GB, with just a short placeholder value, without affecting the other columns, which are a more ordinary collection - a few integers and short strings.
The end result will be a database which is way easier to copy around but which still has all the records of the original; the only change is that an attempt to access one of the PDFs will not return the actual PDF but rather a garbage value. For most testing this will make little to no difference.
What I'm finding is that the UPDATE is taking over an hour for 5000 records, and tons of WAL is being generated, several files per minute. Selecting the non-PDF columns from the entire table takes a few milliseconds, and the only thing I'm doing with the records is updating them to much smaller values. Why so much activity just to remove data? The new rows are tiny.
On Sun, Nov 20, 2022 at 6:24 PM Isaac Morland <isaac.morland@gmail.com> wrote:
What I'm finding is that the UPDATE is taking over an hour for 5000 records, and tons of WAL is being generated, several files per minute. Selecting the non-PDF columns from the entire table takes a few milliseconds, and the only thing I'm doing with the records is updating them to much smaller values. Why so much activity just to remove data? The new rows are tiny.
Simplistic answer (partly because the second part of this isn't spelled out explicitly in the docs that I could find) when you UPDATE two things happen, the old record is modified to indicate it has been deleted and a new record is inserted. Both of these are written to the WAL, and a record is always written to the WAL as a self-contained unit, so the old record is full sized in the newly written WAL. TOAST apparently has an optimization if you don't change the TOASTed value, but here you are so that optimization doesn't apply.
David J.
Hi,
On 2022-11-20 19:02:12 -0700, David G. Johnston wrote:
> Both of these are written to the WAL, and a record is always written
> to the WAL as a self-contained unit, so the old record is full sized
> in the newly written WAL.
That's not really true. Normally the update record just logs the xmax,
offset, infomask for the old tuple. However, full_page_writes can lead
to the old tuple's whole page to be logged.
We do log the old tuple contents if the replica identity of the table is
set to 'FULL' - if you're using that, we'll indeed log the whole old
version of the tuple to the WAL.
I think the more likely explanation in this case is that deleting the
toast values with the PDF - which is what you're doing by updating the
value to = 'redacted' - will have to actually mark all those toast
tuples as deleted. Which then likely is causing a lot of full page
writes.
In a case like this you might have better luck forcing the table to be
rewritten with something like
ALTER TABLE tbl ALTER COLUMN data TYPE text USING ('redacted');
which should just drop the old toast table, without going through it
one-by-one.
Greetings,
Andres Freund