Database indexes in Rails: practical tips and trade-offs

Introduction

We've all hit the moment where a page starts loading slowly and you realize the queries are the bottleneck. Database indexes in Rails aren't magic, but they're one of the highest-impact optimizations you can make. Today we'll explore database indexes in Rails: practical tips and trade-offs—what to index, when to skip it, and how to spot problems before they hit production.

Index foreign keys

Foreign key columns are among the most queried parts of any relational database. When you filter, join, or eager-load associated records, the database scans the foreign key column. Without an index, that's a full table scan. With one, it's a targeted lookup.

Rails migrations create foreign key columns by default, but they don't automatically index them. You need to add the index explicitly.

class CreateOrders < ActiveRecord::Migration[7.0]
  def change
    create_table :orders do |t|
      t.references :user, null: false, foreign_key: true
      t.references :product, null: false, foreign_key: true
      t.decimal :total_amount, precision: 10, scale: 2
      t.string :status, default: 'pending'

      t.timestamps
    end

    # This is already added when you use t.references,
    # but being explicit helps team clarity
    add_index :orders, :user_id
    add_index :orders, :product_id
  end
end

In practice, t.references already creates the index for you (as of Rails 5+), but we've seen teams that override this behavior or add indexes retroactively. The key lesson: verify foreign keys are indexed by checking your schema or running rails db:indexes.

A real-world example: an e-commerce platform we worked with had millions of order records. Queries like Order.where(user_id: 42).recent were taking seconds before the index existed. After adding it, response time dropped to milliseconds. That's the difference between a usable product and one that frustrates users during checkout.

Composite indexes for common filters

Sometimes a single index isn't enough. When you frequently filter or sort on multiple columns, a composite index can be far more efficient than separate indexes.

Imagine a SaaS platform with subscription records. You often query by both account and status:

class Subscription < ApplicationRecord
  belongs_to :account
  scope :active_for_account, ->(account_id) {
    where(account_id: account_id, status: 'active')
  }
end

Without a composite index, the database may use one index and then filter the result set by the other column, or perform multiple scans. With a composite index on (account_id, status), the database finds matching records in a single efficient lookup.

class CreateSubscriptions < ActiveRecord::Migration[7.0]
  def change
    create_table :subscriptions do |t|
      t.references :account, null: false, foreign_key: true
      t.string :status, default: 'pending'
      t.datetime :starts_at
      t.datetime :ends_at

      t.timestamps
    end

    # Composite index for the common query pattern
    add_index :subscriptions, [:account_id, :status]
  end
end

Column order matters in composite indexes. Put the column you filter on most frequently first, then columns used for range queries or sorting. In our example, account_id typically narrows results quickly, so it comes first.

We've also found composite indexes useful for pagination and sorting. If you frequently do Subscription.where(account_id: x).order(created_at: :desc).limit(20), an index on (account_id, created_at) lets the database traverse the index in order without a separate sort operation.

Unique indexes for uniqueness validations

Rails model validations catch duplicates in your application layer, but they don't protect you from race conditions or direct database writes. A unique index is the source of truth and prevents invalid data from ever entering the database.

Consider an API key system where each user should have one active API key:

class ApiKey < ApplicationRecord
  belongs_to :user
  validates :token, presence: true, uniqueness: true
  validates :user_id, presence: true
end

The uniqueness: true validation runs before save, but between validation and insertion, another request could create a duplicate. A unique index on the database side stops this race condition cold.

class CreateApiKeys < ActiveRecord::Migration[7.0]
  def change
    create_table :api_keys do |t|
      t.references :user, null: false, foreign_key: true
      t.string :token, null: false
      t.datetime :expires_at

      t.timestamps
    end

    add_index :api_keys, :token, unique: true
    add_index :api_keys, :user_id
  end
end

If a duplicate token somehow gets inserted (perhaps via a data migration or legacy process), the unique index will raise an error and alert you to the problem. That's far better than silently creating inconsistent state.

Unique indexes also work well with scoped uniqueness. If you need uniqueness within a tenant or account:

class Project < ApplicationRecord
  belongs_to :account
  validates :slug, uniqueness: { scope: :account_id }
end

The corresponding migration uses a composite unique index:

add_index :projects, [:account_id, :slug], unique: true

Check slow query logs

Even with well-designed indexes, you need visibility into what's actually slow. Most databases can log queries that exceed a threshold—PostgreSQL has log_min_duration_statement, MySQL has long_query_time. Use these tools.

In development, you can configure PostgreSQL to log all queries over a certain duration in your database.yml:

development:
  adapter: postgresql
  database: myapp_development
  min_messages: notice
  log_min_duration_statement: 500  # log queries taking > 500ms

In production, we recommend a threshold around 1000–2000 ms depending on your SLAs. Too low and you'll drown in logs; too high and you'll miss real problems.

Once you have slow query logs, analyze them regularly. Look for patterns: do certain queries appear repeatedly? Can they be optimized with an index, or do they need query refactoring? A JSON or array access might not benefit from a traditional index; you might need a GIN index on PostgreSQL or a different approach entirely.

Tools like pgBadger (for PostgreSQL) or Percona Toolkit (for MySQL) help parse and summarize logs. Use them in your CI/CD pipeline or monitoring stack. We've caught N+1 problems and missing indexes this way before they reached production.

Common mistakes

Over-indexing: Every index has a cost. Write performance degrades because inserts, updates, and deletes must maintain the indexes. We've seen tables with dozens of indexes used by only one or two queries. Be selective. Use explain(analyze: true) to verify an index is actually used before creating it.

Forgetting to index in production: A migration that creates an index on a large table can lock the table and cause downtime. Use algorithm: :concurrently in PostgreSQL to avoid locks:

class AddIndexToOrdersUser < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!

  def change
    add_index :orders, :user_id, algorithm: :concurrently
  end
end

Assuming indexes speed up all queries: Indexes help with filtering and joining, but not all query patterns benefit equally. A query on a low-cardinality column (e.g., a boolean status) may perform worse with an index—the database might prefer a full scan. Test with explain.

Ignoring index size: Indexes consume disk space and memory. Composite indexes are larger than single-column indexes. If your database is running low on resources, be ruthless about index selection. Remove indexes that aren't used (most monitoring tools will flag them).

Key takeaways

• Index all foreign key columns by default; Rails migrations with t.references do this automatically, but verify it in your schema.
• Use composite indexes for queries that filter or sort on multiple columns in a predictable order; put the most selective column first.
• Add unique indexes to enforce uniqueness at the database level, not just in your Rails validations; this protects against race conditions and direct database writes.
• Monitor slow query logs in development and production to find performance bottlenecks; use tools like pgBadger to identify patterns.
• Verify indexes are used with explain(analyze: true)` before committing to them; over-indexing hurts write performance and disk usage.
• Use concurrent index creation (algorithm: :concurrently in PostgreSQL) on production tables to avoid locks and downtime.
• Index design is iterative; revisit your indexes as your application grows and query patterns change.