Turn back time:Converting integer fields to bigint using Django migrations at scale
Published June 4, 2025
This video features Tim Bell at DjangoCon Europe 2026 in Athens, Greece.
TALK: Partitioning very large tables with Django and PostgreSQL by Tim Bell
Introduction Database systems like PostgreSQL provide a useful layer of abstraction: you can store and retrieve data using SQL (via the Django ORM) without having to worry about how the database manages the data under the hood. But as tables become extremely large, the abstraction starts to break down, and performance issues become apparent. In this talk, I'll describe what options are available (including partitioning) to manage large database tables, and how we're approaching this problem at Kraken Tech.
More information: https://2026.djangocon.eu/speakers/SKMQND/
Follow DjangoCon Europe on social media:
*X - https://x.com/DjangoConEurope/
*LinkedIn - https://www.linkedin.com/company/djangocon-europe/
*Mastodon - https://fosstodon.org/@djangoconeurope
#django #djangocon #djangoconeurope #djangoconeurope2026
Automatically transcribed, so expect mistakes in names and technical terms.
Speaker 1: I hope you all have loosened up a bit and moved your legs. No, that stand up once more and sit down again. That's the last chance because we're gonna have a nice talk now again It's my pleasure to introduce Tim Bell from Craken Tech. He's come over from Australia to visit us here and I think he likes traveling. He took six flights to get here. And I guess that wasn't necessary, but I'm told you saw Milan, you saw Barcelona. So um I'm I'm expecting a lot of sunshine here. Yeah, so but the the the floor is yours. Table partitioning with Postgres and Django. I'm looking for
Speaker 1: Thank you. Let's welcome Tim
Speaker 2: Thank you. And Kalimera. Hi, I'm Tim and I work at Kraken Tech. We write and operate our Kraken software that supports energy and other utility retail operations for our clients. Kraken currently has about 40 different production installations around the world and we deploy new versions of code about 50 times per day. At Kraken, I've spent the past few years doing stuff with databases. But I want to start with a disclaimer. I am not a database administrator, but the things I've worked on at Draken have been giving me an education in DBA things, and it's been quite a learning experience. Recently, I've given some talks on database topics
Speaker 2: reflecting things that I've learned in 2023 about locking cues and how the Django ORM is a leaky abstraction. In 2024, about making database schema changes safely using multiple steps. And in 2025, about doing big end conversions using Django migrations. It was while doing a big int conversion at work that I encountered an issue that has motivated the next topic for me to dive into. We had an issue. There was a huge table, three terabytes in size. It had nine billion rows. Nine billion? Yes, we were using a big int primary key. And there were 20 to 30 million row updates per day. And if you know about how PostgreSQL, when you update a row, that leaves a dead tuple behind that needs to be cleaned up by vacuuming.
Speaker 2: And the vacuuming needed to run about once per day. Because of the size of the table, vacuuming typically took 16 to 20 hours to complete Now the issue here is that auto vacuuming blocked or delayed the schema changes that we needed to do for begin conversion. So, what could we do about this huge table and the time it was taking to vacuum? That's where table partitioning enters the story. As I said, I'm not a database administrator by background, so I needed to learn about table partitioning. What it is, how it works, and how to do it So I turned to the book PostgresQL 14 Internals, which is my go-to reference for understanding Postgres. It's an excellent resource, extremely detailed.
Speaker 2: Unfortunately, there isn't a chapter on table partitioning. The book doesn't cover the topic at all and doesn't even mention it except once in passing. Luckily, there are some talks about partitioning out there. This one from Karen Jex is excellent. If you're watching this talk on YouTube from the future I recommend pausing now, going to this link, watching Karen's talk, and then coming back to my talk. For those of you in the room, sorry. And of course there are the Postgres docs. Partitioning refers to splitting what is logically one large table into smaller physical pieces. The docs are excellent, but there are many details to discover.
Speaker 2: Understanding the implications of the details takes some work, and there's also the question of how partitioning works with Django specifically. All of these things are what I'm going to be covering in this talk. So let's begin with the basics. Quoting from the Postgres docs again, the partition table itself is a virtual table having no storage of its own. Instead, the storage belongs to partitions, which are otherwise ordinary tables associated with a partition table. So here we have my table, a partition table. It's a virtual table, that's the represented by the bit across the top, and has no rows itself. Each of its ten partitions are actual tables with rows.
Speaker 2: And you can see they have names like my table part one, my table part two, etc. The point that partitions are just like ordinary Postgres tables is really important. They have their own indexes and constraints, but they do need to have the same columns as the main table they're partitions of. So the previous slide was missing some details about partitioning that we're going to fill in here. Partitioning is done by rows, and each row belongs in a partition based on its partition key value. The partitioning method and partition bounds. So here we've partitioned my table with the range partitioning method using the ID column as the partition key
Speaker 2: Each partition has partition bounds defined. So for the first partition, its bounds are ID values greater than or equal to zero and less than 100, and so on for all the other partitions. Note that uh practical use cases would have a much larger partition than 100 values. It's just if I used a million here, the slide would be too wide. Partitioning ensures that each row has a home in at most one partition. So Partitioning is transparent to the application. That you know, Django. That means that Django can insert, update, and delete rows as usual. as well as query as usual.
Speaker 2: And it doesn't need to know that the underlying table is partitioned. But uh down the bottom it says it 's mostly transparent. Spoiler alert, it's a leaky abstraction. I've mentioned a leaky abstraction before. What is it? Here's a slide from my 2023 talk with Joel Spolsky's law of leaky abstractions. All non-trivial abstractions, to some degree, are leaky. And what that means is that some detail that is supposedly abstracted away actually becomes relevant. The Django ORM is one example of a leaky abstraction. We use it to abstract away the complexity of the database that underlies our data models. But that abstraction leaks, and we sometimes need to engage with what's going on in the database.
Speaker 2: Partition tables are another leaky abstraction. You need to pay attention to the details of the partitioning to avoid issues. And we'll be seeing what some of those potential issues are later on Why do we want to partition tables? So, one reason is to reduce table maintenance time vacuuming. That's the motivating factor that got me interested in this. Data lifecycle management, so the idea about efficiently dropping old data. Query performance can be improved by partitioning. And all of these can help with scaling your application. And that's a big issue at Kraken, because we keep growing and we keep putting more customers on one database. Next question is when?
Speaker 2: When should we consider table partitioning? You should consider partitioning your table if it's over 100 gigabytes in size. And I think three terabytes qualifies there. If it has over a hundred million rows. But this is actually a trick question. Once your table has already grown to this size, you might already be experiencing problems. So if you expect your table to grow to above those sizes, then you should be considering partitioning. And later on we're going to look at how to convert a normal table to a partition table. just in case you realize too late that it needs to be partitioned. Finally, given the complexity of partitioning, you should not partition unless you really need to.
Speaker 2: So there are some key decisions to be made when partitioning a table. First is the partitioning method So I've shown you the range method before. There are also list and hash methods, but I'm not going to talk about those in any detail. The choice of the partitioning key is critical and it also helps determine the partitioning method. So some keys work with some of the methods better than others. The number of partitions is also important and dependent on the expected sizes And they kind of you consider those two things together. And a fundamental feature of partitioning is the ability to add or remove partitions as needed. But you need to choose your partitioning method and key appropriately for that to work.
Speaker 2: So for example, if we range partitioning by a created at column, you could have say one month per partition or maybe one year, whatever suited the expected amount of data. So um Here are some principles. Karen Jekgs says in the in the talk I linked to before, partitioning often works best if the application is designed from the ground up with partition in mind. However, if you find that you need to retrofit partitioning to your application, which is what I've found, you may need to make compromises Whenever you're looking to partition your table, you need to choose your partitioning method and partitioning key based on the queries you expect to use and the lifecycle of your data.
Speaker 2: For instance, if you want to remove data that's more than seven years old, then you'll need to have the partitioning putting all of that data in one or more partitions by itself. Another principle is that ideally queries should access only a single partition. When the query includes the partition key in the where clause, Postgres can do what's called partition pruning to achieve that. And just to restate, the choice of the partitioning partition key is critical. So let's look at that partition pruning I just mentioned. The Postgres query planner looks at the WHERE clause of the query. So this is a very simple query where ID equals 271 is the where
Speaker 2: clause. So it uses the ID column, which is the partition key in this case, and the given ID value falls in the third partition, that one there, looking at the partition bounds. So the query only needs to consult that partition and its index. And this is actually done by the query planner before it actually starts executing the query. The other nine partitions are pruned from consideration, so they may as well not exist as far as this query is concerned. So once again, partitioning pruning is possible here because the partition key was in the where clause. There are limitations. I've gone on about the partition key. One of the limitations that's critical is that
Speaker 2: it must be part of any unique constraint. including the primary key constraint. And this comes down to how Postgres implements the constraints through indexes on the tables. And remember, partitions are tables. And if because there are separate indexes, if the partition key is not part of the constraint, it can't guarantee uniqueness across all of the partitions. So if you want to partition on something other than the primary key, you might need to look at using composite primary keys. Now Django 5. 2 added partial support for composite primary keys But it doesn't have support for foreign key relations with database constraints by default. So that's a little thing to be aware of
Speaker 2: and is a reason to avoid composite primary keys. There's another limitation which is Postgres can't partition existing tables. But there are a few ways around that limitation and we're going to be looking into some of them. So far we've been talking about partitioning mostly in Postgres terms. Where does Django fit into all of this? The big surprise is that it has no native support for table partitioning. But because partitioning is mostly transparent to the application, Django doesn't actually need to know if a particular table is partitioned. Queries via the RRM will still just work.
Speaker 2: So yes, it's okay thanks to transparency, but watch out for leaky abstractions. How do we get a partition table with Django? Let's look at some of the options. There's a package called Django Postgres Extra , and it lets you declare a model to use partitioning. That will use a special migration operation to create the table as partitioned. And here's an example from the documentation. However, there's no support for partitioning an existing table. Also, unfortunately, the package doesn't support recent Django versions, but there are other packages that might be able to help, but they're not Django Django specific So PGPartman is a Postgres extension that's very popular and well regarded. It's used to manage initial partitioning as well as maintenance tasks, maintenance tasks such as adding new partitions
Speaker 2: There's no easy way to integrate it with Django to create a models table as partitioned from the start, but it does have a way to convert a table to being partitioned, but that requires copying each row twice in the process. There's another package called Psychopack, and it's been developed by one of my colleagues at Kraken Tech, Marcelo Fernandez. It's a Python package, but doesn't integrate directly with Django as simply as Django Postgres Extra. It has a method for partitioning an existing table, but it also requires copying each row, but only once. So while PGPartman and Psychopack don't integrate directly with Django, you can use them with custom Django migration operations that you write yourself.
Speaker 2: This is a very desirable option for us at Kraken Tech, since we have our code deployed across dozens of environments, actually I said 40 earlier, and want to avoid manual processes. So our preferred way for making database schema changes is via Django migrations. All right. I now want to take us back to the table that motivated my uh investigation of partitioning in the first place. It's a table called Workflows Step. We're going to look at the different considerations that go into deciding how to partition the table, what else we need to do to make that partitioning work well. As I previously mentioned, this is an existing table. It's used in our workflows feature.
Speaker 2: And workflows track the status and context of each step within a workflow. Once the workflow is complete, the steps are not updated any further, which means that old rows are only accessed for reading from that point onwards. This is a really important characteristic of this table that open up opens up a particular way of partitioning the table. The size of the table not only causes issues but also makes trying to solve those issues via partitioning trickier. In particular, we want to avoid copying three terabytes of data if possible. There are other things we could do with this table, such as archiving old rows. That's out of scope for today's talk. So, what does this table look like?
Speaker 2: Finally, some some Django code on the screen. Here's some of the model definition for this table There are some other models that have foreign keys to this model, which I'm not going to show, but that's just something to think to be aware of. It'll come up later. So the question I'm going to start with is what partition key can we use? The ID field, the workflow field, the created at field status. The important question is we need to support the existing queries on the table. And I had a look at all the queries that we do on this table, and most of them are queries by ID. which is the primary key, or by the workflow ID, which is the column underlying the workflow field.
Speaker 2: So skipping the decision process, here are the decisions, the partitioning decisions we arrived at. We'll partition by the ID column. the primary key. This avoids issues with composite keys and it supports queries by ID. And we're going to see later about how we can support queries by workflow ID. And we'll use range partitioning. And the third question is about bounds. And the partition bounds we'll use will actually be determined by the data in the table at the time we partition it. Also note that partition bounds don't actually need to be equal in size for each partition. So one approach to partitioning this existing table is to create a new partition table, copy all the rows from the existing table to the new partition table.
Speaker 2: And then in a transaction, rename the old table to something else and rename the new partition table to have the same name as the old table, the old switcheroo trick. The two packages we mentioned previously can help with this particular approach, PGPartman and Psychopack. They both support doing this process without interrupting normal operations. That's what online means in this context. But copying three terabytes of data is undesirable. It creates a lot of I. O. load on the database and the database is already very busy. And it takes time. At a rough guess, I think it would probably take days or possibly even weeks to copy this data. Can we avoid, uh is there a way to avoid copying all the data? And I think you can probably guess that yes, there is.
Speaker 2: Online partitioning on ID using the mammoth. first partition. I'm calling this the agin method because the blog post you can see linked there is is where I've seen this this method documented. And they've also published a library of SQL code on GitHub to help you do this. What is this? The high-level overview is that we want to make a new partition table and we're going to use the existing table as a mammoth, that means enormous, in other words, first partition. And we'll use range partitioning with the primary key ID as the partition key. So I've got some pictures coming up, I promise you. There's been a lot of text I know. How will this solve our problem?
Speaker 2: How is this going to solve our problem? We know from how this table is used that the mammoth partition will stop being updated once the active workflow is complete. At that point it won't need to be vacuumed again because it's not being updated. And so the size of the table, or the partition as it will be now, is no longer a problem. We can also add new partitions periodically as necessary as we start consuming the IDs for more and more workflow steps. And maybe we can transfer the mammoth partition data to other storage so we can drop that partition at some point in the future. Alright, I promised you pictures. Here's what this looks like. Here's our original table.
Speaker 2: I've shown it with ID values from eight from zero to eight billion, which is a small white lie you can ask me about afterwards if you want to. First thing we do is we're going to rename the table. So I've called it Workflow Step Mammoth. Next we're going to create a new partition table with the original workflow step name. Then we attach the mammoth partition as a partition, sorry, we attach the mammoth table as a partition of the partition table. Finally, we create and attach a new partition that's initially empty that will have all the new rows that are subsequently inserted So to make this process work without disrupting operations, we have a couple of requirements.
Speaker 2: We have to hold any exclusive locks for as short a time as possible And to achieve that, we have to ensure that every step in the process is fast. That means that we can't afford to do any table scans of any large table. So there are some tricks that we use to achieve these requirements. First is that we're going to take exclusive locks on the workflow step table and also on all the tables that have foreign keys that refer to it to make sure that no data is changed while we're doing this process. And the second is to create new constraints as not valid to avoid table scans. Let me explain this trick about creating constraints without table scans. The first step is that we
Speaker 2: can create the constraint as not valid, and that's an instant change. Normally we would then validate the constraint, but that requires a table scan, so we won't do that. Instead, what we do is we just tell Postgres that the constraint is valid. We do this by making the catalog chain shown with this query here. Now, this works just fine. As long as you know that the constraint is actually valid and you have to take responsibility for that And one way we can do that is with the exclusive locks on the relevant tables to prevent data changes. And the trick is that we knew the constraint was valid to begin with, we locked everything.
Speaker 2: We can remove the constraint, create it again, and it will be valid. But we use this trick a few times, so keep it in mind. So, yes, it works fine as long as you know that the constraint is actually valid. Let's look at the partitioning process in a bit more detail. Um, I've omitted some steps for clarity. I know there are still ten. Let's start by looking at just these three steps. So we're gonna we're gonna start by taking the exclusive locks that I mentioned. To ensure consistency. Step two, we're going to we need to drop the foreign key constraints that refer to the existing table because we need them instead to refer to the new partition table instead.
Speaker 2: And then we can recreate the constraints at step 10 using the trick I showed before, knowing that the data hasn't changed since the constraints were dropped at step two. These steps, at step three, we add a check constraint so that when we attach the table in step six, Postscribers can rely on the constraint to ensure that all rows in the table match the partition bounds without doing a table scan. And we're going to rely on the trick from before to make this constraint quickly. At step five, we create the new partition table with the same schema as the original table, and then we attach the partition at step six. And then we can drop the constraint, the check constraint from step three at step seven since it's no longer needed Two more steps.
Speaker 2: At step eight we create a new empty table that will be the second partition. Again, it will have the same schema as the main table and the mammoth first partition. And that includes all the indexes and constraints. At step nine, we don't need to worry about doing a table scan on the new partition because it's initially empty. And so the table scan will be instantaneous. Alright, time to show you some SQL. Here's the SQL that does this partitioning. I've turned it sideways, both because there's so much of it, and also to make sure you don't try to read it. Most of the lines uh here are dropping the eight referring foreign key constraints and then recreating them later. You can kind of see little patterns going on there. One key thing to note is shown by the first and last lines though,
Speaker 2: which are there, and they say begin and commit. This whole operation happens within an atomic transaction. This means that as far as Kraken, the application is concerned, one second the table is not partitioned, the next it is. And because we've ensured the operation, every operation is fast, it's actually really less than one second. Also, all of this is done with a custom Django migration operation. So we at Kraken can deploy this automatically to all our instances with no manual steps involved All right, a few more things to check off. Query performance. Let's think about queries on new active workflows.
Speaker 2: That is, workflows that have begun after we've done this partitioning, and so all their workflow steps are going to be in the new partition. We want to avoid touching the old mammoth partition when we do these queries. So we're going to rely on the query planner to prune the old mammoth partition away. If we're querying by ID, that's fine. That's the partition key, it can prune the the old partition away by uh the value of the idea we're querying for. But what about queries with workflow ID like this one I've got here? Postgres has a feature that enables those to be efficient as well. The Postgres query planner can use constraints to exclude partitions once we've enabled this feature. It's the same concept as partition pruning, but it uses any relevant constraints instead of the partition bounds.
Speaker 2: So we can add this constraint that I've shown here to enable the constraint exclusion to work for our queries by workflow ID. The two values there are basically the minimum and the maximum value of workflow ID in the mammoth partition. So that solves that problem. What is the status of of this work? So I've got a proof-of-concept Django migration operation in development. It's got this lovely small name, which seems appropriate given the size of the problem we're dealing with. And I've written up a Kraken internal proposal to actually do this work. So we haven't done it yet, but I've got, you know, I've been running it locally. A few more things to quickly mention before I run out of time.
Speaker 2: A few caveats. Some schema changes with Django migrations do not work on partition tables. And these are ones you might want. Adding indexes and adding constraints. There are workarounds, but if you just try to do the default thing with Django, it'll it'll fail Another thing is that queries that can't use partition pruning or constraint exclusion may perform extremely poorly when there are many partitions. And if you're interested in this question, I can uh speak to me later and I can give you a link to a brilliant blog post that describes this problem that somebody had. So conclusions. Partitioning is complex. I think I might have persuaded you of that. It's a very leaky abstraction where the details really do matter and they do
Speaker 2: trickle up to the application level But with tricks, it's possible to partition an existing table, but you do need some workarounds for best performance Thank you.
Speaker 1: Thank you very much. Very interesting indeed. So um yeah, I'm I'm curious if there's any questions for Tim
Speaker 2: Hello.
Speaker 3: Thank you. So as uh we um we saw before in the other talk about um multiple databases and supported in Django. The replicas is something that for Postgres, for example, handle by itself. So we are not forced to deep dive and this complex thing. And instead of using these complex tools that you convince me that is very very complex. It's not can be better to just update to the latest version of Postgres which has more capacity capacity to handle uh more rows and more capacity in the table and not
Speaker 3: go inside the rabbit hole or so of double partitioning.
Speaker 2: So it's always good to upgrade to later versions of Postgres. It keeps getting performance improvements. This is the kind of thing, I mean we have been doing that, not as as often as I would I would like. You know, we're still a few versions behind. But this is the kind of thing where where Really um the the fact that we've got to the stage we've got a three terabyte table that's getting updated and needing vacuuming That's really uh I think on us as developers that we need to find a solution to that. It's not something where we'll get a little bit of extra performance. Now we're we're living with it at the moment and in normal operations the vacuuming is not prohibitively problematic. But when somebody like me comes along and wants to do schema changes, it it
Speaker 2: gets in the way. So I think this is kind of the thing where if you've got your if you've found yourself into this problem where you've got a massive table, you really do need to address it. You know, nobody would recommend operating a table that large as you know as a sensible thing to do. Yes, you'll get some performance improvements from later versions of PostgreS. There's even some extra extra support for some things to do with partitioning in later versions that I haven't included here. But yeah, this is one of the things that I think you just have to bite the bullet and and and deal with it. Marcus.
Speaker 4: Tim, can you change the value of the partition key? Like you have your record in the in one of the partition tables. What happens if you change The ID from one to eight nine billion and one.
Speaker 2: Yep. Postgres will handle that. It will see that oh you've updated the the the primary key, the partition key value. It no longer belongs in this partition. I'll move it to the partition it belongs in. Postgres handles that.
Speaker 5: Yeah, thank you for your uh talk. Uh I was thinking would it be possible if you have created the memo table to the mammoth petition to then later still split it up into separate subpartitions so you first split it up in the MAMA to get the out of vacuum problem solved and then later split it up for FC
Speaker 2: Yes you could. Um so that is the kind of situation where you might actually want to look at subpartitioning So any of the partitions, well each partition is a table in its own right, and as a table, it could be partitioned. So I think it's a fairly complex use case, but I think that is something you could you could consider doing. You would still you'd then be at the pla at the situation where you need to copy the data. Because there's no way that you're going to be able to partition that big table without copying the data. And because of that, I would question whether you maybe would prefer to just copy the data from the start using one of those other techniques that I that I mentioned mentioned briefly. But yes, subpartitioning might might let you do that.
Speaker 2: Thank you.
Speaker 1: Thank you. I saw one more question there. That's the last is that right? Yeah.
Speaker 6: Thank you very much. Um the one of the last slides uh talked about I might not have remembered the exact words, uh a check constraint with the workflow ID. optimize things. Whenever in the future you introduce a third partition, how are you gonna handle that part?
Speaker 2: So I had to skip over quite a lot of details. One of those is the maintenance that will then be required on this on this partition table, which includes periodically adding new partitions And other people have have talked about this particular problem and essentially what you're going to do is when you add a new partition. Either immediately or a short period of time after that, you're going to then add a new check constraint on the partition, kind of like the second last partition So we're in the same situation that we have with the mammoth partition, which is there's a constraint that the query planner can look at and say, oh, I know your row is not going to be found in this table, I can exclude it and just look at the most recent one.
Speaker 1: Thank you so much. Tim, wonderful. Give him an applause.
April 15 - 17, 2026
Athens, Greece
Note: We understand that names change, people change, and bodies change. We respect each individual's journey and privacy. If you have any concerns about a video or need us to remove content, please don't hesitate to contact us. We will handle your request with care and promptly address any issues.
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026
Published October 26, 2026