Rails PG Extras

Rails port of Heroku PG Extras with several additions and improvements. The goal of this project is to provide powerful insights into the PostgreSQL database for Ruby on Rails apps that are not using the Heroku PostgreSQL plugin.

Included rake tasks and Ruby methods can be used to obtain information about a Postgres instance, that may be useful when analyzing performance issues. This includes information about locks, index usage, buffer cache hit ratios and vacuum statistics. Ruby API enables developers to easily integrate the tool into e.g. automatic monitoring tasks.

You can read this blog post for detailed step by step tutorial on how to optimize PostgreSQL using PG Extras library.

Shameless plug: rails-pg-extras is just one of the tools that I use when conducting Rails performance audits. Check out my offer if you need help with optimizing your application.

Optionally you can enable a visual interface:

rails-pg-extras-mcp gem provides an MCP (Model Context Protocol) interface enabling PostgreSQL metadata and performance analysis with an LLM support.

Alternative versions:

• Core dependency - Ruby

• Rust

• NodeJS

• Elixir

• Python Flask

• Haskell

Installation

In your Gemfile

gem "rails-pg-extras"

calls and outliers queries require pg_stat_statements extension.

You can check if it is enabled in your database by running:

RailsPgExtras.extensions

You should see the similar line in the output:

| pg_stat_statements  | 1.7  | 1.7 | track execution statistics of all SQL statements executed |

ssl_used requires sslinfo extension, and buffercache_usage/buffercache_usage queries need pg_buffercache. You can enable them all by running:

RailsPgExtras.add_extensions

By default rails-pg-extras uses your app’s default ActiveRecord::Base.connection (typically primary) for running metadata queries, rake tasks and the web UI.

If your app uses Rails multiple databases, the web UI can switch connections dynamically. It reads the available database names from ActiveRecord::Base.configurations for the current environment and selects one via the db_key query param (thread-local per request, so it’s safe under concurrency):

# examples
/pg_extras?db_key=primary
/pg_extras?db_key=animals

To connect to a database that isn’t defined in database.yml (or when using rake tasks / Ruby API outside the web UI), you can also provide an explicit URL via ENV['RAILS_PG_EXTRAS_DATABASE_CONFIG']:

ENV["RAILS_PG_EXTRAS_DATABASE_CONFIG"] = "postgresql://postgres:secret@localhost:5432/database_name"

Alternatively, you can specify database configuration with a method call:

RailsPgExtras.database_config = "postgresql://postgres:secret@localhost:5432/database_name"
RailsPgExtras.database_config = :rails_pg_extras

Usage

Each command can be used as a rake task, or a directly from the Ruby code.

rake pg_extras:cache_hit

RailsPgExtras.cache_hit

+----------------+------------------------+
|        Index and table hit rate         |
+----------------+------------------------+
| name           | ratio                  |
+----------------+------------------------+
| index hit rate | 0.97796610169491525424 |
| table hit rate | 0.96724294813466787989 |
+----------------+------------------------+

By default the ASCII table is displayed, to change to format you need to specify the in_format parameter ([:display_table, :hash, :array, :raw] options are available):

RailsPgExtras.cache_hit(in_format: :hash) =>

 [{"name"=>"index hit rate", "ratio"=>"0.97796610169491525424"}, {"name"=>"table hit rate", "ratio"=>"0.96724294813466787989"}]

RailsPgExtras.cache_hit(in_format: :array) =>

 [["index hit rate", "0.97796610169491525424"], ["table hit rate", "0.96724294813466787989"]]

RailsPgExtras.cache_hit(in_format: :raw) =>

 #<PG::Result:0x00007f75777f7328 status=PGRES_TUPLES_OK ntuples=2 nfields=2 cmd_tuples=2>

Some methods accept an optional args param allowing you to customize queries:

RailsPgExtras.long_running_queries(args: { threshold: "200 milliseconds" })

By default, queries target the public schema of the database. You can specify a different schema by passing the schema argument:

RailsPgExtras.table_cache_hit(args: { schema: "my_schema" })

You can customize the default public schema by setting ENV['PG_EXTRAS_SCHEMA'] value.

Diagnose report

The simplest way to start using pg-extras is to execute a diagnose method. It runs a set of checks and prints out a report highlighting areas that may require additional investigation:

RailsPgExtras.diagnose

$ rake pg_extras:diagnose

Keep reading to learn about methods that diagnose uses under the hood.

Visual interface

You can enable UI using a Rails engine by adding the following code in config/routes.rb:

  mount RailsPgExtras::Web::Engine, at: 'pg_extras'

You can enable HTTP basic auth by specifying Rails.application.credentials.pg_extras.user (or RAILS_PG_EXTRAS_USER) and Rails.application.credentials.pg_extras.password (or RAILS_PG_EXTRAS_PASSWORD) values. Authentication is mandatory unless you specify RAILS_PG_EXTRAS_PUBLIC_DASHBOARD=true or set RailsPgExtras.configuration.public_dashboard = true.

You can configure available web actions in config/initializers/rails_pg_extras.rb:

RailsPgExtras.configure do |config|
  # Rails-pg-extras does not enable all the web actions by default. You can check all available actions via `RailsPgExtras::Web::ACTIONS`.
  # For example, you may want to enable the dangerous `kill_all` action.

  config.enabled_web_actions = %i[kill_all pg_stat_statements_reset add_extensions]
end

You can also configure default ignore lists for the missing foreign key checkers. This helps skip columns that you know should not be considered foreign keys (constraints) or you intentionally do not want to index (indexes).

RailsPgExtras.configure do |config|
  # Accepts an Array or a comma-separated String of entries like:
  # - "posts.category_id" (ignore a specific table+column)
  # - "category_id"       (ignore this column name for all tables)
  # - "posts.*"           (ignore all columns on a table)
  # - "*"                 (ignore everything)
  config.missing_fk_constraints_ignore_list = ["posts.category_id", "category_id"]
  config.missing_fk_indexes_ignore_list = ["feedbacks.team_id", "legacy_id"]

  # Or as a comma-separated string:
  # config.missing_fk_constraints_ignore_list = "posts.category_id, category_id"
  # config.missing_fk_indexes_ignore_list = "feedbacks.team_id, legacy_id"
end

Available methods

measure_queries

This method displays query types executed when running a provided Ruby snippet, with their avg., min., max., and total duration in miliseconds. It also outputs info about the snippet execution duration and the portion spent running SQL queries (total_duration/sql_duration). It can help debug N+1 issues and review the impact of configuring eager loading:

RailsPgExtras.measure_queries { User.limit(10).map(&:team) }

{:count=>11,
 :queries=>
  {"SELECT \"users\".* FROM \"users\" LIMIT $1"=>
    {:count=>1,
     :total_duration=>1.9,
     :min_duration=>1.9,
     :max_duration=>1.9,
     :avg_duration=>1.9},
   "SELECT \"teams\".* FROM \"teams\" WHERE \"teams\".\"id\" = $1 LIMIT $2"=>
    {:count=>10,
     :total_duration=>0.94,
     :min_duration=>0.62,
     :max_duration=>1.37,
     :avg_duration=>0.94}},
 :total_duration=>13.35,
 :sql_duration=>11.34}

RailsPgExtras.measure_queries { User.limit(10).includes(:team).map(&:team) }

{:count=>2,
 :queries=>
  {"SELECT \"users\".* FROM \"users\" LIMIT $1"=>
    {:count=>1,
     :total_duration=>3.43,
     :min_duration=>3.43,
     :max_duration=>3.43,
    :avg_duration=>3.43},
   "SELECT \"teams\".* FROM \"teams\" WHERE \"teams\".\"id\" IN ($1, $2, $3, $4, $5, $6, $7, $8)"=>
    {:count=>1,
     :total_duration=>2.59,
     :min_duration=>2.59,
     :max_duration=>2.59,
     :avg_duration=>2.59}},
 :total_duration=>9.75,
 :sql_duration=>6.02}

Optionally, by including Marginalia gem and configuring it to display query backtraces:

config/development.rb

Marginalia::Comment.components = [:line]

you can add this info to the output:

missing_fk_indexes

This method lists actual foreign key columns (based on existing foreign key constraints) which don't have a supporting index. It's recommended to always index foreign key columns because they are commonly used for lookups and join conditions.

You can add indexes on the columns returned by this query and later c