Skip to content

PGSQL Phriday #008 – pg_stat_statements

Posted by Andreas 'ads' Scherbaum on

The topic for this month's PGSQL Phriday blogging challenge is: pg_stat_statement. And Michael Christofides gave me a perfect opener in his invitation.

For anyone who doesn't know, I'm running a weekly interview series with people from the PostgreSQL community. It's called "PostgreSQL Person of the Week". One of the questions in the default set I give everyone is:

What is your favorite PostgreSQL extension?

And guess what the answer is: by far everyone's favorite is pg_stat_statements!

What does this extension do? It tracks statistics for planning and execution for queries run by users. This can be used to find long-running queries, users who run too many or too heavy queries, or just generate statistics about the workload. In short: very useful data. And the extension itself does not need a lot of resources. Even better.

This extension is so popular that it has double the interview mentions than the next one (which is PostGIS - by itself also a very popular extension). From the slides I occasionally present at conferences or meetups:

PostgreSQL has a lot of extensions, head over to the PostgreSQL Extension network (PGXN) which is operated by David E. Wheeler to find out about around 360 extensions. Currently in my interviews I have 51 different extensions mentioned.

This extension is so useful that people say:

  • Julia Gugel: "I like pg_stat_statements as it helps a lot with performance troubleshooting."
  • Daniel Westermann: "pg_stat_statements, because it is just required if you want to troubleshoot performance related issues. I still wonder why it comes as an extension and is not there by default."
  • Lætitia Avrot: "Of course, I advise my customers to use pg_stat_statements to monitor their performance"
  • Alexander Kukushkin: "The pg_stat_statements extension is something that everyone must enable for performance monitoring and troubleshooting."
  • Flavio Gurgel: "I cannot live without pg_stat_statements, I think it’s mandatory for server optimisation."
  • Anthony Nowocien: "pg_stat_statements and I will be glad to see it in core."

This is just a small selection of quotes, but this shows that everyone loves pg_stat_statements. I encourage you to head over and read more interviews. There's plenty of insight from community members.