Re: UPDATE many records

On 1/7/20 1:10 PM, Israel Brewster wrote:
> 
>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver <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
whileboth 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
isgoing 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;
 
> 
> Makes sense. Use the fast SELECT to create/populate the other table, then the update can just be setting a value, not
havingto 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

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com