Database Indexes: Why Your App Got Slow and How to Fix It

A familiar story: the app was instant in development. Six months after launch, the dashboard takes four seconds, the orders page times out, and the database CPU sits at 90%. Nothing in the code changed. The data grew.

The cause is very often a missing index, and the fix is often a single line. Here's how to find it.

What an index is

Without an index, finding rows means the database reads every row in the table and checks each one — a sequential scan. With 500 rows, that's instant. With 5 million, it's slow, and it gets slower as the table grows.

An index is a separate, sorted structure (usually a B-tree) that maps column values to row locations — like the index at the back of a book. To find WHERE email = '[email protected]', the database looks it up in the index and jumps straight to the row. The cost grows logarithmically with table size instead of linearly: the difference between milliseconds and seconds at scale.

Every table has an index on its primary key automatically. Most other columns don't — including, in PostgreSQL, foreign keys.

The foreign key trap

This is the single most common missing index in ORM-built apps. You have:

orders.user_id → users.id

Postgres automatically indexes users.id (primary key). It does not automatically index orders.user_id. So every "show this user's orders" query scans the whole orders table. It's fast in development, slow in production — the classic shape of the problem.

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

(Some ORMs create foreign-key indexes for you; many don't. Check.)

Finding the slow queries

Don't guess. Measure.

pg_stat_statements — a Postgres extension (enabled on most managed providers) that records every query shape with its total time, call count, and average time:

SELECT query, calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 1) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time first: a 5 ms query called a million times matters more than a 2-second query called twice.

Slow query logging — setting log_min_duration_statement (say, 200 ms) logs every query slower than that.

Your application's tracing or logs, if they record query durations. (Structured Logging: Logs You Can Actually Search.)

Reading EXPLAIN ANALYZE

Take a slow query and ask Postgres how it runs it:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

Before indexing, you might see:

Limit  (actual time=812.4..812.5 rows=20)
  ->  Sort  (actual time=812.4..812.4 rows=20)
        ->  Seq Scan on orders  (actual time=0.03..798.1 rows=1840 loops=1)
              Filter: (user_id = 42)
              Rows Removed by Filter: 2998160
Execution Time: 812.7 ms

The tell-tale signs: Seq Scan on a large table, and a huge Rows Removed by Filter — it read 3 million rows to keep 1,840.

After adding a suitable index:

Limit  (actual time=0.05..0.09 rows=20)
  ->  Index Scan using idx_orders_user_created on orders
        Index Cond: (user_id = 42)
Execution Time: 0.12 ms

From 812 ms to 0.12 ms.

Note: EXPLAIN ANALYZE actually runs the query. Don't use it on UPDATE or DELETE statements against real data without wrapping them in a transaction you roll back.

Choosing what to index

Index columns that appear in:

WHERE clauses of frequent queries
JOIN conditions — especially foreign keys
ORDER BY — particularly combined with LIMIT (pagination)

Composite indexes

For WHERE user_id = ? ORDER BY created_at DESC, one index on both columns serves the filter and the sort:

CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

Column order matters. An index on (user_id, created_at) helps queries filtering on user_id alone, or on both — but not queries filtering only on created_at. Put equality-filtered columns first, then range or sort columns.

Partial indexes

If you mostly query a subset — say, unpaid invoices — index just those rows:

CREATE INDEX CONCURRENTLY idx_invoices_unpaid
  ON invoices (due_date) WHERE paid = false;

Smaller, faster, cheaper to maintain.

Unique indexes

CREATE UNIQUE INDEX also enforces a rule — for example, one account per email. Often better than checking in application code, which has race conditions.

Indexes that won't be used

Functions on the column: WHERE lower(email) = '...' can't use a plain index on email. Create an expression index on lower(email), or store normalised values.
Leading wildcards: LIKE '%smith' can't use a B-tree index. Full-text search or trigram indexes (pg_trgm) handle these.
Low-selectivity columns: an index on a boolean where 95% of rows are true rarely helps (a partial index usually does).
Tiny tables: Postgres will sensibly choose a sequential scan.

What indexes cost

Indexes aren't free:

Every write updates every index on the table. Heavily indexed tables insert and update more slowly.
They take disk space and memory.
Unused indexes are pure cost. Postgres tracks usage:

SELECT relname AS table, indexrelname AS index, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 over a long period are candidates for removal (excluding those enforcing uniqueness).

Creating indexes safely in production

A plain CREATE INDEX locks the table against writes while it builds — on a large table, that's an outage. Use:

CREATE INDEX CONCURRENTLY ...

It takes longer but doesn't block writes. Two caveats: it can't run inside a transaction block (some migration tools wrap everything in one — check how yours handles it), and if it fails it leaves an invalid index to drop and retry. Ship index changes through migrations like any other schema change. (What Are Database Migrations?)

Using an agent to do this

This is a great task for a coding agent with database access to a development or staging copy with realistic data volumes:

Using pg_stat_statements on the staging database, list the 10 queries with the highest total time. For each, run EXPLAIN ANALYZE, explain the plan, and propose an index if one would help. Write the migrations using CREATE INDEX CONCURRENTLY. Don't apply anything to production.

Realistic data volume is the key — an empty dev database will never show the problem.

The checklist

pg_stat_statements enabled; top queries by total time reviewed
Every foreign key used in queries is indexed
Composite indexes match filter-then-sort patterns
EXPLAIN ANALYZE confirms index use for critical queries
Indexes created with CONCURRENTLY via migrations
Unused indexes reviewed periodically

EasySpawn gives each project a managed Postgres, so Claude Code can profile and index against a development database with realistic data before any change reaches production. See how it works or join the waitlist.