Slow database queries? How to find bottlenecks with pg_stat_statements

Why databases slow down in production environments

When an application starts responding slowly, the bottleneck is almost always hidden inside a poorly constructed database query. In modern software systems, figuring out exactly which SQL statement is consuming all memory and processor cycles can feel like searching for a needle in a haystack. In practice, this means that without the right diagnostic tools, developers end up guessing the root cause rather than diagnosing it with real production data.

To solve this challenge without installing heavy external monitoring software, the PostgreSQL ecosystem provides a native tool called pg_stat_statements. It acts like an airplane black box recorder, logging detailed statistics about every command executed by the database. This type of inspection is crucial to complement broader performance strategies, as we detail in our guide on how to optimize slow queries in Postgres, ensuring your system handles heavy traffic without crashing.

What is pg_stat_statements and how does it work

The pg_stat_statements module is integrated directly into PostgreSQL to track planning and execution details for all SQL statements passing through the server. Unlike traditional query logs that flood disk storage with gigabytes of text, this extension maintains a summary inside RAM memory, grouping similar queries together and accumulating their total execution time. In practice, if your application runs the exact same ID lookup one hundred times, the extension groups them into a single statistical row with the cumulative time.

Before extracting useful metrics, you must ensure the extension is enabled in the primary database configuration file, postgresql.conf. You need to add the module to the shared_preload_libraries directive and restart the database service. This step requires administrative privileges and a short maintenance window, as PostgreSQL must load the library as soon as the operating system initializes.

How to enable and configure the extension

Once the PostgreSQL service is restarted with the required parameter loaded, the next step is to actually create the extension inside the specific database you want to monitor. To do this, simply execute a creation command within your SQL terminal. In practice, this means any user with proper administrative permissions can run the command and unlock access to the virtual metric tables.

To activate the extension and start collecting real-time performance data, execute the following command in your favorite database client:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

With the extension installed, PostgreSQL creates a system view called pg_stat_statements. This is where you will query the consolidated performance history of all connected applications, allowing you to cross-reference execution times, affected row counts, and total call frequencies.

How to query the statements consuming the most time

With the tool running and collecting data, it is time to analyze the application's actual behavior to uncover the true performance villains. Instead of looking solely at the isolated execution time of a single query, the secret is to sort results by total accumulated time, which multiplies individual slowness by how often the query gets called.

To list the top ten queries consuming the most processing time on your database server, you can use a structured query directly against the system view. Run the following command in your terminal:

SELECT     round(total_exec_time::numeric, 2) AS total_time_ms,     calls,     round(mean_exec_time::numeric, 2) AS avg_time_ms,     queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;

This command returns a clear list containing the total time spent by each statement, how many times it was called, and the average milliseconds per execution. In practice, a fast query that runs millions of times per day will surface at the top of this list long before an extremely heavy query that runs only once. To complement this implementation in production environments, see our related technical analyses on How to test the laptop power button circuit on the bench and How to Isolate IoT Traffic with a MikroTik VLAN.

Interpreting results and identifying system bottlenecks

When reviewing the list generated by the previous query, pay close attention to the call count combined with the average execution time. If a simple user lookup query takes only twenty milliseconds but gets called ten thousand times per minute, it represents a critical bottleneck draining server resources. In such cases, adding a proper index to the database resolves the issue instantly and relieves CPU pressure.

Another key takeaway is spotting queries that return massive amounts of rows while utilizing minimal resources per row, indicating missing pagination or restrictive filters in the application code. Diagnosing these patterns under real load is what separates unstable systems from resilient architectures ready to scale without unpleasant surprises.

Resetting statistics and maintaining ongoing monitoring

Because pg_stat_statements continuously accumulates data since the last server restart or manual clear, old metrics can mask recent issues caused by a fresh software update. To ensure you are analyzing only current system behavior, it is best practice to zero out statistics periodically or after performing major application deployments.

To restart the extension's statistics counter and start a clean collection window, execute the reset command directly in your database:

SELECT pg_stat_statements_reset();

Maintaining an inspection routine with this tool ensures your engineering team spots performance regressions before end-users ever notice slowdowns. Continuous monitoring transforms database management from a reactive firefighting effort into a predictable, controlled operation.