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_statementsas 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_statementsto monitor their performance”
- Alexander Kukushkin: “The
pg_stat_statementsextension 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_statementsand 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.