Turbocharge Your Rails Apps with Smart Database Indexing

Is your Rails application running slow? Are search queries taking forever to complete? The solution might be simpler than you think. Proper database indexing can dramatically improve search and filtering performance with minimal code changes.

Why Indexes Matter

Think of indexes like the index at the back of a book. Instead of scanning every page, you can quickly find what you're looking for. Database indexes work the same way, allowing your queries to skip full table scans and go straight to the relevant records.

Five Key Indexing Principles

1. Index WHERE clause columns - Any column you frequently filter on deserves an index
2. Use compound indexes strategically - For queries with multiple conditions
3. Consider partial indexes - When you often query specific subsets of data
4. Leverage PostgreSQL's powerful features - Especially for text search operations
5. Monitor and maintain your indexes - They're not "set and forget"

Choosing the Right Index for the Job

Basic Single-Column Indexes

The simplest and most common type of index. Perfect for exact matches on a single column:

# Add these to your migrations
add_index :employees, :email
add_index :employees, :status
add_index :employees, :team_id

# These queries will now be lightning fast
Employee.where(email: "user@example.com")
Employee.where(status: :active)

Compound Indexes: When One Column Isn't Enough

When queries filter on multiple columns together, compound indexes shine:

# One index to rule them all
add_index :employees, [:manager_id, :status]

# Both these queries benefit
Employee.where(manager_id: 1, status: :active)
Employee.where(manager_id: 1)  # Still uses the index (left-most column)

Pro tip: Order matters in compound indexes! Put the most selective column (the one that filters out the most records) first.

Unique Indexes: Enforce Rules, Boost Performance

Unique indexes not only ensure data integrity but also optimize queries:

# No duplicates allowed
add_index :employees, [:featured, :email], unique: true

# Queries that use these columns will fly
Employee.where(featured: true).where(email: params[:email])

Partial Indexes: Focus on What Matters

Why index records you rarely query? Partial indexes cover only the subset of data you care about:

# Only index active and pending employees
add_index :employees,
  [:first_name, :last_name, :email],
  name: "index_employees_on_first_name_last_name_email_not_archived",
  where: "(status = ANY (ARRAY[0, 2]))",

# These queries now run faster AND the index is smaller
Employee.where(status: [:active, :pending])
      .where(first_name: "Paul")

Full-Text Search Indexes: Beyond Simple Equality

For "contains" or pattern matching searches, standard BTree indexes won't help. Enter trigram indexes:

# Enable the PostgreSQL trigram extension first
enable_extension :pg_trgm

# Add a GIN index with trigram support
add_index :employees,
  [:first_name, :last_name, :email],
  opclass: :gin_trgm_ops,
  using: :gin

# Now LIKE/ILIKE queries won't bring your app to a crawl
Employee.where("first_name ILIKE :query OR last_name ILIKE :query",
              query: "%#{term}%")

Putting It All Together: A Real-World Example

Here's how to implement a comprehensive indexing strategy for an Employee model:

# db/migrate/YYYYMMDDHHMMSS_add_search_indexes_to_employees.rb
class AddSearchIndexesToEmployees < ActiveRecord::Migration[7.1]
  def change
    # Enable PostgreSQL extensions
    enable_extension :pg_trgm

    # Basic indexes for foreign keys and status
    add_index :employees, :team_id
    add_index :employees, :manager_id
    add_index :employees, :status

    # Compound index for manager filtering
    add_index :employees, [:manager_id, :status]

    # Full-text search index
    add_index :employees,
      [:first_name, :last_name, :email],
      name: "index_employees_search",
      opclass: :gin_trgm_ops,
      using: :gin

    # Partial index for active employee search
    add_index :employees,
      [:first_name, :last_name, :email],
      name: "index_employees_active_search",
      opclass: :gin_trgm_ops,
      where: "(status = ANY (ARRAY[0, 2]))",
      using: :gin
  end
end

And here's how your filter class can leverage these indexes:

class Employee::Filter
  include ActiveModel::Model
  include ActiveModel::Attributes

  PERMITTED_PARAMS = %i[query team_id manager_id status].freeze

  attribute :query, :string
  attribute :team_id, :integer
  attribute :manager_id, :integer
  attribute :status, :string

  def apply(scope = Employee.unscoped)
    scope
      .then { filter_by_name_and_email(it) }
      .then { filter_by_team(it) }
      .then { filter_by_manager(it) }
      .then { filter_by_status(it) }
  end

  private

  def filter_by_name_and_email(scope)
    return scope if query.blank?
    # Uses the GIN trigram index
    scope.where("first_name ILIKE :query OR last_name ILIKE :query OR email ILIKE :query",
                query: "%#{query}%")
  end

  def filter_by_team(scope)
    return scope unless team_id.present?
    # Uses the team_id index
    scope.where(team_id: team_id)
  end

  def filter_by_manager(scope)
    return scope unless manager_id.present?
    # Uses the compound manager_id+status index
    scope.where(manager_id: manager_id)
  end

  def filter_by_status(scope)
    return scope.visible unless status.present? && Employee.statuses.keys.include?(status)
    # Uses the compound manager_id+status index or status index
    scope.where(status: status)
  end
end

Pro Tips for Index Mastery

1. Order Matters in Compound Indexes

Put your most selective columns first to maximize efficiency. If users filter by manager_id more often than status, put manager_id first in your compound index.

2. Monitor Index Usage

Not sure if your indexes are being used? Check with:

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'employees'
ORDER BY idx_scan DESC;

Low idx_scan values may indicate unused indexes that are just slowing down writes.

3. Remember the Index Size Tradeoff

Indexes speed up reads but slow down writes and take up storage space. Always verify your indexes are being used:

EXPLAIN ANALYZE SELECT * FROM employees
WHERE status = 0 AND manager_id = 1;

Look for Index Scan in the results, not Seq Scan.

4. Partial Indexes Save Space and Boost Performance

If most of your queries target a specific subset of data (like active records), partial indexes can dramatically reduce index size and improve INSERT performance.

5. Text Search Requires Special Treatment

Standard BTree indexes don't help with LIKE '%term%' queries. Use GIN indexes with trigrams for these scenarios. Your users will thank you when their searches don't time out!

Common Pitfalls to Avoid

1. Over-Indexing

Not every column needs an index. Too many indexes slow down writes and waste space. Index strategically based on your most common queries.

2. Under-Indexing Critical Paths

Missing indexes on foreign keys or frequently filtered columns can cause major performance issues. Always index foreign keys and columns used in WHERE clauses.

3. Using the Wrong Index Type

A BTree index won't help with full-text search, and a GIN index is overkill for exact matches. Choose the right tool for the job.

4. Neglecting Index Maintenance

Indexes need care too. Regular VACUUM ANALYZE operations keep them performing optimally.

How to Test Your Indexes

Add this to your test suite to catch performance regressions:

# test/models/employee/filter_test.rb
class Employee::FilterTest < ActiveSupport::TestCase
  test "uses indexes for filtering" do
    filter = Employee::Filter.new(
      query: "john",
      manager_id: managers(:one).id,
      status: "active"
    )

    explain_result = ActiveRecord::Base.connection.explain(
      filter.apply.to_sql
    )

    assert_match /Index Scan/, explain_result
    assert_no_match /Seq Scan/, explain_result
  end
end

Final Thoughts

Smart indexing is one of the most powerful yet underutilized ways to improve Rails application performance. With the right indexes in place, even complex searches can return results in milliseconds instead of seconds.

Remember:

• Index columns you filter on frequently
• Choose the right index type for your query patterns
• Monitor and maintain your indexes
• Test with realistic data volumes

Your database will thank you, and so will your users!

Paul Keen is an Open-source Contributor and a Chief Technology Officer at JetThoughts. Follow him on LinkedIn or GitHub.

If you enjoyed this story, we recommend reading our latest tech stories and trending tech stories.