PostgreSQL and Instrumentation

I’m still learning PostgreSQL and one of the things I’ve been looking at a lot lately is instrumentation. What do I mean? Well, if you’re in SQL Server, think, Dynamic Management Views (DMV), Extended Events, Query Store, <hack, spit> Trace <spit>. How do we know how long a query took to run? PostgreSQL can tell you, but, and this is one of those wild, cool, but, honestly, slightly frustrating things about PostgreSQL, not natively.

Let’s talk about it.

Instrumentation

If you connect up to a PostgreSQL database, you actually do have the equivalent of DMVs. It’s called the Cumulative Statistics System. And yeah, it’s a bunch of views on a bunch of functions. It displays all sorts of data about IO, tables, indexes, etc.. It will even show you acive queries, their waits & run times. However, it doesn’t collect or display historical information on query behaviors. In fact, nothing native to core PostgreSQL does. Isn’t that odd?

Now, in very typical PostgreSQL fashion, this is readily, and easily, solved. Get an extension.

The very first one that’ll come up in a search is pg_stat_statements. Installing it is silly simple since it comes native with most PostgreSQL releases (at least all I’ve dealt with). From there, you get the kind of behaviors you’d expect. Min, max, mean, and best of all standard deviation, on query performance. All from an easily queried view:

SELECT
	pss.calls,
	pss.total_exec_time,
	pss.min_exec_time,
	pss.max_exec_time,
	pss.mean_exec_time,
	pss.stddev_exec_time
FROM
	pg_stat_statements AS pss
WHERE
	pss.queryid = 427240950862911343;

Cool. What else? Where’s the PostgreSQL version of Extended Events?

Funny enough, it doesn’t have that.

You can log duration (without query text) and slow queries (with query text) to the error log though. Which means, there is something in the code measuring performance.

You can also retrieve all sorts of measurements using EXPLAIN, but that requires you to sit and execute the query, not check on how it went in the past. Nope. For that you have to add the pg_stat_statements extension and enable it. There is instrumentation in PostgreSQL, but, it’s different.

Conclusion

If anyone wonders why I was so excited when I saw that Postgresql on Azure SQL had added a Query Store extension, well, this is why. However, I do like the behavior of pg_stat_statements. Seems like a solid tool once you get it going. I also like the number of knobs you have for tweaking it’s behavior.