On 1/7/20 1:43 PM, Israel Brewster wrote:
On Jan 7, 2020, at 12:21 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
On 1/7/20 1:10 PM, Israel Brewster wrote:
On Jan 7, 2020, at 12:01 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
