> > On Jan 7, 2020, at 12:57 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > > On 1/7/20 1:43 PM, Israel Brewster wrote: >>> On Jan 7, 2020, at 12:21 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote: >>> >>> On 1/7/20 1:10 PM, Israel Brewster wrote: >>>>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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 used while both tables exist? If so, that would prevent this from working - I don’t have that much space available at the moment. >>>>> >>>>> 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. >>>>> >>>>> 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 keyed to 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, not having 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 obsoleted by 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 apparently NOT 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 to another 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> >>> >>> >>> -- >>> Adrian Klaver >>> adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx
