Turn back time:Converting integer fields to bigint using Django migrations at scale

Speaker 1: Thanks very much. Um and uh apologies for that. Little delay while we got that thing sorted out there. Hi, my name's Tim Bell. I work at Kraken Tech. You can see here. I will tell you a little bit more about Kraken Tech in just a minute. Let's get into things. First, uh let me explain the title of the talk. First of all, I'd like to turn back the last ten minutes and we can just get that kind of thing out of the way and get onto the content. But um Um I'm not Cher , so I'm not going to sing this. If I could turn back time, if I could find a way, I'd take those back back those words. 32 bits is enough, I'd say. Now, some of you might remember other people talking about arbitrary limits on computing like like uh something Bill Gates said a long time ago.

Speaker 1: Um but I want to start with a conclusion, something that we can you can take away even without having listened to the rest of this talk. You can avoid future issues by using big int fields instead of integer from the beginning of your project. Unless you have a time machine, in which case of course you can go and fix your mistakes at at liberty. But since we didn't do that and we don't have a time machine, I will have plenty to tell you in the rest of this talk. So I work for Kraken Tech, which is part of the Octopus Energy Group. We developed the Kraken Customer System, among others, which is software for energy and other utility retail businesses. It's used by Octopus Energy and other clients in the UK and at least 10 other countries around the world, but not currently

Speaker 1: the Republic of Ireland, to my knowledge. The Kraken Customer System is built on Django with hundreds of apps and thousands of Django models. We use PostgresQL, or Postgres for short, in the form of AWS Aurora. The software is deployed hundreds of times a day to each uh of the many systems we run, you know, for each client. That's the technical background to Kraken Tech that you need to understand the motivation for what I'm talking about today. Kraken Customer has been very successful and has needed to scale in three ways that are relevant to the talk. The size of the customers in terms of their bills, that's the energy customers, the the um the ones that our clients will be sending bills to.

Speaker 1: Um size of database tables in terms of like the number of rows. And the number of Kraken systems we have in production that we are deploying to and need to keep operating all the time These are good problems to have from a business perspective, but from a technical perspective, they've required some work in order to avoid some issues. So I'm going to be talking today about that work specifically converting database columns to begint. There's one completed project I'm going to report on, as well as another project that's currently underway. and some further development work, which is at a very early stage as well. We're going to start by looking at the first problem, which is supporting customers with larger builds.

Speaker 1: So here is a sample Django model. It's a payment model. It's so simple that we are just storing the payment amount. We're not storing anything else. But that's just for illustrative purposes. The main thing is to note how we have chosen to represent the the payment amount We've chosen to use a 32-bit integer number of cents or pence, depending which currency you're using. So for instance, if you store the number 100, that would be 1 euro. 100 euro cents. That choice made sense back in 2015 when this model was created, but it does put an upper limit on the amount of money that can be represented, which is just over 21 million euros or

Speaker 1: pounds or dollars. In 2023, we realized that for one of our newer clients who had some large customers, the $21 million limit was going to be an issue. So we decided we would convert all of the integer fields representing amounts of money to big integer fields using the 64-bit uh begin type in Postgres, the new maximum amount that we that could be represented was 92 quadrillion euros, which would definitely solve our immediate problem. We could have changed to using a decimal type, but that would also have required changes in the Python code as well, not just the database representation. And that's something we did want to avoid. So, here is the simplest way to convert a field to big int

Speaker 1: in Django. Having changed the field to big integer field in the modern model definition, which I had on the previous slide in bold. We run make make migrations, which creates a Django migration file with an alter field operation in it. And what will that alter field do when we deploy? We find that out by running SQL migrate. Which will output some SQL, which is what we can see here. So we're going to do an alter table, alter column, type big int, and using the existing column values to do the conversion. The Postgres docs say that an alter table operation like this will take an exclusive lock and need to rewrite the entire table.

Speaker 1: That means it will block all accesses to the table while it's re rewritten, which could cause a service outage. And depending on the size of the table, that service outage could be an extended period of time. Hours, maybe more. I mentioned that the Kraken software is deployed hundreds of times per day to installations around the world. Because it's around the world and because we deploy to all the systems at the same time. There is no good time of the day to make such a change because it's always going to be business hours somewhere around the world. So we need to find a way to make the change without interrupting normal operations. So the solution to avoiding an outage is to use a multi-phase process made up of individual safe steps.

Speaker 1: And I've got the three phases shown here. The first phase adds a new shadow column, and that's going to be a begint column. The second phase makes that column like the source column in all respects, and I'm being very wishy-washy here, in all respects apart from being 64-bit. Um including backfilling the data from the original column. And then the first third phase just swaps the two columns round. Each step in each phase makes safe changes that move towards the end state of a converted begend column. Safe means we're not going to be interrupting normal operations by having a exclusive lock held for a long period of time. All the operations are done with Django migrations, apart from the backfilling.

Speaker 1: And that means that we just deploy them along in the same way that we'd normally deploy new code versions. That means that it scales well across a large number of systems in production. We don't need to do individual work on each system. Note that from here I'm referring to a column in a database table instead of a field on a Django model. They have a one-to-one relationship. Normally, but when we're creating a shadow column, we're creating a column that Django doesn't know about. And because Django doesn't know about it, it's not represented as a field on the model, it means we can't manipulate it using Django migration operations like the alter field operation that we saw for the from the naive way of doing this.

Speaker 1: So instead we write SQL directly and use the run SQL Django migration operation to execute it. All right, let's get into this in a little bit more detail. So converting money fields to BigInt is a project that I worked on in the first half of 2023, along with with some of my colleagues, including Charlie Denton, who is somewhere Hi Charlie. Great working with you. I'll explain the process in some detail, but I'm also going to skip out a whole bunch of the distractions that occurred along the way um because they don't necessarily add to the story I'm telling, though there are plenty of stories that I can tell, just not in this half hour We're going to use the same simple payment model from before.

Speaker 1: The model's got a primary key field named ID, but that's actually mostly relevant to this process, so I'm not going to show it from here on. And of course, in reality, the model would have many other fields as well, other than the amount. They're also irrelevant to this process, so we're not going to show them. So, first phase. The first step is to add the new shadow begin column. The new column is nullable and it starts out empty. Here's the SQL to do that, just standard alter table, add column, and it's beginned. At the same time, we're going to create a database function and trigger that will keep the new column updated with any changes made to the integer column, the original column. Now don't worry if you're not familiar with Postgres

Speaker 1: functions and triggers. The relevant part is the assignment which is shown in bold. Amount new is amount. Both creating the new column and setting up the database function and trigger are done via a Django migration operation we've written called create begint columns and trigger. I won't show you the implementation, but that's fine. It's implemented using the Run SQL operation, which means it makes database changes, but doesn't change the Django state of the model. So Django doesn't know what we're doing under the hood. All of the SQL operations in the subsequent steps are also implemented as Django migration operations and deployed via our normal processes. And for the sake of time, I'm not going to show those ones either.

Speaker 1: Our deployment process sets a Postgres lock timeout by default when applying migrations, which means that if it tries to acquire a lock and it can't get it within the timeout, it will give up. And two years ago at DjangoCon Europe , I gave a talk about why having a lock timeout is important when making database schema changes. The important thing is that it can cause an outage if you don't have the timeout. Using Django migrations is crucial to scaling across the number of Kraken systems that we have in operation. Alright, phase two. We want to make the shadow column like the integer column. And the first step of that is backfilling. The empty begin column needs to be filled by copying value values from the existing integer column. We copy in batches, iterating over the ID

Speaker 1: , the primary key. So a batch size of 100,000 was found to work quite well. We backfilled by running a Django management command manually on each system. We also tried running SQL manually to backfill, which had some advantages. The important thing is we still had to do that manually on every system. Backfilling the largest tables with over 500 million rows took about two weeks each table. We're backfilling outside of business hours. So um as I mentioned, backfilling across all systems was very labor intensive. Copying the data is just one part of the process of making like the new column like the source column.

Speaker 1: We also need to re-establish constraints and indexes if those are needed. In the example model we had, we had null equals false on the amount field. So we need to do two steps to add a not null constraint on the begin column. The first step here is adding a not null constraint as not valid. Not valid means that the existing rows in the table aren't checked against the constraint which is necessary to avoid locking the table while a full table scan is done. Again, that's the kind of thing we have to avoid to ensure these are safe operations. Now, the as I mentioned, the Django model doesn't know about the amount new column, and we've just but we've just started enforcing that it can't be null.

Speaker 1: Now I don't know if you've ever tried to add a Not null constraint to an existing column, but sometimes if you've got the old version of the call the code, it tries writing to it and it fails because it's not putting a value in for the the new column you've added We've got a d the the database trigger that we created before actually looks after this for us. It makes sure that there's always a value in the amount new column every time we do an update. Step two in adding that not null constraint is we validate the previously not valid constraint. There's I mean there's some Postgres stuff under the hood here we don't need to talk about, but essentially doing the validate, um

Speaker 1: it will take a while because it will need to scan the table, but it the important thing is it's not going to lock out uh access to the table while it's doing that. Right, moving on. The final phase, um, and I should point out that the the phases we've just talked about took in reality took months to achieve. So I'm just talking about it in a few minutes. The final phase is we're going to do a few things, but the eventually we want to swap the columns. We're going to start by dropping the trigger in the function that we created before, and then we do the renaming. So the old column moves to a amount old, the new column comes into an out amount And I should also note that we've started a database transaction before doing any of this.

Speaker 1: That means that if there's an error in any of the things, in any of these steps, um it will roll back to a known safe uh position rather than leaving us in a half and half kind of state. Still part of that transaction. We're going to create a new function and a new trigger and that propagates changes to the New column, the big end column, which is now called amount, back to the old integer column, which is amount old. And this gives us an option that we can back out this change if we discover that it's not working as we expected. And here's that column swap illustrated in terms of table diagrams. After the transaction commits, the system will begin using the new begin column, which is now named Amount.

Speaker 1: And at this point, we want to validate that our system is working normally and that there are no issues. Because if there are issues, we still have the option to back out. Luckily enough for us, it seemed to work. Right, we're almost finished. Now we go back to the field definition on the Django model and we update it. And I'm talking about fields and models again here. We only need to make the change to the Django state of the model because we've already made the database changes. So we run make migrations, having changed to a big end field, but then we edit the resulting migration file. We use separate database and state operation to tell the Django migration system to alter its representation of the model without making any database changes.

Speaker 1: And finally, if we've had no issues, we clean up by just removing the trigger and the function and the old integer column. Now I've left out a whole bunch of details here about setting up constraints, indexes, some other stuff that we did, but you don't need to know about those to understand the general process. So a quick summary of that whole project. Here's what it looked like. We had 14 tables with 29 columns in total. uh 24 production and test systems, 104 pull requests with 95 Django migrations, 192 manual steps, which included backfilling, setting up some indexes I didn't talk about things like that.

Speaker 1: Our largest table had over 660 million rows and the whole project took about five months. And that's five months that Charlie and I are not going to get back. Probably the biggest lesson, we had quite a few lessons learned from this, but biggest one is manually backfilling as a pain. Real pain. I'm going to move on, but we're going to see that point manually backfilling as a pain again. Oh, sorry. I Somebody in our in our images graphics department created the the sobbing uh uh um Katie the the octopus from our logo had to use that On to the second scaling issue, which is the size of database tables.

Speaker 1: So primary key fields Each row in a table has a unique primary key value. So here's a couple of tables. The one on the left has an integer primary key, the one on the right has big int. The one on the left, we try to store a value which happens to be 2 to the power of 31, and that's exceeding the largest value we can represent, so that will fail. On the right when we've converted it to begin to, that's fine. As our tables have grown over the years, we've got tables that have got 2. 1 billion rows. We've needed to be able to have a primary key value larger than these numbers here. The solution till now has been whenever a particular table on a particular system has needed to be upgraded, we just do that manually by the

Speaker 1: Database people jumping in and running some SQL, a series of operations directly on that system. But that does not scale, and scaling is the issue that we're worried about. The other thing is that if that table on that particular system is running out of primary key values, it's likely that same table on a different system which is perhaps a bit newer and hasn't had as long to grow, it'll probably run out of values at the same a as well, at some point in time. Not the same time, but some point later So it would be better if we couldn't create, sorry, if we converted all of those tables of that table across all the systems at the same time. So that's our plan. Going back to this example model, um the single-step version of doing this, converting to it from an auto field to a big auto field.

Speaker 1: Once again, we'll lock the table, rewrite the entire table, and cause an outage of probably days with these tables. So we don't want to do that. So the same solution as before, we're going to use a shadow column and a series of safe steps. So this is exactly the same diagram from before. It's at the high level, it's the same process. There are complications, however. Primary keys will often have foreign keys that refer to them, and there are foreign key constraints. You have to deal with that. I'm not going to talk about that because it's too complicated and will take too long. But that's a significant part of the difference between the previous process and this process.

Speaker 1: As well as foreign key fields, you need to make sure that you deal with creating a unique index on the primary key You can only have one primary key on a table at the same time, so that means how you swap them is going to be tricky. There are also two types of defaults. You can have a generated by a sequence or generated as identity. They're a bit different. You have to look after that. And we've got both of those in our systems. All of those details require attention, but there's nothing fundamentally too difficult to address once you take s uh sufficient care But backfilling, backfilling is still the big issue. And it's going to be a bigger issue here because previously we had tables of like 660 million rows.

Speaker 1: By definition, we're going to have tables with two billion rows, because that's why we want to upgrade them, because they've got two billion rows. So This project is currently in progress. The current status is that we've implemented all the Django migration operations that we need to do this. We've got a backfilling cron job, which I'm going to talk about in detail in a second. We've tested it locally. We're currently using it for the first time in production. Backfilling is underway right now. And then the remaining steps will follow. And if you want to know whether this is successful, you'll have to ask me next year. All right, let's talk about backfilling. As I said, we experienced many issues with backfilling that we wanted to address. It doesn't scale well with a number of systems,

Speaker 1: whether you're running manual SQL or whether you're running a Django management command to do backfilling. And we had to keep an eye on the systems that we were doing the backfilling on because it generates a lot of load. Um, and that's has the potential to disrupt the normal operations. And part of that load comes from the inevitable database vacuuming that is triggered when you do backfilling because you're creating Updated versions of rows, that means you have dead rows or dead tuples in database terminology, and those had to be cleaned up to release and to release space. So we had a wish list for what we wanted to improve with backfilling. It needed to be an automated process once it had been initiated that scales with the number of systems, not overload the database, and be easy to monitor.

Speaker 1: So, as I said, we've got a cron job that we're implementing backfilling with, and this is how we addressed these wishlist items. So for um Uh uh an automated process. Uh the cron cron job is configured to start after business hours for each system and that can be different for each system. Um and of course that's in local time rather than UTC. As if you're familiar with cron jobs, they're normally configured in UTC. They're configured it's configured to finish before the start of business hours. So there's that overnight period that it can run. The backfills you need to do, so particular table specifying which the table is, is configured via Django settings, which in our way of deployment gets deployed as as normal with the rest of the code.

Speaker 1: We record our progress in a database table that we can query. And that table is specific to each system because different backfills might be in different progress on different systems And this will scale with the number of systems we we are operating now. Second point is managing database load. We backfill in batches by ID range as as before, but we can configure the system to sleep for a few seconds in between batches if that's going to be helpful to reduce the load. We also have a round robin if we've got multiple backfilling fillings going on at the same time, so we only backfill one table at a time. The biggest improvement is we're monitoring when we've created enough dead tubules in the table that we know the database is going to trigger an auto-vacuuing process.

Speaker 1: There's a whole bunch of settings in Postgres that get combined together with a bit of simple maths to determine whether a table needs to be auto-vacuued. We repeat those calculations ourselves so that we recognize when we're about to trigger auto-vacuuing, or when we've gone past it actually Um and then we're gonna wait. We're gonna wait until that vacuuming's happened and we can see that the number of dead tuples on that table has reduced. And then we can resume backfilling. And so for monitoring, we publish metrics after each batch so we can see how the numbers going up and to the right, or the lines going up and to the right. We've got a dashboard on Datadog, which we use to view metrics across all systems. And I will show you a couple of screenshots from that dashboard.

Speaker 1: Yep, I think we can see that okay. Uh here's a couple of graphs from the data dog dashboard as of this morning. Um so the top graph, um just ignore that green line at the top. That shows the number of tupels before um sorry shows the backfilling progress in terms of number of rows. The number is a bit small, so that top line is going from just under 400 million. to something above 400 million. That bottom line is under 100 million going to above 100 million. We've only just started doing this in the last week. And the bottom graph shows the number of tutables before auto-vacuuming begins, which can be negative if we've kind of overshot the threshold, which means the table's overdue for vacuuming. So the the yellow line at the top corresponds to the purple line on the top graph.

Speaker 1: And you can see that the the number of tuples before autovacuuming is gradually reducing. The blue line, corresponding to the blue line above, you can see that it suddenly jumps up. That's when auto-vacuuing finished, which means that it suddenly reduce the number of dead rows in the table, which means we've got more capacity to resume backfilling, and that's what happened. Our system detected that autovacuum had finished and it resumed back backfilling. So yeah, these are just a couple of graphs, but we've got ourselves to a situation where we can actually monitor this quite well. So backfilling. I started talking about vacuuming. Even with the improved backfilling

Speaker 1: process, there's still no escaping the problem of vacuuming. Vacuuming is time consuming. In our experience, a few hours of backfilling with one of these larger tables can lead to a few days of vacuuming And we know we're going to have to be backfilling for days as it is, so there's going to be a lot of vacuuming. Vacuuming while it's necessary, it's additional unproductive load on the database. And our database is already quite heavily used. What if we could avoid vacuuming almost entirely? Vacuuming is needed because backfilling a column creates dead tupels. Can we avoid creating dead tupels?

Speaker 1: Oh sorry, there's the sound emoji again. What if we used an entire shadow table instead of a shadow column? Here's a view overview of the process. Now this is all currently in development. I'm going to uh jump over this quickly because I've run out of time. The key thing here is that We are um oh and it's based on uh PG Repack uh Postgres extension. Um the key thing here is if we Set up a whole shadow table instead of a shall shadow column and configure a trigger to copy from the old table to the new table Backfilling doesn't consist of updating an existing row. It consists of inserting a new row in the table, which means we're not creating dead tupels that will need to be vacuumed.

Speaker 1: So this has a lot of potential to completely eliminate all of that time spent vacuuming in the backfilling process. Still very much being implemented by my my colleague Marcello. We haven't tried it in production. There's a lot of details still to be worked out, but it does look very promising. And again, ask me next year how that went. All right, so running out of time, quickly on to the conclusions. The first conclusion from before, avoid issues by using big int instead of integer from the start. Converting columns is a lot of work, creates a lot of extra database load, but using Django migrations to do it. Enables you to scale this process across a large number of systems, which has been the real win for us.

Speaker 1: But we're still working on backfilling. Thank you very much. And I'm told we have time so for questions if there's anyone.

Speaker 2: Florian, great talk. I saw you left it out on purpose, but I'm still going to ask. Uh once you're going to switch to the new big integers Um you have a dependency between the tables because you can't have a foreign key pointing from an integer and pick int, which will lock multiple tables. How are you going to handle that?

Speaker 1: Yeah, so the way you handle that is by setting up Um you essentially are doing two big int conversion processes at the same time, one on the main table and one on the table that has the foreign key. And the the trick is if you get the sequence of steps right You end up creating uh so there's an existing foreign key constraint, you end up creating a new foreign key constraint from the new big int uh column on the other table to the new column on the original table. And yeah, as long as you get the sequence of steps just right, you don't have that problem where you've um you've got that dependency and then when you do the swap again you've got to get rid of the old constraint before you drop columns and as I said it's it's kind of the details that that once again you've got to get right and it takes some

Speaker 1: some effort to get the to to sort that out but Uh uh once you've done that it is straightforward, I assure you.

Speaker 3: Okay, we have time for one more question.

Speaker 4: Um I'm picking something on the vacuum bit. So the process you described with the vacuuming was that you kind of wait until You're just gonna trip it essentially then back off in a way. Did you try consider like you know disabling auto vacuuming and then controlling vacuuming driving it yourself in a certain way? Or was there issues with that? Any concerns on that other than

Speaker 1: that's a that's an interesting idea. Um for us the idea that we wanted to control uh so disable autovacuuming, control vacuuming ourselves. Um It's kind of like that's extra intervention that that we would have to do. And the whole uh point of this backfilling system is we wanted to minimize the amount of intervention that it can just kind of run By itself. The problem with uh allowing so overriding the auto vacuum trigger and letting it go for longer and then doing a vacuum is that So there's actually not a great deal of benefit. So uh I I haven't done the numbers, but my sense is

Speaker 1: No matter how much work you give the vacuum process and how you split it across multiple vacuums, you're going to still end up doing roughly the same amount of work. Does that answer your question? Yeah. Cool

Speaker 3: Thank you. Sarah Bull system