Database indexes: a mental model — a step-by-step guide

Database indexes: a mental model

After five and a half years building production systems, I've seen more performance disasters trace back to missing or misconfigured indexes than almost any other cause. The skill compounds because every platform you build—whether it's e-commerce, CRM, ride-hailing, or SaaS—lives or dies by query speed once data grows. Database indexes: a mental model isn't just theory; it's the highest-return backend skill I know, and once you understand how they work at a conceptual level, you'll diagnose half your slow queries before they hit production.

An index is a sorted copy of columns pointing to rows

Let me start with a mental image that stuck with me early in my career. Imagine you have a filing cabinet with 10 million customer records, unsorted. Someone asks, "Show me all orders placed by tenant 42 in the last 7 days." Without an index, you'd have to open every single drawer and check every record. That's a full table scan. With an index, you've pre-sorted a copy of just the columns you care about (tenant_id, created_at), and each entry points back to the original row. Now you flip to the section labeled "tenant 42" and read only what matters.

The technical reality: an index is a B-tree (or similar structure) that stores column values in sorted order, along with row identifiers (pointers). When the query engine can use an index to narrow the result set, it avoids touching most of the table. On a table with millions of rows, this difference is the gap between milliseconds and seconds.

Here's a real scenario from a travel booking system I worked on: we had a bookings table with 50 million rows. Queries filtering by user_id without an index took 8–12 seconds. After adding CREATE INDEX idx_bookings_user_id ON bookings (user_id), the same query dropped to 40–80 milliseconds. That's a 100x improvement from a single line of SQL.

Reads get faster; writes pay a small cost

Here's the trade-off that makes index decisions real: every time you insert, update, or delete a row, the database must maintain every index on that table. More indexes mean more write overhead. On a high-volume write table, this cost is tangible.

I learned this the hard way on a ride-hailing platform. We had a location_events table receiving 500,000 inserts per second at peak. We started with six indexes to support different queries. Writes were bottlenecked by index maintenance. We pruned to two indexes and queries took a small hit, but throughput improved 40%. The lesson: index strategically, not defensively.

Your mental model should be:

• Write-heavy tables: Be conservative. Index only the columns that appear in your critical query filters. If a report query is occasional and non-critical, let it scan the table rather than maintaining an index just for it.
• Read-heavy tables: Be generous. An e-commerce product catalog or a CRM contact list? Index liberally. The write cost is negligible; the read benefit is massive.
• Balanced tables: Index the high-impact queries. Use EXPLAIN data (which we'll cover) to decide.

In practice, most backend developers overestimate write cost and underestimate read benefit. A single index typically adds 2–5% overhead to writes but cuts reads by 50–95%. The math favors reading in almost every real system.

Composite index order matters: (tenant_id, created_at)

This is where casual index knowledge breaks down and real skill begins. A composite index (multiple columns) is not a random collection—the column order defines which queries can use it efficiently. Getting the order wrong means you've added write overhead and gotten almost no read benefit.

Let me give you a concrete example. In a SaaS platform I built, the events table had this index:

CREATE INDEX idx_events_old ON events (created_at, tenant_id);

We wanted to answer: "Show me all events for tenant 42 created in the last 7 days." The query looked like this:

SELECT * FROM events 
WHERE tenant_id = 42 
  AND created_at > NOW() - INTERVAL '7 days';

Here's the problem: the index is sorted by created_at first. The database can quickly narrow to rows in the last 7 days, but then it has to scan through all of them to find tenant 42. We were still touching thousands of rows we didn't need.

Once I reordered to (tenant_id, created_at), everything changed:

CREATE INDEX idx_events_correct ON events (tenant_id, created_at DESC);

Now the index is sorted first by tenant, so the database jumps directly to tenant 42's section, then uses the secondary sort (created_at) to find the date range. Query time dropped from 2 seconds to 50 milliseconds on a 200 million row table.

The pattern I follow:

1. Equality columns first: Columns you filter with = go first. These narrow the search space to a specific segment of the index.
2. Range columns second: Columns you filter with >, <, BETWEEN, etc. go second. The index's sort order matters here.
3. Sort columns last: If you're ordering results, put those columns last so the index naturally delivers sorted data.

A real query from a trading platform I worked on:

-- Find all trades for account 123, created in the last hour, ordered by price descending
SELECT * FROM trades 
WHERE account_id = 123 
  AND created_at > NOW() - INTERVAL '1 hour'
ORDER BY price DESC;

The optimal index follows the pattern:

CREATE INDEX idx_trades_account_time_price 
ON trades (account_id, created_at DESC, price DESC);

This single index can serve equality filtering (account_id), range filtering (created_at), and sorting (price) in one pass through the index.

Use EXPLAIN before guessing

I've wasted more time debating index design than I care to admit. The solution: stop guessing and use EXPLAIN. Every major database (PostgreSQL, MySQL, SQL Server) has an explain plan tool that shows exactly how your query runs and whether it uses an index.

In PostgreSQL, it's simple:

EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE tenant_id = 42 
  AND created_at > NOW() - INTERVAL '7 days';

The output tells you:

• Whether an index was used (or if it did a full table scan)
• How many rows were examined vs. returned
• Total execution time
• Estimated vs. actual row counts (if they differ wildly, your statistics are stale)

A healthy index plan might look like:

Index Scan using idx_orders_tenant_created on orders
  Index Cond: (tenant_id = 42 AND created_at > now() - '7 days'::interval)
  Rows: 1,234
  Planning Time: 0.05 ms
  Execution Time: 2.34 ms

A bad one (full table scan) looks like:

Seq Scan on orders
  Filter: (tenant_id = 42 AND created_at > now() - '7 days'::interval)
  Rows: 1,234
  Planning Time: 0.02 ms
  Execution Time: 8543.00 ms

The discipline: before you add an index, run EXPLAIN on the slow query. After you add it, run EXPLAIN again. If it's not being used, the index is dead weight. If it's being used but still slow, the order might be wrong, or you need a different index shape entirely.

Common mistakes

• Indexing low-cardinality columns first: If a column has only 2–3 distinct values (like a boolean or status flag), it's useless as the first column in a composite index. Put high-cardinality columns first (tenant_id, user_id, email) to narrow the search space effectively.
• Forgetting to drop old indexes: I've seen systems with 30 indexes, half of which are never used. Old indexes accumulate as queries change. Periodically audit with pg_stat_user_indexes (PostgreSQL) or equivalent, and drop indexes with zero scans.
• Creating too many single-column indexes: A composite index (a, b) can serve queries on just a, so you don't always need separate indexes. Composite indexes are cheaper than maintaining multiple single-column ones.
• Ignoring INCLUDE columns: In PostgreSQL and SQL Server, you can include non-key columns in an index to avoid table lookups. CREATE INDEX idx ON orders (tenant_id) INCLUDE (total_amount) means the index has both columns, so the query engine never touches the main table. This is a huge win for covering queries.
• Not considering query patterns: If 90% of your queries filter by created_at DESC (newest first) but 10% need ASC (oldest first), the index direction matters. Most systems optimize for the dominant pattern.

Key takeaways

• An index is a sorted, searchable copy of columns that points to table rows. It trades write cost for read speed.
• Column order in composite indexes matters enormously: equality columns first, then range columns, then sort columns.
• Use EXPLAIN or EXPLAIN ANALYZE to verify an index is actually being used and delivering the speed you expect.
• On read-heavy tables, adding an index is almost always a win. On write-heavy tables, be selective and measure the impact.
• Index cardinality, column order, and query shape interact in ways that intuition alone won't reveal. Data decides, not opinion.
• Stale, unused indexes are technical debt. Audit them regularly and drop the dead weight.