Re: UPDATE many records

> On Jan 7, 2020, at 12:15 PM, Alan Hodgson <[email protected]> =
wrote:
>=20
> On Tue, 2020-01-07 at 11:58 -0900, Israel Brewster wrote:
>>>=20
>> Really? Why? With the update I am only changing data - I=E2=80=99m =
not adding
>> any additional data, so the total size should stay the same, right?
>> I=E2=80=99m obviously missing something=E2=80=A6 :-)
>>=20
>=20
> PostgreSQL keeps the old row until it gets vacuumed, as it needs to be
> visible to other transactions. Not only that, but every index record
> gets updated to point to the location of the new data row too =
(excluding
> HOT), and those old index blocks also need to get vacuumed. And none =
of
> those rows can get removed until your update finishes.
>=20
> I know this isn't universally true with HOT and fillfactor etc. but =
with
> an update this big I think it's safe to say most of the space will get
> doubled.
>=20
> Plus you'll get a ton of write-ahead logs.

Gotcha. Batches with VACUUM it is! Thanks for the info.

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory=20
Geophysical Institute - UAF=20
2156 Koyukuk Drive=20
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

>=20
>=20
>=20