foreign
they will be able to see us now in that
question time through the camera so wave
hi
hello
are we all ready to go over there
yeah I I'm all set excellent
how about it
thank you uh hello everyone my name is
and I'll be talking to today about n
plus one problem in Django rest
framework here at pycon AU very excited
to be here even though remotely but
since my first time at baikon EU so
pretty much excited
uh
so our agenda today is like just a brief
of what is Dengue rest framework and the
rest of the talk will focus on like what
is n plus one problem how it can happen
within Django rest framework how do we
identify these problems uh in our apis
and how do we resolve these uh problems
uh before I jump into the content who am
I
again myself
Ali I am a software engineer by
profession and currently I am working as
a contractor with rbsoft at Lahore
Pakistan
so ibisoft is mainly mainly a service
provider company and we have a diverse
set of technological skills uh primarily
I've been working with python and Django
for last couple of years
as part of rbsoft Team I've been working
with edx Slash to you as rbsoft
contractor
apart from that I have been involved
with a few internal products development
within RB soft
if you want to get in touch with me over
LinkedIn read something that I have
written on medium or see my GitHub the
links are here I'll share the slides
slice Link at the end of the
presentation
uh with that out of the way let's jump
into the content starting it it off with
jungler's framework
so
if any of you work with Django over like
over the last decade it's highly likely
that you got to interact with djangora's
framework in one way or another uh like
with the Advent of cloud and single page
applications uh we have seen uh arriving
popularity of Django and apis develop on
Django back-ends using dangote's
framework
uh what's beautiful about jaguars
framework is that uh
it is built upon the core Django
features and it integrates really well
with them
so for instance how we expose our apis
using URLs and Views it's all built upon
the Django's views and URLs uh framework
using Django models we can easily create
serializers so how see laser or like how
we represent data in Json and other
format
drf also take care of tcls
deserialization so in a sense if we are
getting data in form of Json or some
other formats uh it does the job of
converting that Json into appropriate
model objects
mainly drf is used for API development
and for that it provides a variety of
options in how we design our views
if we like uh want to write a complete
API from scratch we can utilize API
views
but if we have serializers we can use
view sets and genetics to set up an API
within like three to four lines
and lastly using custom authentication
and permission we can easily add layers
of controls on our apis so in that
regard drf make thing makes things
really easy in how we can design apis
using Django
look moving forward like what is n plus
one problem and how does relational
database come into the play here
so in typical rdbms like my SQL or
postgres uh how we represent real life
objects is in the form of tables so our
data model is scattered across different
tables and the tables are connected are
related to each other via some sort of
relationship beat one one one to one one
to many or many to many
and we join those tables to get the
complete information of an entity
so let's say for this example we have a
customer that can order a bunch of
things and within an order there can be
multiple products so if we want to get
the uh entire order information of a
customer we can do that in two different
ways
first way is that we get customer
information from the customer table
based on the customer ID we can get all
the orders made by that customers and
then based upon order IDs we can get all
the products
the second way of doing that is simply
join the tables using for foreign keys
and then get all the required data in
one or two queries
so these are two different ways of
achieving the same thing
in first method we are making one query
to get the main object and then for each
related object we are making a separate
query so one for one query for main
objects and then for each of the related
object we are making uh separate queries
so it's one plus NQ result n plus one
queries
the second is we are just joining uh
the tables and getting all the
information
so the question here might be okay both
methods are like doing the same things
in a different way so why is n plus 1
and issue when it comes to rdbms
it's not really an issue on the DB level
but when we involve our orms or object
relational mapping that's where n plus 1
becomes a problem
uh so Django is an orm SQL Alchemy is an
orm and with orm we can uh
do a bit of uh abstraction and how we
design our database so we Define our
models using orms and then orms take
care of like generating the appropriate
table adding the appropriate
relationships when we want to fetch
something Oram takes care of all of that
so all of the heavy load of connecting
with uh uh like database server getting
the information that is handled by orm
why is n plus one problem in here is
that uh Oram has to make a DB round trip
for each query
so let's say if uh for the previous
example if a customer has 100 orders so
orm might be making 100 plus queries to
get to get the information for One
customer
the individual queries might be fast but
it's the combination of all the security
that becomes problematic
so essentially the more queries are
there in the fetch operation
the more round trips to the database
will be made by orm and the more round
trips are there the more load we'll be
putting on our database servers and
causing performance issues
so because of how olm works that's why
the N plus one becomes a performance uh
like performance complication
performance problem
okay uh
moving forward I'll be using this
example for like all the explain all of
the things in drf
on online resources the author and book
example is used a lot to explain the N
plus one problem and that is why I'm
using that example here as well so in
this we have an author uh one to one
with Django user uh
author can write multiple books book can
be of a category and we can add tags to
books like New York best time seller
number one seller
Etc
uh so it's like really simple uh
model nothing complicated
the idea here was to show different type
of relationship that we can have and see
how n plus one impacts them so on author
we have a one-to-one with user manager
money with specializations on book
we have a foreign key to author foreign
key with category and manage money with
uh tags
and the API is two apis that will look
is listing all authors with their books
and
list all the books including the person
who authored it so it's like we are
doing similar things but there will be
operational differences
uh
it's just a screenshot of this apis in
action so on the left side is the
authors listing where we are listing the
authors
details and the books that they have
written
on the right side is the book listing
where we are adding the details of books
including the author information
but here we won't be having the books
info information again so it's already
up there
why I'm using listing because in listing
we are fetching objects in bulb and it's
really easy to demonstrate what's going
on behind the scene with listing with
retrieval identifying n plus one one
problem might be tricky but with listing
it's really easy to see what's going on
under the hood
so let's start off with like how n plus
one can happen with drf views
uh
in here we'll be more focused on the
generic and models view sets that drf
provides
in both of these uh options we require a
query set to return our necessary
objects
security sites in Django terms is just a
collection of DB objects it can be zero
one or many
we can either set a value for Q a set
attribute or override get query set
method
so it's that query set that defines how
or which model will be used to get the
objects from
so let's say for both book and auto
listing we are just adding
book.objects.org or author.objects.all
and it takes care of listing all of the
entities
so you can see like it's very easy to
open within three or four lines we have
set up an API view but there are things
happening uh at the back
now the problem with query sets is that
they are lazy like lazy loaded uh DB
won't be hit until the object is really
needed so it is not pre-fetched the
information
once we require that information that's
when DB will be hit together information
so it goes through for the main objects
the related object
on the main object
and the related object objects on the
related objects so for every case uh
unless the object is not uh needed it
won't be retrieved
so this query set behavior is something
that saves into like drf views and cause
problem complications
so let's say uh if we take this example
book Dot objects.org uh
if we want to print out the book
author's username
for each book we'll be hitting the
database because the query set would not
have prefetched the author information
so let's say if we have 100 books we'll
be
hitting at least 100 times
to get the other information for each
book
so like that's a
problem with curioset but that impacts
how drfus are are written
similarly uh on the serializer level uh
serializer and like how we diff we
Define how the objects are or its
related objects will be represented
on serializer uh we can also use Query
search for certain type of fields
uh so let's say uh if I'm getting the
tags information I can uh Define the
query set from where this information
will be fetched and this is like
specific to certain Fields where we can
use Query set
or Source attribute
so Source attribute is where we Define
which attribute to read the information
from
so let's say I'm I'm getting the author
information and we need the email uh I
can
tell that okay read email from
user.email
and ship same is the case for username
now for both of these uh
operations
if these have not been pre-fetched these
calls will result in additional DPA
of course
so if we are listing there will be like
n plus one on the query set but and also
n plus one on DC laser level as well so
unless we have prefetched the user
information
using such uh configuration can result
in additional DB calls when we are
fetching information
so like the fields that take source and
query set attributes are prone to n plus
one uh issues so if like again the
object the iterated object is not
already prefetched it will
cause additional database calls
and again the behavior is that because
curious ads are lazy they do not fetch
the related object unless we need those
objects
so when we are writing uh apis be it in
Django or prf
how can we know that
the code that we are writing has n Plus
One problems so like what uh again uh
I'm using listing to uh to Showcase this
on local we might not have that much of
data to see what might be going wrong
here so for to identify and plus one
problems we have a few tools that we can
use uh one is n plus one the second is
Django debug toolbars so both of these
are the packages that can be easily uh
configured with our Django applications
n plus one is a package specific to
detect n plus one problem so it's it
like analyzes the application logs and
logs the potential and plus one issues
that might be happening within the
application
it does not tell where that issue is
coming from it just logs out that on
certain Fields there might be n plus one
problem
uh this package is not actively
maintained but uh but it still works so
that's plus one about it so
using the same package on both our books
and author listing uh endpoint
it is just tell that there is n plus one
query on book dot tax set or type field
other fields same for the author dot
user Fields author.books fail
it does not tell where this n plus one
is originating from
so that's why it it is better to test
each API in isolation
so take one API
see how n plus 1 is responding against
that and try to figure out where the N
plus one might be coming from
Django debug toolbar is like a very
important but much more heavyweight
package than n plus one because it is
used for debugging the entire Django
apps and it outlines much more
information so it's like what Django
version are using what SQL queries are
being executed to get that information
how many signals or signal handlers were
executed so it provides much more
information for us SQL queries are
important in identifying and placement
problems so if we uh see a lot of
duplicate queries where the queries are
same but these
filtering criteria is changing so let's
say we are only changing IDs so we are
making 100 same uh
SQL theory was only the ID is changing
that indicates that there might be n
plus one problem uh with that endpoint
so like here's the output for the
author's listing so Django debug toolbar
indicates that okay 197 queries were
executed they are 195 similar so it's
telling their
there are duplicate queries here and
there
and we can identify just looking there
okay we are getting you the information
ID is changing we are getting
specialization the ID is changing so
these duplicate queries are an
indication that we are hitting DB more
than we should
same is the case for the books listing
where like 50 queries and their 51
similar and we can see the pattern here
as well that we are getting user
information or ID is changing category
information same the query the same but
only the ID is changing so it is a
repeat uh queries that is uh indication
that we there might be n plus one
problem here
so we can use both of these tools to
to like see if they are n Plus One
problems on our API uh but once we have
identified the N Plus One problems how
do we resolve those problems so that we
are not hitting DB and
for more than it's needed
this is something that is provided by
the Django or we can there are two uh
methods that we can use on query sets
select related and prefetch related
select related is used to
patch our foreign keys or one-to-one
relationship when we are fetching the
main object
so uh it is a join on SQL level and get
three main objects and the one to one
then
foreign key relationship in a single
query and the neat thing about is uh is
that we can also fetch the rated related
objects uh in that same call as well so
we can use one query to get the entity
into information
so
previously we were doing book dot
objects at all now we can change it to
book.objects and then we can specify
which relationship to fetch alongside
books we can like fetch the author
information
authors user information book type
and once we add that we can see a
difference in the log of n plus one
where previously
uh it was telling that type and author
have n plus one now it's just telling
that text set as unfortunate problem so
by using this simple uh method we have
reduced the the N plus one to only one
field
and we can also see a reduction in our
SQL care is that now uh there is one
join operation that's getting the book
information author information and
author user information so all of that
is happening in a single query
there are still duplicate queries that
will be resolved uh in a upcoming sites
we can use the same select related for
on our author listing as well
and again there will be reduction in SQL
queries here at 12.
so where previously uh there were 53
queries for book listing which select
related that can't reduce to 23 for
author we are down to one uh 39.
prefetch related is like used to get our
foreign reverse foreign key or money to
many relationships
the difference with prefetch related is
that uh
it prefetches the required object via
SQL Theory and then performs the giant
operation in Python
so select related uh is doing the join
in DB level prefatch related is doing a
join on our
python level so that's uh
big difference here
uh the idea is that since uh with many
to many relationship we have an
intermediate table so uh when we are
joining three tables for forum like one
man-to-man relationship the resultant
table that is like obtained after all
the Giants can become very huge and that
can cause uh problem issues in itself
so for that purpose uh the mind-to-many
or money to one join is performed in the
python code base
so if we go back to our book listing we
can change this operation so after
selected we can prefatch related the
tags information and like author
specializations
and the result of that is we are getting
all the information and like five
queries so where there were like 50 plus
here is 100 plus Q is now we are getting
that information in five queries
so we can see the DraStic difference
here
uh
the the thing I'll Focus here is that we
we can see that we are fetching uh like
tags or specialization against special
or certain IDs
so like these will be all the authors
that we have fetched and only those
specializations will be fetched and then
python will take care python jungle will
take care of the internal joint
operation
same is the case on the other listing we
can uh chain all of the operations and
then see the
output of that that we're doing all of
the fetching in seven queries
so if we compare SQL queries before and
after uh
previously we were
getting all the books in 53 queries with
both select and prefatch related we are
doing that operation with five queries
on author one for we have gone from 197
to 7. so that's a like pretty big
reduction in SQL theories and how many
DB round trips that we are making
and this select and prefatchellated it
is something that drf also recommends
using to avoid n Plus One problems on
both our serializer level and on view
level
uh
the prefetching is something that drf
does not performs on its own because
like it is using curious as and curious
as or lazy so that's why the
recommendation by drf is that use both
of these built-in tools and get the
required related objects yourself
so they have mentioned that you see
programmer responsibilities to optimize
those queries
so again with plf it's really easy to
create apis but uh with the and and plus
one problem we can see a lot of
performance issues when our application
scale so it's better to identify those
problem beforehand and resolve them
using select or prefetch related
okay we are at the end uh so n plus one
is like a pretty common problem in both
code Django views and drf uh views
we would not know the performance impact
of these issues like unless our
underlying data hits a certain limit so
let's say if we have limited number of
objects both on production or local we
might not know that something is wrong
uh when it comes to fetching information
but once the data increase we will see
we'll start to see the performance
implication or on our production
so like we can use the tools like n plus
one and debug Toolbar to identify these
problems beforehand and fix them before
this problem appears with the scale
one thing to note is that that n plus
one fix up scan results in complex joins
uh on DB levels that can sometimes cause
negative impacts on the performance
so uh in the current example our
relationship is quite simple but they
will be uh reliable applications where
we might have 20 plus one one to one or
one to many or main to many
relationships
if we are prefetching all of that
information in a single go
the resultant join query in SQL might be
inefficient or might not be using index
so if your model is a bit complicated uh
try a combination of Select and prefetch
related to see which combination yields
the best results for you uh you can
maybe use uh caching on certain query
sets to uh avoid hitting the database
again and again
so let's say for the book categories we
can use some sort of cache to previous
uh that information in the cache and use
that cache information so click
the bad
try a bunch of combination see which
yields the best results for you it will
vary on case to case uh basis but in
most cases using select related and
prefatch related will resolve a lot of
the
like performance related issues on the
RF APS
uh
I just talk was inspired from a similar
talk at python Web Conference so it's
just the talk title fighting and fixing
and plus one problem in Django dogs uh
you can like see that uh the recording
of that stock on YouTube the slides for
my talk or present on my GitHub same is
the case for the code
uh there are a bunch of the references
for the different terms that we have
talked about
if you have any feedbacks suggestions
comments like if you were looking for
something that I did not add or if it
was a bit too much please uh leave your
feedback comment uh you can add the
questions on Discord and I'll happy to
answer them async
uh thank you very very much and have a
nice rest of the conference
[Applause]
I hope you can hear all that clapping
yep excellent uh would you like to take
one question from the audience we have
time for one
uh sure yeah okay if we have one
we can do one
okay it looks like most of the questions
will be coming to you on Discord
got it got it because even though your
talk wasn't on asynchronous you're going
to be asynchronous
thank you so much
we will clap again now foreign
[Applause]