Re: UPDATE many records

> On Jan 8, 2020, at 7:52 AM, stan <stanb(at)panix(dot)com> wrote:
>
> On Tue, Jan 07, 2020 at 12:20:12PM -0900, Israel Brewster wrote:
>>> 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.
>>
> I'd love to see you report on how this went.

So after determining that I did, in fact, have enough disk space to duplicate the data, I moved forward with the CREATE TABLE … AS SELECT …. Method. Running the CREATE TABLE command took around 12 minutes for my almost 64million rows. I then created indexes/set constraints/set defaults, etc on the new table until it exactly matched the old one (other than the changed data, of course). This probably took another 5-10 minutes. Two quick ALTER TABLE…RENAME TO… commands later, and the new data was live. The only issue I ran into was initially forgetting to grant the application user permissions on the new table, but of course that was easily remedied.

In the end, really couldn’t have gone much smoother or quicker. Thanks all for the assistance and advice!
---
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
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin