🚀 Indexing in Rails: Speed Up Your Queries

🧠 What is Indexing?

Indexing in Rails (and databases generally) is like a smart shortcut. Instead of scanning every row in a table, Rails (via your database) uses indexes to find data faster—just like looking up a word in a book using the index page instead of reading every page.

🧠 Detailed Explanation

Imagine you’re searching for a friend’s name in a phone book. Instead of reading every name from start to end, you use the index — the names are sorted alphabetically, so you jump directly to the section with the first letter. Fast, right?

The same concept applies to databases. An index is like a shortcut that helps your app quickly find the data it needs without checking every row in the table. Without an index, your database must scan the whole table — which gets slower as data grows.

In Rails, you use add_index in migrations to create these shortcuts. For example, if you search for users by email often, indexing the email column makes that search super fast.

Here’s what indexing helps with:

• 🔍 Quick Searches – Find rows fast using WHERE conditions.
• 📚 Sort Data – Improves ORDER BY performance.
• 🔗 Join Tables – Speeds up joins using foreign keys.
• 🚫 Prevent Duplicates – Use unique indexes to ensure no two users have the same email, for example.

Without indexes, your app will get slower as your database grows. So, indexing is not just a bonus — it’s essential for fast, scalable applications.

🧩 Types of Indexing in Rails (with Examples)

Rails supports several types of database indexes. Each serves a different purpose to optimize queries. Here’s a breakdown with simple explanations and examples:

1. 🔍 Single-Column Index

Use case: Speed up searches on one column.

Example: Searching for users by email.

add_index :users, :email

This improves queries like:

User.find_by(email: "test@example.com")

2. 🧱 Composite (Multi-Column) Index

Use case: Optimize queries that filter by multiple columns together.

Example: Searching posts by user_id and published status.

add_index :posts, [:user_id, :published]

This speeds up:

Post.where(user_id: 1, published: true)

3. 🚫 Unique Index

Use case: Ensure no duplicate values in a column or a set of columns.

Example: Prevent users from registering with the same email.

add_index :users, :email, unique: true

If someone tries to create a second user with the same email, it will fail at the database level.

4. ⏱ Partial Index (Conditional)

Use case: Index a subset of rows to reduce index size and improve write performance.

Example: Index only active records.

add_index :sessions, :user_id, where: "active = true"

This is useful when most records are inactive, but only active ones are queried often.

5. 📆 Index with Ordering (PostgreSQL Only)

Use case: Optimize ORDER BY queries.

Example: Speed up ordering users by last login time.

add_index :users, :last_logged_in_at, order: { last_logged_in_at: :desc }

Faster for:

User.order(last_logged_in_at: :desc)

6. 🏷 Functional Index (PostgreSQL Only)

Use case: Index based on the result of a function.

Example: Case-insensitive search on username.

add_index :users, "LOWER(username)", name: "index_users_on_lower_username"

This helps:

User.where("LOWER(username) = ?", "john")

7. 🔐 Full-Text Index (MySQL / PostgreSQL)

Use case: Search across large text fields (e.g., articles, comments).

Example (PostgreSQL):

execute <<-SQL
  CREATE INDEX index_articles_on_content_fts 
  ON articles 
  USING gin(to_tsvector('english', content));
SQL
  

Use with:

Article.where("to_tsvector('english', content) @@ plainto_tsquery(?)", "rails indexing")
  

8. 🚚 Foreign Key Index

Use case: Required for fast joins and foreign key relationships.

Example: Linking comments to posts.

add_reference :comments, :post, foreign_key: true
# OR
add_index :comments, :post_id
  

Improves:

Post.includes(:comments)

9. ⚖️ Polymorphic Index

Use case: For models with polymorphic associations.

Example: Comments belonging to different models.

add_index :comments, [:commentable_type, :commentable_id]

Optimizes:

Comment.where(commentable_type: "Post", commentable_id: 1)

10. 📈 Primary Key Index (Automatic)

Use case: Every table’s primary key (like id) is indexed by default.

Example: You don’t need to add add_index :users, :id — it's already indexed.

🛠️ How Indexing Works Behind the Scenes

Indexes in a database are like a smart lookup system. Behind the scenes, they use data structures like B-trees, hash tables, or GIN (Generalized Inverted Index for full-text search). These structures are built and maintained separately from your main table.

When you run a query with conditions like WHERE, ORDER BY, or JOIN, the database first checks if there’s an index available. If yes, it uses it to "jump" directly to the result instead of scanning every row.

1. 🔍 Single-Column Index

How it works: The database stores a B-tree of that column’s values with pointers to their row locations.

When you run: User.find_by(email: "john@example.com")
Instead of checking every email row-by-row, it uses the B-tree to jump directly to "john@example.com".

2. 🧱 Composite Index

How it works: A multi-level B-tree is created with a combination of the specified columns.

For add_index :posts, [:user_id, :status], the tree first sorts by user_id, then groups and sorts those by status.

Useful only when queries use leftmost columns of the index.

3. 🚫 Unique Index

How it works: Same as a normal index, but also enforces a constraint to disallow duplicates.

Insert/update operations must check this index to verify uniqueness — adds slight overhead but ensures data integrity.

4. ⏱ Partial Index (Conditional)

How it works: Only rows matching a WHERE clause are stored in the index.

For example: add_index :sessions, :user_id, where: "active = true" only includes rows where active is true.

Makes index smaller and faster, but only works for very specific query conditions.

5. 📆 Index with Ordering (PostgreSQL)

How it works: The index stores values in sorted order (ASC or DESC), helping the database quickly retrieve sorted results.

Useful for avoiding costly in-memory sorting operations.

6. 🏷 Functional Index (PostgreSQL)

How it works: The result of a function (e.g., LOWER(email)) is stored in the index, not the original column value.

When you search with LOWER(email) = 'john@example.com', it directly matches in the index instead of computing the function on every row.

7. 🔐 Full-Text Index

How it works (PostgreSQL): Uses a GIN index that stores a list of terms from the text field and the rows where each term appears.

This is extremely fast for text searches like: WHERE content @@ 'rails'

Great for blog titles, descriptions, comments, etc.

8. 🚚 Foreign Key Index

How it works: Standard B-tree index used to speed up joins between tables with belongs_to or has_many.

For example, add_index :comments, :post_id allows faster access to comments for a given post.

9. ⚖️ Polymorphic Index

How it works: Indexes both commentable_type and commentable_id together so Rails can quickly find the right associated object.

Used for models like comments, where they can belong to many different types (posts, photos, etc.).

10. 📈 Primary Key Index (Automatic)

How it works: Every primary key (usually id) gets a B-tree index automatically by the database engine.

This allows fast retrieval of individual rows using find(id).

⚙️ Algorithms Used for Indexing in Rails

Rails doesn’t directly control how indexes work — instead, it uses the underlying database (like PostgreSQL or MySQL), which uses specific indexing algorithms under the hood.

Here are the most common indexing algorithms used:

1. 🌳 B-Tree (Balanced Tree)

• Default algorithm in both PostgreSQL and MySQL.
• Used for most indexes: single, composite, unique, foreign key, etc.
• Works like a sorted tree structure, allowing fast lookup, insertion, and range queries.
• Time complexity: O(log n) for search, insert, and delete operations.

add_index :users, :email  # uses B-Tree by default

2. 🔄 Hash Index

• Used in PostgreSQL (optional) and MySQL (only for MEMORY tables).
• Faster than B-Tree for exact matches, like = queries.
• Cannot perform range queries (e.g., BETWEEN, <, >).
• Not used by default due to limitations (e.g., no ordering).

# PostgreSQL example (not common)
CREATE INDEX index_users_on_email_hash USING HASH (email);
  

3. 📚 GIN (Generalized Inverted Index)

• Used for full-text search and complex data types like arrays or JSON.
• Stores a reverse map of terms → matching rows (like a word index in a book).
• Slower to update but very fast for text-based search.
• Available only in PostgreSQL.

CREATE INDEX index_articles_on_content_fts 
ON articles USING GIN (to_tsvector('english', content));
  

4. 🧪 GiST (Generalized Search Tree)

• Used for indexing geometric data, range types, or full-text search (alternative to GIN).
• Supports more flexible queries like overlaps or distance conditions.
• Less efficient than GIN for raw text but useful for spatial search.

5. 🧵 BRIN (Block Range Index)

• Used in PostgreSQL for very large tables.
• Indexes ranges of blocks (not individual rows), which makes them very small and fast to build.
• Ideal for time-series data or columns with natural ordering (like created_at).
• Less precise than B-Tree but much lighter in memory and disk.

Summary Table

🧾 Example of Indexing in a Database Table

Let’s say you have the following users table in your Rails application:

+----+-----------------------+------------+
| id | email                 | created_at |
+----+-----------------------+------------+
| 1  | alice@example.com     | 2023-01-01 |
| 2  | bob@example.com       | 2023-01-02 |
| 3  | charlie@example.com   | 2023-01-03 |
+----+-----------------------+------------+

Now, if you frequently search for users by email, like:

User.find_by(email: "bob@example.com")

🟡 Without Index:

• The database performs a full table scan.
• It checks each row from top to bottom to find the match.
• This becomes slower as the table grows to thousands or millions of rows.

🟢 With Index:

• We create an index on email using a migration:

add_index :users, :email

• The database now builds a B-tree structure like:

Index: users_email_idx (B-tree)

         [alice@example.com]
                   \
                [bob@example.com]
                   \
              [charlie@example.com]
  

• Instead of scanning rows, the database jumps directly to the correct node.
• Lookup time is reduced from O(n) to O(log n).

🧪 To View Indexes in PostgreSQL:

Connect to your database and run:

\d users

Output:

Indexes:
  "index_users_on_email" UNIQUE, btree (email)
  "users_pkey" PRIMARY KEY, btree (id)
  

🧪 To View Indexes in MySQL:

Run this SQL command:

SHOW INDEXES FROM users;

This will show index names, types (btree/hash), columns indexed, and uniqueness.

🕵️ How to Check if a Query is Using an Index or Not

Indexes make queries faster — but how do you know if your query is actually using an index? Here’s how you can check it using your database or Rails tools.

🔍 In PostgreSQL

Use the EXPLAIN ANALYZE command before your SQL query:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

If index is used: You’ll see something like:

Index Scan using index_users_on_email on users  (cost=0.15..8.17)

If not: You’ll see:

Seq Scan on users  (cost=0.00..42.00)

“Seq Scan” means full table scan — no index used.

🔍 In MySQL

Use the EXPLAIN keyword before your query:

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Look for the column key in the output:

• If key = NULL: no index used ❌
• If key = index name: index is used ✅

🔍 In Rails (ActiveRecord)

Rails doesn't directly show index usage, but you can print the SQL and analyze it manually:

User.where(email: "john@example.com").explain

This will output something like:

EXPLAIN for: SELECT "users".* FROM "users" WHERE "users"."email" = $1 [["email", "john@example.com"]]
Index Scan using index_users_on_email on users
  

🧪 Example Output Comparison

Index Scan using index_users_on_email on users

Seq Scan on users

📌 Tip

Even if you have an index, the database may not use it if:

• The table is too small (sequential scan may be faster)
• The column has many duplicate values (low selectivity)
• You are using functions like LOWER() without a functional index

🛠️ How to Debug Indexing in Rails

Even if you’ve added indexes, your queries might still be slow. That’s when debugging indexing becomes important. Here's a step-by-step guide to identify and fix indexing issues.

🔎 1. Check if the Index Exists

In PostgreSQL:

\d users

In MySQL:

SHOW INDEX FROM users;

In Rails Console:

ActiveRecord::Base.connection.indexes(:users)

🧪 2. Use EXPLAIN or EXPLAIN ANALYZE

Check whether the index is used or not:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

In Rails:

User.where(email: "test@example.com").explain

📉 3. Look for "Seq Scan" (Bad) vs "Index Scan" (Good)

• Seq Scan: Full table scan — no index is being used.
• Index Scan or Index Only Scan: Index is being used correctly.

🚫 4. Avoid Functions Without Functional Index

If your query uses a function like:

WHERE LOWER(email) = 'test@example.com'

Normal indexes won’t be used. Instead, create a functional index:

add_index :users, "LOWER(email)", name: "index_users_on_lower_email"
  

📊 5. Check Column Selectivity

If the column has mostly the same value (e.g., active is true for 99% of users), the index may not be used. Indexes work best when column values are varied.

🔄 6. Rebuild or Refresh Index

In PostgreSQL:

REINDEX INDEX index_users_on_email;

Useful when table changes significantly (e.g., mass updates or deletions).

🧹 7. Remove Duplicate or Unused Indexes

Too many indexes can slow down writes (INSERT/UPDATE). Identify unnecessary ones:

rails dbconsole
\d+ users
  

📈 8. Benchmark With and Without Index

You can manually remove an index, run a query, and compare performance:

remove_index :users, :email
# Run query
add_index :users, :email
# Re-run query and compare timing
  

🛑 9. Watch for Multi-Column Index Mistakes

In a composite index (user_id, status), queries must filter by user_id first. This is called the "leftmost column rule".

# ✅ Uses index
Post.where(user_id: 1, status: "published")

# ❌ Won’t use index
Post.where(status: "published")
  

🧰 10. Use PgHero or Database Tools

• PgHero: A gem to analyze slow queries, unused indexes, and index bloat.
• PgAdmin/MySQL Workbench: GUI tools for monitoring index usage.

gem 'pghero'

Then run pghero dashboard or queries to detect index issues.

🔄 Flow of Indexing in Rails and Where to Use It

🧭 Flow: How Indexing Works in a Rails Application

1. 1. Identify the query bottlenecks
   Use logs, `EXPLAIN`, or tools like PgHero to find slow queries.
2. 2. Analyze query patterns
   Ask: which columns are filtered, joined, or sorted most often?
3. 3. Add the appropriate index
   Use single, composite, unique, or partial indexes based on the query.
4. 4. Run migration
   Add your index via Rails migration: add_index.
5. 5. Test and benchmark
   Use .explain in Rails or database tools to verify index usage.
6. 6. Monitor and maintain
   Clean up unused or duplicate indexes and monitor bloat regularly.

📍 Where Indexing is Used in a Rails Application

Indexing isn’t just for speed — it’s about scaling, enforcing data rules, and building real-world features. Here’s where indexing is commonly used in Rails:

• 🔐 User authentication: email, username, reset_password_token
• 🔄 Relationships & foreign keys: user_id, post_id for belongs_to/has_many
• 🔄 Polymorphic associations: commentable_type, commentable_id
• 📊 Analytics & dashboards: Index created_at, user_id, and event_type
• 📦 E-commerce & filtering: category_id, price, in_stock, status
• 🔍 Search & lookup: slug, order_number, tracking_id
• ✉️ Invitations and tokens: invitation_token, access_token
• 📂 Admin dashboards: Sorting and filtering by created_at, role, approval_status
• 📃 Pagination + cursor-based queries: id, created_at, updated_at
• 🔎 Full-text search: content, description, title (PostgreSQL + GIN)

🧠 Example Use Case: Admin Dashboard Filters

Admin panel filters user registrations by date and status:

User.where(status: "active").order(created_at: :desc)

You should add this index:

add_index :users, [:status, :created_at]

🧠 Example Use Case: Product Search by Slug

When using slugs in URLs:

add_index :products, :slug, unique: true

This ensures fast lookups and enforces uniqueness.

🧾 20 Detailed Examples of Indexing in Rails

These examples show real-world scenarios of how and why to use different types of indexes in a Rails application.

1. Index on email (single column):
   Speeds up user lookups by email.

   add_index :users, :email

2. Unique index on username:
   Ensures no duplicate usernames are saved.

   add_index :users, :username, unique: true

3. Composite index on user_id and status:
   Optimizes queries like where(user_id: x, status: y).

   add_index :posts, [:user_id, :status]

4. Index on created_at for sorting:
   Improves order(created_at: :desc) performance.

   add_index :articles, :created_at

5. Partial index on active users:
   Faster lookups for common filters.

   add_index :users, :email, where: "active = true"

6. Case-insensitive email search:
   Useful when using LOWER(email) in queries.

   add_index :users, "LOWER(email)", name: "index_users_on_lower_email"

7. Foreign key index on post_id in comments:
   Required for efficient joins.

   add_index :comments, :post_id

8. Index on slug column:
   Speeds up friendly URL lookups.

   add_index :posts, :slug, unique: true

9. Composite index for order filtering:
   Used for combined filters on payment status and user.

   add_index :orders, [:user_id, :payment_status]

10. Unique composite index on user+project name:
    Prevents users from having duplicate project names.

    add_index :projects, [:user_id, :name], unique: true

11. Polymorphic index for commentable:
    Required for polymorphic associations like comments on posts/images.

    add_index :comments, [:commentable_type, :commentable_id]

12. Full-text index (PostgreSQL):
    Enables natural language search.

    
    execute <<-SQL
      CREATE INDEX index_articles_on_content_fts 
      ON articles 
      USING gin(to_tsvector('english', content));
    SQL
          

13. Index on JSONB key (PostgreSQL):
    Querying JSONB fields with GIN index.

    add_index :events, :metadata, using: :gin

14. Index on reset_password_token:
    Speeds up password reset lookups.

    add_index :users, :reset_password_token, unique: true

15. Index for soft delete column:
    If using `deleted_at` column for soft delete.

    add_index :users, :deleted_at

16. Index on session token:
    Useful for quick session validation.

    add_index :sessions, :token, unique: true

17. Index on user_id and created_at:
    Helps with dashboard filtering and analytics.

    add_index :logs, [:user_id, :created_at]

18. Index on status column:
    For frequently filtered enums like status: [:pending, :active].

    add_index :subscriptions, :status

19. Index on invitation_token:
    Fast lookup when accepting invitations.

    add_index :invitations, :token, unique: true

20. Composite index with descending order (PostgreSQL):
    Helps with sorted leaderboards.

    
    add_index :scores, [:user_id, :points], order: { points: :desc }
          

🔧 Gems and Tools for Indexing in Rails

Rails supports index creation out-of-the-box via migrations, but there are additional gems and tools that help you analyze, monitor, and manage indexing efficiently.

1. ✅ ActiveRecord Migrations (Built-in)

• Purpose: Create, remove, and manage indexes using add_index, remove_index.
• Included by default in Rails
• Example:

  add_index :users, :email, unique: true

2. 📊 PgHero

• Gem: gem 'pghero'
• Purpose: Analyze slow queries, unused indexes, index hit rate, and index bloat (PostgreSQL only).
• UI: Comes with a dashboard accessible via browser.
• Docs: github.com/ankane/pghero

3. 🔍 Lol_dba

• Gem: gem 'lol_dba'
• Purpose: Scans models and tells you which foreign keys are missing indexes.
• Command: bundle exec lol_dba db:find_indexes
• Docs: github.com/plentz/lol_dba

4. 🧪 Bullet

• Gem: gem 'bullet'
• Purpose: Detects N+1 queries, which often indicate missing indexes on associations.
• Useful for: Development environments.
• Docs: github.com/flyerhzm/bullet

5. 🐘 PgExtras

• Gem: gem 'pg_extras'
• Purpose: Provides SQL helpers to inspect unused indexes, index size, bloat, etc.
• Use: Console or Web UI (rails pg_extras:index_bloat)
• Docs: github.com/pawurb/pg_extras

6. 🖥 Adminer / PgAdmin / MySQL Workbench

• Type: GUI database management tools
• Purpose: Visualize indexes, inspect query plans, create/delete indexes manually.
• PostgreSQL: PgAdmin
• MySQL: MySQL Workbench
• All-in-one: Adminer (minimal web-based SQL tool)

7. 🚨 Scout APM / NewRelic

• Type: Application performance monitoring (APM) tools
• Purpose: Track slow database queries and help identify where indexes are needed.
• Useful for: Production monitoring and query tracing

✅ Best Implementation of Indexing in Rails – With Full Detail

📌 Use Case: Admin Dashboard Filtering Users by Status and Signup Date

Your app has a users table with 1 million rows. Admins regularly filter users by their status (e.g., "active", "inactive") and by created_at date range for reporting:

User.where(status: "active").where("created_at > ?", 30.days.ago)

🚨 Problem Without Index

• Query takes 5–10 seconds on large tables.
• Rails generates a Seq Scan (sequential scan), scanning all 1 million rows.
• Users complain of slow reports and dashboards.

🎯 Solution: Composite Index on status and created_at

This index allows the database to:

• Quickly filter users by status
• Efficiently narrow down users within a date range
• Use the B-tree to jump directly to matching rows

🛠 Rails Migration

rails generate migration AddIndexToUsersStatusAndCreatedAt

class AddIndexToUsersStatusAndCreatedAt < ActiveRecord::Migration[7.0]
  def change
    add_index :users, [:status, :created_at], name: "index_users_on_status_and_created_at"
  end
end

Run the migration:

rails db:migrate

🧪 How to Verify Index Is Used

User.where(status: "active").where("created_at > ?", 30.days.ago).explain

Expected output (PostgreSQL):

Index Scan using index_users_on_status_and_created_at on users

⚡ Result

• Query speed improved from 7s → 50ms
• CPU usage dropped significantly under load
• Dashboard reports became usable again

📌 Best Practice Notes

• ✅ Always index columns used together in WHERE or ORDER BY
• 🚫 Don’t add too many indexes on every column — they slow down inserts/updates
• ✅ Name your indexes clearly (e.g., index_users_on_status_and_created_at)
• 🧠 Use EXPLAIN or Rails .explain to verify query performance

🧠 20 Technical Questions & Answers on Indexing in Rails

1. Q1: How do you add a basic index in Rails?
   A: Use add_index in a migration.

   add_index :users, :email

2. Q2: How do you make an index unique?
   A: Add unique: true to enforce no duplicates.

   add_index :users, :username, unique: true

3. Q3: How can I check if a query is using an index?
   A: Use:

   User.where(email: "test@example.com").explain

   Look for Index Scan in output.
4. Q4: What is a composite index?
   A: An index on multiple columns.

   add_index :orders, [:user_id, :status]

5. Q5: Why would a query not use an index?
   A: Possible reasons:
   • Table is too small (full scan is faster)
   • Column has low selectivity (same values)
   • You're using a function without a functional index
6. Q6: How do you index a foreign key?
   A: Rails doesn't auto-index foreign keys. You must add:

   add_index :comments, :post_id

7. Q7: How do you index a polymorphic association?
   A: Index both type and id:

   add_index :comments, [:commentable_type, :commentable_id]

8. Q8: How do you add a partial index in Rails?
   A: Use a where clause.

   add_index :users, :email, where: "active = true"

9. Q9: What is the default index algorithm in PostgreSQL?
   A: B-tree, used for most general indexes.
10. Q10: What command lists all indexes in Rails console?
    A:

    ActiveRecord::Base.connection.indexes(:users)

11. Q11: How do you remove an index?
    A: Use remove_index in a migration.

    remove_index :users, :email

12. Q12: How do you optimize a case-insensitive search?
    A: Use a functional index:

    add_index :users, "LOWER(email)", name: "index_users_on_lower_email"

13. Q13: How can I see the index size in PostgreSQL?
    A: Use:

    
    SELECT 
      relname AS index_name,
      pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
    FROM pg_stat_user_indexes;
          

14. Q14: How does index affect insert performance?
    A: Slightly slows down inserts/updates because indexes must also be updated.
15. Q15: Can Rails add index when using add_reference?
    A: Yes, with:

    add_reference :comments, :post, foreign_key: true, index: true

16. Q16: What’s the “leftmost column rule” for composite indexes?
    A: Composite indexes are only used when filtering by the left-most column(s) in the index.

    
    # Will use index
    where(user_id: 1, status: "active")
    
    # Won’t use index
    where(status: "active")
          

17. Q17: How do I index JSONB fields in PostgreSQL?
    A:

    add_index :events, :metadata, using: :gin

18. Q18: Which gem helps find missing indexes?
    A: lol_dba

    bundle exec lol_dba db:find_indexes

19. Q19: How do I debug index bloat or usage?
    A: Use PgHero or pg_extras:

    rails pg_extras:index_bloat

20. Q20: Can I add ordering to an index?
    A: Only in PostgreSQL:

    
    add_index :scores, [:user_id, :points], order: { points: :desc }
          

🔄 Alternatives & Related Concepts

• Materialized Views: Pre-computed query results (mainly in PostgreSQL)
• Denormalization: Storing data in a redundant format to reduce joins
• Counter Cache: Caching count of associated records (e.g., comments_count)

📊 Real-World Case Study

✅ Best Practices for Indexing in Rails – With Examples

Indexing can drastically improve performance—but only if used correctly. Here are the most important best practices every Rails developer should follow, with examples.

1. 🎯 Index Columns Used in WHERE, ORDER BY, or JOIN

If a column is frequently used in filters or joins, it should have an index.

# This is common in authentication
User.find_by(email: "test@example.com")

# Add an index:
add_index :users, :email
  

2. 🔗 Always Index Foreign Keys

Rails does not automatically add indexes for belongs_to. Without indexes, JOIN queries are slow.

# In a comments table:
add_index :comments, :post_id
  

3. 🔐 Use Unique Indexes for Unique Fields

Validations are not enough — database-level uniqueness is a must.

# Enforce unique usernames:
add_index :users, :username, unique: true
  

4. 🧠 Use Composite Indexes for Multiple Filters

If you often filter by multiple columns together, use a multi-column index.

# Filtering orders by user and status
Order.where(user_id: 1, status: "paid")

# Use:
add_index :orders, [:user_id, :status]
  

5. ⚡ Avoid Indexing Columns with Low Selectivity

Don’t index columns like gender or is_active that have very few distinct values. Indexes won't help much.

6. 🎛 Use Partial Indexes for Conditional Queries

If you're always querying a subset of data (like active users), partial indexes reduce size and improve performance.

add_index :users, :email, where: "active = true"
  

7. 🧹 Clean Up Unused or Redundant Indexes

Too many indexes slow down write operations. Remove duplicates.

# Check current indexes:
ActiveRecord::Base.connection.indexes(:users)

# Remove unused:
remove_index :users, :old_column
  

8. 🪜 Use the "Leftmost Column" Rule for Composite Indexes

Only the leftmost columns in a composite index are used for filtering.

# Index:
add_index :logs, [:user_id, :event_type]

# Works:
Log.where(user_id: 1, event_type: "click")

# Won’t use index:
Log.where(event_type: "click")
  

9. 🔎 Add Functional Indexes for Expressions

If your query uses a function (e.g., LOWER()), a normal index won’t help.

add_index :users, "LOWER(email)", name: "index_users_on_lower_email"
  

10. 📈 Monitor Index Usage

Use PgHero, lol_dba, or Rails .explain to monitor and verify usage.

User.where(email: "a@example.com").explain
# Or use PgHero dashboard