Index column order#
A composite index works left to right, like a phone book sorted by surname, then first name. You can find every “Suryavanshi” fast. You cannot find every “Gaurav” without reading the whole book.
CREATE INDEX idx_claims ON claims (status, created_at);
-- Uses the index: the first column is there.
WHERE status = 'PENDING' AND created_at > now() - interval '7 days'
-- Cannot use it: the first column is missing.
WHERE created_at > now() - interval '7 days'Put equality columns first, then the range or sort column. A wrong order does not throw an error. It just scans — fast on your laptop, very slow on ten million rows.
Read the plan#
Run EXPLAIN (ANALYZE, BUFFERS) on the query and look for three things: a Seq Scan on a big table, estimated rows far from actual rows, and a high “Rows Removed by Filter” — rows read only to be thrown away.
The query inside a loop#
// 1 query, then 1 more per claim: 51 round trips for 50 claims.
for (const claim of claims) {
claim.member = await db.members.find(claim.memberId);
}
// 2 queries, however many claims there are.
const ids = [...new Set(claims.map((c) => c.memberId))];
const members = await db.members.findMany({ id: { in: ids } });The slow version often reads better, so it passes code review. The lasting fix is a test: count the queries each endpoint runs, and fail the build when it goes over a limit.
OFFSET gets slower the deeper you go#
OFFSET 100000 makes the database read 100,050 rows to give you 50. Keyset paging reads only the 50 you want, at any depth.
SELECT * FROM claims
WHERE (created_at, id) < ($1, $2) -- the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 50;A smaller pool is faster#
A good start is (CPU cores × 2) + 1 connections — in total, across all servers. Past what the hardware can really run at once, extra connections only add waiting. A short queue in your app in front of a small pool beats a huge pool.
Hot rows#
A counter that every request updates becomes the speed limit of your whole system, because each update waits for the last one. Split it across many rows and add them up when you read it.
Replica lag#
Reading from replicas works until a user saves something and then cannot see it, because the replica is a moment behind. For a couple of seconds after a user writes, read their data from the primary. Then go back to replicas.