Database Performance: The Queries, Indexes, and Habits That Actually Matter

Nine out of ten "the site is slow" reports we investigate end at the database. Not at the server, not at the framework — at a query doing ten times more work than it should. The good news: most database performance problems are boring, predictable, and fixable without a server upgrade. Here's what we actually check.

Measure before you touch anything

Optimizing without measurements is guessing. Two cheap habits catch most problems early:

Turn on the slow query log on production (PostgreSQL: log_min_duration_statement, MySQL: slow_query_log) — anything over 200–300 ms is a suspect, not a statistic.
Capture queries during development. In Laravel, DB::listen() or an in-app query profiler shows every query a page fires — and the N+1 trap becomes visible in one click.

If a query isn't slow in the logs, it's not the problem. Measure first, optimize second.

The N+1 problem: one query that becomes a hundred

The single most common performance bug in Laravel + Vue (Inertia) apps isn't an algorithm — it's lazily loading a relationship inside a loop:

// Bad: 1 query for posts + 1 per post for author — 101 queries total
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name;
}

// Good: 2 queries total, one JOIN for the whole collection
$posts = Post::with('author')->get();

The same trap exists when serializing data for the frontend. On a 50-item list page, the difference between eager loading and lazy loading is the difference between 2 queries and 51. This is the first thing we check in any slow report — it's responsible for more "slow sites" than anything else.

Indexes: cover the hot paths, not everything

Indexes are the difference between a table scan and a point lookup, but they cost write speed and disk space — so index what your queries actually filter on:

Foreign keys used in WHERE or JOIN are the default candidates — they're almost always worth an index.
Composite indexes work left to right. An index on (status, created_at) serves WHERE status = ? and WHERE status = ? AND created_at < ?, but not WHERE created_at < ? alone. Order columns by how selective they are, and by how you filter.
Never index just because you can. A table that's mostly written pays for every index on every insert.

Verify with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL): if you see "Seq Scan" on a hot query with a WHERE clause, you've found the missing index. The explain output doesn't lie.

Small query habits that snowball

Select only the columns you need. SELECT * pulls columns you'll never touch, bloating memory and network transfer.
Paginate, don't paginate-in-theory. Loading ten thousand rows into a Vue table never ends well — paginate on the server and let the frontend page through.
Don't wrap indexed columns in functions. WHERE YEAR(created_at) = 2026 can't use the index on created_at; WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' can.

Catch problems in migrations, not in production

A migration is the cheapest place to fix a schema. Two rules we follow:

Add the index the query needs at the same time you write the query. Reviewing pairs of "query + index" in the same pull request catches more than any retrospective.
Test against a realistic dataset. Ten rows in development hide every performance problem. If production has a million, seed a representative slice.

When an index is discovered late on a big Postgres table, CREATE INDEX CONCURRENTLY adds it without locking writes — a production-safe fix that doesn't require downtime.

Fast enough beats perfectly tuned

Database tuning has sharply diminishing returns. The 80/20 is: fix N+1s, index your WHERE/JOIN hot paths, and paginate big lists. That covers the overwhelming majority of real-world slowness. Once queries are sane, the next performance lever is caching — but that's a separate article. Fix the queries first; they're the source of the truth, and everything else is layered on top of them.