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

From: Andres Freund <andres(at)anarazel(dot)de>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Isaac Morland <isaac(dot)morland(at)gmail(dot)com>, PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Understanding WAL - large amount of activity from removing data
Date: 2022-11-21 20:40:49
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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

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


Andres Freund

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2022-11-21 20:45:39 Re: Proposal: Allow user with pg_monitor role to call pg_stat_reset* functions
Previous Message Robert Haas 2022-11-21 20:39:46 fixing CREATEROLE