Introducing pg:diagnose, a new tool for finding and fixing performance issues with your Heroku Postgres database. The heroku pg:diagnose CLI command unlocks the wealth of built-in information that PostgreSQL stores about its own health and performance, presenting it in simple report that makes identifying and correcting common database problems effortless.
At Heroku, we not only run dozens of internal Postgres systems but also have the privilege of running the Postgres systems of many, many customer databases. In doing this, we've encountered and fixed every problem imaginable – and many that were previously unimaginable. Because of that, we have built up a tremendous amount of experience, knowledge, and intuition around how Postgres behaves.
Today we're happy to announce pg:diagnose. This new tool runs several diagnostic checks on your database and generates a report showing potential problem spots.
Let’s take a look at a sample pg:diagnose report:
First off, that 9 day query is potentially causing severe problems because it prevents database garbage collection, and should be killed with heroku pg:kill 30597.
Next, a 90% hit rate means that 10% of my queries aren’t cached in memory, which means 10% of my queries are going to be much slower than they need to be. I’d probably make a follower of the next higher plan to get more ram, and then do a fast changeover.
And finally, that index is taking up a lot of space, and I’m never using it, so I’ll consider dropping it with a migration that runs DROP INDEX logs_created_at; Everything else is green, so no need to to worry about the rest today.
There's no perfect rule of thumb for how many issues is an acceptable level for your database. While the three issues in this example may be on a lighter side, each issue surfaced with pg:diagnose merits further exploration to ensure your database is performance smoothly.
A few minutes with pg:diagnose and your database can be humming smoothly, and you can be saving several dollars to get the performance you need. pg:diagnose is available today in Heroku Toolbelt version 3.9.0, and I hope it helps you the next time you need to take a closer look at your database.