> On Jan 7, 2020, at 12:21 PM, Adrian Klaver <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> >>> 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; >> 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 > <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 > > <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. 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 > > > -- > Adrian Klaver > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
