Intro to pg_stat_statements: Installing and Setting Up the Extension

The pg_stat_statements extension in PostgreSQL is a tool for collecting query statistics. It lets you see which queries run most often, which ones take up the most time, and how efficiently your database resources are being used. Instead of analyzing every query manually with EXPLAIN, you can get a big-picture view of your database's performance.

Why bother with pg_stat_statements?

Real-time monitoring: You can see which queries are putting the most load on your database right now.

Whole-system performance analysis: Info is available for all queries in the database, not just the ones you decide to analyze manually.

Finding slow queries: It's easy to spot which queries are taking up the most time.

Spotting repeated queries: Lets you optimize caching and add indexes for popular queries.

Installing and Setting Up pg_stat_statements

Now that you get why pg_stat_statements is useful, let's break down how to install and set it up step by step.

1. Check if PostgreSQL is ready. Make sure your PostgreSQL supports the pg_stat_statements extension. This extension has been included by default since PostgreSQL 9.2. To check if it's available, run:

SELECT extname FROM pg_extension;

If pg_stat_statements isn't in the list, it might mean your admin hasn't installed it yet.

Here's what it should look like when the extension is installed and active:

2. Adding the extension.

To activate pg_stat_statements, you need to add it to the list of preloaded libraries in PostgreSQL. This is done in the postgresql.conf config file.

Steps:

1. Find the postgresql.conf file. It's usually in the PostgreSQL data directory.
2. Open it for editing.
3. Add or change the line:

   shared_preload_libraries = 'pg_stat_statements'

Why do you need this? Because pg_stat_statements needs to be preloaded since it tracks queries at the system level.

4. Save your changes and restart the PostgreSQL server to activate them. Here's the command for Linux:

   sudo systemctl restart postgresql
   

If you're developing or testing locally, just restarting the server will do the trick.

3. Creating the extension in your database. After restarting the PostgreSQL server, you can create the pg_stat_statements extension in a specific database. Connect to your database using psql or another tool and run:

CREATE EXTENSION pg_stat_statements;

If everything went well, the command will finish without errors. Now pg_stat_statements is active for your database.

4. Tweaking pg_stat_statements settings.

After installing the extension, it's a good idea to tune its settings for proper stats collection. The main settings can be set in the postgresql.conf file.

Main settings

• pg_stat_statements.track
• Decides which queries will be tracked.
• Values:
  • all — track all queries (recommended for debugging and analysis).
  • top — track only top-level queries.
  • none — turn off tracking.
• Example setting:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Sets the max number of queries to keep in the stats.
  • Default: 5000.
  • If your system has a ton of queries, bump this up, for example:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Decides if stats are saved between server restarts.
  • Values: on or off.
  • It's a good idea to leave it on:

    pg_stat_statements.save = on
    

After changing the settings, restart the PostgreSQL server again.

Checking if pg_stat_statements works

Now that the extension is installed and set up, let's check if it's working. To see the collected query stats, run this query:

SELECT
    queryid,        -- Unique query identifier
    query,          -- Query text
    calls,          -- Number of times the query was called
    total_exec_time,     -- Total execution time (in milliseconds)
    rows            -- Number of rows returned by the query
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

What do the columns mean?

• queryid: unique query identifier, handy for finding the same queries with different params.
• query: the SQL query text that was run.
• calls: how many times the query was called.
• total_exec_time: total time (sum of all calls for this query).
• rows: total number of rows returned by the query.

For example, if you see a query with calls = 100 and total_exec_time = 50000 (50 seconds) taking up most of the system's execution time, that's a clear sign you need to optimize it.

Common pg_stat_statements Use Cases

1. Finding the slowest queries. To find queries that take the most time, sort by total_exec_time:

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

2. Spotting the most active queries. To find queries that run most often, sort by calls:

SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Index usage analysis. If you see a lot of slow queries, check if they're using indexes. For example, in queries with filtering (WHERE), missing an index is often why performance sucks.

Clearing pg_stat_statements Data

Sometimes you might want to reset the collected stats and start fresh. You can do this with:

SELECT pg_stat_statements_reset();

After resetting, all stats are wiped and data collection starts over.

Pro Tips

Limit the amount of stats you collect: If you're working on a high-load system with millions of queries, keep pg_stat_statements.max at a reasonable level to avoid extra load.

Clear stats regularly: It's a good idea to do this before starting a performance analysis so you don't mix old and new data.

Pay attention to slow queries: Even if they run rarely, a single slow query can really bog down your database.

Now you know how to install, set up, and use the pg_stat_statements extension to analyze query performance. In the next lecture, we'll dive deeper into finding slow queries with it and optimizing their execution.