Hacker News new | ask | show | jobs
by morganpyne 5236 days ago
There is another problem unmentioned in this article with his simple table modification example... When you modify a table the DB needs to write the entire table to disk, locking it all the time. Depending on how much data is in the table and how fast your IO is this can be hours and hours of downtime on the live database, for a migration that only took a few seconds locally on your test database.

Running migrations on live databases with lots of data in them is hard. There are many strategies to work around this problem, but generally running an 'alter table' on your primary db server on a huge table while it is in use should be your very last option.
3 comments
newhouseb 5236 days ago
This is true, but note that the article was written in the context of PostgreSQL instead of MySQL. PostgreSQL can do many types of table migrations with no downtime (dropping columns, renaming, adding columns that default to NULL, and creating indexes). Before I made the switch from MySQL to Postgres I did some quick benchmarks which support these claims on a 5 million row test table: https://gist.github.com/1620133.
link
morganpyne 5236 days ago
Guilty as charged for immediately assuming that the problems of the MySQL family apply to other databases. Thank you for pointing out that PostgreSQL does not necessarily have all the same limitations.
link
mceachen 5236 days ago
Absolutely! The author was very thorough with the change process, which is great, but downtime during ALTER should absolutely be broached as well.

A staging system with a db server provisioned similarly to production lets you "smoke test" the migrations and deploy before you take down prod -- but especially with MySQL, that happily blocks all reads during an ALTER, you should really watch out for any table ~> 1M rows (or if they're wide, even fewer than that).

link
glenngillen 5236 days ago
If you're using postgres on Heroku you can get a fork of your production db to test things like these:

http://devcenter.heroku.com/articles/heroku-postgresql#fork_...

link
kfool 5235 days ago
The issue is not performance. The issue is backward compatibility.

ALTER TABLE performance will eventually improve. PostgreSQL 9.1 lifts-off the lock-up limitation.

But what a performant ALTER TABLE will not improve is its intrusiveness to applications. When you change the schema you break the app.

That's the problem.

http://chronicdb.com/blogs/change_is_not_the_enemy

link