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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Understanding WAL - large amount of activity from removing data
Дата
Msg-id CAKFQuwY6nKw38LT7bsyiZ-db7+ALbSO1gKc-vFWC7BL_zYqxwQ@mail.gmail.com
обсуждение исходный текст
Ответ на Understanding WAL - large amount of activity from removing data  (Isaac Morland <isaac.morland@gmail.com>)
Ответы Re: Understanding WAL - large amount of activity from removing data  (Andres Freund <andres@anarazel.de>)
Список pgsql-hackers
Дерево обсуждения
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.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Isaac Morland
Дата:
Сообщение: Understanding WAL - large amount of activity from removing data
Следующее
От: Kyotaro Horiguchi
Дата:
Сообщение: Re: when the startup process doesn't (logging startup delays)