We just upgraded to 3.0 of PgHero (Check the CHANGELOG.md). I learned about the availability of the new version from the Postgres.fm 🎧 podcast.

PgHero is a tool we use to monitor operational issues with PostgreSQL. Some of the main things we monitor are:

  • Slow queries. PgHero uses pg_stat_statements which normalizes and collects statistics about all running queries. We can focus on queries that are called a lot and are slow, as they are consuming the most resources on the server. This approach is a macro analysis approach to load reduction.
  • Unused indexes. We can find unused indexes on tables with high writes and reads, and remove those to save space and improve performance.
  • Invalid indexes. These typically need to be re-created.
  • Constraints that are marked NOT VALID. In some cases, we do not ever intend to validate these constraints. I talked in a lot of detail about this topic with Jason on a recent “Code With Jason” Episode (160 - PostgreSQL with Andrew Atkinson) 🎤 – check it out!

In the episode we discussed Check constraints, but in our database typically we’re looking at foreign key constraints marked NOT VALID. I have some ideas for PgHero on how we can better present constraints in this state.

Some customizations we’ve made

We’re able to add customizations to PgHero on a branch and try them out on our database. For useful functionality, we can propose the changes back upstream as a PR.

  • Monitoring (estimated) index bloat percentage. Although this was rejected for upstream, we’ve found it useful to show the bloat on this page and as a percentage. (PR #393) We do rebuild indexes periodically automatically, so it isn’t as critical.
  • Monitoring database scheduled jobs (earlier closed draft #408). We schedule jobs like index rebuilds and partition management with pg_cron. Listing pg_cron Scheduled Jobs is not something that exists currently in PgHero, but is something I’ve added on our fork and find useful. When the code has appropriate tests and documentation, I plan to propose it upstream again.

Some additional things I want to add

  • Improved handling of partitioned tables

I have a theory there are inaccurate counts (e.g. unused indexes) in PgHero due to the indexes being counted multiple times across partitions for a partitioned table. I need to verify that theory on our fork and on partitioned tables, and if so I’ll work on a fix and propose it back.

What’s new in 3.0?

  • Added visualize_url to config. This looks like a way to link to an explain plan for a query. I’ll need to try this out.
  • Adjustments to AWS Metrics names. We are not currently using this functionality.
  • Removed support for older dependency versions for Ruby, Rails, pg_query, and aws-sdk.

It doesn’t look like there are any major new features. Primarily it looks like 3.0 was about ending support for older dependencies, which makes sense.

PgHero is a very useful tool for us - thank you to the maintainers!