Re: UPDATE many records

On 1/7/20 12:58 PM, Israel Brewster wrote:
>> On Jan 7, 2020, at 11:56 AM, Alan Hodgson <ahodgson(at)lists(dot)simkin(dot)ca> 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
adrian(dot)klaver(at)aklaver(dot)com