rails-pg-extras

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) =>

 #

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).

…

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:

…

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 check if they are receiving scans using the unused_indexes method. Please remember that each index decreases write performance and autovacuuming overhead, so be careful when adding multiple indexes to often updated tables.

RailsPgExtras.missing_fk_indexes(args: { table_name: "users" })

+---------------------------------+
| Missing foreign key indexes     |
+-------------------+-------------+
| table             | column_name |
+-------------------+-------------+
| feedbacks         | team_id     |
| votes             | user_id     |
+-------------------+-------------+

table_name argument is optional, if omitted, the method will display missing fk indexes for all the tables.

You can also exclude known/intentional cases using ignore_list (array or comma-separated string), with 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)

RailsPgExtras.missing_fk_indexes(args: { table_name: "users", ignore_list: ["feedbacks.team_id", "posts.*"] })

missing_fk_constraints

This method shows columns that look like foreign keys but don't have a corresponding foreign key constraint yet. Foreign key constraints improve data integrity in the database by preventing relations with nonexisting objects. You can read more about the benefits of using foreign keys in this blog post.

Heuristic notes:

• A column is considered a candidate if it matches _id and the related table exists (underscored prefixes like account_user_id are supported).
• Rails polymorphic associations (_id + _type) are ignored since they cannot be expressed as real FK constraints.

You can also exclude known/intentional cases using ignore_list (array or comma-separated string), with 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)

RailsPgExtras.missing_fk_constraints(args: { table_name: "users", ignore_list: ["users.customer_id", "posts.*"] })

+---------------------------------+
| Missing foreign key constraints |
+-------------------+-------------+
| table             | column_name |
+-------------------+-------------+
| feedbacks         | team_id     |
| votes             | user_id     |
+-------------------+-------------+

table_name argument is optional, if omitted, method will display missing fk constraints for all the tables.

Examples:

# Per-call ignore list
RailsPgExtras.missing_fk_constraints(args: {
  ignore_list: ["posts.category_id", "legacy_id", "temp_tables.*"]
})

# As a comma-separated string
RailsPgExtras.missing_fk_constraints(args: {
  ignore_list: "posts.category_id, legacy_id, temp_tables.*"
})

table_schema

This method displays structure of a selected table, listing its column names, together with types, null constraints, and default values.

…

table_info

This method displays metadata metrics for all or a selected table. You can use it to check the table's size, its cache hit metrics, and whether it is correctly indexed. Many sequential scans or no index scans are potential indicators of misconfigured indexes. This method aggregates data provided by other methods in an easy to analyze summary format.

RailsPgExtras.table_info(args: { table_name: "users" })

| Table name | Table size | Table cache hit   | Indexes cache hit  | Estimated rows | Sequential scans | Indexes scans |
+------------+------------+-------------------+--------------------+----------------+------------------+---------------+
| users      | 2432 kB    | 0.999966685701511 | 0.9988780464661853 | 16650          | 2128             | 512496        |

index_info

This method returns summary info about database indexes. You can check index size, how often it is used and what percentage of its total size are NULL values. Like the previous method, it aggregates data from other helper methods in an easy-to-digest format.

…

cache_hit

RailsPgExtras.cache_hit

$ rake pg_extras:cache_hit

      name      |         ratio
----------------+------------------------
 index hit rate | 0.99957