If you’ve ever waited too long for a database query to finish, you know the pain of slow performance.
The good news? Indexes are here to save the day!
PostgreSQL is powerful, but without proper indexing, your database can feel like it’s running on a potato.
Indexes are the secret sauce to blazing-fast queries, but they come with trade-offs.
What Are Indexes?
Indexes in PostgreSQL work like the index in a book—rather than scanning every page (row), you jump directly to the relevant section.
Without indexes, PostgreSQL performs sequential scans, which means looking at every row to find a match. Not ideal for performance.
Indexes are especially useful for:
-
Speeding up queries with
WHEREclauses. - Improving join performance.
With indexes, queries execute logarithmically faster instead of linearly.
Imagine going from O(n) → O(log n) complexity.
But... indexing isn’t magic. There are trade-offs.
They consume storage and can slow down writes.
So, you need to be smart about where and when to use them.
How Indexes Work: The B-tree Magic
PostgreSQL’s default index type is the B-tree (Balanced Tree).
Think of it as a tree-like structure that keeps data sorted for fast searching. Here’s how it works:
- Root Node: The starting point for all searches.
- Branch Nodes: Guide the search to the correct leaf node.
- Leaf Nodes: Store the actual data pointers.
For example, if you’re searching for the name “Mac” in a table, the B-tree will:
- Start at the root.
- Compare “Mac” with the current node.
- Traverse left or right based on the comparison.
- Repeat until it finds the exact match.
This process reduces the number of comparisons needed, making searches logarithmically faster.
When to Use Indexes
Use indexes when they significantly improve read performance. Common scenarios include:
| Scenario | Why Indexing Helps? |
|---|---|
Searching by unique fields (e.g., id, email) |
Faster lookups, avoids full-table scans |
Filtering with WHERE clauses |
Quickly finds matching rows |
Sorting (ORDER BY) |
Improves sorting performance |
| Joining large tables | Avoids scanning the entire dataset |
| Full-text search | Efficiently finds keywords inside text |
| Foreign keys | Ensures fast relationship checks |
When Not to Use Indexes
Indexes aren’t free.
Every time you INSERT, UPDATE, or DELETE data, indexes need updating.
This can slow down write-heavy workloads. Avoid indexes if:
- Your table is small (PostgreSQL can scan it quickly anyway).
- Your queries rarely filter by indexed columns.
- Your table has frequent writes, and read speed isn’t critical.
- Your database is heavily transactional, and you need fast inserts/updates.
PostgreSQL’s MVCC mechanism can lead to “Heap-Only Tuples” (HOT) updates, creating dead rows and increasing I/O.
Measuring Index Performance
Before blindly adding indexes, test if they actually help.
PostgreSQL provides EXPLAIN ANALYZE to analyze query execution time. Try this:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
Look for Seq Scan (bad) vs. Index Scan (good).
If adding an index doesn’t reduce query time, it’s not worth it.
Want to remove a useless index? Use:
DROP INDEX index_name;
Types of Indexes in PostgreSQL
Not all indexes are created equal. PostgreSQL offers several types:
B-Tree Index (Default)
Virtually all databases will have some B-tree indexes.
B-trees attempt to remain balanced, with the amount of data in each branch of the tree being roughly the same.
Therefore the number of levels that must be traversed to find rows is always in the same ballpark.
B-tree indexes can be used for equality and range queries efficiently.
They can operate against all datatypes, and can also be used to retrieve NULL values.
B-trees are designed to work very well with caching, even when only partially cached.
-
Great for: Equality and range queries (
=,<,<=,>,>=). - Example:
CREATE INDEX idx_users_email ON users(email);
Hash Index (Improved in PostgreSQL 10+)
Pre-Postgres 10 are only useful for equality comparisons, but you pretty much never want to use them since they are not transaction safe, need to be manually rebuilt after crashes, and are not replicated to followers, so the advantage over using a B-Tree is rather small.
In Postgres 10 and above, hash indexes are now write-ahead logged and replicated to followers.
- Optimized for equality comparisons (
=). -
Not ideal for range queries (
>,<). - Example:
CREATE INDEX idx_users_hash_email ON users USING hash(email);
GIN (Generalized Inverted Index)
GIN is useful when an index must map many values to one row, whereas B-Tree indexes are optimized for when a row has a single key value.
GINs are good for indexing array values as well as for implementing full-text search.
- Used for full-text search and JSONB fields.
- Example:
CREATE INDEX idx_users_bio ON users USING gin(to_tsvector('english', bio));
GiST (Generalized Search Tree)
GiST indexes allow you to build general balanced tree structures and can be used for operations beyond equality and range comparisons.
They are used to index the geometric data types, as well as full-text search.
- Optimized for geometric and range queries.
- Used in PostGIS (spatial data indexing).
- Example:
CREATE INDEX idx_locations ON places USING gist(location);
BRIN (Block Range INdex)
Efficient for large, sequentially stored data (like time-series data).
- Uses less storage than B-Trees.
- Example:
CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);
Advanced Indexing Strategies
Composite Indexes
Index multiple columns when they’re commonly queried together.
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
Only the B-tree, GiST, GIN, and BRIN index types support multiple-key-column indexes.
Whether there can be multiple key columns is independent of whether INCLUDE columns can be added to the index.
Indexes can have up to 32 columns, including INCLUDE columns.
Partial Indexes
Only index a subset of data to save space.
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression.
The index contains entries only for those table rows that satisfy the conditions.
Covering Indexes
Stores additional columns to avoid accessing the main table.
CREATE INDEX idx_orders_covering ON orders(user_id, order_date) INCLUDE (total_price);
All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area .
Unique Indexes
Ensure column uniqueness efficiently.
CREATE UNIQUE INDEX idx_unique_email ON users(email);
Indexes can also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column.
Only B-tree indexes can be declared unique.
The Trade-Offs: Read vs. Write Performance
| Action | Impact of Indexes |
|---|---|
| Reads | ✅ Faster queries |
| Writes (INSERT/UPDATE/DELETE) | ❌ Slower due to index maintenance |
| Storage | ❌ More disk space needed |
| Vacuuming | ❌ Dead tuples require cleanup |
If your app is read-heavy, indexes are a no-brainer. If it’s write-heavy, be selective.
Wrapping Up
Indexes are one of the biggest performance boosters in PostgreSQL. Use them wisely:
- ✅ Use indexes for filtering, sorting, and joins.
- ❌ Avoid indexes on frequently updated tables.
- 🛠 Test with EXPLAIN ANALYZE before adding indexes.
- 🎯 Pick the right index type for your query patterns.
Further Reading 📚
- PostgreSQL Documentation on Indexes
- Understanding B-trees
- Indexing Best Practices
- An Intro to Database Indexes in Postgres
I’ve been working on a super-convenient tool called LiveAPI.
LiveAPI helps you get all your backend APIs documented in a few minutes
With LiveAPI, you can quickly generate interactive API documentation that allows users to execute APIs directly from the browser.
If you’re tired of manually creating docs for your APIs, this tool might just make your life easier.
Top comments (4)
Thanks for the writeup!
Glad you liked it :)
Great article 👏
i do a lot of rescue work on legacy projects and literally the first thing i look at in a new repo is indexing. can often get substantial performance gains for minimal effort right out of the gate.