05-31, 09:15–11:05 (Europe/London), East Drawing Room
How to write complex and efficient queries using Django and PostgreSQL, going beyond the usual N+1 problem and other simple optimizations
Database performance is a critical part of any production app. Inefficient queries are not really a problem on small applications but can make the whole app unusable on large databases, with tens of millions of rows.
The Django doc has interesting examples for the most obvious and simple problems, such as the famous “N+1 query problem”, but “real-life” services usually face more subtle and complex issues.
The goal of this workshop is to go beyond the usual prefetch_related
, only
or values
methods, when these are not enough, and how to detect potential database-related performance issues.
During this workshop, we’ll start by showing how to inspect the SQL generated by our code, to detect basic mistakes, such as the “N+1”.
Once the basics have been covered, we’ll dive into two specific examples to show how to improve queries, little by little, using all the methods the ORM offers, other third-party libraries (such as django_cte
), or even raw SQL.
The first example will focus mainly on Django optimizations alone, while the second, more complex, will be a mix of Django and PostgreSQL optimizations, showing how to detect and fix limitations DB-side
At the same time, we’ll write a script to benchmark all the possible solutions and explain the differences that could appear between our local environment and our production servers.
Finally, we’ll discuss WRITE queries, and how to optimize them as well, both in terms of duration and memory.