> > On Jan 7, 2020, at 12:57 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 1/7/20 1:43 PM, Israel Brewster wrote: >>> On Jan 7, 2020, at 12:21 PM, Adrian Klaver <adrian.kla...@aklaver.com >>> <mailto:adrian.kla...@aklaver.com>> wrote: >>> >>> On 1/7/20 1:10 PM, Israel Brewster wrote: >>>>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver <adrian.kla...@aklaver.com >>>>> <mailto:adrian.kla...@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 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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >>> >>> >>> -- >>> Adrian Klaver >>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com
