Learn how database indexes work in Rails, when to add them, how to use migrations, and how indexes improve query performance.
• 21 min read
• 21 min read
If you have ever waited too long for a page to load and traced it back to a slow database query, you have met the problem indexes exist to solve. A database index is a separate structure that helps your database find rows quickly instead of checking every single one, similar to the index at the back of a textbook. Without it, finding a topic means flipping through the entire book. With it, you jump straight to the right pages.
For Rails applications, indexes matter because almost every request eventually becomes a SQL query, and many of those queries filter, sort, or join on specific columns. As tables grow from hundreds of rows to hundreds of thousands, the difference between scanning every row and jumping straight to the right ones becomes significant.
That said, indexes are not free. They take up storage space, and every insert, update, or delete has to update any indexes on that table too. Adding an index is a trade-off, not a magic switch.
This guide covers what indexes are, how they work conceptually, how to add and remove them with Rails migrations, and how to decide when one actually helps, including foreign keys, unique and composite indexes, sorting, filtering, N+1 queries, PostgreSQL and SQLite differences, and a practical process for evaluating a slow query.
A database index is a structure the database maintains alongside a table to make lookups on specific columns faster. Without one, the database performs a full table scan, reading every row to find the ones that match your query.
Imagine a users table with a million rows, and a query for the user whose email is [email protected]. Without an index on email, the database checks each row one by one. With an index, it can jump to the matching row far more directly, since the values are already kept in a searchable order.
This matters more as tables grow. On a table with a hundred rows, a full scan barely registers. On a table with millions of rows, that same scan can become a real bottleneck, especially for frequently run queries. Indexes do not change what a query returns, only how efficiently the database finds the answer.
Most relational databases build indexes using a tree-like structure that keeps indexed values in sorted order, similar to finding a name in a phone book by repeatedly narrowing the remaining pages instead of reading from the start.
When you run a query, the database's query planner decides how to execute it. If a suitable index exists on the columns in your WHERE clause, JOIN, or ORDER BY, the planner may choose an index scan instead of a full table scan. If no useful index exists, or a full scan is actually cheaper for that query, it reads the whole table instead.
An unindexed lookup means "check everything." An indexed lookup means "jump to the relevant section." Rails does not decide which one happens, the database does, based on the indexes defined and the specifics of the query.
Rails applications lean heavily on Active Record, and every find_by, where, and order call compiles down to a plain SQL statement, subject to the same indexing rules as if you had written raw SQL yourself.
Common operations that benefit from thinking about indexes include finding a user by email during login, looking up records by a foreign key like user_id, filtering orders by status, searching a timestamp range, sorting records for a paginated list, finding a record by UUID, and enforcing that a value like a username is unique.
You do not need to think in raw SQL day to day, but knowing that Active Record queries become SQL queries explains why some parts of your schema benefit from indexes and others do not.
Rails migrations are the standard way to add indexes. A basic migration to index the email column on users looks like this:
class AddIndexToUsersEmail < ActiveRecord::Migration[8.0]
def change
add_index :users, :email
end
end
add_index generates the SQL needed to create an index on the email column of users. When you run bin/rails db:migrate, Rails executes that SQL, and the database builds and maintains the index from then on.
To also enforce that no two users share the same email, add a uniqueness constraint at the same time:
add_index :users, :email, unique: true
A unique index is appropriate any time a column's values must be distinct across the table, such as emails, usernames, or external identifiers. It both speeds up lookups and prevents duplicate values from being inserted, at the database level.
Removing an index uses remove_index in a migration:
class RemoveIndexFromUsersLegacyId < ActiveRecord::Migration[8.0]
def change
remove_index :users, :legacy_id
end
end
An index is worth removing when it no longer supports any real query pattern, such as one left over from a removed feature, or a duplicate of another index covering the same columns. Before removing an index, check whether any code, background jobs, or reports still rely on it, since doing so can turn a query back into a full table scan.
Foreign keys are one of the most common places developers forget to add an index, and one of the places it matters most. Consider this association:
class Post < ApplicationRecord
belongs_to :user
end
Under the hood, posts has a user_id column referencing users. Every user.posts call filters posts by user_id, and without an index there, that query scans the whole table.
Rails makes this easy to get right from the start. When you generate a migration using references, it adds the index automatically:
class CreatePosts < ActiveRecord::Migration[8.0]
def change
create_table :posts do |t|
t.references :user, null: false, foreign_key: true
t.string :title
t.timestamps
end
end
end
Indexed foreign keys are especially useful for joins, filtering by parent, and deleting or updating related records.
Unique indexes prevent duplicate values at the database level, which matters for emails, usernames, external IDs, UUIDs, and other business identifiers that must be distinct.
A Rails model validation like validates :email, uniqueness: true runs in Ruby before saving, and under concurrent requests two can pass that check at nearly the same moment and both insert the same email. A unique index closes that gap, since the database rejects the second insert outright.
add_index :users, :email, unique: true
In practice, most Rails applications should use both: the model validation for a friendly error message, and the database index as the real guarantee.
A composite index spans more than one column. For example, if you frequently query orders by both user_id and status, a composite index can support that pattern directly:
add_index :orders, [:user_id, :status]
Composite indexes make sense when your application regularly filters or sorts using the same combination of columns together, rather than each column separately. They are not simply two single-column indexes combined; the database treats them as a single ordered structure, which is why column order matters and why query patterns should guide which columns you combine.
Order matters in a composite index because the database efficiently uses the leading columns, similar to a phone book sorted by last name then first name: it helps you find "Smith" quickly but does not directly help you find everyone named "John" across all last names.
Consider an index on [:user_id, :status, :created_at]. It works well for queries filtering by user_id alone, by user_id and status together, or by all three. It is less useful for a query filtering only by status or created_at, since those are not the leading part of the index. Put the column your queries filter on most consistently first, and build outward from actual query patterns.
Indexes can also help with ORDER BY clauses. A query like:
Post.order(created_at: :desc)
may benefit from an index on created_at, since the database can potentially read rows in the index's stored order rather than sorting from scratch. Whether that happens depends on the query planner, table size, and whether other parts of the query already narrow the result set. An index does not guarantee sorting becomes free, only that the database has an efficient option available.
WHERE conditions are the most common place indexes help. Examples include:
User.where(email: params[:email])
Order.where(status: "pending")
Post.where(published: true)
Columns that show up frequently in WHERE clauses, especially ones checked on every request like login lookups or status filters, are good candidates for indexing. Not every column deserves one, though: indexing everything adds write overhead without necessarily helping reads, especially for columns rarely filtered on or that do not narrow the result set meaningfully.
Every Active Record query eventually becomes SQL. These calls:
User.find_by(email: "[email protected]")
Order.where(status: "pending")
Post.where(user_id: user.id)
conceptually generate SQL statements with WHERE clauses matching the conditions you passed in. The database then decides, based on available indexes, whether to use an index scan or a full table scan. For more on optimizing how Active Record builds and executes queries, see the CodeCurious guide on optimizing Active Record queries in Rails, which covers patterns beyond indexing, including eager loading and query shape.
Rails associations rely heavily on indexed foreign keys to stay efficient. A belongs_to queries by the foreign key on the child table; a has_many or has_one queries the same column from the other direction. Many-to-many relationships through a join table depend on indexes on both foreign key columns to keep lookups fast in either direction.
class Tag < ApplicationRecord
has_and_belongs_to_many :posts
end
For a join table like posts_tags, indexing both post_id and tag_id supports queries from either side of the relationship, which matters as the number of tags and posts grows.
It is worth being direct here: indexes and N+1 query problems are different issues, and an index does not fix one. An N+1 query happens when your code runs one query to fetch a list of records, then an additional query per record for related data, such as looping over posts and calling post.user.name without eager loading.
An index can make each individual query faster, but it does not reduce how many queries run. A thousand small, fast, indexed queries is still a performance problem, just a smaller one per query. The real fix is eager loading with includes, preload, or eager_load to fetch related records in one or two queries instead of one per record.
Whether an index actually speeds up a given query depends on more than its existence. The query planner weighs table size, the selectivity of the indexed column, and the query's specific conditions before choosing between a full table scan and an index scan.
A column with low selectivity, where most rows share the same value, may not benefit much from an index, since scanning the whole table can sometimes be just as fast. This is why adding an index does not automatically make a query faster, and it can be wrong to promise a specific speed improvement without measuring.
Indexes are often useful for columns that show up repeatedly in your real query patterns, including:
WHERE clausesJOIN conditionsThe common thread: base the decision on how your application actually queries data, not on guessing which columns seem important.
Indexes are not automatically useful everywhere. Cases where an index may not help include:
Every index adds storage overhead and slows writes, so an index not supporting a real query pattern is a cost without a benefit.
Indexes are a trade-off between read and write performance. Every INSERT, UPDATE, or DELETE has to update every index on that table, not just the row itself, so a table with many indexes can see slower writes.
Indexes also consume disk space, sometimes substantially. A database with far more indexes than it needs can become inefficient overall, even with fast individual reads, because write and storage costs pile up across the whole table.
Cardinality refers to how many distinct values a column has relative to the number of rows. A boolean column like published has very low cardinality, since only two values are possible. A status or country column is also relatively low cardinality compared to something like an email address, which is nearly unique per row.
Indexing a low-cardinality column is not automatically useful, because when most rows share the same value, an index does not narrow the search much, and the database may decide a full scan is just as fast. Whether it helps depends on table size, query patterns, the database engine, and how the data is actually distributed, not on cardinality alone.
Some databases support partial indexes, which index only the rows matching a specific condition rather than the entire table. This is useful when your queries typically only care about a subset of rows, such as active users.
PostgreSQL supports this directly:
add_index :users, :email, where: "active = true"
This creates a smaller, more targeted index covering only active users, which can be more efficient than indexing every row when most queries only look at active ones. Partial indexes are a database-specific feature; SQLite supports them too, but the exact syntax and behavior can differ, so portability here is not seamless.
PostgreSQL is a common production choice for Rails apps and offers a solid set of indexing features. By default it uses B-tree indexes, which work well for equality checks, range queries, and sorting, covering most everyday Rails query patterns. It also supports unique, composite, and partial indexes as already discussed, along with a capable query planner that inspects table statistics to decide how to execute a query.
PostgreSQL offers more specialized index types for particular use cases, such as full-text search, though those are worth exploring separately rather than turning this guide into a PostgreSQL deep dive. For a broader comparison of how PostgreSQL and SQLite differ for Rails apps, see SQLite vs PostgreSQL: which should you use.
SQLite has become an increasingly realistic option for Rails production apps, especially with Rails 8's improvements around SQLite deployment. It supports standard B-tree style indexes that behave similarly to PostgreSQL's for most everyday queries, and the same core trade-off applies: indexes speed up reads but add overhead to every write.
Because SQLite handles concurrent writes differently than PostgreSQL, write overhead from indexes deserves closer attention in write-heavy SQLite applications. The CodeCurious guide on optimizing SQLite for Rails 8 production walks through production-specific considerations in more depth.
Before adding a new index, it helps to see what already exists. Rails keeps your schema, including indexes, in db/schema.rb, which you can inspect directly, or regenerate with:
bin/rails db:schema:dump
Opening db/schema.rb shows every add_index call currently defined, the quickest way to check whether a column is already indexed or a composite index already covers a pattern you were about to duplicate. For a closer look at index size and usage statistics, you can also connect directly to your database and use its own tools, such as PostgreSQL's \d commands in psql or SQLite's .schema command.
Rather than guessing, follow a consistent process:
Measure rather than guess. Adding indexes speculatively, without evidence a query needs one, tends to add write overhead without a matching benefit.
Most relational databases, including PostgreSQL and SQLite, support an EXPLAIN command that shows how the database plans to execute a query without actually running it. This is one of the most useful tools for deciding whether an index would help.
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
The output shows whether the database plans a sequential scan, reading every row, or an index scan, using an existing index to jump to matching rows. A sequential scan on a large table for a frequently run query is a strong signal worth investigating. You can run EXPLAIN directly against your database using its native client without needing deep query planner internals to get useful signal from it.
A few patterns show up repeatedly:
t.references with foreign_key: true so Rails adds the index automatically.db/schema.rb before adding one that overlaps an existing composite index.EXPLAIN is available and worth using before and after adding an index.A few realistic scenarios, with their trade-offs:
User email, checked on every login: add_index :users, :email, unique: true. Speeds up authentication lookups and blocks duplicate accounts, at the cost of slightly slower inserts.
A foreign key, used on every association query: add_index :comments, :post_id. Speeds up post.comments, at the cost of slightly slower comment inserts.
A composite index, used for a dashboard filter: add_index :orders, [:user_id, :status]. Speeds up filtering a user's orders by status, at the cost of extra write overhead on the table.
A timestamp, used for sorting a feed: add_index :posts, :created_at. May help sorting and range queries, though the planner ultimately decides whether it is used.
A partial index, focused only on active records: add_index :subscriptions, :user_id, where: "active = true". Keeps the index smaller and more targeted, at the cost of being PostgreSQL-specific in its exact syntax.
Removing an unused index, found by auditing db/schema.rb against real query logs: remove_index :users, :legacy_id. Reduces write overhead and storage with no read-side cost, since nothing depended on it.
Rails 8 does not change the fundamentals of how indexes work, but it is worth using current conventions in migrations. add_index, unique indexes, and composite indexes all work as described throughout this guide. What has shifted is the broader context: SQLite is now a realistic production option alongside PostgreSQL, so indexing decisions increasingly need to account for which database you are deploying with, since behavior can differ slightly between the two. Migrations should still use ActiveRecord::Migration[8.0] and current helpers like t.references rather than manually written foreign key columns.
Adding an index to a production database is not always as simple as running a migration during a deploy. On large tables, building an index can take real time, and depending on your database, it may lock the table against writes while it builds, which can affect live traffic.
Before adding an index to a large production table, consider the table's size, how long creation is likely to take, whether your deployment process can tolerate brief locking or reduced write throughput, and whether your database supports building the index concurrently to avoid blocking writes. It also helps to have a rollback plan and to monitor database load closely after the change ships, rather than assuming success just because the migration completed without error.
Your choice of database affects what indexing options are available. Both SQLite and PostgreSQL support the everyday basics covered here: standard, composite, and unique indexes, all defined through the same Rails migration syntax.
Where they diverge is in more advanced features, like the exact behavior of partial indexes and how each query planner makes decisions under load. Production workloads with heavy concurrent writes may also behave differently between the two in ways that go beyond indexing alone. If you are actively deciding between them for a project, the full comparison in SQLite vs PostgreSQL: which should you use goes into more detail than is useful to repeat here.
Before adding an index, it helps to ask:
WHERE condition?JOIN?db/schema.rb?EXPLAIN?What is a database index in Rails? A structure the database maintains to speed up lookups on specific columns, defined through migrations with add_index.
How do I add an index in Rails? Create a migration, call add_index :table_name, :column_name, then run bin/rails db:migrate.
Does Rails automatically create indexes? Rails indexes foreign keys automatically when you use t.references with foreign_key: true, but not every column.
Should every foreign key have an index? In most cases, yes, since foreign keys are used heavily in association queries and joins.
What is a unique index in Rails? An index that also enforces no two rows share the same value, created with add_index :table, :column, unique: true.
What is a composite index in Rails? An index spanning multiple columns, created with add_index :table, [:column_one, :column_two], useful when queries filter or sort by that combination together.
Do indexes make Rails queries faster? They can, for queries filtering, sorting, or joining on indexed columns, but the query planner ultimately decides whether an index is used.
Can too many indexes slow down Rails? Yes. Every index adds overhead to writes and increases storage use.
How do I check indexes in Rails? Inspect db/schema.rb, regenerate it with bin/rails db:schema:dump, or use your database's own inspection tools.
How do I know if a Rails query needs an index? Identify the slow query, inspect its SQL and execution plan with EXPLAIN, check frequency and table size, then measure before and after.
Does an index fix N+1 queries? No. Indexes can make individual queries faster, but N+1 problems require reducing query count through eager loading.
Are SQLite and PostgreSQL indexes the same? The everyday basics are similar, but advanced features like partial indexes and planner behavior can differ.
What is the difference between a database index and a Rails validation? A validation is an application-level check run before saving. An index, especially a unique index, is a database-level structure enforced regardless of what code calls into the database.
A database index is a structure that helps your database find rows faster by avoiding a full table scan, at the cost of extra storage and slightly slower writes. Indexes matter most on columns used often in WHERE clauses, joins, sorting, and foreign key associations, and Rails migrations make them straightforward to add and remove through add_index and remove_index.
The most important habit is basing indexing decisions on real query patterns rather than assumptions. Not every column needs an index, and adding one does not automatically make every related query faster. Indexes are one part of a larger performance picture that also includes query structure and eager loading for problems like N+1 queries.
As a practical next step: measure before you add an index, index intentionally based on how your application actually queries data, and keep monitoring production behavior afterward rather than treating an index migration as a one-time fix.