| From: | Alan Hodgson <ahodgson(at)lists(dot)simkin(dot)ca> |
|---|---|
| To: | "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:15:33 |
| Message-ID: | 365cae454cea40ca594e9b4b899fb82958f3e17d.camel@lists.simkin.ca |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Israel Brewster | 2020-01-07 21:20:12 | Re: UPDATE many records |
| Previous Message | Israel Brewster | 2020-01-07 21:10:05 | Re: UPDATE many records |