DEV Community: Yashika Vijayvargiya

PostgreSQL Index Types Explained with Real Rails Examples

Yashika Vijayvargiya — Thu, 02 Jul 2026 08:00:26 +0000

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: "john@example.com")

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:

john@example.com
john@example.com

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 = 'john@example.com'

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:

Slow queries
Query patterns
Filter columns
Sort columns
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.

Understanding Indexes Before Learning Index Types

Yashika Vijayvargiya — Wed, 01 Jul 2026 07:59:39 +0000

Before diving into different types of indexes, let’s understand why indexes are needed.

Imagine a library with 100,000 books.

If someone asks for a book named “Clean Code”, there are two ways to find it:

Without an Index

You start from the first shelf and check every book one by one until you find the required book.

This approach is similar to a Sequential Scan in PostgreSQL.

SELECT * FROM books
WHERE title = 'Clean Code';

PostgreSQL may need to scan every row in the table to find matching records.

As the table grows from thousands to millions of rows, query performance degrades significantly.

With an Index

Now imagine the library has a catalog that stores:

Clean Code → Shelf A12
The Pragmatic Programmer → Shelf B03
Refactoring → Shelf C08

Instead of checking every book, you first look at the catalog and immediately jump to the correct shelf.

This catalog is similar to a database index.

An index stores selected column values in a structure that allows PostgreSQL to locate rows much faster than scanning the entire table.

Why Not Index Every Column?

A common beginner question is:

If indexes make queries faster, why not create indexes everywhere?

Indexes improve read performance, but they come with a cost.

Whenever data changes, PostgreSQL must update both:

The table data
Every related index

For example:

User.create!(email: "john@example.com")

If the users table has 10 indexes, PostgreSQL must update all 10 indexes during the insert operation.

This means:

More storage usage
Slower INSERT operations
Slower UPDATE operations
Slower DELETE operations

Because of this trade-off, indexes should be added only for queries that are frequently executed and benefit from faster lookups.

How PostgreSQL Uses an Index

Suppose we have:

add_index :users, :email

And run:

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

Without an index:

Seq Scan on users

With an index:

Index Scan using index_users_on_email

The database can jump directly to matching records instead of examining every row.

A Simple Rule

Create an index when a column is frequently used in:

WHERE clauses
JOIN conditions
ORDER BY clauses
GROUP BY clauses

Examples:

User.find_by(email: email)

Order.where(user_id: current_user.id)

Product.order(created_at: :desc)

Lead.where(status: "active")

These are strong candidates for indexing.

How to Verify an Index Is Being Used

Never assume PostgreSQL is using your index.

Use:

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

Look for:

Index Scan

instead of:

Seq Scan

This confirms PostgreSQL is benefiting from the index.

Understanding these fundamentals makes it much easier to choose the right indexing strategy, whether it is a simple B-Tree index, a composite index, a GIN index for JSONB data, or a BRIN index for massive datasets.

PostgreSQL Concurrent Indexes in Rails: Avoiding Downtime in Production

Yashika Vijayvargiya — Thu, 18 Jun 2026 07:02:08 +0000

When working with large production databases, adding an index may seem like a simple migration.

However, on tables with millions of records, a regular index creation can lock writes and impact your application's availability.

In this article, we’ll explore what concurrent indexes are, why they matter, and how to safely create them in Ruby on Rails.

The Problem with Normal Index Creation

Suppose we have a large users table with millions of records.

A typical Rails migration might look like:

add_index :users, :email

Behind the scenes, PostgreSQL executes:

CREATE INDEX index_users_on_email ON users(email);

While PostgreSQL builds the index, it acquires locks on the table.

On large tables this can lead to:

Slower API responses
Blocked INSERT operations
Blocked UPDATE operations
Blocked DELETE operations
Potential downtime during deployment

For example:

users table = 50 million rows

Creating an index on such a table can take several minutes, and during that time write operations may be affected.

The Solution: Concurrent Indexes

PostgreSQL provides a safer alternative:

CREATE INDEX CONCURRENTLY

This allows PostgreSQL to build the index while minimizing disruption to ongoing database operations.

In Rails, this can be achieved using:

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

How Concurrent Indexes Work

Normal Index Creation

Lock table
↓
Scan rows
↓
Build index
↓
Unlock table

Concurrent Index Creation

Scan table
↓
Build index in background
↓
Track ongoing changes
↓
Finalize index

The application continues to serve requests while the index is being created.

Why disable_ddl_transaction! Is Required

A common mistake is forgetting:

disable_ddl_transaction!

PostgreSQL does not allow:

CREATE INDEX CONCURRENTLY

inside a transaction.

Rails wraps migrations in a transaction by default, so we must disable it before creating concurrent indexes.

Without it, Rails raises an error during migration.

Complete Rails Example

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

  def change

    add_index :bookings, :user_id, algorithm: :concurrently

  end

end

When Should You Use Concurrent Indexes?

Concurrent indexes are recommended when:

The table contains millions of rows
The application is live
Downtime is unacceptable
The database receives frequent writes

Typical examples include:

Users table
Orders table
Bookings table
Transactions table

When Is a Regular Index Fine?

For smaller tables and development environments:

add_index :users, :email

is usually sufficient.

Since concurrent index creation performs additional work, it is generally slower than normal index creation.

Real Production Scenario

Imagine a booking platform with:

bookings table = 100 million rows

A slow query is identified:

Booking.where(hotel_id: params[:hotel_id])

The obvious fix is:

add_index :bookings, :hotel_id

Running a normal index creation during business hours could affect booking APIs.

Instead, deploying:

add_index :bookings, :hotel_id, algorithm: :concurrently

allows the application to remain available while the index is built.

Checking Existing Indexes

To view indexes in PostgreSQL:

SELECT * FROM pg_indexes WHERE tablename = 'users';

You can also use:

\d users

from the PostgreSQL console.

Interview Question:

Why do we use concurrent indexes in Rails?

A good answer is:

Normal index creation can lock writes on large tables and impact application availability. PostgreSQL provides CREATE INDEX CONCURRENTLY to build indexes with minimal locking. In Rails, this is achieved using algorithm: :concurrently along with disable_ddl_transaction!. Although concurrent indexes take longer to build, they are much safer for production systems with large datasets and high traffic

Conclusion

Creating indexes is one of the easiest ways to improve query performance, but doing it incorrectly in production can introduce downtime.

Whenever you’re adding indexes to large, high-traffic tables, consider using concurrent indexes. The migration may take longer to complete, but your users won’t experience disruptions while it runs.

A few extra minutes during deployment are often worth avoiding minutes of downtime in production.

Rails Performance Optimization: Fixing N+1 Queries with includes, preload, and eager_load

Yashika Vijayvargiya — Wed, 10 Jun 2026 10:22:00 +0000

Originally published on Hashnode:
https://railswithyashika.hashnode.dev/rails-performance-n-plus-one-queries

When working with associations in Rails, it's easy to accidentally introduce performance issues. One of the most common problems is the N+1 Query Problem.

In this article, we'll understand what N+1 queries are, how they impact performance, and the differences between includes, preload, and eager_load.

What is an N+1 Query?

Suppose we have the following models:

class User < ApplicationRecord
  has_many :posts
end

class Post < ApplicationRecord
  belongs_to :user
end

We fetch all posts:

@posts = Post.all

And display the author's name:

<% @posts.each do |post| %>
  <%= post.title %>
  <%= post.user.name %>
<% end %>

Queries Generated
Rails first loads all posts:

SELECT "posts".*
FROM "posts";

Then for each post:


- SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT
1; 
- SELECT "users".* FROM "users" WHERE "users"."id" = 2 LIMIT 1; 
- SELECT "users".* FROM "users" WHERE "users"."id" = 3 LIMIT 1;

If there are 100 posts, Rails executes:

1 query for posts
100 queries for users

Total: 101 queries

This is called the N+1 query problem.

Why is it a Problem?

As the amount of data grows:

Database load increases
Response times become slower
More memory and CPU are consumed
Application scalability decreases A page that works fine with 10 records can become painfully slow with 1,000 records.

Fixing N+1 Queries with includes

The simplest solution is:

@posts = Post.includes(:user)

Rails executes:

SELECT "posts".*
FROM "posts";

Then:

SELECT "users".*
FROM "users"
WHERE "users"."id" IN (1, 2, 3, 4, 5);

Only 2 queries are executed regardless of how many posts exist.

Understanding includes

Most Rails developers use includes, but many don't know how it actually works.

Example

Post.includes(:user)

Generated Queries

SELECT "posts".*
FROM "posts";

SELECT "users".*
FROM "users"
WHERE "users"."id" IN (1,2,3,4,5);

Rails loads records using separate queries and associates them in memory.

When Rails Converts includes into JOIN

Consider:

Post.includes(:user)
    .where(users: { active: true })

Now Rails generates:

SELECT
  posts.id,
  posts.title,
  users.id,
  users.name
FROM posts
LEFT OUTER JOIN users
ON users.id = posts.user_id
WHERE users.active = true;

Because the query references the users table, Rails automatically switches to a JOIN strategy.

Understanding preload

preload always loads associations using separate queries.

Example

Post.preload(:user)

Queries Generated

SELECT "posts".*
FROM "posts";

SELECT "users".*
FROM "users"
WHERE "users"."id" IN (1,2,3,4,5);

Notice that the generated SQL is similar to includes.
Key Difference
Unlike includes, preload never converts into a JOIN.
This will fail:

Post.preload(:user)
    .where(users: { active: true })

Error:

missing FROM-clause entry for table "users"

Since no JOIN is generated, Rails cannot reference columns from the users table.

When to Use preload

Use preload when:

You know separate queries are preferred.
You only want to avoid N+1 queries.
You don't need conditions on associated tables.

Understanding eager_load

eager_load always uses a LEFT OUTER JOIN.

Example

Post.eager_load(:user)

Query Generated

SELECT
  posts.id,
  posts.title,
  posts.user_id,
  users.id,
  users.name
FROM posts
LEFT OUTER JOIN users
ON users.id = posts.user_id;

Everything is fetched in a single query.

Filtering on Associated Tables

Post.eager_load(:user)
    .where(users: { active: true })

Generated SQL:

SELECT
  "posts".*,
  "users".*
FROM "posts"
LEFT OUTER JOIN "users"
ON "users"."id" = "posts"."user_id"
WHERE "users"."active" = TRUE;

This works because the users table is already joined.

includes vs preload vs eager_load

Method	Queries	Uses JOIN	Can Filter Associated Table
includes	Usually 2	Sometimes	Yes
preload	2	No	No
eager_load	1	Always	Yes

Which One Should You Use?

*Use includes
*
Post.includes(:user)

Default choice for most cases.

Use preload

Post.preload(:user)

When you explicitly want separate queries and no JOIN.

Use eager_load

Post.eager_load(:user)

When filtering, ordering, or searching on associated tables.

Detecting N+1 Queries

A few ways to identify N+1 problems:

Check Development Logs

Look for repeated queries being executed inside loops.

Use Bullet Gem

Add:

gem 'bullet'

Bullet will notify you whenever an N+1 query is detected.

Use Monitoring Tools

Scout APM
New Relic
Datadog

These tools help identify slow database queries in production.

Conclusion

N+1 queries are one of the most common performance issues in Rails applications.

Understanding the differences between includes, preload, and eager_load can help you write more efficient database queries and build scalable applications.

As a rule of thumb:

Start with includes
Use preload when you want guaranteed separate queries
Use eager_load when you need JOIN-based filtering

A few minutes spent analyzing your SQL queries can save hours of performance troubleshooting later.