Most slow applications are slow in the database. Fortunately the causes are repetitive, and a disciplined workflow resolves most of them without exotic tuning.

Start with measurement

Guessing which query is slow is how you end up with indexes nobody uses. Find the real offenders first.

• Enable the slow query log, or pg_stat_statements on PostgreSQL
• Sort by total time, not only by single-query duration. A 5 ms query run 200,000 times beats one 2 s report
• Reproduce the query with realistic data volume. A plan on a ten-row table tells you nothing

Read the execution plan

EXPLAIN ANALYZE shows what the database actually did.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 1042
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Things to look for:

• Seq Scan on a large table with a selective filter usually means a missing index
• A large gap between estimated and actual rows suggests stale statistics; run ANALYZE
• Sort or Hash nodes spilling to disk point to memory settings or a query that returns too much
• High buffer reads show where the I/O is going

Design indexes for the query

An index helps only when it matches how you filter and sort. For the query above, a composite index fits:

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

Rules of thumb:

• Put equality columns first, then range or sort columns
• A composite index on (a, b) serves queries on a, but not on b alone
• Use partial indexes for hot subsets, such as WHERE status = 'pending'
• Remember the cost: every index slows writes and takes space. Drop the unused ones

Fix the N+1 problem

The classic ORM trap: one query for a list, then one more per row to load a relation.

// N+1: one query for posts, then one per post
const posts = await db.post.findMany();
for (const p of posts) p.author = await db.user.findUnique({ where: { id: p.authorId } });

// Two queries total
const posts = await db.post.findMany({ include: { author: true } });

In Laravel the equivalent is with('author'). Turn on lazy-loading detection in development so the problem fails loudly before production.

Stop over-fetching

• Select only the columns you need instead of SELECT *
• Always paginate list queries
• Avoid OFFSET with large values; use keyset pagination (WHERE id < :last_id)
• Push aggregation into SQL instead of loading rows to sum them in application code

Use transactions and locks carefully

Long transactions hold locks and bloat the database. Keep them short, do not call external APIs inside one, and acquire rows in a consistent order to avoid deadlocks.

Cache last, not first

Caching hides slow queries rather than fixing them, and adds invalidation problems. Fix the query, then cache what remains hot, with a clear expiry strategy.

A repeatable routine

• Identify the costly queries from real statistics
• Inspect the plan
• Fix the query shape, then the index, then the schema
• Re-measure on production-like data to confirm the gain

Performance work is mostly patient measurement. The fix is usually small once you know exactly where the time goes.