Database Indexing Strategies for High-Performance Queries

You just launched your new feature, and the dashboard is loading in 10 seconds. You check the logs, find the culprit query, and slap an index on the column in the WHERE clause. Boom. The query now takes 5 milliseconds.

You’ve just solved the symptom, but you might have created a new disease.

Indexes are the most powerful tool in a database administrator's arsenal for read performance, but they come with a heavy tax on writes and storage. Throwing indexes at a database without a strategy leads to bloated tables, slow inserts, and queries that still aren't fast enough.

In this guide, we’ll move beyond "add an index to slow columns" and explore the architectural strategies required to build high-performance, scalable databases.

The Anatomy of an Index: Why B-Trees Matter

Before we strategize, we must understand how databases find data. Without an index, the database must perform a Full Table Scan—reading every single row from disk to find a match. It’s like reading a textbook from page 1 to find a specific keyword.

Most relational databases (Postgres, MySQL) use B-Tree (Balanced Tree) indexes by default. A B-Tree keeps data sorted and allows the database to traverse the tree in $O(\log N)$ time, turning a search of millions of rows into just 3-4 disk reads.

graph TD
    Root["Root: 40"] --> Left["Left Node: 10-20"]
    Root --> Right["Right Node: 50-70"]
    Left --> L1["Leaf: 5, 8"]
    Left --> L2["Leaf: 12, 15, 20"]
    Right --> R1["Leaf: 45, 50"]
    Right --> R2["Leaf: 60, 70, 85"]

    style Root fill:#f96,stroke:#333,stroke-width:2px
    style Left fill:#ff9,stroke:#333,stroke-width:2px
    style Right fill:#ff9,stroke:#333,stroke-width:2px

Strategy 1: The Cardinality Rule (Not All Columns Are Equal)

Cardinality refers to the number of unique values in a column. Indexes only work well when they help the database filter out the vast majority of rows.

High Cardinality: Emails, UUIDs, Usernames (almost every row is unique). Excellent for indexing.
Low Cardinality: Gender, Boolean flags (e.g., is_active). Terrible for indexing.

If you index a boolean flag is_active where 99% of users are active, the database might just decide to do a Full Table Scan anyway, because reading the index + fetching the rows is actually more work than just reading the whole table.

🧠 Rule of Thumb: Only index columns with high selectivity. If a query doesn't narrow down your result set to roughly 5-10% of the total table, an index won't save you.

Strategy 2: Composite Indexes & The Left-Prefix Rule

The most common mistake developers make is creating single-column indexes for queries that filter by multiple columns.

Imagine this query:

SELECT * FROM orders 
WHERE user_id = 123 AND status = 'shipped';

The Wrong Way:

CREATE INDEX idx_user ON orders(user_id);
CREATE INDEX idx_status ON orders(status);

The database will likely only use one of these indexes, then scan the results for the other condition. It cannot use both simultaneously to filter data efficiently.

The Right Way: Create a Composite Index.

CREATE INDEX idx_user_status ON orders(user_id, status);

The Left-Prefix Rule (Crucial)

A composite index is like a phonebook. A phonebook is sorted by Last Name, then First Name. * If you search for "Smith, John," the phonebook is blazing fast. * If you search for "Last Name = Smith," the phonebook is fast. * If you search for "First Name = John" (skipping the last name), the phonebook is completely useless. You must read the whole book.

The same applies to composite indexes. INDEX(A, B, C) can be used for: ✅ WHERE A = 1 ✅ WHERE A = 1 AND B = 2 ✅ WHERE A = 1 AND B = 2 AND C = 3

But it CANNOT be used for: ❌ WHERE B = 2 ❌ WHERE B = 2 AND C = 3 ❌ WHERE C = 3

Strategy: Always put the column with the highest cardinality (most unique values) on the left of the composite index to maximize filtering efficiency early in the B-Tree traversal.

Strategy 3: Covering Indexes (The Ultimate Performance Hack)

When a database finds a match in the index, it usually has to do a Heap Fetch—jumping to the actual table row on disk to get the rest of the columns requested in the SELECT clause. Heap fetches are expensive I/O operations.

A Covering Index includes all the columns the query needs, so the database never has to look at the actual table at all. It’s an "Index-Only Scan."

-- The query we want to optimize
SELECT user_id, status, created_at 
FROM orders 
WHERE user_id = 123;

We can use the INCLUDE clause (in Postgres/SQL Server) to store the data in the leaf nodes of the index without affecting the sorting tree:

-- Postgres syntax for a Covering Index
CREATE INDEX idx_user_covered 
ON orders(user_id) 
INCLUDE (status, created_at);

By doing this, the database resolves the entire query purely from RAM (or fast disk where the index lives), bypassing the main table entirely. This can yield 10x-100x performance improvements.

Strategy 4: Partial Indexes (Saving Space & Time)

Often, you only query a specific subset of your data. For example, an orders table might have millions of rows, but your dashboard only queries orders that are currently pending.

SELECT * FROM orders WHERE status = 'pending';

Indexing the entire table wastes disk space and slows down writes for all the completed and cancelled orders you don't care about.

A Partial Index only indexes rows that meet a specific condition.

-- Postgres syntax
CREATE INDEX idx_pending_orders 
ON orders(status) 
WHERE status = 'pending';

This index is tiny, lightning-fast, and has zero write overhead for the 99% of orders that are completed.

Strategy 5: Specialized Index Types

B-Trees aren't the only tool in the shed. Depending on your data, other index structures are vastly superior:

Hash Indexes: Only for exact equality (=) checks. They are faster and smaller than B-Trees but cannot do range queries (>, <). Good for session IDs.
BRIN (Block Range Indexes): Used for massive tables with naturally sorted data (like timestamps in time-series data). Instead of indexing every row, it notes the "min" and "max" values of entire disk blocks. It's incredibly tiny and fast for chronological queries.
GIN (Generalized Inverted Index): The standard for Full-Text Search and JSONB columns. If you need to search for a specific key inside a JSON payload, you need a GIN index. sql -- Indexing JSONB in Postgres CREATE INDEX idx_metadata ON products USING GIN (metadata_jsonb);
Bitmap Indexes: Used in data warehousing (like Snowflake) for low-cardinality columns. Not typically used in OLTP databases like Postgres/MySQL.

The Write Penalty: Why You Must Be Ruthless

Every time you INSERT, UPDATE, or DELETE a row, the database must update every single index attached to that table.

If a table has 6 indexes, a single INSERT essentially becomes 7 write operations (1 to the table, 6 to the indexes). Furthermore, indexes must remain balanced. High write volume can cause index bloat and page splits, degrading performance over time.

How to audit your indexes:

1. Find Duplicate or Redundant Indexes If you have INDEX(A, B) and INDEX(A), the second index is redundant. The left-prefix rule means INDEX(A, B) handles queries on A perfectly. Drop the redundant one.

2. Find Unused Indexes Postgres tracks index usage. Run this query periodically to find indexes that are taking up space but never being used:

SELECT schemaname, relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
ORDER BY pg_relation_size(indexrelid) DESC;

(If idx_scan is 0, nobody is querying it. Drop it to speed up your writes).

Conclusion: Measure, Don't Guess

Indexing is a game of trade-offs. You are trading write performance and disk space for read performance.

Never add an index blindly. Always use EXPLAIN ANALYZE before and after your query to see exactly what the query planner is doing. Look for "Seq Scan" (Bad) turning into "Index Scan" (Good) or "Index Only Scan" (Best).

Start with the cardinality of your data, build smart composite indexes, leverage covering indexes for hot paths, and ruthlessly prune unused indexes. Your database—and your users—will thank you.