Re: UPDATE many records - Mailing list pgsql-general
From | Israel Brewster |
---|---|
Subject | Re: UPDATE many records |
Date | |
Msg-id | 08A74B2D-3363-43ED-A919-10877C7E4E5A@alaska.edu Whole thread Raw |
In response to | Re: UPDATE many records (Adrian Klaver <adrian.klaver@aklaver.com>) |
List | pgsql-general |
> > On Jan 7, 2020, at 12:57 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 1/7/20 1:43 PM, Israel Brewster wrote: >>> On Jan 7, 2020, at 12:21 PM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: >>> >>> On 1/7/20 1:10 PM, Israel Brewster wrote: >>>>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: >>>>> >>>>> 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 usedwhile both tables exist? If so, that would prevent this from working - I don’t have that much space available at themoment. >>>>> >>>>> It will definitely increase the disk space by at least the data in the new table. How much relative to the old tableis going to depend on how aggressive the AUTOVACUUM/VACUUM is. >>>>> >>>>> A suggestion for an alternative approach: >>>>> >>>>> 1) Create a table: >>>>> >>>>> create table change_table(id int, changed_fld some_type) >>>>> >>>>> where is is the PK from the existing table. >>>>> >>>>> 2) Run your conversion function against existing table with change to have it put new field value in change_table keyedto id/PK. Probably do this in batches. >>>>> >>>>> 3) Once all the values have been updated, do an UPDATE set changed_field = changed_fld from change_table where existing_table.pk= change_table.id <http://change_table.id>; >>>> Makes sense. Use the fast SELECT to create/populate the other table, then the update can just be setting a value, nothaving to call any functions. From 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 “tuples that are obsoletedby an update”, which is possible. >>> >>> You are not. For a more thorough explanation see: >>> >>> https://www.postgresql.org/docs/12/routine-vacuuming.html#VACUUM-BASICS >>> >>> How much space do you have to work with? >>> >>> To get an idea of the disk space currently used by table see; >>> >>> https://www.postgresql.org/docs/12/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT >> 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 apparentlyNOT going to my DB as I had assumed, but solving that can wait. > > Assuming you are on some form of Linux: > > sudo du -h -d 1 / > > Then you can drill down into the output of above. Yep. Done it many times to discover a runaway log file or the like. Just mildly amusing that solving one problem leads toanother I need to take care of as well… But at least the select into a new table should work nicely. Thanks! --- 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 > >> Thanks again for all the good information and suggestions! >> --- >> 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 >>> >>>>> >>>>>> --- >>>>>> 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 >>>>> >>>>> >>>>> -- >>>>> Adrian Klaver >>>>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> >>> >>> >>> -- >>> Adrian Klaver >>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
pgsql-general by date: