Today I’m announcing the beta for Perfalytics. Perfalytics is a tool I’ve been working on designed to make it easy to analyze query performance for Postgres. As the lead of the Database team at Heap, where I optimize the performance of a Postgres cluster with more than 1PB of data, I’ve done my fair share of Postgres performance analysis. I believe the standard way of debugging a slow Postgres query is tedious and cumbersome.
Debugging a slow Postgres query usually goes something like this. Given the slow query, you run EXPLAIN ANALYZE on it. This will rerun the query with performance instrumentation. This gives you the query plan of the query and how much time was spent processing each part of the query plan.
If you’re lucky, the slow query is reproducible and you will see why the query was slow. In my experience, this is frequently not the case. All too often, by the time you run the query with EXPLAIN ANALYZE, something has changed and the query is now fast. This can happen for a number of reasons including caching, the makeup of the table changing, decreased server load, and many other potential factors.
The idea behind Perfalytics is to turn this way of doing things on it’s head. Instead of running EXPLAIN ANALYZE after the fact to try to reproduce the slow query, why not gather the exact same information at query time? That way if the query is slow, you can see exactly why that particular run of the query was slow.
That’s right. Perfalytics records the query plan and various query statistics for every single query at query time. This means you have all the information you need to debug why a particular query was slow. There’s no need to try to reproduce the problem after the fact.
In addition to making it easier to debug slow queries, collecting this information has a number of other advantages. Because you are collecting this information for every single query, you can look at overall trends. For example, you can, for each slow query, programmatically classify why exactly that query was slow. This lets you see how often different kinds of slow queries come up and tells you exactly where you should focus your efforts.
Once you try using something like Perfalytics, you’ll never want to go back to the old way of doing things.
If you want to try out Perfalytics, sign up for an beta invite.
If you have any questions, feel free to contact me at firstname.lastname@example.org.