Database Indexes for Application Developers: When, Which, and Why Not More

Database Indexes for Application Developers: When, Which, and Why Not More

Most application teams treat indexes as a free performance dial: query feels slow, add an index, move on. It works, until a table has fifteen indexes, every write has slowed down, and nobody remembers which index serves which query.

The fix isn't more indexing discipline in the abstract. It's understanding what an index actually costs, then shipping exactly one at a time.

The real cost of an index on every write

Think of an index like a book's table of contents: add a chapter, and you have to rewrite the contents page too. A database index works the same way — a second structure kept in sync with the first, not a note stapled to the table.

Every INSERT writes a row and an entry in each index on that table. Every UPDATE touching an indexed column rewrites the matching index entries. Every DELETE removes the row from the table and from every index pointing to it. A table with five indexes turns one INSERT into six writes.

That cost compounds. A Percona benchmark grew a PostgreSQL schema from 7 indexes to 39 under a mixed insert/update/select workload. Throughput dropped from roughly 1,400 transactions per second to roughly 600 — about 42% of where it started. Average transaction time rose from 11ms to 26ms.

The query workload never changed; the only variable was how many index structures had to be maintained on every write. This isn't an argument against indexes. It's an argument against adding one on a hunch. Every index you ship is a permanent tax on every future write, and it needs a query that justifies it.

Composite index order: forget "most selective first"

"Put the most selective column first" gets repeated constantly and holds up rarely. The real rule, per PostgreSQL's own documentation, is narrower. Equality constraints on the leading columns, plus one range or inequality constraint right after, are what limit how much of the index actually gets scanned. Columns further right are checked inside the index, which saves a table trip, but they don't narrow the scan range like the leading equality columns.

So the question isn't "which column has more distinct values." It's "which columns does my query filter with =, and which one with a range." An index on (status, created_at) serves WHERE status = 'active' AND created_at > $1 well regardless of selectivity, because status is the equality predicate and belongs first.

Column order should track your queries, not a cardinality guess. A timezone-bucketed dashboard needs its own index shape for the same reason, covered in grouping multi-timezone data in PostgreSQL. Selectivity is a tiebreaker between two orderings that serve the same queries equally, but it's secondary, not the primary rule the "most selective first" myth claims it is. Don't stack columns indefinitely either: PostgreSQL's docs say a single-column index is usually enough, and indexes past three columns rarely help outside stylized usage.

Covering indexes and INCLUDE: free reads until they aren't

A B-tree index can sometimes answer a query with no trip to the table, if every column the query needs is present in the index. That's an index-only scan, the fastest read path available. If a query needs an extra column just for output, not filtering, CREATE INDEX ON orders(customer_id) INCLUDE (total_amount) is the fix. It attaches the column as payload without widening the searchable key.

Here's the catch that cancels the benefit: an index-only scan only skips the table if the heap page is marked all-visible in PostgreSQL's visibility map. If it isn't, the engine visits the row to confirm visibility, and you pay the I/O of a scan with none of the upside.

A mostly-read table stays all-visible and the covering index pays off. A table with constant writes between vacuum runs rarely catches up, and the INCLUDE columns become dead weight. Check autovacuum cadence before counting on this.

Reading EXPLAIN (ANALYZE, BUFFERS) without fear

EXPLAIN ANALYZE output looks intimidating: nested nodes, cost estimates, buffer counts, timing everywhere. Ignore most of it on a first pass. Check one number: estimated rows versus actual rows on the node nearest your problem.

Per PostgreSQL's documentation, the rows figure on a node is what it's expected to emit after its own filtering, not what it scanned. It's routinely lower than the raw count. A close estimate-to-actual match means the planner understood your data. Off by 10x or more means stale statistics, and no new index fixes that on its own — an ANALYZE on the table often does more.

One gotcha before running this against anything but a SELECT: EXPLAIN ANALYZE doesn't just plan the statement, it executes it. Run it on an UPDATE and the write happens. Wrap it in a transaction and roll back instead:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

Why "just index every column" is a smell

Beyond the per-write cost above, a growing pile of indexes has two more consequences. Storage: a table's indexes combined commonly outweigh the table itself. Planning cost: the planner has to weigh every candidate index touching the relevant columns. Near-duplicate indexes give it more chances to pick a worse fit.

There's a feedback loop underneath both. More indexes mean more autovacuum work per pass, so vacuum falls further behind on a busy table, so bloat accumulates, so queries slow down. The usual reflex is to add another index to compensate. Each index shipped without a specific query behind it makes the next slow query slightly harder to fix.

The one-slow-query-at-a-time workflow

The version that holds up in a consulting engagement, on a production database that isn't mine to experiment on freely, stays narrow:

One query, one hypothesis, one change, one measurement. A batch of speculative indexes might fix the problem, but you won't know which one did it. You'll pay the write cost for the rest indefinitely. Change one variable at a time, and every index left standing has a query to justify it.

A checklist before you ship a new index

• Do you have the actual slow query and its EXPLAIN ANALYZE output, not a guess?
• Does the estimated row count roughly match the actual, or do you need ANALYZE first?
• Does column order match how the query filters — equality first, then the range column — rather than raw cardinality?
• Could INCLUDE work here, and does this table stay vacuumed enough to earn an index-only scan?
• Are you shipping this index alone, so its effect can be measured in isolation?
• Does an existing index with the same leading columns already cover this?

Indexing is a targeted tool, not a setting to max out. The teams that get the most out of their database aren't the ones with the most indexes. They're the ones who can point at every index they have and name the query it exists for.