Hi,
I have two tables each with nearly 300M rows. There is a 1:1
relationship between the two tables and they are almost always joined
together in queries. The first table has many columns, the second has
a foreign key to the primary key of the first table and one more
column. It is expected that for every row in table1, there is a
corresponding row in table2. We would like to just add the one column
to the first table and drop the second table to allow us to index this
extra column.
This query would work after adding the column to the first table:
UPDATE table1 SET new_column = table2.new_column FROM table2 WHERE
table1.row_id = table2.row_id;
However, this will take much too long, I have not successfully
completed this on our staging server after running it for 3+ days.
Any extended down time is not really an option. Further, there are
many other tables with foreign keys to table1 so dropping it is fairly
complicated and time consuming as the indexes and foreign keys would
all have to be regenerated.
Does anyone have any other ideas on how this can be done in the most
efficient way possible?
Thanks,
--
Joshua Rubin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general