Enabling pg_stat_statements for PostgreSQL Database System Tuning and Analysis

To facilitate PostgreSQL database system performance tuning and analysis, we ought to collect query statistics, such as, the amount of time that a query takes. For this, we should enable pg_stat_statements extension. The following shows the steps on a Fedora Linux system:

1. Install the postgresql-contrib package. In the system shell, run command
   
   

   sudo yum install postgresql-contrib

   
   
2. Create pg_stat_statements extension for the database whose query performance we wish to analyze, e.g., we wish to tune performance for database template1,
   
   

   echo "CREATE EXTENSION pg_stat_statements;" | psql -U postgres template1

   
   
3. Enable query statistics collection by editing $PGDATA/postgresql.conf where PGDATA is where the database is, e.g.,
   

   
   # Loading the extension
   shared_preload_libraries = 'pg_stat_statements'
   
   # Increase the max size of the query strings that the database system records
   track_activity_query_size = 2048
   
   # Track all statements including those generated by stored procedures
   pg_stat_statements.track = all
   
   # Log queries that run more than 1000 milliseconds. 
   log_min_duration_statement = 1000
   
   

4. Restart the database server, i.e.,
   

   
   sudo systemctl restart postgresql