North Bay Python 2024

How to optimize Postgres queries for Python developers
2024-06-29, 11:20–11:45 (US/Pacific), Barn

Many Python applications use Postgres as their backing database - and that means, when we think about our application performance, we have to think about the database. In this talk we'll do a dive into best practices for optimizing Postgres queries, aimed at application developers.

Starting with EXPLAIN ANALYZE (which shows you a Postgres query plan), we'll walk through common situations like how to debug a bad query plan by looking at row estimates, understanding JOIN order, the role of indexes (and which index types exist in Postgres) and how we can understand how much data is being loaded by the database behind the scenes.

Whether you work with Postgres day to day, or only think about it when there is a slow query problem in your Python application, this will help you have a more productive relationship with the database. And even if you use a different database, a lot of the principles of debugging apply universally.

Founder and CEO of pganalyze, a product for monitoring and optimizing Postgres performance.

Host of the weekly series "5mins of Postgres", that explains what's new with Postgres development, or introduces various Postgres concepts in detail.

Author of pg_query, a C library (with Ruby, Rust, Go, Python & other bindings) to parse queries just like Postgres does.

I love working with PostgreSQL statistics, visualizing them, and using techniques like constraint programming to automatically optimize a Postgres database for a specific workload.