A while back, I shipped a feature that included a few new APIs. Nothing particularly unusual.
Some time after the release, I was going through our performance monitoring dashboard when I noticed something interesting. One of those APIs had started showing up near the top of our execution-time charts.
The weird part? It wasn't even a heavily used endpoint. By daily request count, it wasn't anywhere near our top 30 APIs.
So why was it showing up in our performance charts?
I started digging.
The API was running a PostgreSQL query that filtered on a column without an index. Depending on how much data the query needed to examine, that could mean scanning a large part of the table just to find a handful of rows.
Well, that seemed like an easy fix.
Add an index. Query becomes faster. Problem solved.
I made the change, raised a PR, and sent it to my senior for review.
That's when the interesting part started.
Instead of simply approving the PR, he explained why we couldn't keep adding indexes every time we found a slow query.
And that got me thinking. If an index makes a query faster, why wouldn't we want one on every column? What's the actual downside?
I knew how to create an index. I didn't really understand what I was asking PostgreSQL to do.
So I went down the rabbit hole. PostgreSQL docs, B-trees, query planners, disk pages, and eventually this video by Arpit Bhayani, which helped a lot of things click.
Here's what I learned.
First, what even is an index?
Imagine you have a table containing a few million orders.
SELECT *
FROM orders
WHERE user_id = 12345;Without a useful index, PostgreSQL might have to scan the table, checking rows to find the ones belonging to that user.
That's a sequential scan.
Now imagine you had a separate, organized structure telling PostgreSQL where to find records for each user_id.
Instead of checking rows one by one, it could look up the ID and jump to the matching records.
That's essentially what an index does.
The important detail is that the index is a separate data structure. It's not some magical property that makes your existing table faster.
PostgreSQL maintains it alongside your table, and maintaining it has a cost.
The default index type in PostgreSQL is a B-tree, so let's start there.
What happens when you create an index?
Say we run:
CREATE INDEX idx_orders_user_id
ON orders(user_id);I originally thought PostgreSQL just created some lookup mapping internally.
It's a little more involved than that.
For a typical B-tree index build, PostgreSQL needs to:
- Read the table and collect the indexed values along with references to their corresponding rows.
- Sort those entries into the order required by the index.
- Build the B-tree pages from the sorted entries.
- Write the resulting index structure to storage.
Those row references are called TIDs (tuple identifiers). You'll also see the term ctid in PostgreSQL, which identifies a tuple's physical location.
And PostgreSQL stores these structures in pages, typically 8 KB each.
Think about the amount of work involved when the table has millions of rows. PostgreSQL has to read a lot of data, sort it, and write a whole new structure.
That means CPU, memory, disk I/O, and additional storage.
There's another detail I hadn't considered: creating an index can affect a running application.
A regular CREATE INDEX allows reads but blocks writes to the table while the index is being built.
Imagine adding an index to a busy production table and suddenly having INSERTs and UPDATEs waiting behind the operation.
Not ideal.
PostgreSQL gives us another option:
CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders(user_id);This allows normal reads and writes to continue during most of the index build.
But it isn't free either. A concurrent build requires more work, multiple scans, and waiting for certain transactions. It can also fail and leave an invalid index that needs cleanup.
So even the act of adding an index needs some thought.
Okay, but why are B-trees fast?
This was probably my favorite part to learn.
A B-tree is a balanced search tree. Instead of searching through every value, it helps narrow down where the value could be.
It consists of root, internal, and leaf pages.
A simplified version looks like this:
[ 50 ]
/ \
[20, 35] [70, 90]
/ | \ / | \
... ... ... ... ... ...Illustrative tree, not an exact PostgreSQL page layout.
Say we're searching for user_id = 72.
We start at the root.
72 is greater than 50, so we go right.
Then we use the separator keys in that branch to narrow the search further.
We keep doing that until we reach the leaf page containing the relevant index entries.
The nice thing about a balanced tree is that it stays relatively shallow, even when the number of entries grows significantly.
That's why a B-tree lookup generally has logarithmic search complexity: O(log n).
And because each page contains many entries and PostgreSQL can cache frequently accessed pages, a lookup often requires visiting surprisingly few levels.
There's also something clever about leaf pages.
They're linked in key order, which makes range scans efficient.
For example:
SELECT *
FROM orders
WHERE user_id BETWEEN 1000 AND 2000;PostgreSQL can locate the start of the range and then scan forward through the relevant leaf entries.
You don't need to perform a completely separate tree search for every matching value.
This is one reason B-trees work well for equality lookups, ranges, and certain sorting operations.
But there's a catch.
Having an index doesn't mean PostgreSQL will use it
This was another thing that surprised me.
You can create a perfectly valid index, run your query, and PostgreSQL might completely ignore it.
Why?
Because PostgreSQL has a query planner.
Before executing a query, the planner estimates the cost of different approaches and tries to choose the cheapest one.
Consider:
SELECT *
FROM users
WHERE is_active = true;Suppose 90% of your users are active.
You create an index on is_active.
Would using that index make sense?
Probably not for a query returning almost the entire table.
PostgreSQL could end up reading a large number of index entries and then fetching most of the table rows anyway.
At that point, simply scanning the table could be cheaper.
But consider:
SELECT *
FROM users
WHERE email = 'hello@example.com';If the email is unique, PostgreSQL only needs to find one row.
An index is likely to be very useful.
The difference is how much data the query is expected to retrieve, along with the cost of accessing it.
You can inspect what PostgreSQL actually does with:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 12345;You might see a sequential scan, an index scan, or a bitmap heap scan.
EXPLAIN ANALYZE executes the query and reports what happened. BUFFERS adds information about buffer usage, which helps you understand the I/O behavior.
This is far more useful than guessing.
One thing worth remembering: the planner relies on statistics. If those statistics are stale, its estimates can be wrong.
Sometimes running ANALYZE and inspecting the execution plan is more useful than immediately creating another index.
Composite indexes are interesting too
Let's say our application frequently asks for a user's recent orders.
SELECT *
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;We could create:
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);Now the index contains entries ordered first by user_id and then by created_at within each user.
That's useful because PostgreSQL may be able to find the user's orders in the required order without separately sorting all the matching rows.
But the order of columns matters.
An index on (user_id, created_at) is generally most effective when our query constrains user_id.
If we only filter by created_at, that same index may be much less useful.
There are exceptions, including PostgreSQL's skip-scan optimization in applicable versions and situations, so the leftmost-column rule isn't absolute.
The main thing I took away was that an index should be designed around the queries you actually run, not just the columns that happen to exist.
There's also something called a covering index
Normally, an index helps PostgreSQL find the row, but the database may still need to visit the table to retrieve additional columns.
For example:
SELECT status, created_at
FROM orders
WHERE user_id = 12345;If our index only contains user_id, PostgreSQL may need to fetch the corresponding table rows to get status and created_at.
We can include those additional columns:
CREATE INDEX idx_orders_covering
ON orders(user_id)
INCLUDE (status, created_at);Now the index contains all the columns needed by that query.
PostgreSQL may be able to perform an index-only scan, avoiding some of the work of fetching rows from the main table.
I say may because PostgreSQL still has to check row visibility under MVCC. Depending on its visibility map, it might need to visit heap pages anyway.
Still, it's a useful optimization for certain read-heavy queries.
Of course, the included columns take up more index space.
There's that trade-off again.
So why not just index every column?
We're finally back to the question that started all of this.
An index makes certain reads faster. Great.
But every index has to be stored, maintained, and kept consistent as the underlying data changes.
Let's talk about what that means.
1. Your writes become more expensive
Imagine an orders table with six indexes.
Now you insert 100,000 new orders.
PostgreSQL doesn't just write those rows into the table. It also has to maintain the relevant entries in all six indexes.
That's additional work for every insert.
Updates can be expensive too.
If an indexed value changes, PostgreSQL needs to reflect that change in the index. Under the right conditions, PostgreSQL can use HOT updates to avoid creating new index entries, but unnecessary indexes can reduce those opportunities.
For a system handling a large volume of writes, this starts to matter.
One index probably won't destroy your performance.
But adding indexes without considering the workload can gradually make writes more expensive.
2. Indexes consume real storage
Indexes live on disk.
A large table can have indexes taking hundreds of megabytes or several gigabytes, depending on the columns, number of rows, and index design.
If you have multiple indexes across multiple large tables, the storage adds up.
You can check an index's size:
SELECT pg_size_pretty(
pg_relation_size('idx_orders_user_id')
);And disk space isn't the only consideration. Index pages also compete for memory in PostgreSQL's buffer cache.
3. Indexes need maintenance
PostgreSQL uses MVCC, which means updating or deleting rows can leave old tuple versions and index entries that need cleanup.
VACUUM helps reclaim reusable space and clean up dead entries.
But an index can still become bloated over time, particularly under certain write-heavy workloads.
More pages can mean more storage and I/O.
Sometimes REINDEX is useful, but you shouldn't automatically rebuild every index just because a table has a lot of updates.
4. More indexes give the planner more choices
The query planner has to consider possible ways of executing a query.
Adding indexes introduces additional access paths.
For most ordinary queries, the extra planning cost isn't something I'd worry much about.
But on complex workloads with lots of joins and candidate indexes, it can become relevant.
More importantly, every extra index is another thing you need to understand, monitor, and justify.
Back to that slow API
After learning all of this, I started looking at my original PR differently.
My reasoning had been pretty simple:
- The query filters on a non-indexed column.
- The API is slow.
- An index should help.
And honestly, the index might have been the right solution.
My mistake wasn't necessarily proposing an index.
It was proposing one without properly considering the cost.
The endpoint wasn't even among our most frequently called APIs. That didn't mean its performance was irrelevant, but it did mean I needed to understand how much benefit the change would provide.
Was the execution time actually a problem for users? Was the query examining a large number of rows? How often was it running? How frequently did that table receive writes?
Would the proposed index help other queries too?
And was there a better way to write the query in the first place?
Those were the questions I should have been asking before opening the PR.
An index isn't inherently good or bad. It depends on the workload.
What I check now
These days, when I see a slow database query, I try to understand the problem before jumping to a fix.
First, I look at the query's execution plan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 12345;I want to know whether PostgreSQL is doing a sequential scan, how many rows it's examining, and where the execution time is going.
Then I look at the workload.
For example, pg_stat_statements can help identify queries consuming significant total execution time across repeated calls.
And I can inspect index usage with:
SELECT
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;An index with a low scan count isn't automatically useless. It might support a rare but critical query, or enforce a constraint. The statistics also depend on when they were last reset.
But it gives me something concrete to investigate.
If an index still looks like the right solution, I can test it against realistic data and compare the plan before and after the change.
That's a much better process than adding an index and hoping for the best.
One last thing
Looking back, I'm glad my senior didn't just approve that PR.
It would have been a small performance optimization, and I probably would have moved on to the next task without thinking about it again.
Instead, I ended up learning about B-trees, PostgreSQL storage, query planning, MVCC, and a bunch of things I hadn't really paid attention to before.
The funny thing is, the original problem was just one slow API.
But understanding why an index helps, and what it costs, turned out to be far more useful than the fix itself.
Now, whenever I'm tempted to add an index, I still think about that PR.
Sometimes the answer really is to add one.
I just want to know why first.
Further reading