Thinking in SQL with Django

Speaker 1: Thanks for stepping up with the talk after a late cancellation and putting together this talk on a short notice. Um this is also your first thought. Yeah. Um I think we should uh give him Very warm applause and

Speaker 2: thank you. Thank you everyone. Um happy to be here to uh talk about thinking in SQL with Django. So um what uh how many of you here uh have used uh Django Um and uh okay and uh didn't have a relational database. None of you. Are you you used no no SQL? All right, a couple, but the rest, let's who has used it with a relational database? All right. So the the overwhelming ma majority. So that is Y SQL. And to uh

Speaker 2: underline the importance of uh sql let's go let's see where sql came from according to wikipedia it first appeared in nineteen seventy four here and uh Just uh maybe saying 1974 is not enough, but this is for example what Sony, when they advertised their tape deck in 1973, advertised as technology built to last. So Just that that should uh that should tell us how long uh SQL has survived. And uh if you if we see any um job adverts for Django developers today, we usually uh see technologies like Docker, Kubernetes, and if it has a front-end element, we always see the latest

Speaker 2: kid on the front-end block. Some years ago it was Backbone, JSNJQuery. Now it's uh the newest guys are React. js, view. js, and yet SQL is always lurking there somewhere. It has been used since that time. And as Django developers, I think uh uh we have to pay attention to it. Another aspect of why uh SQL is is worth knowing and worth uh kind of dealing with Is this uh concept, uh, I think it was popularized in Nassim Nicolas Taleb 's book. It's called the Lindy Effect, and it's basically It originated

Speaker 2: by Broadway actors and performers meeting at this Lindy Cafe in New York City. And they arrived at this conclusion that if a show on Broadway has been performed for the last 50 days, then there is another there's a good chance that it will be performing for the next 50 days. Similarly, if it has been performed for the last hundred days

Speaker 2: To do the proof of concept for this talk, I have a GitHub repository. It is linked to from the uh talk page on the Django Day Copenhagen website. There is the code and this for for this talk. While developing the code For profiling, which is step one, is uh I use these uh explain and query are built in. To Django, you don't need to install anything to access those. Explain prints the query, the query execution plan depending on your database. For example, this one is a sequential scan on this table, and that is from SQL Lite

Speaker 2: If you access the query property of any query set, it shows you the SQL that will be evaluated, that will be executed when the query set is evaluated. Then I also used a couple of extra tools. One of them is Django extensions, among which it provides this shell plus uh command, which when you pass dash dash dash print dash SQL to it, uh it prints any SQL that is executed around uh against the database when you're running the code The last tool I use is the already mentioned here Django debug toolbar and we'll use this we'll see the its use later on during this talk So, with profiling out of the way, let's go to the use case

Speaker 2: that this talk is about. And it's a very simple Simple. It's the one uh is the relationship that the docs talk about. It's about pizza and toppings. So a pizza can have one or many toppings, and the topping can be On one or so it's many relationships, it's a many to many relationship. To make it more interesting, we'll add the the requirement uh wherein each topping can be vegetarian, vegan. or neither. And on the menu card I want to see a V when the pizza is vegetarian and a VG when the pizza is vegan How do we determine if a pizza is vegan or vegetarian? A pizza is vegan if all its toppings are vegan.

Speaker 2: So for example, tomato sauce is vegan. Whereas a pizza is vegetarian if all the stoppings are either vegan or vegetarian. So for example, mozzarella is vegetarian. Ham, of course, is neither. So with those requirements, let's go to how we would model these. So in the first approach, we would have two Booleans This is not really great because whenever you have this vegan Satutru, you have to add the Czech constraint that it is vegetarian would be true as well. The second approach would have a choices field, so we would have a drop-down in a UI, for example. And we are storing constants vegan as zero, vegetarian is one, and we're store rating in a way that null values means uh neither of both

Speaker 2: of those. And to compute whether a pizza is vegan or vegetarian, we use Python, the Python all function, and we loop over a condition based on the toppings. So if all again it's vegan, if all toppings are vegan and it's vegetarian if all if all toppings are either vegan or vegetarian Whoa, all right. Um so back to the code. This is how we would render the menu card. This is the query set here and this is the template. We loop over them and we see vegan or vegetarian next to each pizza. This the code to load this data is in the repository if you want to have a look.

Speaker 2: And uh yeah, this is the menu. So let's look, let's go back to where we started and think in SQL. What SQL is powering this screen? this render template and here we have the Django debug toolbar and as you can see for the pizza we have the first query and then the rest of the pizzas are Basically, getting the the topics to get the rating for the pizza, and we have a lot of repeated uh queries there. So it's known as the n plus one or one plus n uh situation which you there is a link in the references which you can read more about there. So how do we go how do we go about fixing it? We follow what Django with Django's doc talks tell us and retrieve everything at once since we know that we will need it.

Speaker 2: And to do to do that, we just add prefetch related. And now all toppings are prefetched in one query. So we have Two queries. So over a large data set that would re result in a massive gain. Did Jengo Debok toolbar also shows us the exact query and data that is retrieved for for our uh uh request So, very good. How do we ensure that we do not lose this gain? And the answer Django provides is in its test case a certain AM queries. So, this within this context here We do the code we run the code and then we ensure that it never executes more than this amount

Speaker 2: bypassing the amount of queries to us certain queries. So that's very good. So we are we are we are profiling. We are uh doing uh um We are perfeting all data required in the minimum number of queries possible, and we are ensuring that we don't exceed that amount going forwards. So, what can we do? This is a result if you remove the pre-fashionated. This is how the task breaks. So, how do we ensure? What can we do more than that? And in this case, we follow this suggestion here. We do database work in the database rather than in Python So previously we had we have on on the pizza

Speaker 2: model we have the is vegan and this vegetarian which have a loop and all loop uh and all uh It's looping over all toppings and then calling the all function to determine whether a pizza is vegan or vegetarian. But if since vegan is a subset of vegetarian and vegetarian is a subset of all toppings We can model this data differently, remove the null constraint, and it will be a straightforward migration because we change all values from null to two. And then rather than looping, we get the max rating, the max rating of the toppings, for a pizza stoppings. So that should be one query This is how it looks like, so these will be removed.

Speaker 2: And now we are using another aspect of Django's ORM. We are now using annotate. We are using conditional expressions, case when to determine is vegan at queer reset level, and is vegetarian as at queer is at level Notice how using a manager results in dry, that is do not repeat yourself. And we can also now access this vegan at queers level. This makes a big difference. For example, you're using Django REST framework And filter cells because now you can order by or filter on those fields. The crux of this is that we have max rating, which is a the maximum rating for a pizza For Epitzas topping, for Epithos toppings, and it is accessed by the many-to-many relationship toppings here

Speaker 2: and the its fit extended field here. So very good, all good, right? Let's see what SQL it generates. And the SQL is this. And yeah, I I noticed that it might be too small there. But the big problem here is that it might not be a problem if the dataset is small, but on large datasets. So let me ask you a question. Who here has been bitten by out air joints? In SQL. Alright. A handful. A handful. Alright. So for those who haven't been bitten by outer joints yet, when you have a large data set. And you try to uh join table which which the query planner doesn't know how to join

Speaker 2: because there are no immediate keys between the two tables, then outer joins are done And here in the in the explain, we see a hash aggregate. This is a query Postgres SQL query plan. And the hash aggregate Uses a temporary hash table to group records, it does not require a pre -sorted dataset, and instead it uses large amounts of memory to materialize the intermediate result. And it's not Python. And what that means, so to to to cut short, the effect of this is uh you would see your database instances CPU go to hundred percent. And your site becoming unresponsive, and you will see HTTP 544 server timeout. So, was this the thing we then we have done before?

Speaker 2: Was this a premature optimization And uh let's get back to thinking in SQL. And it is, it isn't because this should be doable uh without left left outer joins. How can we do it without left-outer joints? This is what we think as a many-to-many relationship, but all many it does not exist in reality because in reality we have an intermediate table for any to for any many-to-many relationship. Which in Django it's called the true table um T H R O U G H. So with the through table, since one this true table basically it has the pizza ID and the topping ID. So shouldn't it be possible to get the max topping here if we know the pizza ID here?

Speaker 2: And this, since the ID of the topping table is stored here. We do not really need a raft out air join. We can do it via an air join. So to recap, where are we? We followed all Django 's uh docs. Suggestions, but we ended up in a situation where DSQL generated by the ORM could be a performance issue. So now we are thinking back you in SQL, not necessarily in Django, how to do this efficiently. And we want to use this relationship to get the inner join And the tool I I I mentioned earlier, ManagePyShell, in this case manage pie shell plus with Django extensions The big benefit of using Python is that you can

Speaker 2: try the code and see the results immediately. And here I am trying to get the max rating for a pizza using the true table. The true table is excess so pizza toppings is the many to many filled. And the true table here accesses directly and allows you to make queries against the intermediate table directly. And this in fact returns the pizza toppings for our Capri Chosa here. And if I do an aggregate max rating on those toppings, we get two. So notice that this Paolo, your answer on stack overflow this morning was related to this query here. So, how are we?

Speaker 2: Are we so what does Django provide to be able to do this query and relate it to our outer query? And the answer is subqueries. The stack overflow answer po from Poro this morning is documented here kind of some someone asked earlier whether whether it's in the docs and it's There is an example which is not identical but similar. It's here. And it's for the subquery class here and another handy class that goes uh with the subquery class is the outer ref class, which refers to a key on the outer query. And in this case it's the pizza ID because we want to relate The query on topping on pizza toppings, the intermediate table, with the outer query, which is querying pizza.

Speaker 2: So that's why here we use pizza ID. However, the first attempt never works. And in fact, I got an error, so I need to work it out differently. So again, thinking back in terms of data and SQL If we order all results here, all toppings for the Capricosa and get the and get all we order them by descending order on rating, since based on our algorithm and get the first one of that result that will always have our max rating. Because zero is vegan, one is vegetarian, and two is neither vegan nor vegetarian. If we get the highest one, it's always

Speaker 2: fits us rating. So if we again I tried to retrieve that and this is the shell I was playing. playing uh trying to arrive at a solution um and uh uh if we order if we get the latest and we order by descending so Not the latest, sorry. We order by descending order on lating rating and get the first one. We get two, which is two is non-vegan, non-vegetarian for this case. And the resulting SQL again, we don't see any left out air joints. It's an inner joints. from the intermediate table here to to top to the topping table. So again, can we uh just

Speaker 2: put this query in in the subquery? And the answer in this case is yes, it works. So here we see the subquery, the conditionals were are still here, is vegan and is vegetarian. Max rating uses the subquery and it gets uh and we see in the resulting SQL that there are no outer joins, so the the results are all inner joins. Um And by profiling, we can see this is a Postgres specific plan. This time the hash aggregate is gone, and instead we have hash joins Hash joins are much faster because they are they are in they work with an intersection of the data, the data

Speaker 2: it using IDs, so it's pre-ordered, so it's the fastest way we can join data Or at least I'm not sure it's the fastest, but it's much faster than the other than the other one. And that, and this it's from personal experience. This is what can bring the site back up. If we look at the changes, so let's say that uh the site was unresponsive because of this date of this data retrieval issue, and uh the PR comes in And notice how little changed. And even uh we didn't even need to change the tests because the tests, while they were helpful in uh allowing me to arrive at a solution, um They were checking for the correctness.

Speaker 2: That is that I didn't break anything in how vegan and vegetarian are are are computed. And that the same amount of queries was still was Was being done. But while the same amount of queries is being done, this is much more efficient. And this brings me to the point of the talk, which is um to be mindful about the SQL because yes uh the the Django RM makes it much easier to just try the write uh your Python code and uh And uh not I do this miss I do this myself. I forget a lot a lot of the time about the SQL it generates. And that's why I once brought the system, the site I was working on down with a left out with a newly edited left

Speaker 2: out a joint. So, what have we done during during this? What have we gone through? And we did relational modeling taking query into consideration. We used model managers to achieve Do not re repeat yourself in logic. So the logic we don't need to repeat these vegetarian anywhere in code base, it's just on the query set. We used a lot of Django good parts here, pre-veget related conditional expressions. The true table, we used the certnum queries. And together all these uh they give us, I in my opinion, very, very fine-grained control over the SQL that that is generated. We didn't need to use raw SQL at any point. And as mentioned by Paolo earlier this morning, ROSQL has its downsides.

Speaker 2: The docs go through them. If you want to read more, uh, the docs, the Django docs, not the references of my talk. Um the They allow us the these functions allow us to achieve dry and not repeat logic around the code base And they allow us to test that logic. And if we already have a continuous integration pipeline that goes automatically, even though we're testing SQL, that goes automatically in with our with the rest of our unit tests. So the conclusion is know your SQL. It's easier said than done. As I said, it's very easy to start writing Django. It is the framework for perfectionists with deadlines. If you have the ORM tool, you just want to use it and get

Speaker 2: things done after all. We are makers to make your thing. But uh it's very um uh important to know your SQL because as I uh discussed earlier It has been the common part of the tech stack for applications for decades now. So Yeah, I uh these are the references from uh for the talks uh about things that uh due to time constraints I wouldn't discuss as part of uh this this talk. And For the future I wish you zero left outer joins and a lot of inner join.

Speaker 2: Thanks, Samil. Thanks, Benjamin, and uh thanks Klaus. Where is he?

Speaker 1: Yeah. Thank you. Right on time. Not according to the schedule, but we started five minutes a yeah. So we have uh five minutes for for questions. Um and uh You go first, audience. Way in the back.

Speaker 3: Yeah, you mentioned uh one of the things you said was they're certain going to break any tests. fix that what if i don't want to test the break in case someone comes along later and says this so complicated let's do max and swap it back again a good way of testing to say we've reverted to a hash aggregate

Speaker 2: My god, so I don't have a

Speaker 1: the the question is uh can we test that people don't somehow like uh revert our optimizations? Uh

Speaker 2: Unfortunately I I don't I don't have an answer. That's uh I would put a comment. Uh but yes, i it looks complicated because it's a one-line change and then it's several lines and there's a query. It's true, it's a very, very good question. There is no uh I don't know of an easy way to guard against that other than a comment please with caps exactly

Speaker 4: I'll have a note sometime inner joints are also like horrible. If you have a few million rows in both sides, it just kills everything.

Speaker 2: Yeah, but the specific one, it should speed things up.

Speaker 4: Yeah, uh there is uh they added a new uh work class on the inner left joints in Django, which is also super useful. So you can do filters on joints themselves.

Speaker 2: Yep, yep. Yep, I didn't have space to

Speaker 1: But you have a blog post.

Speaker 2: There is a blog post, but it's not necessarily on this. It's uh uh it's a basis for a part of this

Speaker 1: Yeah.

Speaker 2: It helps it it evolved over two because it's two years old.

Speaker 1: Yeah.

Speaker 2: Yeah. Actually, the Django docs provide a very good job as well. But again, they don't uh go into this edge case kind of where you expect things to work and uh uh you still need if you want your sq if you in some cases you need to know your sql

Speaker 1: yes any there is a question there there is two uh way in the back first

Speaker 4: The ORM has come on leaps and bounds over the years since its relatively humble beginnings, to the point where Floor Esclow is, as you say, a last resort. When would you find yourself wanting to make use of raw SQL at this point is uh a threshold where you find it easier.

Speaker 1: So the question is if there is a threshold that can guide you uh when it's easier to use raw SQL.

Speaker 2: I I would try to avoid it as much as possible. There are downsides as Paolo mentioned earlier. There's the injection issue. And uh I think uh even though it can return as as a queer said, there are some uh aspects of it, it's not exactly the the the same. So the qu the question is more a human and project specific answer. So if your project you can't afford to spend one day trying to do this without resorting to RAW SQL. then uh if if if if if you hit the time limit and you have the SQL then you might just go with the SQL and add a ticket for the next iteration you know how we work we have deadlines

Speaker 2: unfortunately But unfortunately, sometimes it's good to have deadlines, because then you might do optimization that it's not worthwhile. So it it's more of a human concern, and depending on your situation

Speaker 1: And there was another question in the bank.

Speaker 5: Hi. I have noticed that newer developers have not don't really learn that much of SQL anymore, especially the the people who are so used to OREMs. So they sort of like don't even realize maybe what all the SQL is doing. We have tips for people who have not learned SQL thanks to Or REMs or whatever. To get started on this.

Speaker 1: Really good question about new developers that may not know SQL but use the ORM for the first time. How can they learn SQL?

Speaker 2: So yeah, as as I uh as I said, as I mentioned, uh it's great to have Django provide us with DORM because it speeds things up and makes things the interface between Python code and the relational database, it makes it way better than if you you you you're suddenly seeing SQL code embedded in your in your Python code. It's a bit of a cognitive dissonance So I don't really have a suggestion. The fact that I've been coding for fif over 15 years, I did code store procedures in my initial So uh uh that's how I know my SQL. So uh the the way I the the way I learned it is because I had to. But uh if if they uh uh W what do I suggest?

Speaker 2: And uh maybe uh using Django with tools as the debug toolbar. And you can monitor the SQL that is being done, then in order to understand what the J Django T Bug toolbar is printing on the screen, then you have to start understanding. And then when you see the different times, why does this query takes four take four seconds and the other one takes my uh less than a second? Then with those questions, you start investigating. But if you're working on a Django project, uh that that's the only you you you won't tell your boss, listen, I won I want one day I will be learning SQL rather than working on the project. It's it's it's it's not doable.

Speaker 1: And someone uh mentioned earlier pair programming. That can be a really good uh uh case for pair programming to sit down and look at it.

Speaker 2: Absolutely, absolutely. In a if if you're in in in a team, that's a big benefit.

Speaker 1: Yeah. There's uh oh no there's competing hands. Uh I'm trying this one.

Speaker 6: uh like tools that uh query plan analyzers right that explain the query plan to you because it can be very daunting the first time you look at the output of this

Speaker 2: Yeah.

Speaker 6: And there's those tools where you input it and it tells you exactly what the things are. Like they're just online if you

Speaker 2: Yeah, whenever I use a query planarizer I end up uh Googling because I never I always forget what the terms mean. So hash aggregate I I put the definition there. even for myself as a presenter to remember exactly what it is so that I can explain it during the talk. So it's not I don't know those things by heart and no probably no one does.

Speaker 1: Yeah. So a tip to use query analyzers uh to understand the

Speaker 2: to to and to be okay with not understanding things all the time.

Speaker 1: Um so question there do you want to

Speaker 7: yeah I was wondering like I've also seen the opposite case where people who come from a different background, like many, many years of SQL, and they think in SQL, and so they they they go into Django and they like you know know a little bit about the OMB saying I actually you know I just throw some uh a wrong SQL in that really easy to for me to understand But really difficult for everybody else to understand. And isn't that isn't that like uh you have to have like a balance like yeah maybe you can make it 0. 001 second faster, but you also eat up every other developer's time trying to understand your your raw SQL that you that you go in.

Speaker 2: Who will maintain it?

Speaker 7: Yeah exactly. And like where do you where do you where do you put the balance

Speaker 1: So like same question as as earlier, but the opposite way. Like if you're just coding in in SQL, yeah, how can you find out that you should actually maybe switch to the aura in the

Speaker 2: U. ? So just to add a bit of uh my own personal trajectory I went from MySQL and a lot of store procedures in Oracle. This was back in 2006-2007 to Django and it was my first time using an ORM So at first that's what I tried to do, what you mentioned, that is right SQL. Why do I need an ARM? It's slow it's slowing me down. Actually it was SQL alchemy at the time, because it wasn't Django, it was Terbogers It was another Python web framework at the time before I started using Django. So yeah, I felt like it was hindering me. And I understand that, but the project, if it's a Django project, this is a Django conference. We write Django most of the time And nowadays the the prevalence is that people write Django. So you don't want Test QL and as I mentioned you won't write tests for the all the code you write

Speaker 2: because Even though in terms of coverage one once one test will cover all cases, but ideally you build With with the Django packages like uh Factory Boy and Baker, it's very easy to build uh data and then ensure that you the logic in your queries works for that data. And so uh the answer is uh kind of uh you have to migrate your your you have to m to to m ha mind shift I don't know if it's the right term. From writing everything in SQL to this is a Django application, I will use what Django provides. And as I've shown in this talk, Django provides DORM is very feature complete , in my opinion.

Speaker 1: There is time for one more question, which is from this gentleman here.

Speaker 8: Yes. Sometimes the Django team are not the owner of the SQL. For instance, there's a business uh division uh uh uh data maintainers and they'll kind of set out how to use uh uh the databases. Uh would you uh uh give your thoughts on on that? So

Speaker 1: your thoughts on on database maintainers in in the role of the uh like expanded use of the ORM

Speaker 8: Yes. Uh y when when the Django project is not the owner of uh uh of of the data base.

Speaker 2: Yeah, so so the the case here if I under I I will repeat so that I make sure that we understand each other. It's we have a data team. And we have a web dev or web or applications team. So the applications team is doing Django and the data team owns the database So they have to talk to each other because the code the application team will generate the SQL that is run and uh uh no matter how many indexes you make if something is suboptimal uh the indexes become suboptim a suboptimal solution you you might because I saw a lot of uh in uh in in Working, you see a lot of approaches like caching at different levels and uh also a lot of in the indexes which sometimes are overkill. uh when your SQL could be written, the original statement without any

Speaker 2: bells and whistles can be simplified or made to work more efficiently. So the idea here is Whether you have a data team or not, is to make sure that the SQL is the most efficient. In that case, yeah, they have to talk to each other and define who is responsible at the end of the day because if both of them are not responsible or if both of them are responsible which means none of them are you

Speaker 1: may maybe we can initiate that conversation with them and not uh wait for them.

Speaker 2: Exactly. That's the same idea, yeah. We as Django as the Django part, right?

Speaker 1: Yeah. So we don't have questions for yeah uh we don't have time for any more questions Because we have a cake break. I think it is ready Waterbreak Water break, not cake break.