Re: UPDATE many records
Israel Brewster <[email protected]> Tue, 7 Jan 2020 12:43:47 -0900
| Newsgroups | gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_69A8F739-461B-4E99-9E44-EE48639290F9 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 7, 2020, at 12:21 PM, Adrian Klaver <[email protected]> = wrote: >=20 > On 1/7/20 1:10 PM, Israel Brewster wrote: >>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver = <[email protected]> wrote: >>>=20 >>> On 1/7/20 12:47 PM, Israel Brewster wrote: >>>> One potential issue I just thought of with this approach: disk = space. Will I be doubling the amount of space used while both tables = exist? If so, that would prevent this from working - I don=E2=80=99t = have that much space available at the moment. >>>=20 >>> It will definitely increase the disk space by at least the data in = the new table. How much relative to the old table is going to depend on = how aggressive the AUTOVACUUM/VACUUM is. >>>=20 >>> A suggestion for an alternative approach: >>>=20 >>> 1) Create a table: >>>=20 >>> create table change_table(id int, changed_fld some_type) >>>=20 >>> where is is the PK from the existing table. >>>=20 >>> 2) Run your conversion function against existing table with change = to have it put new field value in change_table keyed to id/PK. Probably = do this in batches. >>>=20 >>> 3) Once all the values have been updated, do an UPDATE set = changed_field =3D changed_fld from change_table where existing_table.pk = =3D change_table.id; >> Makes sense. Use the fast SELECT to create/populate the other table, = then the update can just be setting a value, not having to call any = functions. =46rom what you are saying about updates though, I may still = need to batch the UPDATE section, with occasional VACUUMs to maintain = disk space. Unless I am not understanding the concept of =E2=80=9Ctuples = that are obsoleted by an update=E2=80=9D, which is possible. >=20 > You are not. For a more thorough explanation see: >=20 > = https://www.postgresql.org/docs/12/routine-vacuuming.html#VACUUM-BASICS = <https://www.postgresql.org/docs/12/routine-vacuuming.html#VACUUM-BASICS> >=20 > How much space do you have to work with? >=20 > To get an idea of the disk space currently used by table see; >=20 > = https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-DB= OBJECT = <https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-D= BOBJECT> Oh, ok, I guess I was being overly paranoid on this front. Those = functions would indicate that the table is only 7.5 GB, with another = 8.7GB of indexes, for a total of around 16GB. So not a problem after all = - I have around 100GB available. Of course, that now leaves me with the mystery of where my other 500GB = of disk space is going, since it is apparently NOT going to my DB as I = had assumed, but solving that can wait. Thanks again for all the good information and suggestions! --- 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 >>>> --- >>>> 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 >>>=20 >>>=20 >>> --=20 >>> Adrian Klaver >>> [email protected] >=20 >=20 > --=20 > Adrian Klaver > [email protected] <mailto:[email protected]> --Apple-Mail=_69A8F739-461B-4E99-9E44-EE48639290F9 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 7, 2020, at 12:21 PM, Adrian Klaver <<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 1/7/20 1:10 PM, 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:01 PM, Adrian Klaver <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:<br class=3D""><br = class=3D"">On 1/7/20 12:47 PM, Israel Brewster wrote:<br = class=3D""><blockquote type=3D"cite" class=3D"">One potential issue I = just thought of with this approach: disk space. Will I be doubling the = amount of space used while both tables exist? If so, that would prevent = this from working - I don=E2=80=99t have that much space available at = the moment.<br class=3D""></blockquote><br class=3D"">It will definitely = increase the disk space by at least the data in the new table. How much = relative to the old table is going to depend on how aggressive the = AUTOVACUUM/VACUUM is.<br class=3D""><br class=3D"">A suggestion for an = alternative approach:<br class=3D""><br class=3D"">1) Create a table:<br = class=3D""><br class=3D"">create table change_table(id int, changed_fld = some_type)<br class=3D""><br class=3D"">where is is the PK from the = existing table.<br class=3D""><br class=3D"">2) Run your conversion = function against existing table with change to have it put new field = value in change_table keyed to id/PK. Probably do this in batches.<br = class=3D""><br class=3D"">3) Once all the values have been updated, do = an UPDATE set changed_field =3D changed_fld from change_table where = existing_table.pk =3D <a href=3D"http://change_table.id" = class=3D"">change_table.id</a>;<br class=3D""></blockquote>Makes sense. = Use the fast SELECT to create/populate the other table, then the update = can just be setting a value, not having to call any functions. =46rom = what you are saying about updates though, I may still need to batch the = UPDATE section, with occasional VACUUMs to maintain disk space. Unless I = am not understanding the concept of =E2=80=9Ctuples that are obsoleted = by an update=E2=80=9D, which is possible.<br class=3D""></blockquote><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"">You are not. For a more thorough = explanation see:</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""><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""><a = href=3D"https://www.postgresql.org/docs/12/routine-vacuuming.html#VACUUM-B= ASICS" 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;" = class=3D"">https://www.postgresql.org/docs/12/routine-vacuuming.html#VACUU= M-BASICS</a><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""><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"">How much = space do you have to work with?</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""><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"">To get an idea of the disk space currently used by table = see;</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""><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""><a = href=3D"https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-= ADMIN-DBOBJECT" = class=3D"">https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIO= NS-ADMIN-DBOBJECT</a><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>Oh, ok, I guess I was being overly paranoid on = this front. Those functions would indicate that the table is only 7.5 = GB, with another 8.7GB of indexes, for a total of around 16GB. So not a = problem after all - I have around 100GB available.</div><div><br = class=3D""></div><div>Of course, that now leaves me with the mystery of = where my other 500GB of disk space is going, since it is apparently NOT = going to my DB as I had assumed, but solving that can = wait.</div><div><br class=3D""></div><div>Thanks again for all the good = information and suggestions!</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""><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""><br class=3D""><blockquote= type=3D"cite" class=3D"">---<br class=3D"">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<br class=3D"">Work: = 907-474-5172<br class=3D"">cell: 907-328-9145<br = class=3D""></blockquote><br class=3D""><br class=3D"">--<span = class=3D"Apple-converted-space"> </span><br class=3D"">Adrian = Klaver<br class=3D""><a href=3D"mailto:[email protected]" = class=3D"">[email protected]</a><br = class=3D""></blockquote></blockquote><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""><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"">Adrian Klaver</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""><a = href=3D"mailto:[email protected]" 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;" = class=3D"">[email protected]</a></div></blockquote></div><br = class=3D""></body></html>= --Apple-Mail=_69A8F739-461B-4E99-9E44-EE48639290F9--