Recommended Free Tools
Add a database index in Rails when a recurring query or an integrity requirement justifies it—not simply because a column appears in a query. Use migrations to define the index, then inspect the database’s query plan and workload to check whether it helps. Rails provides the migration and query interfaces; the database adapter and engine determine how indexes are built and used.
What an index does—and what it costs
An index is a data structure a database can use to find rows without examining the whole table in the same way as an unindexed lookup. PostgreSQL’s documentation explains the trade-off: indexes can make retrieval faster, but also add overhead to the database system, so they should be used sensibly. PostgreSQL 18: Indexes.
As an Amazon Associate I earn from qualifying purchases.
That overhead is why “index every queried column” is not a good universal rule. An index may benefit reads that match its structure, while adding storage and work when the database changes indexed data. The right choice depends on the actual query patterns and workload; there is no general speedup figure that applies to every Rails application.
When should you add an index?
Start with a recurring query or a database integrity need. Identify its filters, sort order, and frequency, then inspect its plan and consider the impact on writes as well as reads. Rails’ .explain method exposes the database’s plan for an Active Record relation; the optimizer decides whether to use an index. An index’s existence alone does not prove that it will be selected or improve the overall workload. Rails 8.1: Running EXPLAIN.
#1 Best Overall
- Single-column lookup: Consider an index when the application repeatedly filters or joins on that column and the plan or workload suggests it is useful.
- Combined filtering and sorting: Consider a composite index when queries repeatedly use the same combination of predicates and ordering. Its column order matters; choose it to match actual query shapes rather than assuming permutations are interchangeable.
- Uniqueness: Use a unique database index when the database must enforce that a value or combination of values cannot occur more than once.
- Specialized search: Full-text search or other specialized query patterns may call for a particular index method, but method availability and syntax depend on the database.
How to add an index in a Rails migration
Rails’ add_index accepts a column symbol or an array of symbols. A migration can add a basic index and a composite one:
class AddIndexesToOrders < ActiveRecord::Migration[8.1]
def change
add_index :orders, :customer_id
add_index :orders, [:customer_id, :created_at]
end
endThe first index targets lookups by customer_id. The second is only an appropriate choice if the application’s query patterns support that column combination and order. Consult the Rails 8.0 add_index API for available options, and verify support for your Rails version and database adapter.
Rank #2
Enforce uniqueness at the database level
To prevent duplicate email values in the database, a migration can declare a unique index:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
add_index :users, :email, unique: trueThis is an integrity constraint enforced by the database, not just a lookup optimization. Rails migration guidance warns that application-level validations alone cannot guarantee integrity in every circumstance. Check existing data before adding the index, and coordinate the database constraint with application validation and error handling. Rails: Active Record Migrations.
Use a composite index only for a matching query shape
A multi-column index can support queries involving its columns, but column order affects which query patterns it serves. For example, an index on [:customer_id, :created_at] should be justified by queries that filter or sort in a compatible way. Review the actual predicates and ordering, then check the plan rather than treating a composite index as a general-purpose substitute for separate indexes.
How to inspect whether the database uses an index
Call .explain on the Active Record relation that represents the query of interest:
Order.where(customer_id: 42).order(:created_at).explainRead the returned plan for the chosen access path and estimated work. A plan describes how the database intends to execute that query for its current conditions and data; it is evidence for that case, not a guarantee that the index benefits other queries or the application’s complete workload. Rails documents explain support for PostgreSQL, MySQL, and MariaDB, with engine-specific differences. Rails 8.1 Active Record Querying.
For PostgreSQL, Rails documents additional analysis options, including:
Best Value
Order.where(customer_id: 42).order(:created_at).explain(:analyze, :buffers)Analysis options can execute the query to collect runtime information. Use them only when execution is appropriate for the query and environment; be particularly careful with expensive queries or production data. PostgreSQL-specific options and output should not be assumed to work identically on other adapters. Rails: Active Record and PostgreSQL.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choose index options for your database and deployment
The Rails API documents options including uniqueness, partial indexes, included columns, index methods, ordering, and algorithms. Their availability varies by adapter and database. For example, the API identifies partial indexes as supported by PostgreSQL and SQLite, and included columns as PostgreSQL-only. PostgreSQL’s guide explains that included columns can be available to index-only scans without serving as search keys; its full-text search guidance includes GIN-based examples. Confirm the syntax and behavior for the database actually used by your application. Rails 8.0 add_index API; Rails: Active Record and PostgreSQL.
Build a PostgreSQL index concurrently when appropriate
For PostgreSQL, Rails supports concurrent index creation through algorithm: :concurrently. Rails documents that concurrent creation cannot run inside a transaction, so the migration must disable the DDL transaction:
class AddIndexToOrdersOnCustomerId < ActiveRecord::Migration[8.1]
disable_ddl_transaction!
def change
add_index :orders, :customer_id, algorithm: :concurrently
end
endUse this PostgreSQL-specific option only with a compatible Rails and database setup. Because disabling the migration transaction changes deployment behavior, check the generated migration and operational procedure for your application’s version. See the Rails index API and migration guide.
Quick Recap
A practical decision checklist
- Identify the specific recurring query or integrity rule the index is meant to serve.
- Match the index’s columns, order, uniqueness, and any specialized options to that need.
- Check the query plan with
.explain; use engine-specific analysis only when executing the query is appropriate. - Weigh likely read benefits against storage and write overhead across the workload.
- Confirm adapter support and deployment behavior before running the migration, especially for database-specific methods or concurrent builds.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
