From: | Israel Brewster <ijbrewster(at)alaska(dot)edu> |
---|---|
To: | Alan Hodgson <ahodgson(at)lists(dot)simkin(dot)ca> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: UPDATE many records |
Date: | 2020-01-07 21:20:12 |
Message-ID: | 0477AF8D-AB39-4FCD-A4E7-9E3B3E2358B5@alaska.edu |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Jan 7, 2020, at 12:15 PM, Alan Hodgson <ahodgson(at)lists(dot)simkin(dot)ca> wrote:
>
> On Tue, 2020-01-07 at 11:58 -0900, Israel Brewster wrote:
>>>
>> Really? Why? With the update I am only changing data - I’m not adding
>> any additional data, so the total size should stay the same, right?
>> I’m obviously missing something… :-)
>>
>
> 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.
>
> 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.
>
> 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
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2020-01-07 21:21:52 | Re: UPDATE many records |
Previous Message | Alan Hodgson | 2020-01-07 21:15:33 | Re: UPDATE many records |