Rails PostgreSQL Performance: ActiveRecord Optimization Patterns

Q: What is the difference between includes, preload, and eager_load in Rails?

includes lets Rails decide the loading strategy: it uses preload (separate queries) by default and switches to eager_load (LEFT OUTER JOIN) when conditions reference the included table. Use preload explicitly for has_many associations to avoid row duplication, and eager_load when you need to filter or order by the associated table's columns. For performance-critical paths, be explicit rather than relying on includes to choose.

Q: How do I find N+1 queries in Rails?

The Bullet gem detects N+1 queries automatically in development and test. Enable strict_loading on models or individual associations to raise errors on lazy loading. In production, pg_stat_statements reveals queries with unusually high call counts, and auto_explain logs execution plans for slow queries.

Q: Should I use pluck or select in Rails?

pluck returns raw values (arrays of scalars or tuples) and skips ActiveRecord object instantiation — use it for data extraction and building ID lists. select returns ActiveRecord objects with model methods and associations, but only the specified columns — use it for views and API serialization where you need model behavior.

Q: How do I size the Rails connection pool for PostgreSQL?

Set the pool value in database.yml equal to the number of threads per Puma worker. Total connections from one app server equals workers x threads. Sum across all app servers and background job processes, and ensure the total stays below PostgreSQL's max_connections. When aggregate connections exceed what PostgreSQL can comfortably handle, introduce PgBouncer in transaction mode.

Q: How do counter caches work in Rails with PostgreSQL?

A counter cache stores the count of associated records directly on the parent model. Rails automatically increments this column when a child is created and decrements it when destroyed. Reading the count is a simple column access with no additional query. The trade-off is write contention: every counter update takes a row-level lock on the parent record.

If you'll permit me, we should have a look at what ActiveRecord has been saying to PostgreSQL on your behalf.

How-To Guide · The Butler of Gold Lapel · March 2026 · 28 min read

The commissioned artwork eager-loaded seventeen associations it did not need. We have asked the artist to please use select.

How ActiveRecord talks to PostgreSQL

Every scope chain, every association accessor, every where clause eventually becomes a SQL string that PostgreSQL parses, plans, and executes. ActiveRecord relations are lazy — calling User.where(active: true) builds a query object but does not execute anything. Execution happens when the result is enumerated.

Rails PostgreSQL optimization centers on controlling the SQL that ActiveRecord generates. Use includes, preload, or eager_load to eliminate N+1 queries; find_each for batch processing; and pluck or select for lightweight reads.

Viewing generated SQL

puts User.where(active: true).order(:created_at).to_sql
# => SELECT "users".* FROM "users" WHERE "users"."active" = TRUE ORDER BY "users"."created_at" ASC

rack-mini-profiler shows per-request query counts and timings directly in the browser, and the Bullet gem detects N+1 queries automatically.

Solving the N+1 query problem

The N+1 problem is the most common performance issue I encounter in Rails applications backed by PostgreSQL.

Identifying N+1 queries

# Controller
@posts = Post.where(published: true).limit(50)

# View
<% @posts.each do |post| %>
  <p><%= post.author.name %></p>   <!-- 1 query per post to load author -->
  <p><%= post.comments.count %></p> <!-- 1 query per post to count comments -->
<% end %>

This generates 1 query to load posts, then 50 queries to load authors, then 50 queries to count comments — 101 queries for a single page. In the Rails log:

Rails log output

Post Load (2.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."published" = TRUE LIMIT 50
Author Load (0.4ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1  [["id", 12]]
Author Load (0.3ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1  [["id", 7]]
Author Load (0.5ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = $1  [["id", 12]]
... (48 more)

The Bullet gem is the most effective automated detection tool:

# Gemfile
group :development, :test do
  gem 'bullet'
end

# config/environments/development.rb
config.after_initialize do
  Bullet.enable        = true
  Bullet.alert         = true   # JavaScript alert in browser
  Bullet.bullet_logger = true
  Bullet.console       = true
  Bullet.rails_logger  = true
end

Strict loading (Rails 6.1+) raises ActiveRecord::StrictLoadingViolationError when a lazy-loaded association is accessed:

# Per-query
@posts = Post.strict_loading.where(published: true)

# Per-model (all queries on this model)
class Post < ApplicationRecord
  self.strict_loading_by_default = true
end

# Per-association
class Post < ApplicationRecord
  has_many :comments, strict_loading: true
end

See: N+1 Queries guide

includes — letting Rails decide

includes tells Rails to load the specified association upfront, but leaves the loading strategy to Rails:

@posts = Post.includes(:author).where(published: true)

When no conditions reference the included table, Rails uses preload — two queries:

Preload strategy

SELECT "posts".* FROM "posts" WHERE "posts"."published" = TRUE;
SELECT "authors".* FROM "authors" WHERE "authors"."id" IN (1, 2, 3, 5, 8);

When conditions reference the included table, Rails switches to eager_load — a LEFT OUTER JOIN:

Eager load strategy

SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, ...
FROM "posts"
LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id"
WHERE "authors"."verified" = TRUE;

preload — always separate queries

preload is the preferred strategy for has_many and has_and_belongs_to_many associations. A JOIN on a has_many produces row duplication:

@posts = Post.preload(:comments).where(published: true)

Generated SQL

SELECT "posts".* FROM "posts" WHERE "posts"."published" = TRUE;
SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3, 5, 8);

eager_load — always JOIN

eager_load is preferred for belongs_to and has_one, where the JOIN does not duplicate rows. Required when filtering by the associated table's columns:

@posts = Post.eager_load(:author).where(authors: { verified: true })

Choosing between includes, preload, and eager_load

Scenario	Recommended	Why
`belongs_to`/`has_one`, no conditions	`includes` or `eager_load`	JOIN adds no rows; one query is efficient
`has_many`, no conditions	`preload`	Separate queries avoid row duplication
Any, with conditions on association	`eager_load`	JOIN required to filter/order
Performance-critical path	Be explicit	Don't let `includes` choose for you

Nested eager loading:

# Load posts, each post's comments, and each comment's author
@posts = Post.includes(comments: :author).where(published: true)

Batch processing large datasets

Loading all records at once will crash your Rails process or cause considerable garbage collection pressure. For cursor-based approaches at the SQL level, see keyset pagination.

find_each and find_in_batches

# Loads 1,000 records at a time, yields each individually
User.where(active: true).find_each(batch_size: 1000) do |user|
  UserMailer.weekly_digest(user).deliver_later
end

# Loads 1,000 records at a time, yields the batch
User.where(active: true).find_in_batches(batch_size: 1000) do |batch|
  Elasticsearch::Client.bulk(batch.map { |u| { index: { _id: u.id, data: u.as_indexed_json } } })
end

Both methods use primary key ordering internally — WHERE id > last_seen_id ORDER BY id ASC LIMIT batch_size — providing consistent performance regardless of depth.

in_batches (Rails 5+)

in_batches yields an ActiveRecord::Relation per batch, enabling batch-level operations without instantiating objects:

# Archive old records in batches — no AR objects instantiated
Order.where("created_at < ?", 1.year.ago).in_batches(of: 1000) do |batch|
  batch.update_all(status: 'archived')
end

# Delete in batches to avoid long-running transactions
Event.where("created_at < ?", 6.months.ago).in_batches(of: 5000) do |batch|
  batch.delete_all
end

See: Keyset Pagination

Lightweight reads — avoiding object instantiation

pluck — array results

pluck bypasses model instantiation entirely, returning raw arrays:

# Returns: [[1, "alice@example.com"], [2, "bob@example.com"], ...]
User.where(active: true).pluck(:id, :email)

# Single column returns a flat array
User.where(active: true).pluck(:email)
# => ["alice@example.com", "bob@example.com", ...]

# Useful for building ID lists for subsequent queries
active_ids = User.where(active: true).pluck(:id)
Order.where(user_id: active_ids).update_all(priority: 'high')

select — partial model loading

@users = User.select(:id, :name, :email).where(active: true)
# Each user object has only id, name, and email loaded

Calculations — database-level aggregation

# Single aggregations
Order.where(status: 'completed').count           # => 14523
Order.where(status: 'completed').sum(:total)      # => 1284567.89
Order.where(status: 'completed').average(:total)  # => 88.45
Product.maximum(:price)                           # => 299.99

# Grouped aggregations — returns a hash
Order.group(:status).count
# => {"pending" => 234, "completed" => 14523, "refunded" => 89}

# Bad: loads all records into memory, then sums in Ruby
Order.where(status: 'completed').map(&:total).sum

# Good: PostgreSQL computes the sum
Order.where(status: 'completed').sum(:total)

Raw SQL and beyond the ORM

ActiveRecord covers a wide range of query patterns, but PostgreSQL offers capabilities the ORM cannot express: CTEs, window functions, lateral joins, and complex aggregations.

Executing raw SQL

# Low-level: returns PG::Result (raw rows, no AR objects)
result = ActiveRecord::Base.connection.execute(<<~SQL)
  SELECT date_trunc('month', created_at) AS month,
         COUNT(*) AS order_count,
         SUM(total) AS revenue
  FROM orders
  WHERE created_at >= '2025-01-01'
  GROUP BY 1
  ORDER BY 1
SQL

result.each do |row|
  puts "#{row['month']}: #{row['order_count']} orders, $#{row['revenue']}"
end

# Model-level: returns AR objects with attributes from the query
top_authors = Author.find_by_sql(<<~SQL)
  SELECT authors.*,
         COUNT(posts.id) AS post_count,
         AVG(posts.view_count) AS avg_views
  FROM authors
  JOIN posts ON posts.author_id = authors.id
  WHERE posts.published = true
  GROUP BY authors.id
  ORDER BY avg_views DESC
  LIMIT 10
SQL

Always parameterize user input:

# Dangerous: SQL injection vulnerability
User.where("email = '#{params[:email]}'")

# Safe: parameterized
User.where("email = ?", params[:email])
User.where(email: params[:email])

PostgreSQL-specific features via ActiveRecord

# Querying JSONB
Product.where("metadata->>'color' = ?", 'red')
Product.where("metadata @> ?", { size: 'large' }.to_json)

# Querying array columns
Product.where("'electronics' = ANY(tags)")
Product.where("tags @> ARRAY[?]::varchar[]", ['sale', 'featured'])

Window functions and lateral joins require raw SQL or Arel:

ranked_products = Product.find_by_sql(<<~SQL)
  SELECT products.*,
         ROW_NUMBER() OVER (
           PARTITION BY category_id
           ORDER BY sales_count DESC
         ) AS category_rank
  FROM products
  WHERE active = true
SQL

See: PostgreSQL JSONB Guide | GIN Indexes | Lateral Join | PostgreSQL Performance Tuning

Counter caches and denormalization

Counting associated records is one of the most frequent operations in web applications — and one of the easiest to make unnecessarily expensive.

# Migration
class AddCommentsCountToPosts < ActiveRecord::Migration[7.1]
  def change
    add_column :posts, :comments_count, :integer, default: 0, null: false

    # Backfill existing data
    Post.find_each do |post|
      Post.reset_counters(post.id, :comments)
    end
  end
end

# Model
class Comment < ApplicationRecord
  belongs_to :post, counter_cache: true
end

With this in place, post.comments_count reads a column value — no query at all.

Counter cache contention

Every counter update takes a row-level lock on the parent record. For popular parent records with high write rates, this creates lock contention. Solutions for high-contention scenarios include background counter updates, approximate counts with pg_class.reltuples, and materialized counts refreshed on a schedule.

See: Rails Counter Cache Contention

Connection pool sizing

# config/database.yml
production:
  adapter: postgresql
  database: myapp_production
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  checkout_timeout: 5

# config/puma.rb
workers 4
threads 5, 5

Each thread needs its own database connection. With this configuration, each Puma worker uses a pool of 5 connections. One app server uses 4 workers x 5 threads = 20 connections.

PgBouncer with Rails

# config/database.yml (for PgBouncer in transaction mode)
production:
  adapter: postgresql
  host: pgbouncer.internal
  port: 6432
  prepared_statements: false  # Required for transaction-mode pooling (pre-1.21)

Rails 7.2 improved PgBouncer compatibility. PgBouncer 1.21+ added support for prepared statements in transaction mode, which may allow you to re-enable them.

See: Rails Connection Pool Sizing | PostgreSQL Pooler Comparison

Read replica routing

Rails multiple databases (Rails 6.0+)

# config/database.yml
production:
  primary:
    adapter: postgresql
    database: myapp_production
    host: primary.db.internal
  primary_replica:
    adapter: postgresql
    database: myapp_production
    host: replica.db.internal
    replica: true

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: { writing: :primary, reading: :primary_replica }
end

Enable automatic read/write splitting:

# config/application.rb
config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session

GET and HEAD requests route to the replica automatically. After any write, reads route to the primary for a configurable delay window (default 2 seconds).

Manual switching

# Manual switching for explicit control
ActiveRecord::Base.connected_to(role: :reading) do
  @reports = Report.where(status: 'completed').limit(100)
end

# Force primary for a specific operation
ActiveRecord::Base.connected_to(role: :writing) do
  @user = User.find(params[:id])  # Read from primary
end

See: Rails Read Replica with PostgreSQL Replication Lag

Indexing for Rails models

class AddIndexesToOrders < ActiveRecord::Migration[7.1]
  # Wrap in disable_ddl_transaction! for concurrent index creation
  disable_ddl_transaction!

  def change
    # Composite index for queries that filter by status and sort by created_at
    add_index :orders, [:status, :created_at]

    # Partial index — only indexes pending orders (smaller, faster)
    add_index :orders, :status, where: "status = 'pending'", name: 'index_orders_on_pending_status'

    # GIN index for JSONB column
    add_index :products, :metadata, using: :gin

    # Concurrent index creation — does not lock the table
    add_index :users, :email, unique: true, algorithm: :concurrently
  end
end

Note that algorithm: :concurrently requires disable_ddl_transaction! because concurrent index creation cannot run inside a transaction.

Common indexing oversights

Missing polymorphic indexes:

# Wrong: separate indexes
add_index :comments, :commentable_id
add_index :comments, :commentable_type

# Right: composite index
add_index :comments, [:commentable_type, :commentable_id]

Unused indexes slow writes for no read benefit:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

See: Composite Indexes | Partial Indexes | Rails Polymorphic Association Optimization

Migrations and schema changes

Schema changes on production databases require careful handling. Always use algorithm: :concurrently for indexes. In PostgreSQL 11+, adding a column with a DEFAULT value does not rewrite the table.

Removing columns safely requires a two-step process:

class User < ApplicationRecord
  self.ignored_columns += ["legacy_role"]
end

Deploy the code change first, then drop the column in a subsequent deploy.

The strong_migrations gem catches unsafe migration patterns automatically.

See: Rails Zero-Downtime Migrations

Monitoring ActiveRecord performance

Development tools

Bullet gem detects N+1 queries, unused eager loading, and counter cache suggestions. rack-mini-profiler provides per-request timing and query counts as a badge in the browser.

ActiveSupport::Notifications for custom slow query logging:

ActiveSupport::Notifications.subscribe("sql.active_record") do |name, start, finish, id, payload|
  duration = (finish - start) * 1000
  if duration > 100  # Log queries slower than 100ms
    Rails.logger.warn "SLOW QUERY (#{duration.round(1)}ms): #{payload[:sql]}"
  end
end

Production monitoring

pg_stat_statements records statistics for every distinct query:

SELECT query,
       calls,
       total_exec_time::numeric(10,2) AS total_ms,
       mean_exec_time::numeric(10,2) AS mean_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

See: pg_stat_statements | auto_explain

ActiveRecord is a fine piece of engineering, and Rails' conventions make sensible defaults available to every developer on the team. The techniques in this guide — eager loading, batch processing, lightweight reads, proper indexing, connection pool sizing — are not advanced. They are the difference between an application that uses ActiveRecord well and one that uses it unknowingly. The SQL was always there. Now you know what it says. For PostgreSQL-level tuning that complements these ActiveRecord patterns, see the PostgreSQL performance tuning guide.