Exercise 3: pg_stat_statements — Purpose, Two-Step Setup, and Practical Value — Possible Solution ==================================================================== WHAT pg_stat_statements IS FOR ------------------------------ Per this chapter, "pg_stat_statements tracks execution statistics (call count, total and mean execution time, rows returned) for every distinct normalized query run against the server." Rather than adding a new type or function the way most extensions do, this one adds observability — a continuously-updated record of how every distinct query pattern actually performs over time, without needing to manually instrument or log queries individually. WHY IT REQUIRES A TWO-STEP SETUP ------------------------------ Per this chapter, "unlike a typical extension, enabling it is a two-step process: it must first be added to shared_preload_libraries in postgresql.conf (a configuration change requiring a server restart), and only then can CREATE EXTENSION pg_stat_statements actually register it." A typical extension (like PostGIS) is self-contained enough to be installed with CREATE EXTENSION alone, because it doesn't need to hook into the server's own core execution process from the moment it starts. pg_stat_statements, by contrast, needs to observe and record every query as it executes throughout the server's lifetime, which requires it to be loaded as part of the server's own startup process (via shared_preload_libraries) — a setting that can only take effect after a restart, not something a plain CREATE EXTENSION alone can retroactively apply to a server that's already running. THE PRACTICAL VALUE WHEN DIAGNOSING A SLOW DATABASE ------------------------------ Per this chapter, "querying it, ordered by total or mean execution time, is very often the single most useful starting point for diagnosing 'why is this database slow' — it surfaces the actual worst offenders directly, rather than guessing." Without pg_stat_statements, diagnosing a slow database typically means guessing which queries might be the problem, or manually watching individual queries as they run. With it enabled, an administrator can simply query pg_stat_statements sorted by total_exec_time (to find the queries consuming the most cumulative time across all their executions) or mean_exec_time (to find individually slow queries), immediately identifying the actual worst-performing queries with hard data, rather than relying on intuition about which part of the application "feels slow." WHY THIS WORKS AS AN ANSWER ------------------------------ It states what the extension tracks using the chapter's own wording, explains specifically why its need to observe the server from startup forces the two-step process (unlike a typical self-contained extension), and describes concretely how querying it replaces guesswork with direct evidence when diagnosing performance problems.