Understanding Indexes Before Learning Index Types
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.