PgHero — Performance Dashboard for PostgreSQL

Introduction

PgHero surfaces the performance data that PostgreSQL already collects but buries in system views. It turns pg_stat_statements, index usage stats, and connection metrics into a clean web dashboard that helps you find and fix database bottlenecks quickly.

What PgHero Does

• Ranks queries by total time, calls, and average duration from pg_stat_statements
• Identifies missing indexes by analyzing sequential scans on large tables
• Detects unused and duplicate indexes that waste disk and slow writes
• Monitors active connections, long-running queries, and replication lag
• Provides space usage breakdowns per table and index

Architecture Overview

PgHero is a Ruby application (usable as a standalone Docker image or a mountable Rails engine) that reads directly from PostgreSQL system catalogs and pg_stat_statements. It requires no additional agents or extensions beyond pg_stat_statements, which ships with PostgreSQL. The dashboard is read-only and adds negligible load.

Self-Hosting & Configuration

• Enable pg_stat_statements in postgresql.conf (shared_preload_libraries)
• Set DATABASE_URL as an environment variable pointing to your PostgreSQL instance
• Deploy via Docker, Linux packages, or embed as a Rails engine
• Supports monitoring multiple databases from a single dashboard
• Optional basic auth or integration with your application's authentication

Key Features

• Zero-agent setup — reads from built-in PostgreSQL stats views
• Query analysis with suggested indexes for the slowest queries
• Automatic detection of index bloat, unused indexes, and duplicates
• Connection and replication monitoring out of the box
• Supports PostgreSQL, Amazon RDS, and Aurora

Comparison with Similar Tools

• pgAdmin — general-purpose admin tool; PgHero focuses specifically on performance insights
• pg_stat_monitor (Percona) — enhanced stats extension; PgHero provides a web UI on top of standard pg_stat_statements
• Datadog/New Relic — commercial APM with deep Postgres integration; PgHero is free and self-hosted
• pganalyze — commercial SaaS for query optimization; PgHero covers core use cases at no cost

FAQ

Q: Does PgHero modify my database? A: No. It only reads from system views and pg_stat_statements. It never modifies data or schema.

Q: Can I monitor Amazon RDS instances? A: Yes. Enable pg_stat_statements via an RDS parameter group and point PgHero at the RDS endpoint.

Q: Does it support PostgreSQL on all versions? A: PgHero supports PostgreSQL 10 and newer.

Q: Can I use it without Ruby or Rails? A: Yes. The Docker image runs standalone with no Ruby knowledge required.

Sources

• https://github.com/ankane/pghero
• https://github.com/ankane/pghero/blob/master/guides/Docker.md