Latest / Software Testing with Fexingo: QA, Automation, and Reliable Software Engineering / How to Test Database Migrations Without Downtime
Transcript
- Lucas: So there's this moment in every software project that makes even the most confident engineer pause, and it's not the code review or the deployment. It's the database migration. Luna: Oh, absolutely. One wrong ALTER TABLE in production and you've got an outage that no amount of monitoring can fix. Lucas: Right. And the scary part is, migrations are often tested poorly — or not at all. So today I want to drill into a specific technique that teams use to ship schema changes without taking the service down. It's called expand and contract. Luna: Expand and contract. That sounds like a breathing exercise for databases. Lucas: Ha, kind of. The idea is you make the database backward-compatible first, expand it to support both old and new code, then after the new code is fully deployed, you contract — remove the old columns or tables. The key is testing each phase in isolation. Luna: And that's where a lot of teams get it wrong. They test the final state but not the intermediate ones. Lucas: Exactly. So let me give you a concrete example. Say you have a users table with a single column for the full name, and you want to split it into first and last name. A naive migration would just drop the old column and add two new ones. That breaks any code still running that references the old column. Luna: So expand would be adding the new columns while keeping the old one, and contract would be dropping the old one later. Lucas: Exactly. But the testing part is what I want to focus on. You need to test that during the expand phase, the database still works with the old code and the new code at the same time. And during the contract phase, you need to test that everything still functions after the old column is gone. Luna: And you also need to test the migration itself, not just the schema state. The actual data transformation — copying values from the old column to the new ones — that's where the bugs hide. Lucas: Right. So how do you test that? The best approach is to run the migration on a staging environment that mirrors production as closely as possible. And that means not just the schema, but the data. A tiny sample set won't expose performance issues or edge cases like null values or extremely long strings. Luna: So you need a copy of production data. Anonymized, of course. Lucas: Of course. And you need to test the migration on that copy, preferably multiple times, because migrations are typically idempotent. You run it once, you run it twice, you run it ten times, and you should get the same result. If you don't, you've got a problem. Luna: And while you're at it, you should test the rollback, too. Because no matter how confident you are, there's always a chance you'll need to undo the migration. Lucas: Absolutely. And rollback testing is probably the most underrated part. Teams write a migration, test it forward, but rarely test the down migration. And then when something goes wrong in production, they're stuck. Luna: So let's talk about feature flags. I've seen teams use them to make migrations even safer, by controlling when the new code paths are active. Lucas: Yes, that's a great point. Feature flags let you decouple deployment from release. So you can deploy the code that reads the new columns, but keep it behind a flag. Once the migration is verified, you flip the flag, and then later, you can remove the old code and contract the schema. Luna: And the testing of the flag itself—making sure the flag works correctly in all states—that's part of the migration testing too. Lucas: Right. So let's get into the specifics of what a good migration test suite looks like. First, you want to test the migration on a clean database, to make sure it works from scratch. Then you want to test it on a database that's at the previous version, with data that mimics production. And you want to test it with a full production-sized dataset to catch performance regressions. Luna: And don't forget to test the migration with concurrent queries hitting the table while it's running. Because in production, you can't just lock the table for an hour. Lucas: Exactly. That's where the expand and contract pattern helps. The expand phase should be non-blocking, using things like adding a column with a default value, or creating a new table and backfilling it in batches. You test that the reads and writes still work during each batch. Luna: And the contract phase—dropping a column or a table—that's usually quick, but you still need to test that the application doesn't reference it anymore. You can even use a tool that scans your codebase for references to the old schema. Lucas: Good point. There are linters and static analysis tools that can help with that. And you should also test that your ORM or query builder is generating SQL that matches the new schema. That's a common source of bugs. Luna: Let's talk about a real example. I recall a fintech company that had a migration go wrong because they didn't test the interaction between the migration and their ORM. The migration ran fine in staging, but in production, the ORM was caching the old schema and started throwing errors. Lucas: That's a classic. ORM caching is a sneaky one. So the takeaway is: test the application against the migrated database, not just the database itself. Run your entire integration test suite against the post-migration schema. Luna: And that's what we do on our own team. Any time we have a migration, we spin up a full stack in a container and run the whole smoke test suite. Lucas: That's great. And you should also consider load testing during the migration. Simulate production traffic on a staging copy while the migration runs, to see if there are any lock contention issues or performance degradation. Luna: I think there's also a human side to this. Migrations often happen during off-peak hours, and the person responsible might be on-call. If they've never practiced the rollback, they're going to panic. Lucas: Yes. So do a migration drill. Actually run the migration and the rollback in a production-like environment with the on-call engineer. That way, if it goes wrong at 2 a.m., they know exactly what to do. Luna: So to recap, the core pieces are: expand and contract, feature flags, test on production-sized data, test the rollback, test with concurrent load, and run your full test suite against the migrated schema. Lucas: That's the checklist. And the nice thing is, these practices aren't just for huge companies. Even a small startup with a single Postgres database can use them. Luna: And if today's conversation gave you something you can apply to your own projects, that's exactly what we hope for. And if you find value in these episodes, and you'd like to keep them ad-free, you can support the show at buy me a coffee dot com slash fexingo. Lucas: Yeah, that link is also in the show notes. Every bit helps us keep doing this. But let's get back to the technical side — there's one more thing I want to mention about migrations: testing the data itself, not just the schema. Luna: Oh, you mean like data validation after the migration? Lucas: Right. After a migration that transforms data, you should run queries to verify that the data looks correct. For example, if you split a full name into first and last, you want to check that no names were lost or mangled. You can write automated data checks that compare the old data to the new data, or at least spot-check a sample. Luna: And you can even do that in production, after the migration, as a canary check. Lucas: Exactly. And that's part of the testing that happens after you've contracted the schema. You want to run a reconciliation script that compares the old and new data, ideally before you drop the old columns. Luna: That's a smart safety net. So data integrity is just as important as schema integrity. Lucas: Absolutely. And that's the thing — a migration test isn't just about whether the SQL runs without error. It's about whether the system still works correctly, data is intact, and performance is acceptable. That's the full picture. Luna: So next time you're about to write a migration, think about all the stages of testing. It might feel like extra work, but it's nothing compared to a production incident. Lucas: Well said. And that's what we're here for — to help you avoid those incidents. Thanks for joining us, and we'll catch you in the next episode.