Seamless Postgres Query Optimization

About This Tutorial

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?