It’s been over 2 years since PgHero 1.0 was released as a performance dashboard for Postgres. Since then, a number of new features have been added.

  • checks for serious issues like transaction ID wraparound and integer overflow
  • the ability to capture and view query stats over time
  • suggested indexes to give you a better idea of how to optimize queries (check out Dexter for automatic indexing)

PgHero 2.0 provides even more insight into your database performance with two additional features: query details and space stats.

Query Details

The new Query Details page helps solve this.

This page allows you to deep dive into an individual query. View charts of total time, average time, and calls over the past 24 hours to see how they’ve moved.

For those who annotate queries, you’ve likely realized the comment in PgHero only tells you one of the places a query is coming from since similar queries are grouped together. Now, you can get a better idea of all the places it’s called.

If you don’t annotate queries, you should!!

This page also lists tables in the query and their indexes so you can quickly see if an index is missing, and an “Explain” button is usually available to help you debug (but may be missing if PgHero hasn’t captured an unnormalized version of the query recently).

Space Stats

In addition, this page now reports unused indexes to help reclaim space. If you use read replicas, be sure to check that indexes aren’t used on any of them before dropping.

You can also view the growth for an individual table or index over the past 30 days.

Lastly, there’s syntax highlighting for all SQL for improved readability.

Much better :)

So what are you waiting for? Get the latest version of PgHero today.

Note: If you use PgHero outside the dashboard, there are some breaking changes from 1.x to be aware of.