I don't have a "best" answer, but I have a "least bad" answer that might let you get things done reasonably fast.
My table had 2MM rows and the update performance was chugging when I tried to add a secondary timestamp column that defaulted to the first.
ALTER TABLE mytable ADD new_timestamp TIMESTAMP ;
UPDATE mytable SET new_timestamp = old_timestamp ;
ALTER TABLE mytable ALTER new_timestamp SET NOT NULL ;
After it hung for 40 minutes, I tried this on a small batch to get an idea of how long this could take -- the forecast was around 8 hours.
The accepted answer is definitely better -- but this table is heavily used in my database. There are a few dozen tables that FKEY onto it; I wanted to avoid switching FOREIGN KEYS on so many tables. And then there are views.
A bit of searching docs, case-studies and StackOverflow, and I had the "A-Ha!" moment. The drain wasn't on the core UPDATE, but on all the INDEX operations. My table had 12 indexes on it -- a few for unique constraints, a few for speeding up the query planner, and a few for fulltext search.
Every row that was UPDATED wasn't just working on a DELETE/INSERT, but also the overhead of altering each index and checking constraints.
My solution was to drop every index and constraint, update the table, then add all the indexes/constraints back in.
It took about 3 minutes to write a SQL transaction that did the following:
- dropped indexes/constaints
- update table
- re-add indexes/constraints
The script took 7 minutes to run.
The accepted answer is definitely better and more proper... and virtually eliminates the need for downtime. In my case though, it would have taken significantly more "Developer" work to use that solution and we had a 30 minute window of scheduled downtime that it could be accomplished in. Our solution addressed it in 10.