Seamless SQL optimization, v2.0

Nikolay Samokhvalov


16:00 (UTC+3) , russian

There are two types of SQL query analysis:

  1. "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)

  2. "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?