PgHero Integration

Date: November 26, 2025
Category: Features / Database
Status: Active

Overview

PgHero is integrated as a PostgreSQL performance dashboard, accessible via the CRM at /pghero.

Since the app uses webpack (not Sprockets), PgHero's assets are copied to the public/ folder for static serving. A Rack::Rewrite rule strips locale prefixes from asset paths.

Configuration

Initializer

# config/initializers/pghero.rb
PgHero.config_path = Rails.root.join('config', 'pghero.yml')

YAML Configuration

# config/pghero.yml
development:
  query_stats:
    enabled: true
    slow_query_ms: 50
  index_suggestions:
    enabled: true
  explain:
    enabled: true
    analyze: true  # Safe in development

production:
  query_stats:
    enabled: true
  explain:
    enabled: false  # Never EXPLAIN ANALYZE in production

Routes

# config/routes/crm.rb
mount PgHero::Engine, at: '/pghero' if defined?(PgHero)

# config/routes.rb - Exclude from locale filter
filter :locale, exclude: %r{/(?:...|pghero|...)/}

Accessing PgHero

  1. Log in to CRM as admin
  2. Navigate to https://crm.warmlyyours.me:3000/pghero
  3. Dashboard shows database health overview

Features Available

Feature Description
Overview Health checks summary (connections, vacuum, slow queries)
Queries Slow query analysis from pg_stat_statements
Space Table sizes and bloat analysis
Connections Active database connections
Live Queries Currently running queries
Maintenance Vacuum and analyze status
Explain Query plan analysis (EXPLAIN ANALYZE)
Tune PostgreSQL configuration suggestions

Required PostgreSQL Extensions

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS hypopg;  -- For index recommendations

These are created automatically by the Docker init script:
docker/pg-init-scripts/05-pghero-extensions.sql

Asset Setup

PgHero's assets are committed to public/ for static serving. They only need to be regenerated when the PgHero gem version is updated:

./script/copy_pghero_assets.sh
git add public/stylesheets/pghero public/javascripts/pghero public/images/pghero
git commit -m "chore(pghero): update assets for pghero X.Y.Z"

Only run this when: PgHero gem version changes in Gemfile.lock

Files

File Purpose
config/initializers/pghero.rb PgHero configuration
config/pghero.yml Database-specific settings
config/routes/crm.rb Route mounting
config/routes.rb Locale exclusion for /pghero
config/initializers/260_redirections.rb Rewrite rule for asset paths
script/copy_pghero_assets.sh Asset extraction (run on gem update)
docker/pg-init-scripts/05-pghero-extensions.sql PostgreSQL extensions
public/stylesheets/pghero/ Static CSS (committed)
public/javascripts/pghero/ Static JS (committed)
public/images/pghero/ Static images (committed)

Troubleshooting

406 Not Acceptable errors

Ensure /pghero is in the locale exclusion pattern in config/routes.rb.

Missing query stats

Verify pg_stat_statements extension is enabled:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

No index suggestions

Verify hypopg extension is enabled:

SELECT * FROM pg_extension WHERE extname = 'hypopg';

Production Deployment

1. PostgreSQL Extensions

Enable pg_stat_statements on your production PostgreSQL server:

-- As superuser (postgres)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Add to postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- Grant access to application user
GRANT pg_read_all_stats TO your_app_user;

⚠️ Important: Enabling pg_stat_statements requires a PostgreSQL restart.

2. Assets (Already Committed)

PgHero assets are committed to the repository - no deployment step needed.

If you update the PgHero gem version, regenerate assets locally:

./script/copy_pghero_assets.sh
git add public/stylesheets/pghero public/javascripts/pghero public/images/pghero
git commit -m "chore(pghero): update assets for pghero X.Y.Z"

3. Configuration

The config/pghero.yml is already configured for production:

production:
  query_stats:
    enabled: true
    slow_query_ms: 50
  index_suggestions:
    enabled: true
  explain:
    enabled: false  # Never EXPLAIN ANALYZE in production!

4. Security

PgHero is protected by admin authentication:

  • Route: authenticate :account, ->(account) { account.is_admin? }
  • Only admins can access /pghero

5. Multiple Databases

For replica or read-only database analysis, add to config/pghero.yml:

production:
  databases:
    primary:
      url: <%= ENV['DATABASE_URL'] %>
    replica:
      url: <%= ENV['DATABASE_REPLICA_URL'] %>

See Also