Seamless Postgres Query Optimization
Seamless Postgres Query Optimization is a methodology that allows every backend engineer, even without deep database knowledge and 10-year Postgres experience, to move step-by-step to find and eliminate the worst bottlenecks in any SQL.
There are two types of Postgres query analysis:
- "Macro": analyzing the workload as a whole (three major approaches: using metrics provided by pg_stat_statements or similar, log analysis with pgBadger or similar, and sampling of pg_stat_activity)
- "Micro": diving into details of single query execution (EXPLAIN command being the central tool here)
And there are huge gaps between them that become noticeable at scale. The main challenges:
- Switching between "macro" and "micro" without a huge overhead
- Verifying optimization ideas reliably
- Deploying changes risk-free
- Solving these tasks at a scale requires advanced DBA experience and–sometimes–intuition. Or better tools that (fortunately!) very recently started to appear.
In this tutorial, we will learn how to establish a smooth and seamless SQL optimization process in your organization:
- what tools should you choose in your particular case?
- how to close the gaps mentioned above?
Nikolay Samokhvalov
- Founder of Postgres.ai: automation of database testing in CI/CD, modern database monitoring for Postgres, databases in clouds. Among clients: Chewy.com, GitLab.com, Mercury.com, Miro.com.
- Postgres.TV (YouTube) and Postgres.FM (podcast) co-host
- 18+ years of experience in the field of open-source databases