Re: UPDATE many records

On Jan 7, 2020, at 12:21 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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

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