Is it safe to run migrations on a live database?

I have a simple rails-backed app running 2-3 million pageviews a day off a Heroku Ronin database. The load on the database is pretty light, though, and it could handle a lot more than we're throwing at it.

Is it safe for me to run a migration to add tables to this database without going into maintenance mode? Also, would it be safe to run a migration to add a few columns to the core table responsible for almost all of the reads and writes?

Downtime is not acceptable, even for a few minutes.

If running migrations live isn't advisable, what I'll probably do is set up a new database, run the migrations on that, write a script to sync th开发者_C百科e two databases, and then point the app at the new one.

But I'd rather avoid that if possible. :)

Sounds like your migration includes:

• adding new tables (perhaps indexes? If so, that could take a bit longer than you might expect)
• adding new columns (default values and/or nullable?)
• wrapping your changes in a transaction (?)

Suggest you gauge the impact that your changes will have on your Prod environment by:

• taking a backup of Prod (with all the Prod data within)
• running your change scripts against that. Time each operation

Balance the 2 points above against the typical read & write load at the time you're expecting to run this (02:00, right?).

Consider a 'soft' downtime by disabling (somehow) write operations to the tables being effected.

Overall (or in general), adding n tables and new nullable columns to an existing table would/could likely be done without any downtime or performance impact.

Always measure the impact your changes will have on a copy of Prod. Measure 'responsiveness' at the time you apply your changes to this copy. Of course this means deploying another copy of your Prod app as well, but the effort would be worthwhile.

Assuming it's a pg database (which it should be for Heroku).

http://www.postgresql.org/docs/current/static/explicit-locking.html

alter table will acquire an access exclusive lock. So, the table will be locked.

On top of this, you will be required to restart the Rails application in order for it to be aware of any new models. If you are going to be adding tables to the application or modifying model code in any way.

As for pointing to a new app with a freshly modified database, how are you going to do the sync of the data and also sync the changes in data between the two databases in the time that the sync takes?

Adding tables shouldn't be a concern, as your application won't be aware of them until proper upgrades are done. As for adding columns to a core table, I'm not so sure. If you really need to prevent downtime, perhaps it's better to add a secondary table that (linked by an ID with the core table) adds your extra columns.

Just my two cents.