On Tue, Jan 07, 2020 at 12:20:12PM -0900, Israel Brewster wrote:On Jan 7, 2020, at 12:15 PM, Alan Hodgson <ahodgson@lists.simkin.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