How to alter/migrate the schema of a table with tons of records I have a table which has about 1 million records, and recently i'm going to ship a new feature which requires 2 new fields. But I found it takes about minutes to get it finish, and during the migration, i have to shutdown the service, as the table was locked.

Is there any ideal way to do such kind of jobs without making the online service offline?

Thanks.
What I do in this case is:


create a shadow table with the new schema
move over all the data
xlock the original table
apply the changes since moving the data
rename the original table to bak
rename the "new" table to the correct name


This makes your downtime minimal at the cost of disk space.