Database Query Performance and Indexing: The Rules That Actually Matter

What Database Query Performance Discipline Actually Protects

Ask five engineers why a query got slow and four will blame the database. That's usually wrong. The person who wrote SELECT * or skipped the LIMIT wasn't cutting a corner - they were doing the fastest, most defensible thing available to someone who could only see their own endpoint. A query that returns in 40ms in development gives no signal about what it costs at ten million rows.

None of the seven principles below are really about carelessness. They're about the fact that indexing is the visible 20% of query performance work, and the other 80% - round trips, result size, containment - is invisible from any seat that only ever tests against a small dataset.

1. Indexes Must Match Real Query Patterns

A join that used to return in 40ms starts taking 4 seconds as a table crosses a few million rows, and the query hasn't changed - only the data has grown into a foreign key column nobody indexed. Index every foreign key column in the migration that creates it, and index every column that appears in a WHERE or ORDER BY clause in the migration that introduces the query - not in a follow-up migration written during an incident. The primary key index covers lookups by ID. It covers nothing else.

CREATE TABLE orders (
  customer_id BIGINT REFERENCES customers(id)
);
-- no index on customer_id

CREATE INDEX CONCURRENTLY idx_orders_customer_id
  ON orders (customer_id);

Every index added here slows writes to that table and costs storage - index the columns a query actually filters, joins, or sorts on, and always build with CONCURRENTLY. A plain blocking build holds a write lock for the full duration.

2. Wrapped Columns Make Indexes Invisible

A team indexes email, confirms the index exists in the schema, and still watches the login endpoint run a full table scan under load. The query is WHERE LOWER(email) = ? — a reasonable line to write, and one that makes the index on email invisible to the planner, because the planner sees a function call, not a bare column it can use for an access path. Never apply a function, cast, or transformation to the indexed side of a predicate.

SELECT id FROM users
WHERE LOWER(email) = 'sam@acme.com';

SELECT id FROM users
WHERE email = LOWER('sam@acme.com');

The same rule breaks a different way with a leading wildcard: LIKE '%refund%' can't anchor to a B-tree index, but LIKE 'refund%' can. Genuine substring search should reach for a trigram index instead of a leading-wildcard workaround.

3. Per-Row Queries Multiply Round Trips

A dashboard that lists 50 orders makes 51 database round trips: one to fetch the orders, and one more per order to lazily load its customer. Nobody wrote a loop that looks alarming — an ORM did it automatically the first time someone touched order.customer inside a for loop. The same tax hits the write side: inserting one event at a time pays a full round trip per row instead of per batch.

INSERT INTO events (user_id, kind)
VALUES (1, 'click'), (2, 'click'), (3, 'click');

Vlad Mihalcea's canonical writeup of the N+1 query problem covers the ORM side of this same mistake in detail — the fix there is an eager-loading directive, but the underlying defect is identical: one query per row instead of one query for the set.

Eager-loading every association by default just relocates the N+1 problem into one oversized query. Scope eager loads to what the specific code path needs, the same way a batch size should match the write volume, not exceed it.

4. Unbounded Queries Grow Into Outages

A paginated list endpoint is fast on page 1 and unusably slow on page 200, because OFFSET 4000 tells the database to read and discard four thousand rows before it can hand back the twenty the client asked for. The same shape shows up with no pagination at all: an endpoint with no LIMIT, quietly fine until a customer with 200,000 records hits it and the request times out or exhausts memory.

SELECT id, status FROM orders
WHERE created_at < :last_seen
ORDER BY created_at DESC LIMIT 20;

This is the keyset pagination technique — sometimes called the seek method — and it replaces OFFSET's linear cost with a WHERE condition on the last value the client already saw.

Keyset pagination can't jump straight to an arbitrary page number the way OFFSET can. If the product genuinely needs 'go to page 47,' that's a real constraint to negotiate, not a detail to quietly drop.

5. Stop Shipping Data You Don't Need

An endpoint that only displays a customer's name and order status still pulls every column on the row, including a JSON blob nobody renders, because SELECT * was the fastest thing to type. A few tables away, a report sums a year of transactions by fetching every row into application memory and adding them up in a loop - work the database was already built to do in one pass, without putting the raw rows on the wire.

SELECT * FROM orders WHERE customer_id = :cid;
-- summed in application code after fetching every row

SELECT id, status, total_cents FROM orders WHERE customer_id = :cid;
SELECT SUM(amount) FROM payments WHERE customer_id = :cid;

Pick any query on a hot path and check whether it could qualify for an index-only scan if it named its columns instead of selecting everything — usually the same query worth checking for app-side aggregation too.

6. Timeouts Contain the Blast Radius

One slow query holds a connection open. Ten slow queries at once hold ten connections open. If the pool has no ceiling, the database runs out of connections; if the pool has a ceiling but no acquisition timeout, every other request queues silently behind those ten until the whole service looks down - even though nine services out of ten never touched the slow query.

Does every connection pool in your services have an explicit maximum size and acquisition timeout, or is it running on whatever the driver defaults to? Check the statement timeout on the same connections while you're there.

PgBouncer's own configuration reference and Postgres's documentation on statement_timeout are the two settings worth having memorized — one bounds the wait for a connection, the other bounds how long a query gets to hold one.

7. No Reviewer Scales to Every Query

None of the six principles above is controversial, and no engineer who reads this guide will disagree with a single line of it. The problem shows up anyway, because a codebase with 150 engineers doesn't have six query patterns - it has thousands of call sites, spread across services and ORMs, each hiding its own version of the same six mistakes behind a slightly different API.

Count how many distinct call sites, across any three services, issue raw SQL or ORM queries outside a shared query layer. If that number is in the hundreds and no single team owns all of them, the discipline above is already drifting somewhere nobody can see from a code review.

This is the principle that makes the rest of this guide's argument land, because nobody who introduced any single violation did anything a reviewer would have flagged. Query performance decays as an aggregate property of the codebase - which is why it has to be checked as one.

How Pandorian Enforces This Guide

Every principle above becomes a specific, checkable condition once it's expressed as a rule instead of a habit someone has to remember. Pandorian enforces this guide across three surfaces:

Caught at PR level

Every pull request is checked against the diff before merge. A wrapped predicate, a missing LIMIT, a query sitting inside a loop - these surface with the specific line, the rule, and a suggested fix where one is mechanical.

Full scans

An on-demand scan across the whole codebase establishes the day-one baseline: the unindexed foreign keys from three years of migrations, the SELECT * queries written before anyone owned this standard, the N+1 call sites nobody had reason to look for.

Scheduled scans

The same full scan, run on a cadence, catches drift introduced outside any pull request - a config change that dropped the statement timeout, a dependency upgrade that reset a connection pool default.

A Taste of the Guideline Package

Four of the checks that ship in this package - each runs at the PR level, on full or scheduled scans, or several at once.

FAQ