Writing Safe Database Migrations | Markus Holtermann

Speaker 1: Hello everyone, thank you for tuning in to my talk about writing safe database migrations in Django. For those of you who don't know me, I'm Markus Holterman. I'm a senior engineer at microbiolytics. We built hardware and software to analyze chemical liquids and try to revolutionize the industry and the context of industry 4. 0. I'm also a member of the Django security and operation teams. And in the past I've contributed a bunch to Django's migration framework. Five years ago, at DjangoCon Europe 2016 in Budapest, I gave a talk, Don't be afraid of writing migrations.

Speaker 1: Back then, while migrations in Django weren't particularly new, they shipped in 2014 after all, I saw many folks struggle and worry about touching the migration files. Many people were hesitant modifying them, and even fewer wrote migrations by hand. Mind you, I wouldn't recommend writing the entire migration file by hand, because Django has tools that can generate at least parts of it for us. But I do recommend looking at them, understanding what they do, and then see if they need to be adjusted. Because in the end, as an engineer, you will always know more about the application than Jenko

Speaker 1: will ever be able to. As an example, when you add a null non-nullable field to a model and run make migrations, Django will ask you for one off default value. That value would be set for all existing rows. This is the correct behavior when there's data in that table already But if you know that the table is empty everywhere, it can easily appear to be useless. As an engineer you can make a distinction there. As an engineer you can decide that it's fine not to have a default value. And as an engineer you know more about the project than Django does. But Django must be conservative to ensure the default

Speaker 1: way works for everyone. If you're still new to Django, I'd recommend that you have a look at that talk after this year's DjangoCon. To not miss out on all this cool stuff that we see here this year. But for now, let's consider consider today's talk a sequel to my previous one. I want to start this talk with some general considerations. Questions like how and when do you apply migrations? How are migrations related to deployments? What are the do's and don'ts that follow from that? If you run a somewhat serious site, you are aiming for something called zero downtime deployments.

Speaker 1: One common way for that is something called a rolling upgrade or a staged rollout. On a high level, it works like slits. In the beginning, all your servers run the same old version of your application. This is indicated by this blue circle. Then you start rolling out new version for a few servers. You keep the state for a short while to ensure everything keeps working. Over time you deply a new version to more and more servers until the new version is running everywhere The clear benefit of this rolling upgrade or storage stage rollout is the ability to notice issues early on, way before the new version is running everywhere.

Speaker 1: The benefit is in addition to the fact that your site remains fully available during the entire time. But these benefits come at a price. As you can easily imagine, running two versions of your application means you need to be they need to be compatible. Essentially, whenever the new version is running, the previous version needs to be able to keep functioning the way it did before. And whatever the new version does, it must not interfere with the previous version. This is not always easy, and unless this becomes a habit, this requirement can easily be forgotten.

Speaker 1: At least until the point of the next deployment, and that next deployment fails because of this backwards incompatibility. But while it's often far from trivial to ensure backwards compatibility is kept for the business logic, it comes it becomes even more tricky when we involve databases. Whatever you do with objects in the database, you need to remember that there can always be this one server that has not been updated yet, and thus runs the previous version. Which brings me back to one of the opening questions. When do we deploy migrations?

Speaker 1: Since you never really know when the last server was updated, but it's somewhat easy to figure out when you start a deployment, well I'd recommend to always apply migrations right before you start deploying your code. Now this comes with some serious implications as well. If you do so, you cannot remove a model or field from a model in the same release as the one that contains the migration. If you were to do that, because migrations run first, you'd remove a database table or column from a table, while some servers may still try to use it Which then will cause server errors.

Speaker 1: If you want to remove something, you will need to make true tape releases The first one removes the usage of the model or the field from all your code. The release is deployed everywhere, and then a second release can remove the table of field from the database. This is now safe since there's no code anymore that may access them. In other words, when you add something to a model or you loosen constraints Such as the maximum length of a char field, for example, you must do that before the deployment. When you tighten the constraints or remove something,

Speaker 1: You must have two releases. The first one that works with the new constraints, and the second one that then puts them in the database or removes the field or table. Which leaves the question, how do you rename a field? And the short answer to that is that you don't. The long answer is that you add a new field, you copy over the data, and then remove the old field. Something fairly similar to the third migration recipe from my previous talk. Which brings me to the next question. How do we deploy migration?

Speaker 1: Well, I'd love to present the perfect solution to you. But I can't. And I can't for several reasons. For once, everyone, every team, every company has their own process for releasing new versions of their software. The spectrum of these processes works is sheer endless. That's there's that super hip startup that has a CICD pipeline that automatically deploys to staging. And if all of them pass, the CICD automatically deploys to production. And the release pipeline runs for each merge pull request.

Speaker 1: On the other hand, you have enterprises, where database administrator types in the SQL statement manually. To make the change to the database. And that process is preceded with a change request process where three department heads have to sign off of. Or something like that. If you look a bit a little bit closer and below the surface of the variety of these processes, we can see that they all have something in common. They all only make changes in one direction. Forwards. If you think about it, it actually makes sense If you made a change to the database, you might not be able to undo it.

Speaker 1: Data that was removed is gone. You can't magically undo a drop table statement once a transaction is committed. What you can do, however is to recreate the table and restore the data from a backup that you obviously took before and apply the applying at the immigration. And you also obviously tested. Because that's what one does, isn't it? The thing is, the moment something is shared with others, and that's kind of the point of databases, you have to expect that somebody else is using it So why does Django provide a way to unapply a migration? Well, I don't know.

Speaker 1: But I do know that it's quite a useful tool during development. But when it comes to databases for staging and production and such, I'd really recommend to not go backwards. Depending on when exactly the migration occurs compared to the new when the new code is deployed, you may even be running code that expected a migration to be applied. And given two migrations that depend on each other, but where the first one has some unrelated additional changes to the second one, how would you roll back those unrelated changes? The answer to that is a new migration that rolls back to the corresponding changes.

Speaker 1: This only goes forward and apply migrations before deployment has gone so far for the Django projects I maintain that the entry point script for the Docker containers is like this I will first try to connect to the database, PostgreSQL in this case, until it succeeds. Once done, I apply a migration in the project. and then execute the actual command, such as running GUnicorn, for example. This approach works very well There's a small gotcha though. Since applying the migration is part of the entry point of each Docker container, Django will attempt to apply migrations each time a container starts

Speaker 1: Which adds to the startup time. However, if no migrations need to be applied, the migrate command is almost like a no op. However, when you think back about the staged rollout, you must make sure that the very first stage is exactly one Docker container. Otherwise, you have multiple containers that concurrently try to apply migrations Now, after all this theory, let's look at something more hands -on Our database models evolve over time, and one of the most frequent changes we do to our models is adding a field.

Speaker 1: And doing so seems rather harmless, doesn't it? We have two models. In the first one, we add a nullable field. In the second one, we add a field with an explicit default value. That's fine, right? First, let's look at the migration that Django creates. For those of you who have you have looked at the migrations files before, this is nothing new. For everybody else, let me briefly explain what you can see here. First, this migration depends on another one, namely Migration zero zero zero one underscore initial from the app add field.

Speaker 1: Which means This migration right here can only ever be applied to the database when that dependency has been applied. Or in reverse, when you are applying this migration That dependency will be applied before. Secondly, you see a list of operations An operation is Django's abstraction around some database instructions that alter your database, such as adding and removing database columns or creating and removing database tables. and a lot more. The two operations here each add a field called field to the models add

Speaker 1: field model1 and add fieldmodel two respectively. The field that is added is then described there. We can now use Django's SQL Migrate command to run the underlying SQL commands All of these commands still look fairly harmless, don't they? Well, you might have guessed it, the answer is no. The first alter table is kind of okay, but the second one can cause you some real headache To understand why, we need to understand how databases handle these types of schema alterations. Adding a nullable column, as we do in the first case, is nothing more than some metadata update.

Speaker 1: The so-called table header will include the new column And a flag that it's nullable. And that's it. None of the existing records will need to be updated. Any new record that has a non-null value for this column though will just include that value For our second case, however, the database will not only need to add the column to the table header, but it will also need to go through all database records in the table and set the default value. And this can take quite some time if you have a table with lots of records. Additionally, since your database will take a fairly heavy lock on your table

Speaker 1: , You might even render your site inaccessible, in case the table you're modifying is used rather frequently, because both read and write queries might be blocked. That is unless you use Postgres 11 on Newer, which also deals with the second case in a very clever and efficient way. However, since you might not know which database your code is running on, for example because you're writing a reusable Django app. It's a good idea to always take approach number one and scratch the idea of adding a default value out of your head. Now even this is a remote conference

Speaker 1: and I can hear some of you scream, but I want a default value Well, okay, you can get a default value. The migration recipe number two in the talk linked before gives you step-by-step instructions. However, I'd only recommend that approach for tables with a fairly small amount of records. That is because Django runs each migration inside a transaction. And if you're updating a hundred million records at once, depending on what your application or rather s its users might be doing during the time You can easily get to the point where the transaction needs to be rolled back.

Speaker 1: Imagine going through 99 million records and then the transaction fails. That's more than annoying To ensure that doesn't happen, you need to get a write lock on all records in the table, which can again lead to an unavailability of your entire site So, how do we deal with this? We write a management command that we then run after applying the migration that adds a nullable field. The management command will lock at most 5000 objects or rows at a time, and then update their field value.

Speaker 1: By using SELECT for update for each chunk, you can be sure that the field value for those objects won't be overwritten by anybody else in the meantime. Sure, running this command will take longer than updating all records at once while locking your table, but it also allows your site to be operational. Which very often is more important, I guess. But coming back to what I said earlier As an engineer you know more about the project than Django does. This applies here as well. If you know that the table you're adding a field to is small, or maybe even empty. It's absolutely okay to add a default value.

Speaker 1: Which brings me to another topic Databases are usually pretty good at retrieving data very efficiently. So much so that until a certain threshold A full table scan can be more efficient than looking up a row in an index. But at some point your table outgrows that point and you need an index. So you add one. Modern Django versions provide not just one, but two ways to do so. Firstly, the old way that's been around forever. You can set db index to true on a field, and Django will create an index.

Speaker 1: Secondly, since Django 1. 11, you can define class-based indexes in a models meta class. They are far more flexible and powerful. And since Django is 3. 2, you can even add indexes on expressions, also known as functional indexes. There's actually a third option, the index together attribute in a models meta class. Allows you to create an index on multiple columns. Personally, I'd consider them outdated as well. Additionally, for the example at hand, I'm going to ignore them. Because they behave identically to dp index

Speaker 1: and can be replaced with class-based indexes. Looking at the auto-generated migration, you can see an alter field which adds the dp index equals true, as well as an add index operation. A downside of the add alter field operation is that you don't really see on the Python level what changed on a field. You need to search for the last migration operation involving a field in order to be able to tell what index was added or what changed on the field. In contrast to that, the add index operation is clear in what it does. It adds an index

Speaker 1: When we now look at the generated SQL, we can see something very, very interesting. Firstly , dbindex not only adds a single index, but it adds two. The first one is one that we all expect. The second one, however, is one that Django adds to make like queries more efficient. Secondly, the name for the auto-generated db index is unpleasant to re-look at. The eight random characters are part of an MD5 hash over several attributes to uniquely identify that index. Using the class-based index, we can however

Speaker 1: define our own index name, which makes it so much more pleasure pleasant to look at. Using meaningful index names has the added benefit that it's easier to debug database issues. The index name can carry additional context that then allows the database administrator to debug certain issues more effectively. But it's important to know that some databases, among them PostgreSQL, requires an index name to be unique within the database. Using my IDX as I did in the example here is probably not the best idea.

Speaker 1: But it's short and makes it easy to read to and the makes the code fit on the slides. So this is fine and solves the purpose here. Now, if you go ahead and apply this migration on your database, you'll be fine when there's not really any load on it, and when a table doesn't have a lot of records. However, as with the add column example earlier, this operation can lock your table for quite a while. And the worst thing, using db index, it locks your table twice. Once for each index. Even if you never use the one for the like queries.

Speaker 1: I gotta admit though, using a chauffeur as an example here is the worst example I could give. If you set db index on an integer field, Django will only create one index But this demonstrates that it's a good idea to look at the migration files and see what they'll actually do. So how do we fix the table lock issue? Well, Postgres can build indexes concurrently while allowing access to the data in the underlying table. That however comes with the downside that this needs to run outside of a transaction.

Speaker 1: Since each migration runs within a transaction, we need to set atomic to false. Then we can use add index concurrently to turn our class-based index into one that's added concurrently. Now let's look at the actual SQL that is generated from this As you can see, the begin and commit statements are gone, and the last create index statement now has an additional concurrently. Now, if you're asking yourself how you deal with that on MySQL and MariaDB, I got to disappoint you. You don't. Because luckily you don't even need to, because adding indexes

Speaker 1: there happens without locking the whole table, at least in modern versions Even with all these suggestions and tips, one thing remains. You should test your migrations. I'm not necessarily talking about unit tests. Yes, maybe it depends. Now I mean you should test your migrations in a production-like environment. Have some test scenarios available that you can refer to when migrations touch a particularly large table, or one that's accessed frequently See and try out how the database behaves.

Speaker 1: But it's important to understand that this level of testing of migrations is not something I'd do for each migration But it's something you can help you that can help you understand how your database works and what impact on the production environment you might see But in the end, whatever you do in a testing environment, your production environment will behave slightly differently. even if it's just for the users that behave different than usual on that day. Which brings me to the end of this talk. Let me briefly summarize what we've seen today. It's usually a good idea to apply migrations before you deploy and run new code.

Speaker 1: While not trivial, it's relatively easy to wrap one's head around it. Create model and add field can go into the same release as the code. Delete model and remove field need a separate release. Renaming is a combination of add and remove. It's a good approach to only ever go forward. Rolling back database migrations can lead to additional unexpected behavior, in addition to the one you're already facing. When adding fields to existing models, make it a habit to add nullable columns without a default value. It's a good pattern that's always safe

Speaker 1: And if you want default values, that's fine. But populate existing rows manually, at least for larger tables. And when you add indexes, try to add them concurrently. Again, especially on bigger tables Whenever you apply migrations, something could go sideways. So make sure you have working backups. And working backups means you test your backups. And lastly, when you have particularly complex migrations, test them in a production-like environment, in a product with production-like data Only that way you can get an understanding of how your database actually might behave.

Speaker 1: Thank you. Hi there.

Speaker 2: Hello. Um, do you think we could do anything in Django to make the um make it more obvious to um Do migrations well.

Speaker 1: Sorry, there's either two people with different people were talk talking something or half only half of that. Reached my end online.

Speaker 2: I think the latter. Um can we make some changes to Django to encourage doing migrations the right way?

Speaker 1: W what do you mean by right way though?

Speaker 2: I mean to encourage using patterns that are um Um safer from in the ways that you covered in the talk.

Speaker 1: I think the um a lot of stuff could happen through proper documentation and pr s as um Adam already pointed pointed out he has a pull request apparently open for hey use meta-indexes over indexed together and so on. I think that's a that's a good first step. And hack maybe even deprecate index together eventually or just have it fade out phase out over some time while adding a clean way to migrate this. Like maybe even automatically detect index together and rewrite it in the the and and

Speaker 1: write it as a add index operation. There's I think we should be able to do that under the hood in the inside the the um migration state handling Yeah, somebody would need to do that, figure out how to how to actually do this. But I suppose that should be possible. Similar to the um db uh db index on the field level Um so that could literally be something that we do all that we could do automatically. Um This cookie banner is blocking the

Speaker 1: Q<unk>A for me. There we go. Okay. Does this answer the your question?

Speaker 3: Uh hello. I have um I I think I probably look this up, but might as well do it here. Uh so on that example where you you say we should do the some operations with the management command, should that be run in the same migration file or should there be two different migration files? Uh

Speaker 1: the management command would be run outside migrations.

Speaker 3: Okay, so you