Re: UPDATE many records

>
> 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