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(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 1/7/20 1:10 PM, Israel Brewster wrote:
>>>> On Jan 7, 2020, at 12:01 PM, Adrian Klaver
>>>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com