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

On Sun, Nov 20, 2022 at 6:24 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)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.