Re: UPDATE many records

On 1/7/20 12:58 PM, Israel Brewster wrote:
>> On Jan 7, 2020, at 11:56 AM, Alan Hodgson <[email protected]> wrote:
>>
>> On Tue, 2020-01-07 at 11:47 -0900, 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.
>>
>> The original update you planned would do that, too.
>>
>> You probably need to just do the update in batches and vacuum the table
>> between batches.
>>
>>
> 
> Really? Why? With the update I am only changing data - I’m not adding any additional data, so the total size should stay the same, right? I’m obviously missing something… :-)

https://www.postgresql.org/docs/12/sql-vacuum.html

"VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL 
operation, tuples that are deleted or obsoleted by an update are not 
physically removed from their table; they remain present until a VACUUM 
is done. Therefore it's necessary to do VACUUM periodically, especially 
on frequently-updated tables."

> 
> ---
> 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
[email protected]