(All tags.)
Slow queries happen, and when they do, it can be tough to dissect why they’re slow. This difficulty is compounded by using Django’s ORM, since it generates the SQL for you, so you may have little idea of the actual queries “under the hood”.
Your PostgreSQL server is seizing up, with some naughty queries consuming too many resources or blocking others. Don’t panic! You can stop those problem queries and stabilize your system.
I recently upgraded my client ev.energy to PostgreSQL 13. The first feature listed in this version’s release notes is “Space savings and performance gains from de-duplication of B-tree index entries”. I reindexed all tables after the upgrade to take advantage of this deduplication and saw index storage savings of up to 90%.
At yesterday’s DJUGL meetup I heard a talk from Esau Rodríguez about a buggy new system deployment he survived. His team were deploying a new version of a system that changed the way it connected to its MySQL database, while simultaneously moving session keys from integers to UUID’s. When they discovered the database connection change was flaky, they rolled back to the previous version. This quickly lead to a user reporting being able to see another’s data!