Agile Databases

There is a subgroup of the agile community focused on agile database development and database refactoring. Just do a search. At IMVU, we definitely do agile development of our databases and the application layer code that talks to them. We expect most new engineers to be capable of writing a database schema and pushing it to production within a week of starting, with automated tests that let us change things without fear of breakage, of course. We have no traditional DBAs, although one of our engineers acts as one, reviewing schemas before they’re applied in production.

The database refactoring resources that we have seen have been deficient in one area: they assume that you have small amounts of data, or the ability to take down your site or service so that you can apply schema changes. (Which lock tables until the schema is applied.) The problem that we have not seen addressed yet is how to change table structure when you have millions of customers and heavy usage. Each hour the site is down translates into lost revenue. Yeah, there are ways to get around this, such as applying schemas to slaves and then failing the master over to the slave, but these require a fair amount of infrastructure and pain to support.

But if I could choose one thing to get from “the agile database community”, it would be a modification to MySQL that let you add or remove indices or columns in the background, even if that particular table was under heavy use, and even if the table had degraded performance during the alter. Maybe it could be implemented as a table with special metadata that shows how to get the data from the old table if it does not exist in the new one. And maybe Oracle supports this already, and I just want a new feature in MySQL.

Update: It turns out that if you have partitioned your customers across enough databases, and have some spare capacity, you can apply schema changes across your databases in parallel, and it’s not so bad. So maybe that’s how to be agile and have users. :)

Leave a Reply

Your email address will not be published. Required fields are marked *