PostgreSQL allows logging slow queries to a log file or table. Parsing these logs will help you easily determine which queries are slowing down your database. Here are the steps to enable slow query log in PostgreSQL. You can also use them to enable slow query log in RDS, Redshift and other PostgreSQL databases.
How To Enable Slow Query Log in PostgreSQL
You can identify slow queries in PostgreSQL by simply modifying a few settings in postgresql.conf file
1. Find location of postgresql.conf
Open terminal and run the following command to find the location of postgresql.conf file for your database.
$ find / -name postgresql.conf
/etc/postgresql/9.1/main/postgresql.conf
Bonus Read : PostgreSQL Materialized View
2. Open postgresql.conf
Run the following command to open postgresql.conf file in a text editor
$ vi /etc/postgresql/9.1/main/postgresql.conf
Bonus Read : How to Get Last Row Per Group in PostgreSQL
3. Enable slow query log in PostgreSQL
Search for the following line
#log_min_duration_statement = -1
Uncomment it by removing # at its beginning. Also replace -1 with a query runtime threshold in milliseconds. For example, if you want to log queries that take more than 1 second to run, replace -1 with 1000
log_min_duration_statement = 1000
Save and exit the file
Also look for the following line,
logging_collector = on
Make sure logging_collector is set to on.
Bonus Read : How to Create Histogram in PostgreSQL
4. Find slow query log
You will also find another variable in postgresql.conf
log_directory = 'pg_log'This indicates that PostgreSQL log file is located at /var/lib/pgsql/data/pg_log/
5. Restart PostgreSQL database
Run the following command to restart PostgreSQL
Ubuntu/Debian
$ sudo systemctl restart postgresql
CentOS/Redhat
# /etc/init.d/postgresql restart
Hopefully, the above article will help you log slow queries in PostgreSQL.
