Database Optimization: Indexing Strategies for High-Traffic Apps

Slow databases kill applications. When a page takes seconds to load, the bottleneck is very often a single query scanning far more data than it needs to, and the fix is very often an index. A few lines of SQL can turn a query that reads a million rows into one that reads a handful of pages. This guide covers how indexes work, how to design them for real query patterns, how to verify they're used, and the mistakes that quietly undo all of it. Examples use PostgreSQL, but the principles carry to most relational databases.

What an index actually is

An index is a separate structure the database maintains alongside your table, sorted so lookups are fast. The default kind in PostgreSQL is a B-tree: a balanced tree the database can descend in a few steps rather than reading the table row by row.

Without an index, a query like this forces a sequential scan:

SELECT * FROM users WHERE email = 'alice@example.com';

The database has no way to know where that email lives, so it checks every row. On a million-row table that means reading the entire table from disk to return one row. With an index on email, the database descends the B-tree in a handful of page reads and jumps straight to the matching row:

CREATE INDEX idx_users_email ON users (email);

The difference is not a percentage improvement; it changes the shape of the work. A sequential scan grows linearly with table size, while a B-tree lookup grows logarithmically. That's why a query that felt fine at ten thousand rows falls over at ten million, and why the same query returns in milliseconds once indexed.

The trade-off: every index must be updated on every INSERT, UPDATE, and DELETE that touches its columns, and each consumes disk space. Indexes make reads cheaper by making writes slightly more expensive. That exchange is worth it for columns you actually query, and pure waste for columns you don't.

Single-column indexes

The basic form serves any query that filters, joins, or sorts on that column:

CREATE INDEX idx_users_email ON users (email);

Good candidates are columns in WHERE clauses, JOIN conditions, ORDER BY clauses, and DISTINCT operations. Primary keys are indexed automatically, but foreign keys in PostgreSQL are not, and unindexed foreign keys are a common real-world performance hole: every join and cascading delete pays for it.

Composite indexes: order matters

A composite index covers multiple columns, and the order you declare them in decides which queries it can serve:

CREATE INDEX idx_orders_user_date
ON orders (user_id, created_at);

Think of it like a phone book sorted by last name, then first name. It's excellent for finding "Smith, then John" and useless for finding everyone named John regardless of last name. This index serves WHERE user_id = 5 and WHERE user_id = 5 AND created_at > '2026-01-01', but a query filtering only on created_at can't use it, because rows are grouped by user_id first.

The design rule that follows from this:

1. Equality filters first (user_id = 5)
2. Range filters after (created_at > '2026-01-01')
3. Sort columns last, so ORDER BY can read rows already in order

Get the order backwards and the index degrades to filtering a much larger slice of the tree than necessary, or gets skipped entirely.

Covering indexes: skip the table entirely

Normally an index lookup is a two-step trip: find the entry in the index, then fetch the full row from the table. If the index itself contains every column the query needs, PostgreSQL can answer from the index alone with an index-only scan:

-- The query only needs email, filtered by id
SELECT email FROM users WHERE id = 123;

-- INCLUDE stores email in the index without making it part of the key
CREATE INDEX idx_users_id_email
ON users (id) INCLUDE (email);

The INCLUDE clause adds payload columns to the index leaf pages without bloating the searchable key. For hot queries that read one or two columns from huge tables, this eliminates the table visit entirely and is one of the cheapest big wins available.

Matching indexes to query patterns

The right index depends entirely on how you query, so guessing rarely works. A cheat sheet for common patterns:

Two deserve a closer look. An expression index stores a function's result; a plain index on email cannot serve WHERE lower(email) = ... because the function hides the raw column:

CREATE INDEX idx_users_email_lower ON users (lower(email));

A partial index covers only rows matching a condition, keeping it small when you query a thin slice of a big table:

-- Pending orders are a tiny, hot subset of a huge table
CREATE INDEX idx_orders_pending
ON orders (user_id) WHERE status = 'pending';

Knowing what not to index matters too: columns you rarely filter by, and tables so small a scan is already instant. Every unused index is pure write overhead.

Verify with EXPLAIN ANALYZE

Never assume an index is used; ask the database. EXPLAIN ANALYZE runs the query and shows the actual plan:

EXPLAIN ANALYZE
SELECT * FROM orders o
WHERE o.user_id = 123
  AND o.status = 'pending'
ORDER BY o.created_at DESC;

If the plan shows Seq Scan on orders with a filter, the database is reading the whole table. Add the matching composite index:

CREATE INDEX idx_orders_user_status
ON orders (user_id, status, created_at);

Re-run the EXPLAIN ANALYZE and you should see Index Scan using idx_orders_user_status and, crucially, no separate Sort node: because created_at is the last key column, the index hands back rows already in order. When reading plans, focus on the scan type, the actual row counts versus the planner's estimates (large gaps mean stale statistics; run ANALYZE), and any sort or hash steps processing more rows than the query returns.

One production note: plain CREATE INDEX locks the table against writes while it builds, which on a large table can mean minutes of downtime. Use CREATE INDEX CONCURRENTLY on live systems.

Beyond indexes

When indexes alone stop being enough, three techniques carry most high-traffic systems.

Denormalized counters. Counting related rows on every page view gets expensive at scale, even indexed. Storing a maintained count trades a little write complexity for near-free reads:

-- Normalized: counts comments on every request
SELECT COUNT(*) FROM comments WHERE post_id = 5;

-- Denormalized: store the count on the post,
-- updated by a trigger or in application code
ALTER TABLE posts ADD COLUMN comment_count INT NOT NULL DEFAULT 0;

The cost: the counter can drift if any write path forgets to update it, so keep the update logic in one place.

Partitioning. Splitting a huge table into per-range child tables lets queries touch only relevant partitions, and lets you drop old data instantly instead of running giant deletes:

CREATE TABLE orders_2026_06 PARTITION OF orders
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- This query now reads one partition, not the whole history
SELECT * FROM orders
WHERE created_at >= '2026-06-01'
  AND created_at < '2026-07-01';

Partitioning shines for time-series data like orders, events, and logs. It's a poor fit for tables queried without the partition key, since those queries must check every partition.

Caching. The fastest query is the one you never run. A cache layer like Redis absorbs repeated reads of hot data:

import json

def get_user_profile(user_id: int):
    cache_key = f"user:{user_id}"

    # Try the cache first
    cached = redis.get(cache_key)
    if cached:
        return json.loads(cached)

    # Fall back to the database, then cache for an hour
    user = db.query(User).filter(User.id == user_id).first()
    redis.setex(cache_key, 3600, json.dumps(user.to_dict()))
    return user.to_dict()

The hard part of caching is invalidation. Decide how stale each piece of data may be, set TTLs to match, and delete keys when the underlying row changes. A cache without an invalidation plan is a bug factory.

Keep indexes healthy

Indexes are not fire-and-forget. Query patterns drift, and indexes that no longer serve anything keep taxing every write. PostgreSQL tracks usage for you:

-- Find indexes that have never been scanned
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Drop what's confirmed unused
DROP INDEX idx_unused;

-- Rebuild a bloated index without blocking writes
REINDEX INDEX CONCURRENTLY idx_orders_user_status;

Check this on a schedule, but read it carefully: an index might exist solely for a monthly report, so confirm before dropping. Heavily updated indexes also accumulate bloat; a concurrent reindex restores their speed.

Common mistakes

• Indexing everything. Each index taxes every write and competes for cache memory. Index for the queries you actually run.
• Wrong column order in composite indexes. Equality columns first, then ranges, then sort columns. Backwards order can make the index worthless.
• Wrapping indexed columns in functions. WHERE lower(email) = ... skips a plain index on email; use an expression index instead.
• Leading-wildcard searches. LIKE '%gmail.com' can't use a B-tree. If you need substring search, look at trigram indexes (pg_trgm) or full-text search.
• Never reading query plans. Without EXPLAIN ANALYZE, you're decorating tables with indexes and hoping.
• Forgetting foreign keys. PostgreSQL doesn't index them automatically, and joins suffer for it.

Frequently asked questions

How many indexes are too many for one table?

There's no universal number; there's a budget. Each index slows writes and consumes memory, so a write-heavy table tolerates fewer than a read-heavy one. If you can't name the query an index serves, it's a candidate for removal.

Should I use several single-column indexes or one composite index?

For queries that always filter on the same combination, one composite index in the right order wins. PostgreSQL can combine single-column indexes with bitmap scans, but it's typically slower, and a composite on (a, b) also serves queries on a alone.

Why isn't PostgreSQL using my index?

The usual suspects: the table is small enough that a scan is genuinely cheaper, a function or type cast is hiding the column, the query matches only a non-leading column of a composite, or statistics are stale (run ANALYZE). EXPLAIN tells you which one you're dealing with.

Do indexes help with writes at all?

Mostly they cost writes, with one exception: UPDATE and DELETE statements with a WHERE clause use indexes to find the target rows quickly. Unique indexes also enforce constraints, which is a correctness win worth its write cost.

Where to go from here

Indexing rewards a methodical loop: understand your query patterns, create the matching index, prove it with EXPLAIN ANALYZE, and prune what's unused. If you want to build that skill hands-on, Kodion's interactive SQL & Databases course takes you from your first SELECT through indexes, query plans, and production practices, and the Database Design & ORMs track shows how these decisions play out in real application schemas.