Why Your Database Is Slow (And It's Not What You Think)

Most developers blame their database when queries are slow. Add more indexes. Switch databases. Rewrite queries.

But after indexing 2,800+ articles and optimizing query performance across a production knowledge base serving millions of page views, I learned something: most slow databases don't have a query problem — they have an index strategy problem.

Here's what actually matters.

The Index You Don't Have Is the Slowest Query

The most common performance killer isn't missing indexes. It's missing composite indexes.

You have an articles table with category_id, status, and published_at. You indexed each column separately. PostgreSQL (or MySQL) can only use one index per table scan. It picks the most selective one and does a full scan on the rest.

The fix:

CREATE INDEX idx_articles_category_status_published 
ON articles (category_id, status, published_at DESC);

One composite index replaces three single-column indexes and can be 10-100x faster for filtered queries.

Rule of thumb: Index the columns you filter by (WHERE), in the order of most selective to least selective. Add ORDER BY columns at the end.

The Index You Have Might Be Working Against You

Every index speeds up reads but slows down writes. Each INSERT, UPDATE, or DELETE must update every index on that table.

On our knowledge base, we had a table with 14 indexes. Write operations took 4x longer than necessary. After auditing query patterns, we removed 7 indexes that were never used by the query planner.

Check unused indexes:

-- PostgreSQL
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY idx_scan ASC;

If an index has zero scans in production, it's costing you on every write for nothing.

Covering Indexes: The Secret Weapon

A covering index includes all the columns a query needs. The database never touches the table — it gets everything from the index alone.

-- Query
SELECT title, slug, created_at FROM articles WHERE status = 'published';

-- Covering index
CREATE INDEX idx_articles_covering 
ON articles (status) INCLUDE (title, slug, created_at);

For read-heavy pages like our article listing, covering indexes turned 50ms queries into sub-millisecond lookups. The database serves the entire response from a B-tree in memory.

The N+1 Problem Hides in Plain Sight

You optimized your queries. Your indexes are perfect. And it's still slow.

Check your application layer. The most common cause of "slow database" complaints is actually N+1 queries — fetching related data one row at a time.

# Bad: N+1
articles = db.query("SELECT * FROM articles WHERE status = 'published'")
for article in articles:
    author = db.query("SELECT * FROM users WHERE id = ?", article.author_id)

# Good: Single query with JOIN
results = db.query("""
    SELECT a.*, u.name as author_name 
    FROM articles a 
    JOIN users u ON a.author_id = u.id 
    WHERE a.status = 'published'
""")

Use your ORM's eager loading. Log every query in development. If you see the same query pattern repeating with different IDs, you have an N+1.

When to Stop Optimizing

Premature optimization wastes time. But unmeasured optimization wastes more.

Before you add an index:

1. Run EXPLAIN ANALYZE on the slow query
2. Check if it's doing a sequential scan on a large table
3. Verify the query pattern is common (not a one-off admin report)
4. Test the index impact on write performance

After you add an index:

1. Run EXPLAIN ANALYZE again — verify it's actually being used
2. Monitor write latency on that table
3. Check index size — a bloated index can hurt more than help

The Real Answer

Fast databases aren't about the latest technology. They're about:

1. Knowing your query patterns — log everything, optimize what's hot
2. Using composite indexes strategically — one good composite beats five singles
3. Removing dead weight — unused indexes are silent performance drains
4. Fixing N+1 at the application layer — no database can save bad query patterns
5. Measuring before and after — EXPLAIN ANALYZE is your best friend

If you want to dive deeper into database performance, architecture patterns, and the infrastructure lessons we learned scaling to millions of page views, I've documented it all in my knowledge base: codcompass.com

---

What's your most painful database performance story? Drop it below — I've seen it all.