Enabling pg_stat_statements

The pg_stat_statements extension provides query performance statistics and is essential for identifying slow or frequently executed queries. To use this extension, you must first add it to the shared_preload_libraries setting.

⚙️ Step 1: Add pg_stat_statements to shared_preload_libraries

Depending on your PostgreSQL setup, you need to update the shared_preload_libraries configuration either:

• If you're using a standard PostgreSQL installation: Edit your postgresql.conf file and add:

shared_preload_libraries = 'pg_stat_statements'

• If you're using Patroni: Run the following command to update the cluster config:

patronictl edit-config

Then update or add the following section under postgresql.parameters:

postgresql:
  parameters:
    shared_preload_libraries: pg_stat_statements

💡 After editing the configuration, Patroni will apply the change automatically, but a restart is still required for this setting to take effect.

🔄 Step 2: Restart PostgreSQL

Restart the PostgreSQL service to apply the shared_preload_libraries change:

# For standalone PostgreSQL
sudo systemctl restart postgresql

# For Patroni-managed clusters
patronictl restart <cluster_name>

⚠️ Important Note about pg_stat_statements

You can run:

CREATE EXTENSION pg_stat_statements;

without first adding it to shared_preload_libraries, and the command will succeed. However:

● The extension will not function correctly until pg_stat_statements is added to shared_preload_libraries.

● It will not collect or display any query statistics.

● Queries like SELECT * FROM pg_stat_statements; will return empty results or raise a warning such as:

"pg_stat_statements must be loaded via shared_preload_libraries"

We're almost ready. Now we can add our database.

So let's jump on to the next step!