How to Use pg_stat_statements in PostgreSQL for Query Monitoring

To use pg_stat_statements in PostgreSQL, first enable the extension by running CREATE EXTENSION pg_stat_statements; and configure postgresql.conf to load the module. Then query the pg_stat_statements view to see aggregated query statistics like execution count and total time.

Syntax

The pg_stat_statements module provides a system view named pg_stat_statements that aggregates statistics about SQL queries executed by the server.

Key parts:

• CREATE EXTENSION pg_stat_statements; - Enables the extension in your database.
• shared_preload_libraries = 'pg_stat_statements' - Must be set in postgresql.conf before server start.
• pg_stat_statements view - Query this view to get statistics like query, calls, total_time, and more.

sql

CREATE EXTENSION pg_stat_statements;

-- Query example:
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Example

This example shows how to enable pg_stat_statements, restart PostgreSQL, and query the statistics for the top 5 slowest queries.

sql

-- Step 1: Edit postgresql.conf to add:
shared_preload_libraries = 'pg_stat_statements'

-- Step 2: Restart PostgreSQL server

-- Step 3: Enable extension in your database
CREATE EXTENSION pg_stat_statements;

-- Step 4: Query the statistics
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Output

query                                         | calls | total_time | rows
----------------------------------------------+-------+------------+------
SELECT * FROM orders WHERE customer_id = $1;  |  1500 | 12345.67   | 1500
UPDATE products SET stock = stock - 1 WHERE id = $1; |  800 | 6789.12    | 800
INSERT INTO logs(event, created_at) VALUES ($1, now()); |  3000 | 4567.89    | 3000
SELECT * FROM customers WHERE email = $1;     |  1200 | 3456.78    | 1200
DELETE FROM sessions WHERE expires < now();   |  500  | 2345.67    | 500

Common Pitfalls

Common mistakes when using pg_stat_statements include:

• Not setting shared_preload_libraries before starting PostgreSQL, which prevents the module from loading.
• Forgetting to restart the PostgreSQL server after changing postgresql.conf.
• Not creating the extension in the database with CREATE EXTENSION pg_stat_statements;.
• Expecting real-time stats immediately; stats accumulate over time as queries run.

sql

/* Wrong: Trying to create extension without preload */
CREATE EXTENSION pg_stat_statements;

/* Right: Set preload, restart server, then create extension */
-- postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
-- Restart server
CREATE EXTENSION pg_stat_statements;

Quick Reference

Command/Setting	Description
shared_preload_libraries = 'pg_stat_statements'	Load the pg_stat_statements module at server start
CREATE EXTENSION pg_stat_statements;	Enable the extension in the current database
SELECT * FROM pg_stat_statements;	View aggregated query statistics
SELECT pg_stat_statements_reset();	Reset all collected statistics
ORDER BY total_time DESC LIMIT 5;	Get top 5 slowest queries

Key Takeaways

Enable pg_stat_statements by setting shared_preload_libraries and restarting PostgreSQL.

Create the extension in your database with CREATE EXTENSION pg_stat_statements;.

Query the pg_stat_statements view to analyze query performance statistics.

Remember to restart the server after changing configuration for the extension to load.

Use pg_stat_statements_reset() to clear statistics when needed.