Re: Understanding WAL - large amount of activity from removing data

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