Skip to content

Rails.md

Latest commit

 

History

History
249 lines (172 loc) · 4.11 KB

Rails.md

File metadata and controls

249 lines (172 loc) · 4.11 KB

PgHero for Rails

💎

Installation

Add this line to your application’s Gemfile:

gem 'pghero'

And mount the dashboard in your config/routes.rb:

mount PgHero::Engine, at: "pghero"

Be sure to secure the dashboard in production.

Suggested Indexes

PgHero can suggest indexes to add. To enable, add to your Gemfile:

gem 'pg_query'

and make sure query stats are enabled. Read about how it works here.

Insights

PgHero.running_queries
PgHero.long_running_queries
PgHero.index_usage
PgHero.invalid_indexes
PgHero.missing_indexes
PgHero.unused_indexes
PgHero.unused_tables
PgHero.database_size
PgHero.relation_sizes
PgHero.index_hit_rate
PgHero.table_hit_rate
PgHero.total_connections
PgHero.locks

Kill queries

PgHero.kill(pid)
PgHero.kill_long_running_queries
PgHero.kill_all

Query stats

PgHero.query_stats_enabled?
PgHero.enable_query_stats
PgHero.disable_query_stats
PgHero.reset_query_stats
PgHero.query_stats
PgHero.slow_queries

Suggested indexes

PgHero.suggested_indexes
PgHero.best_index(query)

Security

PgHero.ssl_used?

Replication

PgHero.replica?
PgHero.replication_lag

Users

Note: It’s unsafe to pass user input to these commands.

Create a user

PgHero.create_user("link")
# {password: "zbTrNHk2tvMgNabFgCo0ws7T"}

This generates and returns a secure password. The user has full access to the public schema.

Read-only access

PgHero.create_user("epona", readonly: true)

Set the password

PgHero.create_user("zelda", password: "hyrule")

Grant access to only certain tables

PgHero.create_user("navi", tables: ["triforce"])

Drop a user

PgHero.drop_user("ganondorf")

Security

Basic Authentication

Set the following variables in your environment or an initializer.

ENV["PGHERO_USERNAME"] = "link"
ENV["PGHERO_PASSWORD"] = "hyrule"

Devise

authenticate :user, lambda { |user| user.admin? } do
  mount PgHero::Engine, at: "pghero"
end

Query Stats

Query stats can be enabled from the dashboard. If you run into issues, view the guide.

Historical Query Stats

To track query stats over time, run:

rails generate pghero:query_stats
rake db:migrate

And schedule the task below to run every 5 minutes.

rake pghero:capture_query_stats

Or with a scheduler like Clockwork, use:

PgHero.capture_query_stats

After this, a time range slider will appear on the Queries tab.

By default, query stats are stored in your app’s database. Change this with:

ENV["PGHERO_STATS_DATABASE_URL"]

System Stats

CPU usage is available for Amazon RDS. Add these lines to your application’s Gemfile:

gem 'aws-sdk'
gem 'chartkick'

And add these variables to your environment:

PGHERO_ACCESS_KEY_ID=accesskey123
PGHERO_SECRET_ACCESS_KEY=secret123
PGHERO_DB_INSTANCE_IDENTIFIER=epona

Multiple Databases

Create config/pghero.yml with:

default: &default
  databases:
    primary:
      url: <%= ENV["PGHERO_DATABASE_URL"] %>
    replica:
      url: <%= ENV["REPLICA_DATABASE_URL"] %>

development:
  <<: *default

production:
  <<: *default

Specify a database with:

PgHero.with(:replica) { PgHero.running_queries }

Customize

Minimum time for long running queries

PgHero.long_running_query_sec = 60 # default

Minimum average time for slow queries

PgHero.slow_query_ms = 20 # default

Minimum calls for slow queries

PgHero.slow_query_calls = 100 # default

Minimum connections for high connections warning

PgHero.total_connections_threshold = 100 # default

Bonus

  • See where queries come from with Marginalia - comments appear on the Live Queries tab.
  • Get weekly news and articles with Postgres Weekly
  • Optimize your configuration with PgTune and pgBench