I want to better understand how to debug and fix performance issues in PostgreSQL.

I have a query that usually costs 1 second to run. However, for some reason, it started taking 30 seconds to run for a period of time! After some minutes and some explain analyze invocations, it went back to normal. I didn't do anything to fix it, nor did I understand why it got better all of a sudden.

How do I debug a problem like this? What kind of logging and instrumentation should I have in place to understand what caused this and how to fix it?

  • 2
    System busy overall? Table(s) undergoing a large update? Large analytic type query being run? The Gods not appreciating your invocations? :-) On a more serious note, this is the reason you should have monitoring software installed before problems arise! Do you have anything like sysstat or nagios or similar?
    – Vérace
    Apr 19, 2018 at 3:14
  • No. Just some simple logs in pg. Was considering using the ELK stack to get all kinds of verbose logs
    – ivarec
    Apr 19, 2018 at 3:15
  • 1
    Have you considered the excellent (and Open Source) sysstat tools?
    – Vérace
    Apr 19, 2018 at 3:34
  • Go this link. It may help you..! dbrnd.com/2016/02/… Apr 19, 2018 at 5:41

1 Answer 1


It is a very broad question, even more so as you give absolutely no details about your setup and kind of database (volume, type of queries, active connections, size of RAM, dedicated server or not, etc.)

You can start by enabling PostgreSQL to log slow queries, see the log_min_duration_statement in the configuration. That will give you historical data that you would then be able to analyze and maybe correlate with other things (like from the list of @Vérace)

You will then have various tools to help, as described on https://wiki.postgresql.org/wiki/Logging_Difficult_Queries :

  • pgFouine
  • PQA
  • EPQA
  • pgsi

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.