Saqib

Posted on Aug 6

Database Indexing Explained: The Hidden Cost Behind Every Fast Query

#ai #programming #architecture #learning

Your API endpoint is taking 800ms. You open the query plan, see a sequential scan on a 12-million-row table, and do the classic move: CREATE INDEX. The query drops to 12ms. Everyone celebrates. Two weeks later the same table is under heavy write load, inserts start timing out, vacuum is screaming, and someone is asking why the disk is full of index bloat.

That is the real story of indexes. They are not free performance buttons. They are a deliberate trade-off that senior engineers make carefully, not a default you sprinkle on every column that appears in a WHERE clause.

The Problem Indexes Actually Solve

Before indexes, the only way a database found rows was to read every page of the table. On a small table this is fine. On a table that no longer fits in memory it becomes a full table scan, and latency scales with table size.

Indexes exist so the engine can jump straight to the relevant pages instead of reading everything. They solve the classic read-vs-write tension: most systems are read-heavy, so we accept extra work on writes to make reads cheap. The moment the workload becomes write-heavy or the index set grows too large, that trade-off flips.

This is why early systems often lived without many indexes. When tables were small and memory was scarce, the cost of maintaining extra structures frequently outweighed the benefit. As data volumes exploded, indexes became non-negotiable for anything that needed predictable latency.

How an Index Actually Works

Most relational databases use B-tree indexes (or close variants). A B-tree is a balanced tree where:

Leaf nodes contain the indexed column values plus pointers (or the full row in covering indexes) to the table data.
Internal nodes act as a roadmap so the engine can find the right leaf in logarithmic time.

When you run:

SELECT * FROM orders WHERE user_id = 42;

the planner can:

Walk the B-tree for user_id.
Land on the leaf pages that contain matching entries.
Follow the pointers (or use the included columns) to fetch the actual rows.

If the index is selective enough and the table is large, this is dramatically cheaper than a sequential scan. If the index is not selective (think a boolean is_active column with 90% true values), the planner often ignores it and scans the table anyway.

Other index types exist for different access patterns:

Hash indexes — equality only. Limited usefulness in most production engines.
GiST / GIN — full-text search, arrays, JSON, geospatial.
BRIN — very large, naturally ordered data (time-series, append-only logs). Extremely cheap to maintain.
Partial indexes — only index a subset of rows (WHERE status = 'active'). Often the highest-leverage tool people forget.

flowchart TD
    A[Query arrives] --> B{Planner chooses path}
    B -->|Index available & selective| C[B-tree lookup]
    B -->|No useful index| D[Sequential scan]
    C --> E[Leaf pages]
    E --> F[Heap / table fetch]
    F --> G[Return rows]
    D --> G

The key insight: the index does not store the full row by default. It stores keys + pointers. Every time you need columns that are not in the index, you still pay the random I/O cost of going back to the table (the “heap fetch”). This is why covering indexes matter.

Why Companies Actually Use Them

Companies do not add indexes because textbooks say so. They add them when:

Latency SLOs are being missed on critical paths.
A query that used to be fine suddenly scans millions of rows after organic growth.
Support tickets start mentioning “the app feels slow in the afternoon.”
An analytics query is killing the primary and they need a read replica with different indexes.

In high-scale systems you will also see specialized indexes:

Composite indexes ordered to match the most common filter + sort combination.
Expression indexes on lower(email) or date_trunc('day', created_at).
Indexes on foreign keys purely so cascades and joins do not degrade.
Separate indexes on the read replica that the primary never carries.

The decision is almost never “add an index.” It is “add this specific index because the measured cost of not having it exceeds the measured cost of maintaining it.”

When to Use Indexes

Use them when all of the following are true:

The column (or combination) appears frequently in WHERE, JOIN, or ORDER BY.
The selectivity is high enough that the index will actually be chosen by the planner.
The table is large enough that a sequential scan is expensive.
You have measured the write amplification and it is acceptable under current load.
You understand the maintenance cost (bloat, vacuum pressure, storage).

High-value patterns:

Primary keys and unique constraints (these are indexes by definition).
Foreign keys that are joined often.
Columns used in the most common filters of your hottest endpoints.
Covering indexes for read-heavy APIs that always return the same small set of columns.

When NOT to Use Indexes

This is where most junior engineers get it wrong.

Do not index:

Low-cardinality columns (gender, status with 3 values, boolean flags) unless you use a partial index.
Columns that are written far more often than they are read.
Every column that appears in any query “just in case.”
Columns on tiny tables that already fit in memory.
Columns whose values change constantly (the index becomes a write hotspot).

Also be careful with:

Wide composite indexes. Every extra column increases the size of every leaf entry and slows writes.
Indexes on columns that are updated in the same transaction as the primary key. You pay the index maintenance cost twice.
Creating indexes on a live high-write table without CONCURRENTLY (Postgres) or equivalent. You can lock the table for a long time.

A classic anti-pattern: someone adds five indexes to “speed up reporting,” then the OLTP workload starts missing its latency targets because every insert now updates six structures instead of one.

Real Production Trade-offs

Indexes are not free. Here is what you actually pay:

Cost	What it looks like in production	How bad it can get
Write amplification	Every INSERT/UPDATE/DELETE must update the index	Insert latency climbs, CPU spikes
Storage	Indexes can be larger than the table itself	Disk pressure, backup size grows
Bloat	Dead tuples in indexes after heavy updates	Index scans become slower over time
Vacuum / maintenance	Autovacuum works harder	Long-running vacuums, lock issues
Cache pressure	Hot indexes compete with table data in shared buffers	More cache misses overall
Planner complexity	More indexes = more possible plans	Sometimes the planner picks wrong

In one system I worked on, a single poorly chosen composite index on a high-churn events table added ~40% to write latency and increased daily storage growth by 15 GB. Removing it and replacing it with a partial index on only the “open” events cut write time significantly while still serving the important queries.

Another common reality: the index that makes the 95th percentile query fast can make the 99.9th percentile write path miss its SLO. You have to decide which percentile matters more for that particular service.

Common Beginner Mistakes

Indexing every column in the WHERE clause

Selectivity matters more than presence. A non-selective index is often worse than no index because the planner still has to consider it.
Ignoring the order of columns in a composite index

(user_id, created_at) can support WHERE user_id = ? and WHERE user_id = ? AND created_at > ?.

(created_at, user_id) cannot efficiently support the first query.
Forgetting covering indexes

If the query only needs three columns and you include them in the index (INCLUDE in Postgres, or just add them to the key), you avoid the heap fetch entirely. This is often the difference between “pretty fast” and “blazing.”
Creating indexes on the primary during peak traffic without concurrent options

You can lock writers for minutes or hours on a large table.
Never looking at pg_stat_user_indexes (or equivalent)

Indexes that are never used still cost you on every write. Dead indexes are pure overhead.
Assuming the index will stay healthy forever

Heavy updates create bloat. Without proper autovacuum tuning or periodic REINDEX, performance degrades silently.
Treating the query planner as magic

Sometimes you still need to rewrite the query, add statistics, or force a plan. Indexes are only one tool.

How Senior Engineers Think About This Decision

A senior engineer does not ask “Should I add an index?”

They ask a series of more precise questions:

What is the actual latency distribution of the query under realistic load?
How selective is this predicate on production data (not the tiny staging dataset)?
What is the current write QPS and how much headroom do we have?
Is this query on the critical path for users, or is it background/analytics?
Can we solve this with a better schema, a materialized view, a read replica, or application-level caching instead?
If we add this index, what is the rollback plan and how do we measure the impact?

They also think in terms of lifetime cost. An index that saves 200ms today but adds permanent write overhead and storage growth for the next five years may not be a good deal.

In many high-scale systems the final decision looks like this:

“We will add a partial covering index on (user_id) INCLUDE (status, amount) only for rows where status IN ('pending', 'processing'). We will create it concurrently during the low-traffic window, monitor write latency and index size for 48 hours, and have a drop script ready.”

That is engineering, not cargo-cult indexing.

Practical Checklist Before You Create an Index

Run EXPLAIN (ANALYZE, BUFFERS) on the real query with production-like data volume.
Check selectivity: how many rows does the predicate typically return?
Look at existing indexes — can you extend one instead of creating another?
Estimate write impact: how often is this table written?
Prefer partial or covering indexes when possible.
Create concurrently if the table is live and large.
Add monitoring for index size, bloat, and usage stats.
Document why the index exists and under what conditions it can be removed.

Final Thought

Indexes are one of the highest-leverage tools in a relational database. They are also one of the easiest ways to quietly destroy write performance and waste money on storage.

The difference between a junior and a senior engineer is not whether they know how to create an index. It is whether they understand the full cost, measure before and after, and are willing to remove an index that is no longer earning its keep.

Next time your query is slow, do not reach for CREATE INDEX first. Reach for the query plan, the statistics, and a clear understanding of the trade-off you are about to accept.

That is how you keep systems fast for years instead of just for the next deploy.

DEV Community