10 Requirements for Managing Database Changes

The host for PGSQL Phriday #009 is Dian Fay, who has asked us to discuss how we manage database changes.

Oh my, Dian picked a topic that’s near and dear to me. In fact, one of my (still visible) T-SQL Tuesday blog posts was about this very topic… 7 years ago!

Quick aside: For those of you that don’t know, the announcement of the PGSQL Phriday initiative referenced the fact that it is modeled off the T-SQL blogging event that has been ongoing for 12+ years now.

As a developer and DBA for more than 20 years, understanding how to track, manage, and ship database changes has been a career-long journey (and I’m still not done!). Quite accidentally, figuring out how to help my team do this well in an otherwise Agile development environment became somewhat of a passion for me many years ago.

So let me take you on a really brief overview of that old T-SQL Tuesday article to help you understand why I care about it so much in the PostgreSQL space.

Models, Diffs, Scripts, and a Circus

A circus 🤡? Really? Let me explain.

My experience with managing database changes goes back to an app developed in the late 1990, where the team used ER/Win to model the database (this was before my time. I’m not quite that old). When database changes were requested by the development team, a process similar to this was followed:

• The ER model was updated
• Once enough “changes” were queued up, a fresh “create” script was generated
• A clean database was created using that script
• Another set of 15+ year old scripts compared the two databases
• The resulting “diff” was then reviewed by hand to generate the “migration” script
• The migration script was then run on development databases for the developers to use
• Changes were requested to fix errors in the original requests…
• Rinse and repate

Whew!

For those of you that don’t know, those ER tools were really expensive (probably still are for the ones that exist) and only a few developers had access to the tool. They didn’t have a great DX either.

Aside from the lack of automation and ability of our developers to be more integrated into the process, there was always the one looming issue that we just couldn’t reconcile.

If Joe left and joined the circus (see, I got you there), we were stuck.

We knew this was a bottleneck for some time and we had tried multiple times to change the process. Our ability to iterate on new feature development went through one person and a set of 15-year-old scripts. It didn’t match our otherwise Agile process of front-end code and data analysis projects.

As I mentioned in the old article, the key requirements for us came down to the following:

• Having a known state of the database at each changeset and release, stored in source control
• the ability to easily create migration scripts for each release
• functionality to verify the state of a database against the known, good state
• match the iteration cadence of other feature development

There are other things I care about, but getting here would be a net win from where we were.

In the end, however, the important parts for me were about bringing the changes closer to other development and allowing the larger team to have more database exposure and control. Part of my goal was also education. Too often we hear complaints of developers not understanding how to write SQL and interact with the database efficiently, but we were shielding them from gaining more experience and understanding in a meaningful way.

Eventually we got close. You can read the other article if you want more details.

But that was all using SQL Server and the integrated set of tools in the .NET development environment. How could I do this with PostgreSQL?

Source Control with PostgreSQL

When I moved full-time development from SQL Server to PostgreSQL, it felt like I was starting the entire journey over again. At the same time, it caused me to dig more deeply into what tools were available, known processes for gaining control over database development, and empowering the development team (alongside experienced DBAs) to bring more agility to the database change process.

Fortunately, I wasn’t the only one asking these same questions. There just weren’t a lot of solid answers that I could find.

Most tooling that I found focused on managing manually written migrations. Sqitch, for instance, does a good job at integrating a TDD approach with migration and rollback capabilities. But still, it’s only migration based. There is no state that clearly lists all database objects at each migration and release.

Other approaches like Migra or Pyrseas take the purely state model approach. This makes it easy to see object-level changes over time and have the model to compare against, but the long-term automation tooling still leaves a lot to be desired across larger teams. Don’t get me wrong, I honestly like both projects and I’m glad they exist, but this still doesn’t get me everything I would like.

And finally, there’s there ever growing list of ORM solutions that attempt to manage the database change process for you. Code database objects and queries in your language of choice and then let the tooling work the changeset magic! Spoiler alert, that’s really hard and I haven’t seen one toolset do this perfectly well without any intervention. Sorry.

As it turns out, the solution I wanted was being developed by Redgate, the maker of the tools that had helped us move further years ago in my SQL Server world.

Quick disclaimer

I don’t want this note to get lost in some sidebar callout. 8 months ago, I joined Redgate to help them integrate their tools with PostgreSQL (and other databases beyond SQL Server). This isn’t an advertisement post, I promise.

I’ve always appreciated how Redgate approaches and invests in the SQL Server database community, and I wanted to help them do that more with PostgreSQL. I didn’t even realize how far along they were in bringing PostgreSQL DevOps into existence before I joined. It just happens that most of the “requirements” below were being worked on before I arrived.

Whatever tool you use, this list of requirements is my personal perspective based on years of success, failure, and trying to improve developer education and experience. I think the way we approach this with the Flyway tooling is about the best around currently, but I sincerely only say that because it ticks many of the boxes I was always hoping for.

Now back to the show!

My 10 Requirements for Effective Database Change Management

I didn’t plan to get to 10 “requirements” for database change management processes and tooling. But without thinking it through ahead of time, I ended up with 10.

I hope these ideas are helpful as you explore the state of your current database change management process and where you hope it will be a six months or a year from now.

Requirement 1: Database State Management

First and foremost, we work with database objects and I believe the state of those objects should be stored in source control. This allows us to see, using common source control diffs, how objects changed over time. Questions like these are really easy to answer without searching migration scripts.

• Has table X been modified in this release? Did a column datatype change or was a new column added?
• Has this object always been named this way?
• Was a critical index dropped accidentally?
• Did the new index we needed actually get added to the database?
• What options were used in pervious iterations of a function?

The list could keep going, but when you store the current state of the database and commit it to source, any developer on the team can easily start to investigate object changes. This is incredibly powerful.

Flyway Teams and Enterprise provide this capability with a desktop client that makes it much easier for developers to get more comfortable with database changes.

Requirement 2: Easily Managed Development Branches

Because we can store the state model of the database and all objects, this also makes it straightforward for developers to work with their local development databases as code, in feature branches. This means that multiple developers can work with and commit to the model without coding a migration for every step until the feature is ready for deployment.

Now, when other members of my team commit changes to a feature branch model, I can apply those changes to my local database to stay in sync and ensure our changes won’t clash. Once again, if the goal is to prioritize developer productivity and collaboration, this goes a long way in supporting that.

Teams don’t have to wait for feature branches to be merged into a development branch to check for issues or conflicts. Instead, just like other parts of the codebase, multiple developers can verify a feature on their local machines before a final migration is written for the feature, saving multiple script change cycles.

Requirement 3: Consistent and Predictable Migration Scripts

Relying on a tool to help you generate migration scripts brings consistency to the entire database DevOps process.

“But wait, Ryan, didn’t you say auto-generated tooling like ORMs or State-only tools is bad?”

No. What I said is that most of those tools aren’t magic. And neither is the state comparison engine and migration script generation of Flyway. The difference is that most ORM/state-based toold don’t give you easy ways of verifying the changes that will be made and changing the order or priority. It probably is possible, but in my experience it’s rarely straightforward.

Instead with an approach similar to Flyway, your state changes are generated into a migration script (hopefully) with the order of operations correct, alongside the various nuances of object changes. But because it’s not fully perfect in every case, the migration script is presented for review and modification. Again, this puts the ultimate power of change management into the hands of folks that know the system and it helps educate the wider team, too.

Requirement 4: Repeatable Scripts and Static Data

Changes and migrations aside, there are still scripts that need to run with each migration and release to do any number of things. Some examples include:

• verifying object and schema privileges
• ensuring that static data has been maintained
• running procedures that link existing data with new schema objects

Whatever the reason is, the tooling is most effective when we can ensure common scripts will always be run to keep the database in peak performance. Requiring a DBA to take additional steps outside of the migration process is time consuming and error prone.

Requirement 5: Easy Drift Detection

When you manage tens or hundreds (or thousands) of client databases, migration problems are bound to occur. Years ago, we would handle this by ensuring our biggest client databases were pre-checked for upgrade problems before the actual migration occurred. But because this was a manual process, it was time consuming and limited to a few people.

When drift detection simply becomes part of the automated pipeline process, the team (and our clients) can rest, knowing that the process won’t blindly migrate databases known to be in a different state.

Almost every time I get pushback about database change management, this is the one topic that gets teams fired up.

“Anyone with the right privilege can just login to the database and change things. There’s just no way to stop people from modifying a disconnected artifact!”

While I agree, if you have a known state and track the migrations that have been applied, it should be easy to verify that the schema matches what is expected before any additional changes are made. If not, it’s a hard fail and the team needs to intervene.

The fact that changes can be applied at will to client databases and your process doesn’t mitigate it is a different problem that I don’t have the time to tackle here. 😉

Requirement 6: Coding Standards and Compliance Checks

The best developers and DBAs in the world still make mistakes. Who among us hasn’t dropped a table, index, stored procedure… or database… and brought an application to its knees. 🙋‍♂️ (hey you, stop lying!)

The tooling and process you use should have repeatable and standardized checks that will verify, even before code review starts, that simple mistakes haven’t been made.

• Are there any DROP TABLE statements in here we weren’t expecting?
• Do the store procedures follow our agreed upon naming convention?
• Does this script modify permissions in a way that we don’t allow?
• Does every new object have a caption added?

There are so many tools that have been created and used in the “regular” coding world to LINT and verify code, and your SQL scripts should be subject to the same kinds of checks. There are a small (but growing) number of tools for doing this, but you’d be surprised how powerful a good set of RegEx rules can be at catching and verifying the SQL code you (and your tooling) are writing.

Catch the simple errors before they get to testing and production. 🎉

Requirement 7: Develop and Test on Production-like Data and Load

Honestly, this is probably the hardest one in the list. In todays cloud-first, mostly disconnected development environments, it’s really hard to reproduce lifelike database load. Likewise, most shops don’t have the budget to simulate it or provide the resources for each developer to have a massive copy of the database.

Let’s not even discuss the security requirements to hide sensitive data, PII, and GDPR. 👈 These are all good things, just difficult for most teams to manage.

Still, the ultimate goal of managing database changes should have the goal of moving in this direction. There are a few exciting opportunities in the PostgreSQL world that brings this closer to reality.

The first is a service like Neon database, a cloud-first PostgreSQL fork. They’ve included the concept of database branching. This efficiently creates a copy-on-write version of the database at a known state, so that only the changes are stored in the branch. Every developer can easily create their own branch (or a branch for a previous point in time) to test migrations and database changes.

Postgres.ai is another copy-on-write solution that allows you to use your current infrastructure and PostgreSQL proper to do similar branching.

And finally there are tools like Redgate Clone. This provides an opportunity across multiple database platforms to use backups of your masked, production data (or smaller subsets of production data) as data images, akin to regular containers. Creating a clone for each developer is fast and efficient because the technology only stores the changes in the developer copy. So, a 500GB database could have tens of developers, each with their own copy, and only need a total of maybe 550GB of space. If a developer wants to start over and “rebase”, they can drop the clone and start over in seconds. 💥

Whether Redgate Clone approach is the right tool for you or a technology like Neon or Postgres.ai, the concept of more production-like data, with production-like load is the ultimate goal throughout the lifecycle of your data pipeline.

Requirement 8: Accurate Reporting

These steps should lead to more efficient, automated processes that empower teams. But in order to have more confidence, we need tools to accurately track and report on each stage.

• What did the final migration script look like?
• If there was drift, did we record it for each database and store it for later investigation?
• What warnings or errors were produced by the coding rules we have in place?

If the reports are created automatically and easily added as artifacts to pipelines or source control, the team gains more control over how to improve the process.

Requirement 9: Pipeline Automation (aka. CI/CD)

If the first four things are working well and the tooling is available teamwide, then it should be easy to start incorporating database changes into the dev/test/stage/release pipeline. Whether you use GitHub, Gitlab, Azure DevOps, Jenkins, Octopus Deploy, or some other CI/CD tooling, commits to the database project should run through the same automation as other code.

This might be scary I know! 😱 But this really needs to be the ultimate goal for managing database changes.

If you make progress in any of the requirements above, automation gets incrementally easier and your team will gain more confidence. If your tooling doesn’t provide the Docker images or command-line applications to integrate into your pipeline scripting, find a new tool. You’re not going to make progress in efficient, confident, repeatable database changes if you’re chained to manual processes.

I promise, the first time you watch a set of database changes merge and deploy to each environment, running checks and tests along the way, recording everything as it goes, you won’t ever want to go back.

Requirement 10: Commitment

I promise I didn’t add a non-technical requirement just to get to 10! 🤞(shoot)

But in all seriousness, this is probably the most important requirement for any changes you make to your existing process. I present on Database DevOps and change management often, long before I worked at Redgate. (Groupby 2018, PGConf NYC 2021)

By far, the biggest pushback I receive after every talk comes from developers that want to change, but they feel lost within their organizations and unable to help demonstrate the benefits of changing the “tried and true” processes. Often, at the sight of any issues, teams will bail and head back to what’s safe and known, even if it means moving more slowly and with more errors.

Fight against that. Make incremental changes, looking at the various “requirements” above and seeing which you can start to incorporate first. When you hit a roadblock, push through it and improve the process. Don’t turn and run!

You, your team, your company, and your clients, will be thrilled where you end up, responding to changes and opportunities with ease.

Happy change management!

And thanks again to Dian for presenting the topic!