Indexing Strategy for App Developers: Stop Slow Queries

Last month, our internal dashboard started timing out during the morning spike. We were seeing requests take around 4.2 seconds to resolve, which is unacceptable for a simple user activity feed. After digging into the logs, I found a classic case of a missing index causing a full table scan on a table with over two million rows.

Why you need an indexing strategy for app developers

Most developers treat indexes like magic—they add them when a query feels slow, cross their fingers, and hope for the best. That’s a recipe for bloated databases and degraded write performance. A proper indexing strategy for app developers requires understanding how your database engine (we use PostgreSQL 15) actually retrieves data.

If you aren't looking at your EXPLAIN ANALYZE output, you're just guessing. I used to think adding a B-Tree index to every column was the "safe" way to go. I was wrong; I ended up with an index-heavy schema that slowed down our INSERT operations by roughly 30% because the database had to update five different trees for every new record.

Reading the EXPLAIN plan

When I finally ran EXPLAIN ANALYZE on our problematic query, the output was clear:

SQL
EXPLAIN ANALYZE SELECT * FROM activities WHERE user_id = 1234 ORDER BY created_at DESC LIMIT 20;

The database was performing a Seq Scan (Sequential Scan). It was reading every single row on disk to find the matches for that specific user. That's fine for 100 rows, but it’s a killer at scale.

When you see a Seq Scan on a large table, your first instinct should be to check your WHERE, JOIN, and ORDER BY clauses. In my case, I needed a composite index. I initially tried an index on user_id alone, but the query still had to perform an external sort for the created_at field. By creating a composite index on (user_id, created_at DESC), I allowed the engine to jump straight to the correct user block and read the records in the pre-sorted order.

Common pitfalls and trade-offs

Don't fall into the trap of over-indexing. Every index is a trade-off. While they make reads faster, they make writes slower because the database has to maintain the index structure. If you're building a system with high write volume, be surgical.

We’ve also learned that Docker for app developers: A mental model that sticks is crucial for testing these scenarios locally. By running a local PostgreSQL container with a production-sized data dump, I can verify that my index actually helps before pushing to production. It’s better to fail in a container than on a live instance.

Here are a few rules of thumb I follow:

1. Cardinality matters: Don't bother indexing columns with very low cardinality (like a boolean status flag) unless your query filters by it exclusively and the table is massive.
2. Leftmost prefix rule: If you have a composite index on (a, b, c), the index works for queries filtering on a, (a, b), or (a, b, c). It won't help if you only filter by b or c.
3. Avoid functions in WHERE: Using WHERE LOWER(email) = '...' will ignore standard indexes. You’d need a functional index for that, which is a different conversation.

When to stop indexing

There comes a point where an index is just dead weight. If you have an index that’s never used, drop it. PostgreSQL makes this easy to check with pg_stat_user_indexes. I run a quick script every quarter to find indexes with zero idx_scan hits. It’s surprising how much cruft accumulates in a growing application.

Just like managing state in a frontend framework or handling Caching and revalidation in the Next.js App Router: A Practical Guide, database optimization is about knowing when to let the system do the work and when to intervene.

I’m still not convinced we have the perfect balance. We recently moved some of our search functionality to ElasticSearch because our SQL queries were getting too complex to maintain with just indexes. Sometimes, the best indexing strategy is admitting that a relational database isn't the right tool for every single query type.

Frequently Asked Questions

How many indexes is "too many"? There's no magic number. It depends on your write-to-read ratio. If you're doing 1,000 writes per second, keep your index count low. If you're doing mostly reads, you can afford more.

Does index order matter in a composite index? Yes, significantly. Put the column you use most often for equality checks first, and the column you use for sorting or ranges last.

Should I index foreign keys? Almost always, yes. If you’re joining tables on those columns, you’ll want them indexed to avoid full table scans during joins.

I’m currently experimenting with partial indexes for our audit logs—indexing only rows where status = 'failed'. It’s cut our index size for that table by about 60%. It’s a work in progress, and I’m sure I’ll find some edge case where it bites me later, but for now, the performance gain is worth the risk.