📊 PgInsights

PostgreSQL performance monitoring for Rails apps

PgInsights is a Rails engine that gives you a web dashboard for monitoring your PostgreSQL database performance. Think of it as a lightweight alternative to external monitoring tools that lives right inside your Rails app.

I got tired of switching between different tools to check database performance. Sometimes you just want to quickly see which indexes aren't being used, or find slow queries without setting up a whole monitoring infrastructure.

PgInsights gives you that - a simple dashboard you can access at /pg_insights in your Rails app.

• Runs health checks in background jobs if you have them set up
• Falls back to running directly if you don't
• Caches results so repeated visits are fast
• Configurable timeouts to prevent slow queries from hanging

Monitor your PostgreSQL database performance and identify potential issues at a glance.

Run custom queries and visualize results with built-in charting capabilities.

Track database configuration changes and performance trends over time.

View detailed information about individual database snapshots including parameters and metadata.

Monitor key performance indicators and database health metrics across time periods.

Compare database configurations and performance metrics between different time periods.

Detailed comparison view showing parameter changes and performance differences.

Add to your Gemfile:

gem 'pg_insights'

Run the installer:

bundle install
rails generate pg_insights:install
rails db:migrate

That's it. Visit /pg_insights in your browser.

The engine works out of the box, but you can customize it:

# config/initializers/pg_insights.rb
PgInsights.configure do |config|
  # === Background Jobs ===
  config.enable_background_jobs = true
  config.background_job_queue = :pg_insights_health
  
  # === Health Check Settings ===
  config.health_cache_expiry = 5.minutes
  config.health_check_timeout = 10.seconds
  
  # === Timeline & Snapshot Settings ===
  config.enable_snapshots = true                    # Enable timeline feature
  config.snapshot_frequency = 1.day                 # How often to collect snapshots
  config.snapshot_retention_days = 90               # How long to keep snapshots
  config.snapshot_collection_enabled = true         # Master switch for snapshot collection
end

Note: PgInsights uses on-demand background jobs, not automatic scheduling.

When health checks run:

• ✅ When you visit the health dashboard /pg_insights/health and cached data is older than health_cache_expiry (default: 5 minutes)
• ✅ When you click the "Refresh" button in the dashboard
• ✅ When you run rails pg_insights:health_check manually
• ❌ NOT automatically - PgInsights doesn't run background jobs on its own

How caching works:

Visit at 2:00 PM → Runs health checks, caches results for 5 minutes
Visit at 2:03 PM → Uses cached results (still fresh)  
Visit at 2:06 PM → Data is stale, triggers new background jobs

Background job setup (optional but recommended):

If your app has background jobs (Sidekiq, Resque, etc.), PgInsights will use them for better performance:

# Check if background jobs are working
rails pg_insights:status

Without background jobs: Health checks run synchronously when you visit the page (slower but works)
With background jobs: Health checks run asynchronously (faster, non-blocking)

Optional: Automatic recurring checks

If you want health checks to run automatically (not just on-demand), set up a scheduler:

# Using whenever (runs every hour)
every 1.hour do
  runner "PgInsights::RecurringHealthChecksJob.perform_later"
end

# Using sidekiq-cron
Sidekiq::Cron::Job.create(
  name: 'PgInsights Health Checks',
  cron: '0 * * * *',
  class: 'PgInsights::RecurringHealthChecksJob'
)

Note: Even with automatic scheduling, the jobs are smart - they only run expensive queries if the cached data is actually stale.

Navigate to /pg_insights in your app. The interface is pretty straightforward:

All queries are read-only (SELECT statements only) and have timeouts to prevent issues.

# Configuration & Status
rails pg_insights:status              # Check configuration and background job status
rails pg_insights:test_jobs           # Test background job functionality

# Health Checks
rails pg_insights:health_check        # Run health checks manually (synchronous)
rails pg_insights:stats              # Show usage statistics

# Timeline & Snapshots
rails pg_insights:collect_snapshot    # Collect a database snapshot immediately
rails pg_insights:start_snapshots    # Start recurring snapshot collection
rails pg_insights:snapshot_status    # Check snapshot configuration and status
rails pg_insights:cleanup_snapshots  # Clean up old snapshots

# Data Management
rails pg_insights:reset              # Reset all PgInsights data (queries + health checks)
rails pg_insights:clear_data         # Alias for reset (backward compatibility)
rails pg_insights:cleanup            # Clean up old health check results (30+ days)

# Development & Testing
rails pg_insights:seed_timeline      # Generate fake timeline data for testing
rails pg_insights:sample_data        # Generate sample health check data

• Only SELECT queries allowed
• Query timeouts prevent long-running queries
• Focuses on public schema by default
• No modification of your data

rails generate pg_insights:clean
# Remove gem from Gemfile

• Rails 6.1+
• PostgreSQL
• For slow query detection: pg_stat_statements extension (optional)

Found a bug or have an idea? Open an issue or send a pull request. The codebase is pretty straightforward.

Development setup:

git clone https://github.com/mezbahalam/pg_insights.git
cd pg_insights
bundle install
bundle exec rake spec

MIT License. See LICENSE file.

_{Built with ❤️ by Mezbah Alam
Inspired by pg_hero and other database-monitoring tools}