Herding your database queries: diagnosing, improving and guarding performance of DB interactions

All right, thanks Carol. Can everybody see my screen? Oh wait, we're not on Zoom. So thanks uh and thanks everyone for coming. Thank you to the organizers uh and volunteers. You're all great. So my name is Ilya. I work for Pathai. We're a startup out of Boston. We are in a digital pathology space. medical space. We are focused on improving patient outcomes through use of AI in digital pathologies. Basically we we work with high resolution biopsy scans. So being um uh uh having a huge machine learning component to to our company.

Uh you can imagine we're a Python shop, uh but we also use Python uh in our product uh code bases and Django in particular. Been using Django actually for quite a few years. Started using it with Django CMS in a in a charity website of them maintaining and then more professionally in the last three years, learned some interesting lessons and wanted to share some of them. In particular Some things that stem from using the awesome ORM, but sometimes you can run into issues with excessive queries. And so I'll be talking about how middleware can be used to attract them, some ideas for how to get rid of those excessive queries, as well as how to guard against increases in the future.

So most of you probably know ORM. Object relational mapping is awesome. Django ORM in particular. Once you have your models defined, you get your database scheme out of it. And then in runtime, it helps you get the data out of the database, save it back when you make changes. Amazing Right, very very sort of naive example here. Let's say you have a model called book. Typically you would probably have a foreign key reference to an author. And then in your code, if you have that instance of the book model in memory, all you need to do is reference the author attribute. And there you go, you get the author. You can chain them together. author dot name will get you the name.

Great. However, Boot got author will run a database security, or at least it can, depending on the state of what's going on in memory, what you've done prior to invoking this. author And this is a typical trade-off in software engineering versus convenience versus control. author, awesome, convenient. But you don't really know what goes on under that. You don't directly control whether it will or won't call a database. And like a little asterisk there, of course, you can be way ahead of me and you have already done your homework And you've done select related and you have perhaps a bit more control. Right, so you might say, well, so what? So it calls the database, which

and by the way it will cache the result, right? So if you do it again twice in a row for the same instance. You already have it retrieved, no big deal. Well, in reality, if you have an application with any degree of uh sophistication in in it, It's quite easy to end up with lots of code that spawns lots of queries sometimes without you realizing it. And we learned the hard way. We were preparing for our first production release of one of our platforms for clinical trial services. We ran some load tests um with a bit more data than you would typically have in in development, and we realized that things were slow. And one of the reasons why things were so slow is that some endpoints ended up

invoking thousands of database queries and they would scale they would not scale well meaning that uh the more objects you would uh deal with the the more queries would get run Of course, it resulted in unacceptable user experience. You also have this risk to stability if you if this is something that goes to production. You can get overwhelmed with traffic, your database will will choke, your backends won't do anything because they won't get anything from the database, so nothing good will come from that. So where does it all come from? Sometimes it's as obvious as just having a for loop running queries. Okay, well don't do that. Maybe you can optimize. But sometimes it's it's more subtle than that. So if you use something like Jenga

Rest framework, which is also awesome. But what you would end up doing there is you have your typical serializers wrapping your models and then views exposing the serializers, and you have something like this. which is um a typical response you might get asking for books. Um so this is A very simple serializer. So serializer demonstrating a couple of fields there. One very obvious, I'm highlighting it in yellow. That's the kind of the same idea. Author. name, you tell it where to get the data from. A very reasonable thing. If you return a book, you want to return the actual name of the author. Great. But every time you render the serializer, you will um you will make a database query, or at least

unless you plan ahead. And I'll talk about that a bit later. But in a very naive, straightforward implementation There's a database query there for every single book you're about to return. And then there's this other field which is actually a property Which is also a fairly reasonable thing you can do. Let's say you you this is a library and you want to keep track of how many copies of a book are actually available. So you can write this filter statement, which Which works well, you know, you can check if something is borrowed or not. Amazing. But again, that filter means you will get a database query out of this And in this case, by the way, it won't care whether uh you've already ran it just now. It will run it again because that filter, you know, by definition, it

It just goes and does it. Makes no assumption. So you end up with two two queries for serializer. One a simple one with a primary key. author. The other one is a bit more complex because it's looking for more things and doing some comp um conditionals in there. And then on top of hitting your database hard with all of this, because what if you have a thousand books that you want to retrieve all at once You also have to pay, of course, the price of processing all this in your backends. And so potentially you'll need to run more backends in order to keep up with the traffic. So um and like I was saying, you know, sometimes the the sources of these queries are not uh obvious at all. You can have a lot of code, some of it is directly um

invoking queries you can tell other times it's less direct and and sometimes it's kind of the things that other parts of the framework do for you like like the rest framework Um and so we looked at a few options. Um and I'm sure the there's probably already a solution out there that I haven't uh that we haven't explored at the time, but at the time we looked at Django debug toolbar, Django silk is something similar. Well we didn't know at the time, we know now, but it it is something similar. Uh of course database logging. Those of you who attended the the logging talk earlier, that that's a great option, but You just get a stream of these things, like every single query that runs uh gets logged, and what do you do about that? Like where is it coming from? How much is it costing you?

Like which requests are they associated with? Not at all obvious. So we ended up rolling our own solution, but it's not that much code because luckily a lot of ingredients are already there for us. So first of all there's the middleware concept itself. For those of you who don't know, this is just a callable object you register and you get a chance essentially of doing something before and after each request is executed So great for instrumenting things. Again, the other thing is Django database connections. uh you can examine all of your connections, go through them and do something. And the the something that we want to do is execute wrapper and that's again yet another callable that you can register with every connection.

And it it's there almost precisely for this reason is for instrumenting your database calls. And lastly, because we're going to be instrumenting lots of them, the exit stack is just a useful concept to accumulate all of these contexts and exit them all at once. And the resulting solution, like the core of it, looks fairly simple. There's a lot more to it. I will be sharing. There's a blog post that we just published today. And there's a reference to the there's a pointer to a open source reference implementation that you all can look at. But the the core uh concept here is that uh you have the middleware in the call method. Um You go through all the connections, you load this wrapper called query stats.

That's the only thing, but the main thing that we implemented. uh that has most of the code in it and then um you enter those contexts uh you accumulate them in the exit stack uh And then that's it. And then you run your uh your request by coding get response, and that's about it. And then the again the core piece What is it we collect? So inside each database call, we get to look at the SQL statement, parameters, if you really care. We decided not to care, uh, like if the parameterized SQL statement is enough And so we collect the query, the cold stack at the time, and also just the clock time that it took to run it, because that's a

um that's a measure of like how expensive the thing is And uh you can then once you collect the stats, you can do all sorts of sorting, like you can uh, for example, um prioritize the the most frequently called uh identical query like if the same if you see the same query being called you know a thousand times that's your smoking gun that's where you want to start looking first And so that's a typical output. So at a high level before dialing down too much about where they're coming from and what they look like. You can just take a glance at a at a given request and say, oh, okay, here we've got nine queries. They they took 36 milliseconds. To run, you know, clearly

not dominant part of this particular request, so maybe not a big deal. And then uh the important question there is what happens if you have a lot more objects in your database? Or if you're asking for more output, um how will these queries scale? If you're seeing increases, that's that's a red flag. That's probably a bad idea, and then you need to really understand what those queries are and where they're coming from And then at the detail level, if you decide to sort of output more details, that's where you see the actual query. And then you can Also dial down to the actual call stack location where uh where they're being uh exe executed Yeah, and so what we like I I guess I'm gonna be a little repetitive here, but yes, we'll so pay attention to the toll number

queries. Same query executed many times, clear candidate for optimization And then yeah, if having those cold stacks is super important, especially in a situation where you have a lot of code that you haven't really cleaned up, haven't really optimized in a sense. There's a lot to to comb through and so knowing exactly where they're coming from is super useful. And so there are many techniques for actual optimization. It all depends on the nature of the data, the nature of the queries, but uh the the two most uh frequently recommended ones are select related. And that's where in addition to retrieving your primary object in the query set you tack onto it whatever the the

straight up foreign key relations from there. And prefetch related applies in a little more complicated situations like actually in this the the number of books available uh scenario that I had earlier. So I'll I'll show that in a second. In addition, uh if serializer logic is complicated is if it's kind of hard to get the right data to the right place using Select related or prefetch related. The one thing to remember with SELECT related and prefetch related is that uh yes they're there and they're super convenient, they will instantiate the entire other related model. So that can carry its own costs, right? You might say, great, I'll you know I'll get an author with every book, but well what if the author model happens to be

fairly expensive to to retrieve from the database. So in that case, maybe that's not what you want. So serializers uh the contest give you uh gives you a lot more control there because what you can do is In your endpoint, you run your optimized queries, just the right amount, load the data in in this context, and then inside the serializer you can then access it and essentially grab it. You can think of it as a very localized cache for the duration of your request. Of course, all of that adds complexity and kind of couples different parts of your code, so use with care. uh comes with you know with price of complexity and potential bugs in the future so we need to be protecting against those bugs as well. And then cached property I wouldn't necessarily recommend this

as a as a first a choice solution for anything, but sometimes if you end up referring to the same property a lot and it's expensive to calculate for whatever reason, partly maybe because you're grabbing something from the database um you can choose to cache as well and it will be there on that particular instance for the duration of its you know existence in in in that um memory So specific examples. If you go back to what we had before. uh that serializer that i showed with a couple of fields right so if if it it's probably being used by something like this book view set it has the the basic query set of books uh And it uses the serializer, and then you have that inherent cost of two queries per per instance, as I mentioned earlier.

So this is the first and very simple fix Where you just tack on select related on the author and the author will be there in the same query and then later on any references to it will not cause any more queries. So that solves you know one of the two We 're still left with the other one and the other one actually takes two steps. So first you want to prefetch related and because this is the reverse relation, that's why you know prefetch related comes into play compared to select related And so for every uh book you get physical books retrieved. So with prefetch related, you don't eliminate the extra queries, but you uh you just get one per type. So in so in this case

Since there's only one other related object, physical books, you'll just get one extra query regardless of how many instances of the primary object you're retrieving here. And then the second part to it is if you remember in the first example we had the filter which always will always run. database query. Again, here you have to basically do it the other way. Like you might look at it and think, oh, this something's wrong here. Well the good news is that. all doesn't run a query if the thing is already retrieved. And that's the saving race here was with prefix. And so if you essentially if you filter it, you know, effectively it's doing the same thing, but it's taking advantage of the prefetch related that already ran. And so with that, we're down to

uh no more linear growth with the number of books that we need to return. And so that that's a very simple example. A lot of the times uh You know, in real life you you need a lot more than that, but just to give you a taste and then yeah, this probably deserves its own in-depth talk of various optimization techniques. And one other thing I'll say, which is super useful with both select and prefetch related is that because they work you know they tack onto the main query set here. They work really well with pagination. And then pagination is also your friend. This is one way to both reduce the uh the database queries and cost of database queries and um and just uh limit the amount of data you need to read and keep in memory for the time of the request, etc.

etc. So pagination works really well with this as well. So so so if you're only retrieving 10 books out of a thousand it's it's only going to retrieve related authors and physical books for those 10 books. So that's the other good news. So, you know, we fixed those problems. Can we go home? Can we you know deploy to production on Friday and be happy? Well, maybe, uh, but not for too long. And my recommendation is at that point you know, don't don't stop and protect your gains. And the again Django ecosystem has this great other thing. among other things, but uh Django uh a certain max number of queries is a fixture that you can grab and use in your unit tests

And so if um there's also an a similar one for the exact number of queries. I don't know that you want that. I think upper limit is probably a better way. Uh we'll get you a more reliable test. So Write those unit tests in those, particularly in those endpoints where you feel like you may end up introducing more queries over time. And you probably want to write those tests in a way that will exercise them against many objects so that again you can observe if there's a linear increase uh suddenly getting introduced. So with that you get Some protection and then well what if some new endpoint gets added and then you do you don't you forget to write a test? That that's also a problem. But that's where uh one solution could be using coverage context

So that's the a lot of you might might know about unit tests in coverage tool in general, but there's also something called coverage context where you can then examine what gets covered in a particular context and that could be endpoints or some other code scopes. And so you can do regular reviews of those things. and that way find out um if you've introduced something that's not yet covered. Yeah, and then one other thing, and then again that that that uh was voiced very well in the logging uh talk earlier today is that observability Super important because once you're in production , you don't get a benefit of like quickly

going and fixing this extra thing that you missed. basically knowing at that point, you know, having some tools that you can turn on more instrumentation, or even at least having some very basic metrics, very basic metrics such as number of queries per request or how long you're spending in those database calls uh will let you you know if you log that you know pipe that to Kibana or something like that then And you can install metrics, alerts, do regular reviews, all the good stuff, depending on your operational posture, as they say. And then at least you're then you will become more aware of of these things happening. You can plan on addressing them before they they blow up, right?

uh I guess allusion to the herd, right? You want to herd your queries, you don't want them to stampede and you know kill your database. So And that's basically it. And like I said, we have a blog post. This was kind of technical, but the blog post goes in a bit into a bit more detail. uh where you can see uh more code and a reference to the to the reference implementation um in github And yeah, feel free to ping me on Slack here. I'm on the conference Slack as well as, I don't know, I think my email is here somewhere as well. Yep, and uh huge thanks to the Django community. Lots of like I said, all the ingredients were there, what we just

we just put together using those. Thanks to my colleagues for helping with the actual implementation.