Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Slow query log
Like most databases, Postgres has a built-in slow query log feature that automatically logs queries to the main Postgres log file if they take over a certain amount of time and it's really easy to set up.
In your main postgresql.conf file (which is often somewhere like /etc/postgresql/9.6/main/postgresql.conf), either edit or add a line like so:
log_min_duration_statement = 1000
After restarting Postgres or reloading the config with SELECT pg_reload_conf();, this directive causes any queries that take over 1000 milliseconds (one second) to be logged.
(The location of the log file varies but is /var/log/postgresql/postgresql-9.6-main.log on my test setup, for instance.)
To deliberately run a long query for testing purposes:
select pg_sleep(10) /* just testing */;
Note the presence of the comment within the query, that'll appear in the log too so that anyone else monitoring will be aware of what's going on.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment