Your Django-based application is deployed and running smoothly, accumulating more users and more rows in your database every day. One day, you will inevitably and suddenly encounter an unexpected performance problem, either because a specific page of your application suddenly loads slowly and your users complain, or because the load on your servers is a lot higher than usual – even though you are experiencing normal levels of traffic to your application. Using your development instance, you’ve already checked that you use select_related() and prefetch_related() properly and do not cause unexpected numbers of database queries and your caching works properly.

What most likely happened is that one or more SQL queries suddenly turned pretty slow since your data set has grown and the database is choosing a non-optimal way to execute your queries. But now what?

In this talk, I’ll show some tips and tricks on how to find the slow SQL query among all the other ones, even if you have no fancy instrumentation tooling in use. I will then show you how to use the EXPLAIN command to find out what the database is actually doing and how to identify and fix common problem sources such as a missing index, or – even worse – an index that exists but is not actually used.

This talk will mostly focus on PostgreSQL, but the basic strategies also translate to other database engines.

Video: https://youtu.be/sPO6wDlvKVI