Postgres Insights with PgHero

What is PgHero?

PgHero is a gem written by Andrew Kane for getting quick insights into how your Postgres database is performing and where there is room for improvement. It provides a quick overall status of your database, a look at the queries that are running, table indexes, table space, and can give you a way to get explanations of queries without having to jump down to the psql console.

What Does it Solve?

The information it shows is all stored in the pg_system tables, which can be a pain to navigate and doesn't provide an easily accessible view of the most important data. This tool does... it only does a few things, but it does them well. It is mainly useful for seeing where you can improve performance by installing an extra index, and can point out slow running queries.

Installation

It is very easy to install to Rails. You simply add the gem to your Gemfile.

gem 'pghero'

After that, you'll need to mount this (it is a Rails Engine) in your routes.rb file.

mount PgHero::Engine, at: "pghero"

If you're using this on production servers you will most definitely want to add some security which can be done either by Basic Authentication, or if you are using Devise you can mount your engine like so:

authenticate :user, lambda {|user| user.admin? } do
  mount PgHero::Engine, at: "pghero"
end

Ready For Use

Now you can simply access it by visiting the spot where you mounted it. One thing I ran into, and which clearly is discussed in the README is that you might run into some errors if pg_stat_statements isn't enabled. There are instructions in the GitHub repository on how to solve this quite common issue.