dian m fay

It's almost Phriday again! This is a monthly blogging event for the PostgreSQL community. The rules:

• publish something on-theme on or near Friday, June 2nd
• include "PGSQL Phriday #009" in your title or first paragraph, and link to this invitation post
• share it! The best way to reach the greater Postgresphere is to get syndicated on Planet Postgres, but you can also share on #pgsqlphriday in the community Slack or post to social media with the #PGSQLPhriday hashtag

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.

Every now and then, someone will extol the benefits of version-controlling your schema -- Grant Fritchey discussed this at PGDay Chicago just last month -- or write a how-to for a specific framework. There's a slow current of academic interest in the topic which seems to have limited feedback into industry, publications tending toward the descriptive or the heavily specialized with only the occasional experiment like PRISM seeing daylight. But the people deploying changes day to day don't tend to talk much about the nitty-gritty details or the experience of modifying a running database, because change management is plumbing.

Plumbing is really important, and there are a lot of fascinating technical, procedural, social, even philosophical aspects to it. Let's haul a few of them into the spotlight!

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!