How to get Foreign Keys horribly wrong in Django

Speaker 1: Don't be intimidated by the one out of one hundred and six slides. First of all, I'm very happy to be here. I've been a member of the Django community for about maybe eight, nine years. I've built successful products using uh Django and other open source technologies. And I'm uh writing about Django, I'm talking about Django, and I'm very happy to be here. So thank you all for coming. Um my name is uh Haki, I'm a software developer, I'm also a team lead, and uh today I want to talk to you about foreign keys. So the topic is foreign keys, but as you'll see, I'm going to use this as an excuse to talk about other stuff as well. So

Speaker 1: The basis of our talk is going to be a simple Django app. We're going to build a product catalog in Django, okay? So the first model that we're going to have is a category, basically ID and the name of a product category in our catalog. Next, we're going to have a product. We have ID, we have name, we have description, and then we have a foreign key to the category, which is appropriate because this is a talk about foreign keys Next, we have the sort order of the product inside the category. When we display the catalog UI, we want to make sure that feature products displayed first. Less popular products displayed at the bottom, so we have the salt order within the category. To make sure that we don't repeat the salt order within the category, we have a unique constraint at the top.

Speaker 1: You can see unique together category, salt order. Next, we want to keep some audit information about our products, who created the product and who edited the product. Okay, this is the two foreign keys at the bottom. Since this is a talk about foreign keys, I just wanted to highlight that we have three foreign keys, category, and two foreign keys to the user model in Django. Okay So, what we're going to do now, yes, yesterday they told us that we need to do more code reviews. So, what we're going to do is we are going to go through a process of doing code review to this very, very simple app. And in the process, we're going to see a lot of Little things that you need to pay attention to when you're building Django models. Okay, so just to get us warmed up, if any of you saw this unique together and felt uncomfortable in your seat, it's for a good reason

Speaker 1: unique together if you go to the documentation you see that it's uh soon to be deprecated. I don't know when, but probably someday, hopefully soon. And that you should probably use unique constraint instead. Okay, so this is a simple change. We're going to do it together, okay? So We go to our model, we drop the unique together and replace it with a unique constraint. This is basically the same thing. Okay? So the first takeaway, and that's an easy one, you always have to start with something easy Don't use unique together, use unique constraints, you have better control, and it's encouraged by the documentation. Okay So now let's have a look at our schema. Okay, this is our Postgres schema slash D describe the product table.

Speaker 1: Anything sticks out to you. I encourage you to answer. You can yell at me. It's fine. It's part of the process. Anything sticks out. Let me give you a hint. Anything sticks out. That's right. We have two indexes prefixed by category. Okay, so when we query by category, basically that Postgres can now use one of the two indexes, right? So maybe we can drop one of them, right? So the first index is the unique constraint we just created. Basically, it's not a good idea to drop a unique constraint because it's not used just for performance, it's also used to enforce some kind of constraint So the in the next index, it's not that clear where this index came from.

Speaker 1: So this index came when we defined a foreign key. Okay, so we also talked about this yesterday in Adam's talk. When you create a foreign key in Django. Django will implicitly add an index on this foreign key. Now, and most of the times this is a good idea, like this is a good default. If I had to make this decision, I'd probably come to the same conclusion But it's not always a good idea. If you continue reading, you can see that you can control this behavior by setting db index to false Okay, so let's go ahead and do that. So we go back to our model, we add db index equals false. And just to leave something for the next guy, I'm also adding a comment explaining That this is indexed by unique constraint. The good reason I decided to ditch this index, right?

Speaker 1: So now we generate the migrations, and what's next? What comes next? Apply the migration. Stop right there, my friend. Stop right there. Right Whenever we apply migration, we first need to look at the migration. Okay? So this is the migration that we just generated. It looks harmless. It looks very, very innocent, right? We just alter the field, we change from db index true to db index false. What can possibly go wrong with such a simple migration? So then we take the next step. Now we want to look at the SQL generated by this migration. What do you see? Look at this SQL. What do you see?

Speaker 1: What do we actually want to do? We have a unique we have a foreign key constraint and we want to drop the index, right? What is Django doing? Django is recreating the foreign key without the index. Is this what we want? Absolutely not, right? Jengu is recreating the foreign key. So why is this bad? First of all, dropping an index is a potentially blocking operation, right? If we have a very large index and we're dropping the index, it blocks. The second and possibly the more problematic issue here is that recreating the foreign key requires validating the foreign key. And validating the foreign key is both blocking and potentially very, very slow, right? If we have huge tables, this can take a lot of time.

Speaker 1: But we don't actually want to validate the foreign key. The foreign key is fine. We just want to drop the index, right? Okay, so let's actually do that because apparently Django can't identify the situation by itself, so we need to help him. So To understand how we can change the way Django executes stuff in the database, okay, and people, I'm actually very happy that I'm not the first one to show you this up. But we need to understand how migrations work. So the when you do make migration, what happens is Django will take all your migrations, right, apply them in order, generate some state. They would compare that to the state of the models like the ones you have in your code and generate uh migrations from the difference. These are the migrations files containing the operations, right?

Speaker 1: And finally, when we issue migration, it takes these operations, converts them to SQL, and applies them to the database. So if we want to provide different SQL , Then what Django is generating, we can use this special op called separate database and state. Okay, you've seen it yesterday, so let's see how it works. This is the change that we're going to make Okay, so instead of doing altar field like Django suggested, we're now doing separate database and state. So let's break it down. First Separate database and state allows us to execute stuff on the state and different things on the database, right? So the state operations that's the easy part. Just take whatever Django generated and stick it in there. Okay This is exactly what Django generated.

Speaker 1: Very easy. Next up we have the database operations. This is what we actually want to execute in the database. In our case, all we actually want to do is drop the index. Okay? So let's check the SQL generated by the migration now, and we can see that this is the SQL that we asked, we said we want to use, right? So the next takeaway is always check the SQL generated by migration before you execute it because who knows what you'll find there. Maybe it's recreating foreign keys. I don't know. Okay? Now We're applying the migration, we now get the SQL that you want, right? But something is missing. What if we made a mistake and we want to roll back?

Speaker 1: So, Django has this very nice feature that allows you to unapply migration. If we try to unapply our migration, we're going to get this nasty error saying that we can't, because the migration is not reversible. Now this is an easy fix, right? We used RANSQL, and the second argument to this function is the opposite SQL. So if we want to drop the index, the opposite would be to Create the index, that's right. So we want to create the index. Where do we get this SQL command from? From the migrations that edit it. In this case, the initial migration, right? So if we want to reverse the migration now, we can see that it's working. Beautiful. So the next takeaway, whenever possible, provide reverse SQL to any operation.

Speaker 1: Who knows when you're going to need it So what if we have a very, very busy system, right? We said that dropping an index is a blocking operations. If we have a busy system and we have a very big index, dropping the index can cause some problems with Live traffic, right? So one thing we can do is we can drop the index concurrently, okay? But there's a catch here that we need to understand. So let's break it down To drop an index concurrently, okay, in Postgres, it drops the index without obtaining exclusive locks, which means it doesn't interfere with ongoing traffic on our system, right? So it can take a little bit more time, but I think that it's worth it if you have a business system.

Speaker 1: The next cache that we need to be aware of is that Postgres implements something called transactional DDL, which is a great feature. It basically allows you To run a bunch of changes to your schema, ultra drop create, and then commit them at the end. The thing is, concurrent operation cannot be executed inside an atomic block So you need to turn atomic off, which means that now if you have a lot of operations inside your migration and something fails in the middle, you risk Leaving your system in an inconsistent state. Okay? So if you have just one op, it's fine. You said atomic false No worries. If you have a lot of operation in your migration, it might be a good idea to split the non-atomic part to a separate migration.

Speaker 1: Okay? So our next takeaway is if you have a business system, use concurrent operations on indexes, job create indexes, okay? So this is our final migration. This is what it looks like. It took some work, but we made it. Right now it's fine, it's safe, it's reversible, everything that we need. So now that we are all foreign key experts, okay, we have another foreign key created by, right, points to the user. Now the question is, do we need the index on this field? What do you think? What if I told you that Created by is used mostly for audit purposes. Nobody cares

Speaker 1: like how many give me all the products that Aki created. Nobody cares about that, right Given a product, they want to know that Aki created it, but nobody cares. Give me all select star from products where created by equals Aki. So why do you say can we drop this index? It was created implicitly for us by Django. We didn't make this decision explicitly anyway, so can we drop it? Okay, I see yes, I see no, you're all very quiet. So I just say sure, let's drop it, okay? Let's drop it. Now, before we drop an index on a foreign key, and some of you caught this, we need to stop And think for a second, okay? So how exactly are indexes on foreign key are being used, okay? So The first use is pretty straightforward.

Speaker 1: Like when you do select stuff from products were created by equals Aki, then the index is being used, right? We said that this is not the case for us, right? We don't use that The other occasion where foreign key is being used is not that obvious. Okay? So when we you delete related objects, user for example. The database in Django needs to make sure that nobody references this user, right? So they would issue queries to check that nobody's using, nobody's referencing this object Okay, so this, what you see right here is what we call written in blood. And the reason for that is We had this scenario in our system when we had

Speaker 1: uh uh a process where we deleted users, okay? So I don't know. Django made this uh unfortunate decision to implement registration on top of the user model. So what's happening is if someone creates a user and never validates the email. You s you have a bunch of users with unvalidated emails, right? So we didn't want that in our system. That's a pretty big table, and you don't want trash there. So we started deleting user after a while. Like if you didn't validate your email after a week, we just delete the user. Okay? But then we started getting this very strange arrows. These delete commands started to time out. Why did they time out? I mean so simple. Nobody ever used this user. It's indexed. What's the problem?

Speaker 1: So when we start investigating, we came to this conclusion. So let's see a demonstration, okay I create this user, I call him hockey. Okay? I just created this user, nobody is referencing this user. Now let's delete this user. Okay? Now see what's going on I turned SQL login on and I delete this user. See how many things are happening behind the scenes when you try to delete a user. So first we can see two queries there looking for products referenced by either created by or last edited by. Now if you notice, this is the exact type of queries that can use the index in our foreign key, right? Then you open a transaction, you start deleting rows from other tables that we didn't even think about that also reference the user.

Speaker 1: In this case, these are internal Django tables for admin log, user groups permissions, and so on. And only then, at the end, we actually delete the user Now I want you to see something interesting. Look at how long it took to commit this transaction. It took around 300 milliseconds. Why is that Why do you think it took so much time to commit this deletion? No. It might be, but there is another reason because Django was very nice to do all of these checks, right? And if you ever try to delete something from the admin, you'll see that you get a breakdown of what's going to be deleted.

Speaker 1: And when you delete something in Django, you get a dictionary back saying which object got deleted. But with all due respect to Django, when I delete something, Postgres also needs to make sure of all of these things, right? Because Postgres is the one guaranteeing that the foreign key holds. So if you look at the timings, I would say that what's happening during commit is basically all of this stuff on a world cache. Okay? So basically we do all of these checks twice when we try to delete something. So the next takeaway is that indexes on foreign keys are also used indirectly. when we delete related object, in this case a user. So while we'll not use them directly, there

Speaker 1: might be used indirectly when we delete the related object. So we are going to keep this index. But we are going to do so explicitly. And we're also going to add a comment explaining why we decided to keep this index. So the next takeaway, and I can't emphasize this enough, Always explicitly set db index on a foreign key and add a comment explaining why. I went to the extent of adding a check, a Django check to make sure that you always said db index explicitly. Otherwise you fail CI Someone come yell at you. Now let's move on to the next index. But you say, can we ditch this index? Right, we have this index last edited by, right? It's snollable.

Speaker 1: We don't care about the users who edited a product. We just want, you know, mostly for audio purposes. So you say no, I see someone shaking their head. Thanks for the participation, man. You're you're great. So we're not using it to filter by anything, but we already know that it's used when we delete associated uh objects in this case a user so we are going to keep it but we're not done with this index okay once again let's see a demonstration For this demonstration, I'm actually going to add some data to my tables, right? So I'm going to create 100 users, 50 categories, okay, and I'm going to add a million products, okay

Speaker 1: By the way, one cool thing I learned throughout this demonstration is that Django has lorem ipsum filter, which generates blobs, chunks of text. Very useful for stuff like that. So if you'll notice I generate a million products and then one every 1000 products Has actually been edited. Okay? This is to mimic what happens in real life. Most products are not edited, only some of the products are being edited. Adam, you know where this is going I think you do. Okay. So now let's go to our database after we created this index and look at the sizes. There's actually two interesting things that are going on here Okay? The first one, which is a bit off topic, but I'm just going to see

Speaker 1: because I think it's interesting and I have an extra ten minutes. Thank you. So if you noticed we have an index on primary key, right? So this index is indexing an integer. 21 megabytes. And then we have the indexes on created and last edited by. These are also indexes on integers. They both contain the same number of rows. So how come the primary key is 21 megabytes and the other indexes are just six megabytes? I'm sure at least some of you know. You can yell the answer if you know. Exactly, my friend. Starting at Postgres 15, Postgres can implement an optimization called index deduplication

Speaker 1: that can most more efficiently store duplicate values in indexes. So the main difference between our two indexes is that primary key is unique. So you can make use of deduplications. But created by and last edited by contain a lot of repeating values, a lot of duplicate values. We just have 100 users and a million products. So lots of duplication. Postgres can optimize and therefore the difference in size. But that's actually not the point here. What is the point? Let me give you a hint. What do you see here? We have two indexes on the user and both of them are the same size. Yeah, just shout it out. No no don't tell them the answer man. Just say why they are the same size.

Speaker 1: Okay. Should I stop now? I don't know. Okay. So if we look at the actual data, okay, if we look at the actual data, we see that We have a million values, a million actual users set in created by. This is a not known field. When we create the product, we set the created by. But less edited, most products are not edited, so most values are null. So we only have 1,000 values. That's like less than a tenth of a percent. Okay, so most values are null. So if most of the rows don't actually contain a row, how come these indexes are the same size? Now this game is a surprise to me, and I've been a DBA for like 20 years, and this game is a shock to me. when I transition from Oracle to Postgres.

Speaker 1: But apparently in Postgres, no values are in fact indexed Okay, and this is a very, very big surprise to all of you using other databases. I never imagined that nulls are indexed because nulls effectively mean no value, right? But in Postgres they are indexed. Now most of the time we don't actually care about null values. Nobody wants to search for all the products that never been edited. And if they do and you actually have 99% of the table You probably can't actually make use of this index, it doesn't make any sense, right? So we can use something called a partial index, which is an index built over a subset of the table Which subset you ask? The one we actually care about.

Speaker 1: In this case, we only care about rows that actually has a value, which are not null So what we're going to do now is first we're going to set db index to false and we're going to add a comment. We say this is indexed in meta. Look up. You're going to see where it's indexed And now we're going to add an index on last edited by, and we're adding a condition. The condition is only where last edited by is not null, only when we actually have a value. Okay Now, if we create this index and compare the sizes again, look, this is amazing from six megabytes. I went down to 32k. Now you're probably asking, come on man, I can spare six megabytes.

Speaker 1: I want to go to lunch. Why do I need all this work for, right? Dude, don't be cheap. It's like friction of a cent But if you have a very, very big table, your B trees can become huge. Okay? And the bigger they get, the more difficult it becomes Right to recreate these indexes. So if you know in advance that you're not interested in this portion of the table, it's a good idea to make use of partial indexes early on. So The next takeaway is use partial indexes when make when makes sense. I personally found that nullable foreign keys are usually great candidates for partial indexes. Okay? So are we done Did we forget something?

Speaker 1: What? Do tell. What did I forget? Okay, stop. We always need to check the migration, right? We just created a migration. We didn't look at the SQL. So let's take a look at this migration. What do you see? We have an alter field to set the db index to false, and then we create an index on the product, right? So if you look at the SQL, what do you see? We've already been down this road. We've set D B index to false. Django is recreating. The uh foreign key, which is a bad thing. We just talked about this, so let's take care of that. Once again, exact same thing, separate database and state, create drop the index, move the the Django

Speaker 1: op into the state operations. Now, we can also do another thing, right? We also talked about dropping and creating an index being a blocking operation. So we want to create the index concurrently. We don't want to interfere with our system. Now this time, if you notice I was able to use the built-in the drop-in replacements Django provides for concurrent operations in Postgres Previously, I couldn't use remove index concurrently because my index was not defined in indexes. It was implicitly created by the foreign key. So if you try to remove index concurrently on the other index, Django will tell you, I don't know this index. Okay? Because it wasn't created in indexes.

Speaker 1: But this time I can actually use uh add and remove index concurrently. Okay. So this is our complete migration What do you say? This is a tough one. I'll give it to you. This is a tough one. Look at the migration. What would you change? You're now given a review. Split it up? Yeah. Okay, interesting thought. Okay. Any other ideas? I know what you mean because it's atomic and you can get stuck. But I have a solution for that. Reorder. Yeah. Okay, thank you, Adam. You get a gold star. So look at the the order of the operations that we're executing. So

Speaker 1: first we are dropping the full index And then we are creating the partial index, right? So as the as you just said, since we are atomic false If we drop the full index at this point in the migration, there is no index, right? So if creating the partial index takes some time. We are leaving our system in a state that it has no index. So this can actually affect the um the system if it actually using this index. So what would you do? That's right, my friend. We are just going to change the order. So we are going to first create the partial index. At this point, we have two indexes.

Speaker 1: That's fine. And then we're going to drop the full index. So what you said, you can do it, you can split the migrations, but then you are you'll just reach the same situation where you're left without an index. If you simply change the order It's it's less risky. Okay? So let's actually do this change. We just move add index concurrently from the bottom to the top, and now we have a safe migration. Okay? So another takeaway: adjust the order of migration to reduce the effect on a running uh uh system. And usually the idea is you first create and then you drop. Okay Okay. No more migrations, I promise. So many talks about migration, it's like a big deal in Django, I guess

Speaker 1: So let's actually see some code, okay? Let's actually see some code. So let's add a function, process of editing a product. And these these are our requirements. This is what we actually want to implement Okay, so we want to update the name and description. Okay. And we also want to keep track of the user who last edited the product. And we also want to implement a business rule where we want to prevent non -superusers from editing products that were edited by other superusers. Okay? This is you can translate it to whatever permission checks you have in your systems So the naive implementation would be a simple method that operates, an instance method that operates on a product. You provide the name, the description, the user, you do the check, right?

Speaker 1: For the uh created by. You set the values you save, right? So this is a naive implementation. Now the problem with instance methods, okay, and now you're going to have to work your imagination a bit, is that it's not safe When it's executed by many concurrent transactions at the same time. Okay? And the main problem with executing such a function by many many processes at the same time is that usually their the copy that they have in memory is not necessarily what's currently in the database. So this is a very very like silly example of a problem with concurrency, but if you need to maintain things like counters, it becomes an actual problem. Okay. So

Speaker 1: If we want to make this safe when executed by concurrent transaction at the same time, there's two prominent approaches: pessimistic approach and optimistic approach. So this talk is not about concurrency, so we're just going to go with the pessimistic approach. And this is basically how it goes. You start by obtaining a lock on the road to make sure that nobody else operates on it. Then you do whatever you want to do, update the row, and then you release the lock. In Django, this translates to select for update to obtain a lock, then you save, and then you commit, which releases the lock. Okay. So to obtain a lock, we're going to use Django Select for update, which uses the for update clause for the select statement in Postgres.

Speaker 1: So our refined implementation looks like that and there are a few changes. First of all, when we operate on an instance method, we don't actually control how the uh object was fetched. If we want to control how the object and f is fetched and actually obtain a lock on it, we can use an instance method. So we switch to a class method. So now instead of operating on self, we receive the ID as an argument to the function. Then we open a transaction, we fetch the product using select for update to obtain the lock. We do whatever we want to do and we save. Okay? So now that I convinced you that you need to use a class method What do you think we can optimize? Okay? And they give you a big hint, and I'm also going to ask you how many round trips to the database we have here?

Speaker 1: How many round trips to the database? Three? That's from the beginning. That's right. We first fetch the product, that's one. We save, that's two. And in the middle, when we check the when we check the super user, we access created by And Django is going to go and fetch this object from the database. So when you see something like that in code review, and this is like the lowest hanging fruit of optimization in Django, what do you do? Select related. Select related, that's right. So let's add select related. Okay? Now 99. 9 % of developers would stop here. Okay, but there's a catch. Let's imagine we have two sessions. The first session on the left starts our editing process, right?

Speaker 1: So it's open a transaction, it goes ahead and fetch the product. We select for update and now we're select related. Different session, completely unrelated, might as well be from a different system, is trying to make to turn user 97 inactive. When it tries to save, it gets blocked. Why is that? Super strange. Why is that? Yes, Mr.

Speaker 2: Update. Okay, will be created by the computer.

Speaker 1: That's right. You should have given this talk. Okay. So if you look at the SQL generated by our by our function, we can see that We're using select and the select related added an inner join to the user. And when we use select for update, the for update clause is basically locking all the rows referenced in this query. Since we have a join here, this also includes the user. Now do we want to lock the user? Is there a reason we should lock the user? No, that's right, no reason. So There is an argument to select for update which allows you to specify what you actually want to lock.

Speaker 1: Okay? So in this case, I want to lock just the product. So I can use the special keyword called self. Now I'm especially proud of this feature because it's a contribution from one of my teammates, right? So select for update off self. Would now translate to select for update off catalog product. So now I can obtain a lock on the product. I can also select related the user without locking it. Okay, so our next takeaway is always explicitly state which tables you want to lock using select for update. And I will even go as far as saying if if you're not using select related right now Add off self just to make sure that nobody comes in the future.

Speaker 1: Add select related and screws up the locking You wanted to do. Okay? So this is our final model. Okay? There is no AI that can write this model. Only we can write this model. Okay? I'm telling you. Only humans can write this model. There's a lot of thought that went into this model. Now I don't know if you noticed, but throughout this class Class. It kind of feels like a class. But throughout this talk, we we uh had a lot of takeaways. I'm not going to go over them, don't worry, but we had lots of takeaways, okay And uh if you're interested, uh you can read uh these stuff. I would just highlight the first one, okay, and that's about using the Django check framework.

Speaker 1: to actually enforce some of this stuff like make sure that you set db index, make sure that you set off on select for update and so on. And uh and that's it. That's me. Thank you all very much. It feels like a tough question.

Speaker 2: Um I don't think it's that hard gonna be. Um the and a few of the migrations you had, the explicit SQL like create index and drop index Do you see any benefit of using the add index operation there instead of writing the raw SQL?

Speaker 1: Well Yes. Yes, because uh if you're using the uh operations provided by Django, then you get portability across different databases and it's probably safer and future-proof. Um I usually find that I can't use them. When I can use them, I do use them. For example, when uh we wanted to uh remove index concurrently, I use the built-in migration, I do that. Only when I absolutely can't use them, I resort to run SQL. Yeah, good question.

Speaker 3: Uh thank you for the interesting talk. The question is kind of twofold. Um once you mentioned reordering um the operations uh inside a single uh migration Uh I still think that and I guess Tim will agree, uh if you hit Ctrl C or something during the migration and you abort the migration. You are still uh in a somewhat inconsistent state where you uh require manual intervention to um fix some things. Um How how do you propose to handle that? And the other thing is how do you handle um multiple databases in general? Create index concurrently is nice, but um it only works on Postgres. So how can you write migration files that Work for multiple databases and still be efficient for every database.

Speaker 1: Okay. Long question. So uh as to your first question. Um First of all, if you're using an atomic transaction on a database that supports transactional DDL, then if you are bored in the middle, you are not left in an inconsistent state. Yeah, I know, I know, I know. If you are using a non-atomic migration, then you need to be a bit more careful about the order of operations. In our case, if you hit Ctrl-C in every situation, in every point of the migration, at the worst case, you are either left in your initial state or with two indexes. So I would say that in this case it's safe. I would say

Speaker 1: why why not? Oh no.

Speaker 4: Create index concurrently. is that it will leave behind the partially created index. I know. And then if you've got, if you've written as create index concurrently rather um Uh if you if you write if not exists, for instance, it won't fail, but it will leave a index. And if you just come back to it and if there's not if if if you've got um If you haven't got not exists, it will attempt to create it, it already exists and is invalid, and at that point it will fail again. So the we've we've encountered this situation, the way we've dealt with it is to first check If the ex index exists and is not valid and if so, delete it first. So

Speaker 1: drop if exists.

Speaker 4: Yeah. Okay.

Speaker 1: Good talk. Seriously, good tip. No, I I take it.

Speaker 4: Um I don't think there is a drop if exists. I think we There is a job if it exists. Oh okay, but I think we had to check to see if it was invalid. Because if it exists, that's fine, because let's say you got interrupted later on after it had been created and you're coming through again if it if it's valid and exists you you keep you want to leave it there right yeah and then just keep moving on to the next thing that you you miss

Speaker 1: Okay, so the key takeaways that non-atomic migration are super dangerous and should avoid them at any cost

Speaker 4: Except you you want to use create index concurrently because otherwise you're gonna lock out access to the table if you've got a very big table and you're creating the index on it.

Speaker 1: Yeah, I know. Thank you. And to your second question, I have the luxury of only having to deal with Postgres, so I call this luxury, but I'm paying in other places, you know. Any other questions? Yeah. Uh

Speaker 5: thank you for the talk. I thought it was great.

Speaker 1: Thank you.

Speaker 5: Do you have opinions on whether people should write tests for their migrations, especially when they're doing something slightly more advanced? Or are you more of the approach that, you know, you've mentioned checks and perhaps you've agreed these certain rules and perhaps those are tested? Uh yeah. Do you have any opinions about testing micronships?

Speaker 1: Yes, yes. That's a great question. And I'm going to tell you my approach. And I think it's a bit different than what you think it is. So what we've done in order to make sure that developers are very much aware of what actually being executed is we created a GitHub action. that looks at the PR and whenever there's a migration it adds a comment with the actual SQL. This way, when you review a PR, you have to look at the SQL generated by the migration. And if there's a problem You can identify it. If you're asking me if I think creating some kind of automated checks uh to try and identify it automatically, I think that would be great. But it's a question of how big your team is. Does it make economical sense to invest so much time in such a thing?

Speaker 1: For me, I have a a relatively small team, just ten people. So uh, you know, I can just go throw something out of them if they are doing something unsafe in a migration. So my approach is mostly just put it in front of their eyes so they can't miss it. I don't think you can ask questions, man.

Speaker 6: Okay. You've said always look at the SQL. That sounds like great advice, but most of us use the ORM so we never have to even think about SQL. Is the ORM just too leaky and we have to level up?

Speaker 1: First of all, I'm kind of like the SQL guy, so I'm not afraid of SQL. I'm also uh in camp SQL. I believe that everyone should know SQL. And I'll tell you even more than that. I feel very, very comfortable with SQL, and a lot of the times I feel that the ORM provides an actual value for me, and I much rather use the RM. So I would say to your question, I would not ditch the ORM, but I would pay close attention to the places where the ORM struggles. Okay? And by the way, just to be fair, a lot of the things I show here is about kind of decisions that Django is making for you. Now while I criticize or I don't even criticize while I show the the the downsides of these decisions, it's important to understand that Django is a framework

Speaker 1: Always needs to target the lowest common denominator. Like if you have something that is correct 80% of the time, it makes sense to set this as the default. So I don't think that having DB index two on four in keys is a bad idea. I'm just saying that at some point you need to be aware that this is what's happening. And know the limitations and the default values and the default and the implicit behaviors of the ORM so you can adjust them to get the most out of them. There's a lot of other implicit behaviors in Django that you need to be aware of. Uh but that's the topic for maybe next year. I don't know. Any more questions?

Speaker 7: Great talk. Thank you very much. Two questions, not very related to each other. So about the concurrence index, let's say we have adding a concurrent index in a migration and just to clarify the migration will run the process that runs the migration will only exit when the concurrent index is done. Is this correct If we have a very big table and it takes like five minutes to add the index concurrently and we're doing it concurrently not to lock, but the migration process will be there for five minutes, right?

Speaker 1: Well the migration process execute the command and waits until it ends, yeah.

Speaker 7: Although it's completely potential

Speaker 1: migration, you would learn them serially, yeah.

Speaker 7: Yeah, so a potential caveat of this is Usually migration is part of the release process and this will hold the release process for five minutes and sometimes you might want to do this separately from other things.

Speaker 1: So but it's really dependent on how you do your deployment. Uh if you stop the service, apply the migration and and start it back up, then yeah, you have a problem. But if you're careful and very uh aware of what your migration is doing, it's usually a good idea to uh not stop the service, run the migration while the system is running. And then just restart the service, reload the code once it's done. There was actually a lighting talk yesterday about someone implemented post-and-pre migration. I think that's a very you That's a very elegant solution. I need to talk to you about this. Yeah. Second question?

Speaker 7: And second question is, uh would you recommend Uh would you recommend people fiddling with SQL without any abstraction so they can get a sense of what's happening and what the abstraction is giving them. And as you mentioned, what the abstraction is Not taking away from them but making implicit decisions uh instead of them. So uh would you recommend this and uh what would be like the steps? Like simply open Postgres or read a book on SQL or What would your recommendation be?

Speaker 1: First of all, read a book is a great recommendation. Okay? In general. Which book Well I I personally I don't underst I I'm having a hard time understanding the fear of SQL. I think that if you need to uh if you reach a point where the URM can do what you wanna do, then yeah, go ahead and run SQL. It's fine. So uh to your question, if you need to use SQL, use SQL. The only place where this might be super, super challenging and dangerous is if you need to support different engines and then it starts to become a bit difficult. But as I said earlier, if you have the luxury of not having to deal with lots of different databases, then I think it's a good investment to take the time to learn the capabilities of your specific database.

Speaker 1: So you know you can make safe migrations and get the most out of it. Yeah. Migration is a very hot topic, apparently. And also concurrent operations. Yes, Mr. Shy.

Speaker 8: Yeah, hi. Thank you. This was a great talk. Uh I want to uh incite uh a small battle. No. Um when you showed uh how you uh in inspect the database You used uh the PSQL, the the basic text utilities that comes with Postgres. Adam yesterday recommended several graphical tools that uh allows you that help you look at the database. Uh do you have a an I I'm I'm ho uh expecting that This expresses your preference and can you uh uh expand on that a little?

Speaker 1: Well uh I I'm uh while I think UI tools have their place. When uh when you SSH to a server to solve a production problem, you can't use UI. So I like to keep my heads dirty and get used to using the terminal. So UI tools are uh nice and I know PG Muster, I know Michael, great guy, great product, but I like you know I like my explain analyze right there in PSQL. But yeah, you can use print uh query set explain. It's a great utility. And I I use it often when I don't want to open the PSQL terminal. Yeah.

Speaker 8: Thanks again for the for the talk. What do you think can be um something we can h uh had in the Django core or the Django HRM, uh starting from y what you found? We can had some feature to solve uh some or to improve the the immigration part and avoiding using row SQL for example to let other people with other databases use it.

Speaker 1: Yeah.

Speaker 8: And maybe someone are interested to work on that.

Speaker 1: Yeah, good question. I uh I know that I showed several limitations of the migration framework here. Uh for example that you can't use uh drop uh sorry, remove and add index concurrently on indexes which are not part of which were not defined through indexes, meta indexes. like the implicit indexes on four keys. I don't know what is the reason for that, but I'm guessing that it would be nice if I could use these operations on indexes created on all the indexes created by Django. This is by the way, I think also a problem for unique together. And this is part of the reason why it's discouraged. I saw here is uh I don't remember honestly. I need to think about it. But yeah, I'm guessing that oh yeah, the fact that it recreates the foreign key when I just want to drop the index, that's unfortunate.

Speaker 1: I'm actually not entirely sure why that is. Um but I'm sure there's a good reason. No? You think no? Okay. So uh concurrency expert here says there's no reason, so easy pickings for you. Go go implement that. Yeah.

Speaker 6: I just had to look at the check command and um it didn't seem to find what you did what you reported. Is it is check supposed to find all of these automatically or have to configure it?

Speaker 1: Oh, you looked at the check command and you couldn't see my personal rules. Yeah, so what I did is I uh I extended, I implemented my own checks. uh to enforce my own coding style. For example, make sure that uh db index is explicitly set on foreign keys. So I implemented my own rules. I implemented a bunch of other rules. For example, always provide verbus names, stuff like that. So uh if you're talking about the check uh you can check out the article and see how to implement uh custom checks. I think that another person gave a talk yesterday and implemented some uh custom checks here. So I guess I'm not the only one.

Speaker 2: Thank you.

Speaker 1: Yeah, thank you. Hello, uh get off the stage