Database Indexing in Ruby on Rails: When, Why, and How

Published: 10 September 2026 | Updated: 10 September 2026

Key takeaway: Rails database indexing is often the highest-impact fix for slow queries once N+1 problems are solved. Index the queries you actually run: composite indexes for multi-column filters, partial indexes for the small slice of rows you query most, and algorithm: :concurrently for large production tables. Verify every index with EXPLAIN ANALYZE, because each extra index adds cost to every write.

Who This Guide Is For

This guide is for Ruby on Rails developers, backend engineers, and engineering leads who run PostgreSQL in production and need to decide which indexes to add, change, or remove. It assumes you’re comfortable with ActiveRecord and migrations, but not that you’re a database specialist.

When to Use This Advice

Use the techniques in this guide when:

• A page, report, or API endpoint is slow, and EXPLAIN ANALYZE shows a Seq Scan on a large table.
• Database CPU spikes during peak traffic while your application servers have spare capacity.
• A table has grown from thousands of rows to millions, and queries that were once instant now take seconds.
• You need to add an index to a live production table without blocking writes.
• Write latency is creeping up, and you suspect unused or overlapping indexes.

Indexing is the wrong fix in two cases. On small tables, a sequential scan is often faster than an index lookup. And if a page fires hundreds of small queries, fix the N+1 problem first, because no index can fix that pattern.

Use Cases This Guide Covers

• SaaS dashboards and reporting: composite indexes for multi-column filters, as in the case study where an 8-second report dropped to under 500 milliseconds.
• E-commerce and order management: fast lookups by customer, order status, and date.
• Soft deletes and archived records: partial indexes that cover only the active rows you actually query.
• High-traffic APIs: unique indexes on email addresses, UUIDs, and external IDs.
• Production schema changes: concurrent index builds on large tables with zero downtime.

These patterns apply across SaaS, e-commerce, fintech, and logistics platforms, or any Rails application where data grows faster than caching can keep up.

“A single well-designed index can reduce a query from several seconds to a few milliseconds. But a poorly designed indexing strategy can slow down every write operation in your application.”

As Ruby on Rails developers, we spend a lot of time optimizing ActiveRecord queries, eliminating N+1 queries, and adding caching layers. Yet one of the most impactful performance improvements often happens below the Rails application—in the database itself.

Whether you’re building a SaaS platform, an e-commerce application, or an API that serves millions of requests, your database eventually becomes the bottleneck. When that happens, adding more application servers rarely solves the problem. Instead, the answer is often a better indexing strategy.

In this article, we’ll cover:

• What database indexes are
• How indexes work internally
• Different types of indexes
• Partial indexes
• Composite indexes
• Zero-downtime indexing in production
• Drawbacks of excessive indexing
• Real-world Rails examples
• Best practices for production applications

Understanding How Databases Search Data

For example, imagine you have a library with five million books.

When someone asks for a book by its title, there are two approaches.

Without an Index

First, the librarian starts from shelf one.

Book 1

↓

Book 2

↓

Book 3

↓

…

↓

Book 5,000,000

Eventually the book is found.

In fact, this is exactly what databases call a Sequential Scan (Seq Scan).

With an Index

Instead of checking every shelf, the librarian opens the catalogue.

Harry Potter → Shelf 18

Rails Guide → Shelf 42

Ruby Cookbook → Shelf 81

As a result, the book is found immediately.

In other words, that catalogue is essentially what a database index is.

What Exactly is an Index?

An index is a special data structure maintained by the database that stores values from one or more columns in a sorted format, along with pointers to the corresponding table rows.

Most relational databases such as PostgreSQL and MySQL use a B-Tree (Balanced Tree) as the default index type.

Instead of scanning every row, the database traverses the tree.

           Root

          /      \

      A-M          N-Z

     /  \         /   \

  Adam Bob    Mike Zack

Searching becomes logarithmic rather than linear.

Instead of checking five million rows, the database checks only a few levels of the tree.

Working Example

For instance, suppose we have this model.

class Customer < ApplicationRecord
end
Schema:
create_table :customers do |t|
  t.string :name
  t.string :email
  t.string :city
  t.string :status
end

Additionally, the application frequently executes:

Customer.find_by(email: "[e-mail]")

Without an Index

SQL executed:

SELECT *
FROM customers
WHERE email='[e-mail]';

Execution plan:

Seq Scan on customers

Consequently, the database checks every row.

With 5 million records:

Row 1

↓

Row 2

↓

Row 3

↓

…

↓

Row 5,000,000

Adding an Index

Migration:

class AddEmailIndexToCustomers < ActiveRecord::Migration[7.1]
  def change
    add_index :customers, :email
  end
end

Run:

rails db:migrate

Afterward, the query changes dramatically.

Execution plan:

Index Scan

Instead of scanning the table, PostgreSQL looks inside the index.

Email Index

[e-mail]

↓

[e-mail]

↓

[e-mail]

Therefore, query execution becomes almost instantaneous.

Composite Indexes

Similarly, suppose every dashboard request executes:

Order.where(
  customer_id: current_customer.id,
  status: "completed"
)

Bad approach:

add_index :orders, :customer_id
add_index :orders, :status

Better:

add_index :orders,
          [:customer_id, :status]

Now PostgreSQL can answer the query using a single index lookup instead.

Partial Indexing

This is one of the most underused yet powerful indexing techniques in PostgreSQL.

For example, suppose your application uses soft deletes.

Customer.where(deleted_at: nil)

If 95% of your rows are deleted, why should PostgreSQL index them?

Instead:

add_index :customers,
          :deleted_at,
          where: "deleted_at IS NULL"

Thus, the index now contains only active customers.

Benefits:

• Smaller index
• Less storage
• Faster lookups
• Faster writes

Partial Index vs Regular Index

| Regular Index | Partial Index |
| Indexes every row | Indexes only matching rows |
| Larger disk usage | Smaller disk usage |
| Slower updates | Faster updates |
| Useful for general searches | Useful for filtered searches |

Example

Regular index

add_index :customers, :status

Indexes:

Active

Inactive

Pending

Deleted

Archived

Partial index

add_index :customers,
          :status,
          where: "status='Active'"

Indexes only:

Active

Achieving Zero Downtime While Adding Indexes

In particular, one mistake developers make is running:

add_index :customers, :email

On a table with millions of rows, PostgreSQL may lock the table while building the index, blocking reads or writes depending on the operation.

Rails supports concurrent index creation for PostgreSQL.

class AddEmailIndex < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :customers,
              :email,
              algorithm: :concurrently
  end
end

Why disable_ddl_transaction!?

Specifically, PostgreSQL cannot create indexes concurrently inside a transaction. Rails wraps migrations in transactions by default, so you must disable it.

Benefits

• No long table lock
• Reads continue
• Writes continue
• Safe for production deployments

For this reason, this is the recommended approach for large production databases.

Verifying the Index

Above all, never assume the database is using your index.

Run:

EXPLAIN ANALYZE
SELECT *
FROM customers
WHERE email='[e-mail]';

Before indexing:

Seq Scan

After indexing:

Index Scan

Sometimes PostgreSQL still chooses a sequential scan if it estimates that scanning the table is cheaper, especially for very small tables or low-selectivity queries.

The Hidden Cost of Too Many Indexes

Still, many developers think:

More indexes = Faster database.

However, that’s not true.

Every index must also be updated whenever data changes.

For instance, imagine this insert:

Customer.create(...)

Without indexes:

Insert Row

With eight indexes:

Insert Row

↓

Update Index 1

↓

Update Index 2

↓

Update Index 3

↓

…

↓

Update Index 8

In short, every additional index increases write overhead.

Drawbacks of Excessive Indexing

• Slower INSERT operations
• Slower UPDATE operations
• Slower DELETE operations
• Increased storage usage
• Longer backup times
• Longer restore times
• Increased VACUUM maintenance in PostgreSQL
• More memory consumed by indexes

Hence, indexing should always be driven by actual query patterns, not guesswork.

Production Case Study

In one of our production reporting systems, a dashboard loaded customer reports filtered by:

• Customer ID
• Reporting Group
• Status

Initially, the table had individual indexes on each column. The query planner still had to combine results, leading to response times of over 8 seconds.

We analyzed the execution plan using EXPLAIN ANALYZE and replaced the individual indexes with a composite index matching the query pattern:

add_index :reports,
          [:customer_id, :reporting_group_id, :status],
          algorithm: :concurrently

Because the migration used algorithm: :concurrently and disable_ddl_transaction!, it was deployed to production without blocking application traffic.

Overall, the results were significant:

• Response time dropped from 8 seconds to under 500 milliseconds
• Database CPU usage decreased during peak traffic
• No application code changes were required

Finally, this reinforced an important lesson: understanding how your application queries data is often more valuable than adding hardware.

Best Practices

• Index foreign keys used in joins.
• Use unique indexes for unique columns like email and UUIDs.
• Prefer composite indexes for common multi-column filters.
• Use partial indexes when only a subset of rows is queried frequently.
• Create indexes concurrently in production to avoid downtime.
• Validate index usage with EXPLAIN ANALYZE.
• Remove unused indexes periodically.
• Monitor slow query logs and index bloat.

Frequently Asked Questions

When should I add a database index in Rails?

Add an index when a column appears in frequent WHERE, JOIN, or ORDER BY clauses on a table large enough for sequential scans to hurt. Foreign keys, unique fields such as email, and columns behind slow dashboard filters are the usual candidates. Confirm the need with EXPLAIN ANALYZE rather than indexing speculatively.

What is the difference between a composite index and a partial index?

A composite index covers several columns in one index, such as customer_id and status, and serves queries that filter on those columns together. It works best when the query filters on the leading column. A partial index covers only rows matching a condition, such as active records, which keeps it smaller and cheaper to maintain.

Does add_index lock the table in PostgreSQL?

A standard add_index runs CREATE INDEX, which blocks inserts, updates, and deletes on the table until the build finishes, while reads continue. On large production tables, use algorithm: :concurrently with disable_ddl_transaction! so writes keep flowing. If a concurrent build fails, drop the leftover invalid index before retrying.

Why is PostgreSQL not using my index?

The planner skips an index when it estimates a sequential scan is cheaper, which is common on small tables or when a query matches a large share of rows. Stale statistics, or conditions that don’t match the index’s leading column or partial-index predicate, can also cause it. Run ANALYZE, then check the plan with EXPLAIN ANALYZE.

Can too many indexes slow down a Rails application?

Yes. Every index must be updated on each insert, update, and delete, so unused indexes add write latency, storage, and VACUUM work without speeding up reads. Review pg_stat_user_indexes periodically and drop indexes with an idx_scan count of zero, after confirming they don’t back a unique constraint.

Final Thoughts

Database indexing is one of the highest-impact optimizations you can make in a Rails application. But the goal isn’t to add indexes everywhere—it’s to create the right indexes for the queries your application actually executes.

In conclusion, by combining thoughtful indexing, query analysis, and zero-downtime deployment techniques, you can build Rails applications that continue to perform well as your data grows from thousands to millions of records.

The post Database Indexing in Ruby on Rails: When, Why, and How appeared first on Bluetick Consultants Inc..