PostgreSQL Index Types Explained with Real Rails Examples

Part-1 Understanding Indexes before learning Index Types

Now that we understand what indexes are and why they matter, let’s explore the different types of indexes available in PostgreSQL and when to use them in Rails applications.

1. Single Column Index

The simplest and most commonly used index.

Query

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

Migration

add_index :users, :email

SQL Generated

CREATE INDEX index_users_on_email ON users(email);

When to Use

• Email lookups
• SKU lookups
• UUID searches
• Foreign keys

Real Example

Product.find_by(sku: params[:sku])

Without an index, PostgreSQL scans the entire table.

With an index, PostgreSQL can jump directly to the matching row.

2. Unique Index

A unique index improves lookup performance while also preventing duplicate values.

Migration

add_index :users, :email, unique: true

What It Solves

Prevents:

[e-mail]
[e-mail]

Common Use Cases

• Email addresses
• Usernames
• External IDs
• API tokens

Rails Validation vs Database Constraint

Many developers write:

validates :email, uniqueness: true

This is not enough.

Always add a database unique index because validations can be bypassed during race conditions.

3. Composite (Multi-column) Index

Used when queries filter using multiple columns.

Query

Order.where(user_id: 1, status: "paid")

Migration

add_index :orders, [:user_id, :status]

Important Rule

Index order matters.

[:user_id, :status]

Works efficiently for:

WHERE user_id = 1

WHERE user_id = 1
AND status = 'paid'

But not for:

WHERE status = 'paid'

Real Example

In e-commerce:

Order.where(user_id: current_user.id, status: "completed")

4. Partial Index

Indexes only a subset of rows.

Migration

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

Example Query

User.where(active: true)

Why Use It?

Suppose:

10 million users
9 million inactive 
1 million active

Creating an index only for active users makes the index:

• Smaller
• Faster
• Less memory intensive

Common Uses

• Soft deletes
• Active records
• Published content
• Pending jobs

5. Index with Order

Optimizes sorting operations.

Query

Product.order(created_at: :desc)

Migration

add_index :products, :created_at, order: { created_at: :desc }

Benefits

Useful for:

Product.order(created_at: :desc).limit(20)

Common in:

• News feeds
• Activity logs
• Product listings

6. GIN Index

GIN stands for Generalized Inverted Index.

Used for:

• JSONB
• Arrays
• Full-text search

JSONB Example

add_index :users, :preferences, using: :gin

Query

WHERE preferences @> '{"theme":"dark"}'

Real Rails Example

User.where("preferences @> ?", { theme: "dark" }.to_json)

Without GIN indexes, JSONB searches become slow as data grows.

7. Full Text Search Index

PostgreSQL can act as a search engine.

Migration

add_index :articles, "to_tsvector('english', content)", using: :gin, name: "index_articles_on_content_search"

Query

SELECT * FROM articles WHERE to_tsvector('english', content) @@ plainto_tsquery('rails indexing');

Useful For

• Blogs
• Documentation sites
• Product search
• Knowledge bases

8. Concurrent Index

One of the most important production techniques.

Problem

Running:

add_index :users, :email

on a huge table can lock writes.

Solution

class AddIndexToUsersEmail < ActiveRecord::Migration[8.0] 
  disable_ddl_transaction!
  def change 
    add_index :users, :email, algorithm: :concurrently 
  end 
end

Benefits

• No downtime
• No table lock
• Safe for production

Real World

Never add indexes to large production tables without considering concurrent creation.

9. Expression Index

Indexes a calculated value instead of a column.

Query

User.where("LOWER(email) = ?", email.downcase)

Migration

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

Benefits

Supports fast:

• Case-insensitive searches
• Date transformations
• String manipulations

10. Covering Index (INCLUDE)

Introduced to reduce table lookups.

Migration

add_index :orders, :user_id, include: [:status, :total]

Why Useful?

Query:

Order.select(:status, :total) .where(user_id: current_user.id)

PostgreSQL can answer directly from the index.

This is called an Index Only Scan.

Benefits

• Fewer disk reads
• Faster queries

11. BRIN Index

BRIN stands for Block Range Index.

Designed for huge tables.

Migration

add_index :events, :created_at, using: :brin

Best For

• Logs
• Analytics data
• Audit records
• Time-series events

Why?

Instead of storing every value, BRIN stores summaries for ranges of pages.

Result:

• Extremely small indexes
• Very low maintenance overhead

Example

100 million events

A BRIN index may be only a few MBs compared to hundreds of MBs for a B-tree index.

12. Hash Index

Optimized for equality lookups.

Migration

add_index :users, :email, using: :hash

Query

WHERE email = '[e-mail]'

Limitation

Does not support:

<
>
BETWEEN
ORDER BY

Because B-tree indexes support more operations, Hash indexes are rarely used.

Which Index Should You Choose?

Final Thoughts

Indexes are not about adding them everywhere.

A good database engineer first identifies:

1. Slow queries
2. Query patterns
3. Filter columns
4. Sort columns
5. Join columns

Then chooses the index that matches the workload.

Always verify improvements using:

EXPLAIN ANALYZE

and remember that every index speeds up reads but adds overhead to writes. The best indexing strategy is the one that balances both.

Concurrent Indexes: https://railswithyashika.hashnode.dev/postgresql-concurrent-indexes-in-rails-avoiding-downtime-in-production

Originally published at https://railswithyashika.hashnode.dev on July 1, 2026.