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_statementswhich 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?
visualize_urlto 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!