$ show ankane/pghero
PgHero is a Ruby gem packaged as a mountable Rails engine (and a standalone Docker image) that provides a web-based performance dashboard for PostgreSQL. In production it connects to one or more databases through dynamically created ActiveRecord subclasses — one connection pool per database — and queries PostgreSQL system catalogs and statistics views (pg_stat_statements, pg_stat_activity, pg_stat_user_indexes, pg_statio_user_tables, pg_stat_progress_vacuum, pg_stat_replication, pg_sequences, and more) to render health checks, space usage and growth, live queries, historical and current query stats, safe EXPLAIN plans, connection breakdowns, maintenance/vacuum state, and server settings. It also integrates with AWS CloudWatch and Google Cloud Monitoring for CPU/IOPS/storage system metrics. Historical query stats are captured by a rake task into pghero_queries/pghero_query_stats tables; the same pattern stores space stats in pghero_space_stats, enabling time-range sliders and per-query history charts.
The LLM identified 4 files worth highlighting for reuse:
A self-contained safe EXPLAIN wrapper. It runs EXPLAIN inside a rolled-back transaction with statement timeout, builds options additively, validates the output format against a strict allowlist to prevent injection, and refuses multi-statement or COMMIT-bearing input using a clever capability probe (`SELECT 1; SELECT 1` must fail). It also opts-out sensitive options (ANALYZE, BUFFERS, WAL) unless explicitly requested. Cleanly extractable as a generic 'safe query introspection' utility for any Postgres tool.
A genuine index recommendation engine. It parses SQL with pg_query, extracts single-table WHERE/ORDER BY structures, ranks candidate columns by distinct-value and null-fraction statistics from pg_stats, simulates progressive row-count reduction to decide where to stop adding columns, special-cases LIKE/ILIKE with GiST trigram indexes, and checks for existing covering indexes before recommending. The heuristic planner (best_index_helper, row_estimates, parse_where/parse_sort) is self-contained and could back a standalone index-advisor CLI or service with minimal adaptation.
Implements a clean multi-database connection pattern: each database config (URL string or Rails spec name) gets a dynamically generated subclass of a shared abstract ActiveRecord class, guaranteeing exactly one connection pool per database without global state. Connection creation is lazy, adapter type is validated up-front with a readable error, and a 5-second connect timeout is injected. This pattern is directly reusable in any Rails app or gem that must talk to several databases concurrently.
Contains a battle-tested btree index bloat estimation query (derived from the well-known jberkus/mbanck algorithm) that computes wasted bytes per index from pg_stats width/null-fraction data, plus duplicate-index detection that compares column lists, index expressions, and predicates. The bloat SQL is parameterized and self-contained, and is worth lifting verbatim into any Postgres ops or capacity-planning tool.