Re: UPDATE many records
Israel Brewster <[email protected]> Fri, 10 Jan 2020 08:39:27 -0900
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_67F6148C-CFC3-45CC-AFFB-24F81B4581A6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 8, 2020, at 7:52 AM, stan <[email protected]> wrote: >=20 > On Tue, Jan 07, 2020 at 12:20:12PM -0900, Israel Brewster wrote: >>> 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???m not = adding >>>> any additional data, so the total size should stay the same, right? >>>> I???m obviously missing something??? :-) >>>>=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. >>=20 >> Gotcha. Batches with VACUUM it is! Thanks for the info. >>=20 > I'd love to see you report on how this went.=20 So after determining that I did, in fact, have enough disk space to = duplicate the data, I moved forward with the CREATE TABLE =E2=80=A6 AS = SELECT =E2=80=A6. 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=E2=80=A6RENAME TO=E2=80=A6 = 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=E2=80=99t have gone much smoother or quicker. = Thanks all for the assistance and advice! --- 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 > "They that would give up essential liberty for temporary safety = deserve > neither liberty nor safety." > -- Benjamin Franklin --Apple-Mail=_67F6148C-CFC3-45CC-AFFB-24F81B4581A6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; = charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;" = class=3D""><div><blockquote type=3D"cite" class=3D""><div class=3D"">On = Jan 8, 2020, at 7:52 AM, stan <<a href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:</div><br = class=3D"Apple-interchange-newline"><div class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">On Tue, Jan 07, 2020 at = 12:20:12PM -0900, Israel Brewster wrote:</span><br style=3D"caret-color: = rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: = normal; font-variant-caps: normal; font-weight: normal; letter-spacing: = normal; text-align: start; text-indent: 0px; text-transform: none; = white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><blockquote type=3D"cite" = style=3D"font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = orphans: auto; text-align: start; text-indent: 0px; text-transform: = none; white-space: normal; widows: auto; word-spacing: 0px; = -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><blockquote type=3D"cite" class=3D"">On= Jan 7, 2020, at 12:15 PM, Alan Hodgson <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:<br class=3D""><br = class=3D"">On Tue, 2020-01-07 at 11:58 -0900, Israel Brewster wrote:<br = class=3D""><blockquote type=3D"cite" class=3D""><blockquote type=3D"cite" = class=3D""><br class=3D""></blockquote>Really? Why? With the update I am = only changing data - I???m not adding<br class=3D"">any additional data, = so the total size should stay the same, right?<br class=3D"">I???m = obviously missing something??? :-)<br class=3D""><br = class=3D""></blockquote><br class=3D"">PostgreSQL keeps the old row = until it gets vacuumed, as it needs to be<br class=3D"">visible to other = transactions. Not only that, but every index record<br class=3D"">gets = updated to point to the location of the new data row too (excluding<br = class=3D"">HOT), and those old index blocks also need to get vacuumed. = And none of<br class=3D"">those rows can get removed until your update = finishes.<br class=3D""><br class=3D"">I know this isn't universally = true with HOT and fillfactor etc. but with<br class=3D"">an update this = big I think it's safe to say most of the space will get<br = class=3D"">doubled.<br class=3D""><br class=3D"">Plus you'll get a ton = of write-ahead logs.<br class=3D""></blockquote><br class=3D"">Gotcha. = Batches with VACUUM it is! Thanks for the info.<br class=3D""><br = class=3D""></blockquote><span style=3D"caret-color: rgb(0, 0, 0); = font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none; float: none; display: inline !important;" = class=3D"">I'd love to see you report on how this went.<span = class=3D"Apple-converted-space"> </span></span><br = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" = class=3D""></div></blockquote><div><br class=3D""></div><div>So after = determining that I did, in fact, have enough disk space to duplicate the = data, I moved forward with the CREATE TABLE =E2=80=A6 AS SELECT =E2=80=A6.= 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=E2=80=A6RENAME TO=E2=80=A6 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.</div><div><br = class=3D""></div><div>In the end, really couldn=E2=80=99t have gone much = smoother or quicker. Thanks all for the assistance and = advice!</div><div>---</div><div>Israel Brewster<br class=3D"">Software = Engineer<br class=3D"">Alaska Volcano Observatory <br = class=3D"">Geophysical Institute - UAF <br class=3D"">2156 Koyukuk = Drive <br class=3D"">Fairbanks AK 99775-7320</div><div>Work: = 907-474-5172<br class=3D"">cell: 907-328-9145</div><blockquote = type=3D"cite" class=3D""><div class=3D""><br style=3D"caret-color: = rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: = normal; font-variant-caps: normal; font-weight: normal; letter-spacing: = normal; text-align: start; text-indent: 0px; text-transform: none; = white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;" class=3D""><span style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none; float: none; display: inline !important;" = class=3D"">--<span class=3D"Apple-converted-space"> </span></span><br= style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">"They that would give up = essential liberty for temporary safety deserve</span><br = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;" class=3D""><span = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none; float: none; = display: inline !important;" class=3D"">neither liberty nor = safety."</span><br style=3D"caret-color: rgb(0, 0, 0); font-family: = Helvetica; font-size: 12px; font-style: normal; font-variant-caps: = normal; font-weight: normal; letter-spacing: normal; text-align: start; = text-indent: 0px; text-transform: none; white-space: normal; = word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: = none;" class=3D""><span class=3D"Apple-tab-span" style=3D"caret-color: = rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: = normal; font-variant-caps: normal; font-weight: normal; letter-spacing: = normal; text-align: start; text-indent: 0px; text-transform: none; = white-space: pre; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;"> </span><span class=3D"Apple-tab-span" = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: pre; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;"> = </span><span class=3D"Apple-tab-span" style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = pre; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: = none;"> </span><span class=3D"Apple-tab-span" style=3D"caret-color: = rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: = normal; font-variant-caps: normal; font-weight: normal; letter-spacing: = normal; text-align: start; text-indent: 0px; text-transform: none; = white-space: pre; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;"> </span><span class=3D"Apple-tab-span" = style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: = 12px; font-style: normal; font-variant-caps: normal; font-weight: = normal; letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: pre; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;"> = </span><span class=3D"Apple-tab-span" style=3D"caret-color: rgb(0, 0, = 0); font-family: Helvetica; font-size: 12px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = pre; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: = none;"> </span><span style=3D"caret-color: rgb(0, 0, 0); font-family: = Helvetica; font-size: 12px; font-style: normal; font-variant-caps: = normal; font-weight: normal; letter-spacing: normal; text-align: start; = text-indent: 0px; text-transform: none; white-space: normal; = word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: = none; float: none; display: inline !important;" class=3D"">-- Benjamin = Franklin</span></div></blockquote></div><br class=3D""></body></html>= --Apple-Mail=_67F6148C-CFC3-45CC-AFFB-24F81B4581A6--