Re: UPDATE many records

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

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