Basically my question is the same as this one, but WITH dependencies, so drop/renaming the table is not a trivial option (I assume).
We are refactoring a large, poorly designed table which has many columns and references to it. It currently has a text field that should be a foreign key. The naive update looks like:
ALTER TABLE ADD COLUMN new_id int REFERENCES list(id);
UPDATE myTable SET new_id=(SELECT id FROM list WHERE name=old_text);
ALTER TABLE myTable DROP COLUMN old_text;
The above takes practically forever because the table is large, and basically gets temporarily doubled due to UPDATE
being equivalent to INSERT
/DELETE
.
We do not need everything done in one transaction. So we are considering some sort of external script to do the updates in batches of 5000 or so, but tests indicate it will still be painful/slow.
Suggestions on how to improve performance?