PostgreSQL Optimization in Rails: N+1 and Slow Queries

A SaaS dashboard that takes 4.2 seconds to load will lose users, while the same dashboard at 180ms feels instant. That gap is almost always the database, and the fixes are more straightforward than you’d expect.

This guide covers the techniques that make the biggest difference in production Rails applications.

Understanding Query Cost

Measure query cost with EXPLAIN ANALYZE before optimizing anything. PostgreSQL shows you the execution plan, actual row counts, and wall-clock time per node - the difference between a sequential scan and an index scan on a 10-million-row table is typically 1000x. Every decision below depends on first reading one of these plans correctly.

Enable Query Logging in Development

# config/environments/development.rb
config.active_record.logger = Logger.new(STDOUT)
config.log_level = :debug

# Or use the bullet gem
gem 'bullet', group: :development

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.console = true
end

Use EXPLAIN ANALYZE

# In Rails console
User.joins(:transactions).where(created_at: 1.year.ago..).explain

# Or raw SQL
ActiveRecord::Base.connection.execute("
  EXPLAIN ANALYZE
  SELECT users.*, COUNT(transactions.id)
  FROM users
  LEFT JOIN transactions ON transactions.user_id = users.id
  GROUP BY users.id
").to_a

Look for:

• Sequential scans on large tables (bad)
• High execution time
• Rows scanned vs. rows returned (massive difference = inefficient)

Monitor in Production

# Use rack-mini-profiler
gem 'rack-mini-profiler'

# Or APM tools
gem 'skylight'  # My favorite for Rails apps
gem 'newrelic_rpm'

Problem #1: N+1 Queries

An N+1 happens when Rails runs one query to fetch a parent collection and then one extra query per row to load an association. For 100 users with associated transactions that is 201 queries instead of 2. Fix it with includes, preload, or eager_load, and install the Bullet gem to catch new ones in development. This is the single most common performance killer in Rails apps.

The Problem

# app/controllers/users_controller.rb
def index
  @users = User.all
end

<!-- app/views/users/index.html.erb -->
<% @users.each do |user| %>
  <tr>
    <td><%= user.name %></td>
    <td><%= user.transactions.count %></td>  <!-- N+1! -->
    <td><%= user.latest_transaction&.amount %></td>  <!-- N+1! -->
  </tr>
<% end %>

Database queries: 1 (fetch users) + N (count transactions for each) + N (fetch latest transaction) = 1 + 2N queries

For 100 users: 201 queries. For 1000 users: 2001 queries.

The Solution: Eager Loading

# app/controllers/users_controller.rb
def index
  @users = User
    .includes(:transactions)  # Load transactions
    .left_joins(:transactions)
    .select('users.*, COUNT(transactions.id) as transactions_count')
    .group('users.id')
end

Database queries: 2 (users + transactions). Always 2, regardless of user count.

Advanced Pattern: Preload with Scope

class User < ApplicationRecord
  has_many :transactions
  has_one :latest_transaction,
          -> { order(created_at: :desc) },
          class_name: 'Transaction'
end

# Controller
def index
  @users = User.includes(:transactions, :latest_transaction)
end

<!-- View -->
<% @users.each do |user| %>
  <tr>
    <td><%= user.name %></td>
    <td><%= user.transactions.size %></td>  <!-- No query! -->
    <td><%= user.latest_transaction&.amount %></td>  <!-- No query! -->
  </tr>
<% end %>

Even Better: Counter Caches

For counts, use counter caches:

# Migration
class AddTransactionsCountToUsers < ActiveRecord::Migration[8.0]
  def change
    add_column :users, :transactions_count, :integer, default: 0, null: false

    # Backfill existing data
    User.find_each do |user|
      User.reset_counters(user.id, :transactions)
    end
  end
end

# Model
class Transaction < ApplicationRecord
  belongs_to :user, counter_cache: true
end

Now user.transactions.count is instant - it reads a pre-computed column.

How much does this actually matter? Here is how the three common ways of counting associated records behave as the table grows. These are representative numbers for warm-cache reads on commodity PostgreSQL with narrow rows - absolute values shift with hardware and row width, but the shape holds:

The takeaway is not “counter caches are always right.” It is that a raw COUNT gets slower as your data grows, and the page that felt fine at 10K rows will not feel fine at 10M. Decide how fresh the number has to be, then pick the cheapest approach that meets it.

Problem #2: Missing Indexes

Missing indexes force PostgreSQL to scan entire tables row by row. Adding the right index can turn a 3-second query into a 5ms one.

Identify Missing Indexes

# This query is slow
Transaction.where(user_id: 123, status: 'completed').order(created_at: :desc)

# Check execution plan
Transaction.where(user_id: 123, status: 'completed').order(created_at: :desc).explain

# Look for "Seq Scan" (bad) vs "Index Scan" (good)

Add the Right Index

class AddIndexToTransactions < ActiveRecord::Migration[8.0]
  def change
    # Compound index for WHERE + ORDER BY
    add_index :transactions,
              [:user_id, :status, :created_at],
              name: 'index_transactions_on_user_status_date'
  end
end

Rule of thumb: Index columns used in:

• WHERE clauses
• JOIN conditions
• ORDER BY clauses
• GROUP BY clauses

Index Column Order Matters

# If you query by user_id AND status
add_index :transactions, [:user_id, :status]

# But if you also query by user_id alone
add_index :transactions, [:user_id, :status]  # Works for both!

# But NOT if you query by status alone
# This index won't be used for: where(status: 'completed')
# You'd need a separate index on [:status]

Left-prefix rule: An index on [a, b, c] can be used for queries filtering:

• a
• a, b
• a, b, c

But NOT for: b, c, or b, c alone.

Partial Indexes

For columns with many NULLs or specific values you query frequently:

# Only index completed transactions
add_index :transactions,
          [:user_id, :created_at],
          where: "status = 'completed'",
          name: 'index_completed_transactions'

# Much smaller index, faster queries on completed transactions

Unique Indexes

Enforce uniqueness at the database level:

add_index :users, :email, unique: true
add_index :transactions, [:user_id, :external_id], unique: true

Problem #3: Inefficient Queries

Rewrite queries that do more work than the answer needs: use exists? instead of count > 0, pluck instead of loading full records, database aggregation instead of Ruby iteration, and find_each instead of each on large scopes. These rewrites cost one line of code and routinely turn multi-second requests into sub-100ms ones.

Use EXISTS Instead of COUNT

# Slow: counts all matching records
if user.transactions.where(status: 'pending').count > 0
  # ...
end

# Fast: stops at first match
if user.transactions.where(status: 'pending').exists?
  # ...
end

Use SELECT to Limit Columns

# Loads all columns (including JSONB, TEXT, etc.)
@users = User.all

# Loads only what you need
@users = User.select(:id, :name, :email)

For tables with JSONB or TEXT columns, this can cut memory usage by 2-5x and reduce network transfer proportionally.

Use LIMIT

# Scans entire table
User.where('created_at > ?', 1.year.ago).to_a

# Stops after finding 100
User.where('created_at > ?', 1.year.ago).limit(100).to_a

Always use LIMIT for:

• Autocomplete dropdowns
• “Recent items” lists
• Preview queries

Batch Processing

# BAD: Loads all records into memory
User.all.each do |user|
  user.process_data
end

# GOOD: Processes in batches of 1000
User.find_each(batch_size: 1000) do |user|
  user.process_data
end

# For custom queries
User.where(active: true).in_batches(of: 500) do |batch|
  batch.update_all(last_checked: Time.current)
end

Problem #4: Slow Aggregations

Push aggregations down to PostgreSQL instead of computing them in Ruby. user.transactions.sum(:amount) runs a single SUM in the database and returns a scalar; loading records into memory and calling map(&:amount).sum in Ruby reads every row, allocates objects, and runs the arithmetic in the web process. For reports that can tolerate slight staleness, materialized views turn multi-second aggregations into instant reads.

Use Database Aggregations

# Slow: Loads all records into Ruby memory
transactions = user.transactions.to_a
total = transactions.sum(&:amount)
average = transactions.map(&:amount).sum / transactions.size

# Fast: Database does the math
total = user.transactions.sum(:amount)
average = user.transactions.average(:amount)
count = user.transactions.count

PostgreSQL is optimized for aggregations, so push them down to the database rather than pulling rows into Ruby to do the math.

Materialized Views for Complex Reports

For reports that are expensive to calculate:

# db/migrate/20250101_create_user_statistics_view.rb
class CreateUserStatisticsView < ActiveRecord::Migration[8.0]
  def up
    execute <<-SQL
      CREATE MATERIALIZED VIEW user_statistics AS
      SELECT
        users.id AS user_id,
        users.name,
        COUNT(DISTINCT transactions.id) AS transaction_count,
        SUM(transactions.amount) AS total_amount,
        AVG(transactions.amount) AS average_amount,
        MAX(transactions.created_at) AS last_transaction_at
      FROM users
      LEFT JOIN transactions ON transactions.user_id = users.id
      WHERE transactions.created_at > NOW() - INTERVAL '1 year'
      GROUP BY users.id, users.name;

      CREATE UNIQUE INDEX ON user_statistics (user_id);
    SQL
  end

  def down
    execute "DROP MATERIALIZED VIEW IF EXISTS user_statistics"
  end
end

# app/models/user_statistic.rb
class UserStatistic < ApplicationRecord
  self.primary_key = 'user_id'

  # Refresh materialized view
  def self.refresh
    connection.execute('REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics')
  end
end

# Schedule refresh (e.g., every hour)
# config/schedule.rb (with whenever gem)
every 1.hour do
  runner "UserStatistic.refresh"
end

Now complex reports are instant - just SELECT * FROM user_statistics.

Problem #5: JSON Columns Performance

JSON columns are powerful but can be slow if not indexed properly.

# Schema
create_table :transactions do |t|
  t.jsonb :metadata, default: {}, null: false
end

# Add GIN index for JSONB
add_index :transactions, :metadata, using: :gin

# Now these queries are fast
Transaction.where("metadata @> ?", { payment_method: 'credit_card' }.to_json)
Transaction.where("metadata -> 'payment_method' = ?", 'credit_card')
Transaction.where("metadata ->> 'amount' = ?", '100.00')

Specific Key Indexes

# If you frequently query a specific JSON key
add_index :transactions, "(metadata -> 'payment_method')", name: 'index_transactions_on_payment_method'

Problem #6: Connection Pool Exhaustion

Connection pool exhaustion happens when your Rails processes request more database connections than the pool allows, causing requests to time out waiting. Fix it by sizing the pool to match your thread count, and front PostgreSQL with PgBouncer in transaction pooling mode when you have many application servers sharing a database - this can 5-10x your effective connection capacity without raising PostgreSQL’s own limit.

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

Symptoms:

• could not obtain a database connection within 5 seconds
• Requests timing out under load

Solutions:

1. Increase Pool Size

production:
  pool: 20  # Match Puma workers * threads

2. Use PgBouncer

PgBouncer pools connections at the database level:

# pgbouncer.ini
[databases]
myapp = host=localhost dbname=myapp_production

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

3. Close Long-Running Connections

# config/initializers/active_record.rb
ActiveRecord::Base.connection_pool.with_connection do |conn|
  conn.execute("SET statement_timeout = 30000")  # 30 seconds
end

Production Case Study: From 127 Queries to 4

The clearest way to show all of this is a real one. A B2B SaaS analytics dashboard I worked on took 4.2 seconds to load its main screen, and the support tickets were stacking up. The numbers here are from that engagement; the code is reconstructed and the domain generalized, but the shape is exactly what happened.

The first step was to measure, not guess. rack-mini-profiler pinned to the corner of the page showed 127 SQL queries on a single dashboard load, with 3.8 of the 4.2 seconds spent inside the database. That one number rules out “the app server is slow” and points straight at query count.

Before Optimization

Three lines in this controller look harmless, and every one of them is a trap:

# app/controllers/dashboards_controller.rb
def show
  @user = current_user
  @activities = @user.activities.order(created_at: :desc).limit(10)
  @total_usage = @user.usage_records.sum(&:value)   # Ruby Array#sum, not SQL
  @monthly_cost = @user.invoices.sum(&:total)       # loads every invoice
  @saved_items = @user.bookmarks.map(&:title)       # loads full objects
end

sum(&:value) is Ruby’s Array#sum, not SQL SUM. It loads every usage record into memory and adds them up in the web process. The invoices line does the same. map(&:title) loads full bookmark objects just to read one column. And the activities view triggered a separate N+1 on a related resource for each of the 10 rows. None of it is obviously wrong on the page, which is exactly why it survived code review.

Before:

• Page load: 4.2 seconds
• Database queries: 127
• Database time: 3.8 seconds

The False Start

The first instinct was to wrap the whole action in Rails.cache.fetch. Page load dropped to 200ms and everyone felt clever, right up until the next deploy cleared the cache and every dashboard in the system hit the cold path at the same moment. Caching had hidden the problem, not fixed it. So the cache came back out until the queries underneath were actually fast.

After Optimization

# app/models/user.rb
class User < ApplicationRecord
  has_many :activities
  has_many :usage_records
  has_many :invoices
  has_many :bookmarks

  # Aggregations run in the database, then cached for headroom
  def usage_summary
    Rails.cache.fetch("user_#{id}_usage_summary", expires_in: 5.minutes) do
      {
        total_usage: usage_records.sum(:value),   # SQL SUM, one number back
        monthly_cost: invoices.sum(:total),        # SQL SUM, one number back
        items_count: bookmarks.count
      }
    end
  end
end

# app/controllers/dashboards_controller.rb
def show
  @user = current_user

  # Eager load with limits
  @activities = @user.activities
                     .includes(:related_resource)
                     .order(created_at: :desc)
                     .limit(10)

  # Cached aggregation
  @usage_summary = @user.usage_summary

  # Single query for bookmarks
  @saved_items = @user.bookmarks.pluck(:title)
end

# Add indexes
add_index :activities, [:user_id, :created_at]
add_index :usage_records, :user_id
add_index :bookmarks, :user_id

Three changes did almost all of the work: sum(&:value) became sum(:value) so PostgreSQL does the arithmetic and hands back a single number, the activities N+1 died with includes(:related_resource), and map(&:title) became pluck(:title) to fetch one column instead of whole rows. The five-minute cache went back on last, as headroom rather than a cover-up.

After:

• Page load: 180ms (23x faster)
• Database queries: 4
• Database time: 45ms

The lesson that stuck: the dashboard never needed more hardware or more caching. It needed to stop asking the database for things it could compute in a single query.

Monitoring & Ongoing Optimization

1. Identify Slow Queries

# PostgreSQL slow query log
# postgresql.conf
log_min_duration_statement = 100  # Log queries > 100ms

# Or use pg_stat_statements
CREATE EXTENSION pg_stat_statements;

# Then query
SELECT
  query,
  calls,
  total_time,
  mean_time,
  max_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

2. Set Up Alerts

# config/initializers/notifications.rb
ActiveSupport::Notifications.subscribe('sql.active_record') do |name, start, finish, id, payload|
  duration = (finish - start) * 1000

  if duration > 1000  # Queries over 1 second
    Rails.logger.warn "Slow Query (#{duration.round}ms): #{payload[:sql]}"

    # Optional: Send to error tracking
    Rollbar.warning("Slow database query", {
      duration: duration,
      sql: payload[:sql]
    })
  end
end

3. Regular VACUUM

PostgreSQL needs maintenance:

-- Manual
VACUUM ANALYZE;

-- Automatic (recommended)
-- postgresql.conf
autovacuum = on

Optimization Checklist

Before deploying to production, check:

• All foreign keys have indexes
• Queries use eager loading (no N+1)
• Counter caches for counts
• Indexes on WHERE/ORDER BY columns
• Partial indexes where appropriate
• Database-level aggregations (not Ruby)
• SELECT only needed columns
• LIMIT on all lists
• Batch processing for large datasets
• Connection pool sized correctly
• Slow query logging enabled
• EXPLAIN ANALYZE on critical queries

Advanced Techniques

1. Composite Primary Keys (Rails 7.1+)

# For many-to-many tables
create_table :user_roles, primary_key: [:user_id, :role_id] do |t|
  t.integer :user_id, null: false
  t.integer :role_id, null: false
end

2. CTE (Common Table Expressions)

User.with(
  active_users: User.where(active: true),
  recent_transactions: Transaction.where('created_at > ?', 30.days.ago)
).joins('INNER JOIN active_users ON...')

3. Window Functions

# Rank users by transaction volume
User.select('
  users.*,
  RANK() OVER (ORDER BY transaction_count DESC) as rank
').joins(:transactions)

Production Gotchas That Cost Me Hours

The techniques above are the easy part. These are the failure modes that show up weeks later, in production, once the data has grown and the assumptions you made on a laptop no longer hold.

Counter Caches Drift, and the Drift Is Silent

A counter_cache is only correct if every write goes through Active Record callbacks. The moment something touches the table outside Rails - a bulk insert_all, a raw SQL DELETE, an update_column, a database-level cascade - the cached count is wrong and nothing tells you. I have watched a “12 comments” badge sit next to a thread that had 9 comments for months. Two defenses: run reset_counters on a schedule to reconcile, and if writes genuinely happen outside Rails, enforce the count with a database trigger rather than trusting the callback. Worth knowing too: every child insert updates the parent row, so a very hot parent (a busy account, a popular post) can turn into a lock-contention point under concurrent writes.

REFRESH MATERIALIZED VIEW CONCURRENTLY Is Not Free

CONCURRENTLY lets reads continue during a refresh, which is why everyone reaches for it, but the cost shows up late. It requires a unique index on the view, it does roughly twice the work (it builds the new result set, then diffs it against the old one), and it still takes a brief exclusive lock during the final swap. On a large view refreshed every few minutes, a single refresh can take longer than the interval, refreshes start to overlap and queue, and your “real-time” dashboard ends up serving data that is 20 minutes stale. Track refresh duration as a first-class metric. When it creeps toward your refresh interval, narrow the view or widen the interval before it falls over.

Eager Loading the Wrong Thing Is Just a Different N+1

includes is not a “make it fast” button. Eager-load a has_many that you only render for a handful of the rows on the page and you have loaded thousands of records for nothing. Worse, eager_load forces a single LEFT JOIN, and joining two has_many associations in one query produces a cartesian product: 100 parents with 50 children and 20 notes each is 100,000 rows materialized in memory, not 17,000. When you need multiple collections, prefer preload (one query per association) over eager_load, and only load associations the page actually uses.

Partial Indexes Only Help When the Predicate Matches Exactly

A partial index like WHERE status = 'completed' is used only when PostgreSQL can prove your query asks for the same subset. where(status: 'completed') with a literal value uses it. where(status: params[:status]) usually does not, because the value is not known when the query is planned, so the planner falls back to a wider index or a sequential scan. Partial indexes are excellent for known, constant filters (a soft-delete flag, a single active state) and useless for dynamic ones. Confirm with EXPLAIN that the index is actually chosen before assuming it helped.

The Connection Pool Number That Bites You Is the One You Forgot

pool in database.yml is per process, not per application. A Puma setup with 3 workers and 5 threads needs 15 connections on one server; multiply by the number of app servers, add every Solid Queue or Sidekiq process, then the Rails console someone left open in a tmux pane. PostgreSQL ships with max_connections = 100. You can exhaust that without a single traffic spike, and the symptom (could not obtain a database connection) looks like a load problem when it is really an arithmetic one. This is the situation PgBouncer exists for: hundreds of client connections sharing a small pool of real PostgreSQL connections.

Limitations and When to Be Careful

These techniques work in most cases, but optimization is not free.

Index Overhead

Every index speeds up reads but slows down writes. A table with 10 indexes will have noticeably slower INSERT and UPDATE operations. For write-heavy tables (audit logs, event streams, analytics), be selective about which indexes you add.

Materialized Views Are Not Free

Materialized views consume disk space and require refresh operations. A REFRESH MATERIALIZED VIEW CONCURRENTLY takes an exclusive lock on the unique index during the swap phase. For large views refreshing frequently, this can cause brief lock contention.

Caching Can Hide Problems

The case study above uses Rails.cache.fetch for dashboard stats. Caching masks slow queries instead of fixing them. If your cache goes cold (deploy, Redis restart, key rotation), all that hidden latency comes back at once. Fix the underlying query first, then cache for extra speed.

When These Techniques Are Not Enough

If you’ve applied everything here and still hit performance walls, the problem might be architectural:

• Data volume: Beyond 100M+ rows, consider partitioning or TimescaleDB for time-series workloads
• Connection limits: PostgreSQL handles ~500 connections well. Beyond that, PgBouncer helps but you may need read replicas
• Query complexity: Some analytical queries will never be fast on an OLTP database. Consider a separate analytics store or materialized views with longer refresh intervals
• Write throughput: If you’re doing 10,000+ writes/second, look into batch inserts, COPY, or dedicated write-ahead patterns

The Bottom Line

Database optimization is about measuring first and fixing the biggest bottlenecks:

1. Eliminate N+1 queries - the single biggest win in most Rails apps
2. Add compound indexes matching your actual query patterns
3. Use database aggregations instead of loading records into Ruby
4. Monitor continuously - production data reveals problems that development never will

These techniques have consistently reduced page load times from seconds to milliseconds, cut database costs by 60-70%, and enabled handling 10x more traffic on the same hardware.

Start with EXPLAIN ANALYZE on your slowest queries. The numbers will tell you exactly where to focus.

Need help with database performance? I help teams with PostgreSQL optimization, query tuning, and scaling decisions. If your Rails app is hitting database bottlenecks, reach out at [email protected].

Further Reading

• Solid Cache in Rails 8: Database-Backed Caching
• TimescaleDB in Rails: A Practical Implementation Guide
• Deploy Rails 8 with Kamal to a VPS
• How to Integrate Silverfin API - compound indexing for financial snapshot data
• Odoo API Integration in 2026: JSON-2, Webhooks, Dashboards - indexing the warehouse tables behind an ERP dashboard
• PostgreSQL EXPLAIN Visualizer
• Bullet gem - N+1 detection
• Skylight - APM for Rails