Rails PgHero: Postgres Health Dashboard for Slow Queries, Missing Indexes, and Space Monitoring

Last quarter I did a database audit for a Series B SaaS running on Rails 7.2 and Postgres 15. Their engineering team was convinced they had a scaling problem. What they had was five queries eating 62% of the database CPU, three tables with 8 GB of dead tuples, and eleven unused indexes wasting 14 GB of disk. Total time to find all of that: about twelve minutes with Rails PgHero mounted at /pghero.

They did not need a bigger database. They needed a doctor. Rails PgHero is that doctor, and it is a hundred lines of Ruby, a mountable engine, and a stethoscope for your Postgres cluster.

After nineteen years of Rails and probably four hundred database audits, PgHero is still the first thing I install on a client system when I need to understand where the pain is coming from. Not Datadog, not New Relic, not pganalyze. Just PgHero. It answers the questions I actually have in the first thirty minutes: which queries hurt, which indexes are missing, which indexes are lying around unused, and which tables are quietly rotting.

What Rails PgHero Actually Does

Rails PgHero is a mountable Rails engine that queries Postgres system views (pg_stat_statements, pg_stat_user_tables, pg_stat_user_indexes, pg_stat_activity) and renders the results as a dashboard. That is it. No agents, no external service, no data leaves your infrastructure.

The dashboard surfaces eight things that matter:

• Slow queries — the top offenders by total time, ranked by total_time / calls.
• Long-running queries — anything active longer than a threshold (default 60s).
• Missing indexes — sequential scans on large tables that would benefit from an index.
• Unused indexes — indexes Postgres has never touched in the tracked window.
• Invalid indexes — failed CREATE INDEX CONCURRENTLY operations you forgot about.
• Table space & bloat — bloated tables that need VACUUM FULL or pg_repack.
• Duplicate indexes — two indexes covering the same columns.
• Live connections — who is connected, from where, running what.

It is not a full APM. It will not draw you a flame graph or track a query through service boundaries. It is the ten-cent tool that answers the questions that come up during every production incident I have ever run.

Installing Rails PgHero

The install is boring in the good way. Add the gem and mount the engine.

# Gemfile
gem "pghero"

# config/routes.rb
Rails.application.routes.draw do
  authenticate :user, ->(u) { u.admin? } do
    mount PgHero::Engine, at: "pghero"
  end
end

That authenticate block is not optional. PgHero exposes real production query text, connection info, and role names. If your app has admin users, wrap it. If not, wrap it in HTTP basic auth via Rack::Auth::Basic, or better, put it behind a Tailscale-only route on your infra.

Now enable pg_stat_statements. This is the Postgres extension that lets PgHero see per-query timing. Without it you get connections and space; with it you get the slow-query list that will actually change your afternoon.

-- as a superuser, once per cluster
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

On managed Postgres (RDS, Aurora, Google Cloud SQL, Supabase, Neon), you also need pg_stat_statements in shared_preload_libraries. On RDS that is a parameter-group change and a reboot. On Google Cloud SQL it is a flag. Do it before you go looking for slow queries or PgHero will smile blankly at you.

The Slow Queries Tab: The One That Pays for Itself

Open /pghero in production and go straight to “Query Stats.” You will see something like:

Rank | Total Time | Calls   | Avg Time  | Query
1    | 4:12:33    | 892,451 | 16.9 ms   | SELECT * FROM orders WHERE user_id = $1
2    | 2:41:17    | 41      | 3928 s    | SELECT COUNT(*) FROM events WHERE created_at > $1
3    | 1:58:04    | 12,441  | 571 ms    | SELECT ... FROM users LEFT JOIN ... ORDER BY updated_at

Three things to look for in that list, in order:

1. The top query by total time. This is the one costing you the most CPU across a day. It might be fast per call (like #1 above, 17 ms) but running so often that fixing it — usually with a better index or a counter cache — buys you room to breathe.
2. The query with the massive avg_time. #2 above is running for over an hour per call. That is a report someone runs from a slow admin page, and it is holding a snapshot open the whole time. Rewrite it with a materialized view. See Rails materialized views with Scenic if this is a common pattern for you.
3. Any query that shows up you did not write. Sometimes it is a gem instrumenting itself. Sometimes it is a rogue admin dashboard. Sometimes it is Devise doing a full-table pg_stat_statements check on every request because someone misconfigured the extension.

The truncated query text can be extended by setting pg_stat_statements.max and pg_stat_statements.track_utility in your Postgres config. PgHero will show you the normalized form ($1, $2) — the actual values are not tracked, which is deliberate. If you need parameterized query examples, that is what a real APM like Sentry Performance or auto_explain gives you.

Turning a Slow Query Finding into a Fix

PgHero shows you the query. Turning it into a fix is where a Rails developer earns their money. My cheat sheet:

• WHERE column = $1 on a large table with no index. Add the index. EXPLAIN will confirm.
• ORDER BY column DESC LIMIT n with a filter. You want a composite index covering the WHERE columns first, then the ORDER BY column.
• COUNT(*) with WHERE created_at > $1. Approximate it with pg_class.reltuples for a full-table estimate, or add a partial index. Or use a counter cache — see Rails counter cache to eliminate N+1 count queries.
• SELECT * FROM ... LIMIT 25 OFFSET 10000. Switch to keyset pagination. Ancient at this point but still surprisingly common — see Rails cursor pagination vs offset.

Missing Indexes: The Fastest Wins

The “Space” and “Missing Indexes” tabs are the equivalent of a doctor spotting a broken bone on an x-ray. PgHero flags any table where Postgres has been doing sequential scans on more than 10,000 rows and no index is helping.

# config/initializers/pghero.rb
PgHero.long_running_query_sec = 60
PgHero.slow_query_ms = 20      # anything averaging > 20ms is "slow"
PgHero.slow_query_calls = 100  # ...only if it runs at least 100 times in the window

I set slow_query_ms low (20ms) on any app that runs at real traffic. The default (100ms) hides queries that only look fast because they run against a warm buffer cache. Twenty milliseconds is roughly the point where an index would win.

For missing indexes, PgHero can suggest what to add:

PgHero.suggested_indexes
# => [
#      { table: "orders", columns: ["user_id"], using: "btree" },
#      { table: "events", columns: ["team_id", "created_at"], using: "btree" }
#    ]

These are suggestions, not commands. Always add them with CREATE INDEX CONCURRENTLY in production and use add_index :orders, :user_id, algorithm: :concurrently in migrations. Even then, run it behind Strong Migrations so the migration is blocked if you forget.

Unused Indexes: The Ones Nobody Talks About

Missing indexes get attention. Unused indexes are the ones that quietly cost you. Every index adds write amplification on INSERT, UPDATE, and DELETE. On a hot write table with fifteen indexes, that is fifteen B-tree updates per row change. If eleven of them are never read, you are burning CPU and disk for nothing.

PgHero flags them:

Index                          | Size    | Scans
orders_shipped_at_idx          | 2.1 GB  | 0
orders_promo_code_idx          | 1.4 GB  | 0
users_signup_source_idx        | 890 MB  | 0

Zero scans since the last stats reset means Postgres has never used this index to answer a query. Sometimes that is because the query planner picks a different plan (unlikely if the index is well-shaped). More often, it is a leftover from a feature nobody uses anymore, or from a query that got rewritten and never had its index removed.

Do not drop them the same afternoon PgHero flags them. Stats resets happen — after a major version upgrade, after a pg_stat_statements_reset(), after a failover. Watch a “zero scans” flag for a week or two before you drop the index. Then drop it with DROP INDEX CONCURRENTLY.

Long-Running Queries: The 3 AM Page

The “Long Running Queries” tab shows anything active for more than the threshold you set (60 seconds by default). This is where you catch the thing that will page your on-call at 3 AM if you do not intervene.

PgHero.long_running_query_sec = 60

The two flavors I see constantly:

• Idle in transaction — a background job opened a transaction, did some work, and then made an HTTP call that is still pending. The transaction is holding row locks. Everything waiting on those rows is queuing up. See Rails pessimistic locking with SELECT FOR UPDATE for how these get created.
• A slow report — someone ran an admin export. It is now holding a snapshot open, and autovacuum cannot reclaim dead tuples on any table it touched. Table bloat starts climbing.

Kill them from PgHero directly. There is a “Kill” button next to each row. It calls pg_terminate_backend(pid). Do it during the incident, then figure out how they got created afterward. Add a statement timeout to prevent recurrence:

# config/database.yml
production:
  variables:
    statement_timeout: 30000   # 30 seconds for web

For long-running background jobs, set the timeout per-connection instead of globally.

Table Space and Bloat

The “Space” tab shows table and index sizes ranked by disk usage. It is the tab I open first when a client says “the database is filling up faster than we expected.” The story is almost always one of these:

• A log_entries or webhook_events table with no retention policy. Add a deleted_at column, run a nightly DELETE ... WHERE created_at < 90.days.ago, and consider Postgres partitioning with pg_partman if it is more than 100 GB.
• A JSONB column storing the full API response for audit purposes. Move it to S3 with a foreign key to the object, or split it into a logs table you can prune.
• Bloat from high-churn tables. PgHero shows an estimate — if a table has 10 GB allocated but 3 GB of live rows, that is 70% bloat. Autovacuum should reclaim it, but on very high-churn tables it can fall behind. pg_repack is the operational fix. See the bloat metric in PgHero’s Space tab.

Query Stats History with Multiple Databases

PgHero can persist query stats to its own table so you can see trends over time, not just the current pg_stat_statements snapshot.

# config/initializers/pghero.rb
PgHero.databases = {
  primary: { url: ENV["DATABASE_URL"] },
  analytics: { url: ENV["ANALYTICS_DATABASE_URL"] }
}

# db/migrate/xxx_create_pghero_query_stats.rb
class CreatePgheroQueryStats < ActiveRecord::Migration[7.1]
  def change
    create_table :pghero_query_stats do |t|
      t.text :database
      t.text :user
      t.text :query
      t.integer :query_hash, limit: 8
      t.float :total_time
      t.integer :calls
      t.timestamp :captured_at
    end
    add_index :pghero_query_stats, [:database, :captured_at]
  end
end

Then set up a recurring job — Solid Queue recurring jobs is perfect for this — to capture stats every five minutes:

# app/jobs/pghero_query_stats_job.rb
class PgheroQueryStatsJob < ApplicationJob
  queue_as :low

  def perform
    PgHero.capture_query_stats
    PgHero.clean_query_stats(before: 14.days.ago)
  end
end

# config/recurring.yml
production:
  pghero_query_stats:
    class: PgheroQueryStatsJob
    schedule: every 5 minutes

Now the Query Stats tab has a time-range picker. You can compare “what was slow at 2 PM Tuesday” to “what is slow now,” which is invaluable for catching regressions from a deploy.

Space Stats History and Alerts

The same treatment works for table space over time:

PgHero.capture_space_stats

And PgHero has built-in alerting hooks. Wire it to your notifier:

# config/initializers/pghero.rb
PgHero.methods_added_to_notifier = true

# app/notifiers/pghero_notifier.rb
class PgheroNotifier
  def self.slow_query(query, options)
    return unless options[:total_time] > 3600  # only alert > 1h total time
    SlackNotifier.ping("Slow query in #{options[:database]}: #{query[0..200]}")
  end
end

I usually do not turn this on until an app has run PgHero for a few weeks without alerts. Otherwise you get 200 “slow query” Slack messages the first hour and everyone learns to mute the channel.

Production Security Checklist

PgHero shows real production data. Treat it like Rails console access.

• Authenticate the mount. Devise authenticate :user, ->(u) { u.admin? }, HTTP basic, or an infra-level firewall. Never leave /pghero open.
• Use a read-only Postgres role for the PgHero connection. It only needs SELECT on pg_stat_statements, pg_stat_activity, pg_stat_user_tables, pg_stat_user_indexes, and the pg_terminate_backend function if you want the Kill button.
• Do not expose it to Cloudflare Access without SSO. The Kill button is real. A screenshotted URL from an intern’s laptop can end an outage.

The read-only role setup:

CREATE ROLE pghero LOGIN PASSWORD 'strong-password';
GRANT pg_monitor TO pghero;
GRANT EXECUTE ON FUNCTION pg_terminate_backend(integer) TO pghero;

Then in your pghero.rb initializer:

PgHero.databases = {
  primary: { url: ENV["PGHERO_DATABASE_URL"] }  # uses the pghero role
}

What PgHero Does Not Do

Two years ago I would have said “you need pganalyze too.” Now I say “you need pganalyze if you are past 500 GB of data or your engineering team owns their Postgres.” Below that, PgHero + the raw output of EXPLAIN (ANALYZE, BUFFERS) on the queries it flags is enough.

What PgHero misses:

• No query plans. For any query PgHero flags, run EXPLAIN (ANALYZE, BUFFERS) yourself in a psql console. Pipe the output through explain.dalibo.com to visualize it.
• No index depth or B-tree fragmentation. These matter at scale. pgstattuple extension gives you the raw numbers.
• No lock trees. For deadlock investigations you want pg_locks joined against pg_stat_activity, which PgHero does not surface. Datadog Database Monitoring or a custom script.
• No historical pg_stat_statements at the row level. PgHero aggregates it into buckets. If you need raw historical query events, pganalyze or a custom pipeline is the answer.

For 95% of Rails apps, none of that matters. Install PgHero, wire the query stats history job, put it behind admin auth, and check it every Monday morning. That single practice will do more for your database health than any APM.

Frequently Asked Questions

Do I need pg_stat_statements for Rails PgHero to be useful?

Not for everything. Without pg_stat_statements you still get connections, long-running queries, table space, unused indexes, and duplicate indexes — which is already valuable. But the Slow Queries tab is empty. On managed Postgres you enable it via a parameter group change and a reboot; on self-hosted, add pg_stat_statements to shared_preload_libraries in postgresql.conf and restart the server. Then CREATE EXTENSION pg_stat_statements in your database. Enable it before you install PgHero so the stats have some history when you first log in.

Rails PgHero vs pganalyze vs Datadog Database Monitoring — which one should I use?

They are not the same product. Rails PgHero is the free, self-hosted “first tool you install” that answers 90% of database health questions in one dashboard. pganalyze is a paid SaaS that adds query plan history, index recommendations with cost estimates, and vacuum tracking — worth it above 500 GB or if Postgres is your career. Datadog Database Monitoring is worth it if you already pay Datadog and want database traces correlated with APM spans across services. Below 500 GB and outside of enterprise Datadog contracts, start with PgHero. You will not outgrow it as fast as you think.

How do I secure the /pghero mount in production?

Never leave it open. Wrap it in a Devise authenticate block that checks for admin, or use Rack::Auth::Basic with a credential stored in Rails credentials. On top of that, restrict it at the network layer — put it behind a Tailscale-only route, Cloudflare Access with SSO, or a VPN. The Kill button on the Long Running Queries tab actually terminates connections, so treat access to /pghero with the same seriousness as Rails console access. Use a read-only Postgres role for the PgHero database connection, granted pg_monitor and EXECUTE on pg_terminate_backend only.

Can I run Rails PgHero on multiple databases like read replicas and analytics?

Yes. Configure PgHero.databases in config/initializers/pghero.rb with one entry per database URL. The dashboard shows a dropdown to switch between them. On read replicas, be aware that pg_stat_statements counters live on the primary — replicas will show a subset of stats. Use PgHero on the primary for query analysis and on replicas mainly for connection health and long-running query detection. Multiple databases also matter if you use Rails read replicas with automatic switching — you want to see both writer and reader load in one place.

Need help getting your Postgres out of the danger zone? TTB Software does database audits, index and query tuning, and production Postgres reviews for Rails teams. We have been shipping Rails on Postgres for nineteen years.