PGSQL Phriday #009

Invitation from Dian M Fay and summary post of all submissions

The host for this month’s event is Dian Fay, someone I’ve only met virtually because of some excellent content Dian has published for previous PGSQL Phriday blogging events.

For me, that speaks to the power of these little monthly challenges, prompting a group of people to write about a common topic because we all learn so much from one another. I’ve been really blessed by the detail and thought process that Dian provides in each blog post.

For the challenge this month, Dian is asking about a topic near and dear to me, managing database changes!

The challenge from Dian

This month’s topic is database change management, aka schema evolution. I’ve been doing this in one form or another, using one framework or another (and on one less-memorable-than-you’d-think occasion writing my own in a thousand lines of Ant XML) for almost as long as I’ve worked in software. If you interact with databases in more than a read-only capacity, you’ve probably done your share of it as well. It’s common, it’s necessary, it’s not very glamorous.

Some starting points:

  • how does a change make it into production? Do you have a dev-QA-staging or similar series of environments it must pass through first? Who reviews changes and what are they looking for?
  • what’s different about modifying huge tables with many millions or billions of rows? How do you tackle those changes? Do you use the same strategy for smaller tables?
  • how does Postgres make certain kinds of change easier or more difficult compared to other databases?
  • do you believe that “rolling back” a schema change is a useful and/or meaningful concept? When and why, or why not?
  • how do you validate a successful schema change? Do you have any useful processes, automated or manual, that have helped you track down problems with rollout, replication, data quality or corruption, and the like?
  • what schema evolution or migration tools have you used? What did you like about them, what do you wish they did better or (not) at all?
  • tales of terror in the Kletzian mode are also of course very welcome!