How To Enable Slow Query Log in PostgreSQL

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.