From: | Israel Brewster <ijbrewster(at)alaska(dot)edu> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Mark Zellers <markz(at)adaptiveinsights(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: UPDATE many records |
Date: | 2020-01-07 21:43:47 |
Message-ID: | A7813C13-3B77-4F22-87F1-7B1A94751EB1@alaska.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Jan 7, 2020, at 12:21 PM, Adrian Klaver <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> 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;
>> 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 <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 <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.
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
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2020-01-07 21:57:44 | Re: UPDATE many records |
Previous Message | Adrian Klaver | 2020-01-07 21:21:52 | Re: UPDATE many records |