I needed to expand the size of one of the varchar columns in a table of my 135GB database. To do this, I used the following command:
ALTER TABLE mb_fix_message RENAME COLUMN mb_symbol TO mb_symbol_old;
ALTER TABLE mb_fix_message ADD COLUMN mb_symbol VARCHAR(25);
UPDATE mb_fix_message SET mb_symbol = mb_symbol_old;
ALTER TABLE mb_fix_message DROP COLUMN mb_symbol;
During the update I ran an analyze and got the following output:
INFO: analyzing "public.mb_fix_message"
INFO: "mb_fix_message": 12502398 pages, 3000 rows sampled, 176684832 estimated total rows
The update has been running for 26 hours now. My scheduled nightly vacuum ran and took about 12 hours and finally finished this morning. The symbol fields (old and new) are not indexed. Is there anything I can do to see how much has been completed / how long this should take?
Will this affect insert performance in my table tomorrow when users begin to insert using transactions?
Sorry for all the questions, any help would be greatly appreciated.
Thanks,
Andrew Janian
Scottrade, Inc.