Making a Postgres App Faster, in Order

Most performance work on a database-backed application is the same four moves: index what gets filtered, stop issuing a query per row, stop making round trips you do not need, and cache what is genuinely expensive. The moves are not interesting. The order is.

Why the order matters

Do them backwards and each one hides the next. Cache first and the slow query is still slow — you just only feel it on a miss, at which point you have the original latency plus a stampede of concurrent misses all running the same bad query. Batch the N+1 before adding the index and you turn one hundred slow queries into one query that reads the whole table.

Indexes first, because they make individual queries cheap. Then N+1, because they make the number of queries small. Then round trips, because latency is not compute. Caching last, because a cache is a bet that the work underneath is both expensive and stable, and you cannot know that until the first three are done.

Indexes

A B-tree index is a sorted copy of some columns plus a pointer back to the row. That framing answers most questions people have about them, including why they are not free: every index has to be maintained on every insert, update and delete that touches its columns. An unused index is not neutral, it is a tax on writes and a claim on memory.

The first thing to get right is composite column order, because it is the most common thing to get wrong. An index on (tenant_id, created_at) can serve:

WHERE tenant_id = $1                              -- yes, leftmost prefix
WHERE tenant_id = $1 ORDER BY created_at DESC     -- yes, sorted for free
WHERE tenant_id = $1 AND created_at > $2          -- yes
WHERE created_at > $2                             -- no leading column:
                                                  -- scans the whole index, or the table

The rule that falls out: equality columns first, then the column you range over or sort by. An index sorted by tenant then time can jump to a tenant and walk forward through time. Sorted by time then tenant, it cannot answer “this tenant, recent first” without scanning.

Sorting for free is the underrated half of that. A query with ORDER BY … LIMIT 20 backed by a matching index reads twenty rows. Without it, Postgres sorts the entire matching set and discards all but twenty — the same result at a completely different cost.

Why the index is being ignored

The frequent frustration is an index that exists and is not used. There are only a few reasons.

A function wraps the column. WHERE lower(email) = $1 cannot use an index on email, because the index stores email, not lower(email). Index the expression instead: CREATE INDEX ON users (lower(email)).
The types do not match. Comparing a bigint column to a string parameter forces a cast on the column side, with the same effect as wrapping it in a function.
The value is not selective enough. If a condition matches a large share of the table, a sequential scan genuinely is cheaper than jumping between index and heap, and the planner is right to prefer it. Indexing a two-value status column on a table where half the rows share a value achieves nothing.
The statistics are stale. The planner chooses on estimated row counts. When the estimate is off by orders of magnitude, it picks badly — visible in EXPLAIN ANALYZE as a wide gap between estimated and actual rows. ANALYZE the table and look again.
Prefix matching under a non-C collation. LIKE 'foo%' needs an index built with text_pattern_ops to be usable.

Everything above is diagnosed with one command, and it is worth reading properly rather than skimming for the word “Seq”:

EXPLAIN (ANALYZE, BUFFERS) SELECT …;

-- What to read, in order of usefulness:
--   actual rows vs estimated rows   → bad estimate means stale statistics
--   Buffers: shared hit vs read     → "read" is disk, "hit" is cache
--   Seq Scan on a large table       → missing or unusable index
--   Sort / Hash                     → work the index could have done
--   Nested Loop with high loop count → an N+1 that moved into the database

Partial and covering indexes

Two Postgres features that are underused relative to how much they buy.

Partial indexes index a subset of rows. The classic case is a queue or status column where the interesting rows are a tiny minority:

CREATE INDEX idx_jobs_pending ON jobs (created_at)
  WHERE status = 'pending';

On a table of ten million jobs where a few thousand are pending, this index holds a few thousand entries. It stays in memory, it costs almost nothing to maintain, and it makes the queue query instant. A full index on status would be larger, slower and mostly useless.

Covering indexes let a query be answered from the index alone, without visiting the table at all:

CREATE INDEX idx_posts_feed ON posts (tenant_id, created_at DESC)
  INCLUDE (title, slug);

One caveat that catches people: an index-only scan still has to confirm row visibility, and it does that through the visibility map. On a table with heavy write churn and lagging autovacuum, the map is out of date and Postgres falls back to reading the heap anyway. The covering index is not wrong, it is just quietly not helping.

In production, build indexes with CREATE INDEX CONCURRENTLY. The plain form takes a lock that blocks writes for the entire build, which on a large table means a write outage. The concurrent form does not, at the cost of being slower, being unable to run inside a transaction, and leaving an INVALID index behind if it fails — which you have to drop and rebuild rather than ignore.

Finding what to index is a query, not a guess. pg_stat_statements ranks queries by total time, which is the number that matters — a 4ms query called two hundred thousand times costs more than a two-second report someone runs twice a day. pg_stat_user_tables shows sequential scans against index scans per table, and pg_stat_user_indexes shows indexes with zero scans, which are pure write tax and should be dropped.

N+1 queries

One query to fetch a list, then one more per item. Twenty rows on a page becomes twenty-one queries, and it does not announce itself: each query is fast, the endpoint is merely slow, and it gets worse in exact proportion to how much data a customer has.

const orders = await repo.find({ where: { tenantId } });
for (const order of orders) {
  order.customer = await customers.findOne(order.customerId);   // N
}

ORM lazy loading is the usual source, but the shape appears anywhere: a loop calling a service method, a GraphQL resolver that fetches per field, a React server component mapping over results. If a query is inside a loop, it is this.

The general fix is to collect the identifiers first and issue one query for all of them:

const orders = await repo.find({ where: { tenantId } });
const ids = [...new Set(orders.map((o) => o.customerId))];
const customers = await repo.query(
  `SELECT * FROM customers WHERE id = ANY($1)`, [ids]
);
const byId = new Map(customers.map((c) => [c.id, c]));
orders.forEach((o) => (o.customer = byId.get(o.customerId)));

Two queries regardless of list length. A DataLoader-style batcher generalises this — it collects lookups within a tick and flushes them as one ANY query — and it is the right tool when the fetch points are spread across resolvers rather than sitting in one loop.

The trap on the other side is fixing N+1 by joining everything. Join orders to items and to tags in one query and the database returns orders × items × tags rows — a hundred orders with ten items and five tags each is five thousand rows carrying the same order data over and over, which is often slower than the N+1 you replaced. One join per collection is fine; two multiply.

When you genuinely want a nested shape in one trip, let Postgres build it:

SELECT o.*, agg.items
FROM orders o
LEFT JOIN LATERAL (
  SELECT json_agg(json_build_object('id', oi.id, 'qty', oi.qty)) AS items
  FROM order_items oi WHERE oi.order_id = o.id
) agg ON true
WHERE o.tenant_id = $1;

One round trip, no row multiplication, and the aggregation happens next to the data instead of across the network.

The durable fix is not the patch, it is the regression test. Count queries per request in development and assert a ceiling in the test for the endpoint — this route issues at most three queries. That test fails the day someone adds an innocent-looking lazy relation, which is the day it is cheap to fix rather than six months later under load.

Round trips

Once each query is cheap and there are few of them, what remains is waiting. Fifty queries at two milliseconds of network latency each is a tenth of a second of doing nothing, and it does not show up in any single query's timing.

Write in bulk. A loop of individual UPDATEs becomes one statement against a VALUES list, or one INSERT … ON CONFLICT DO UPDATE. For genuinely large loads, COPY is an order of magnitude beyond either.
Parallelise what is independent — but bound it. Firing forty concurrent queries at a ten-connection pool means thirty of them queue, and you have added scheduling overhead to the same serial wait.
Pool connections, especially serverless. Every Postgres connection is a backend process with real memory cost, and function instances that each open their own will exhaust max_connections long before the database runs out of CPU. A transaction-mode pooler in front is not optional at that shape.
Paginate by key, not offset. OFFSET 10000 makes the database produce and discard ten thousand rows on every page load. Keyset pagination reads only what it returns:

-- instead of OFFSET, carry the last row's sort key
WHERE tenant_id = $1 AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Note that this is exactly the (tenant_id, created_at) index from the first section doing its job. Deep pages cost the same as the first one.

The same shape exists between services. An endpoint that calls three internal services in sequence, each doing its own queries, is an N+1 with worse constants — and there the fix is usually to stop crossing the boundary per item rather than to make the boundary faster.

Caching, last

Now that the work underneath is honest, caching is worth it. Cheapest first:

Do not cache. If the query is fast and the data changes constantly, a cache adds an invalidation problem and buys almost nothing.
HTTP and CDN caching for public reads. The cheapest request is the one that never reaches your server. s-maxage with stale-while-revalidate serves the old value instantly while refreshing behind it, which is usually what a marketing or content page wants.
In-process memory for small, hot, rarely-changing reference data — feature flags, config, currency rates. Zero network cost, but every instance holds its own copy, so invalidation means broadcasting or accepting a short TTL.
Redis for derived results shared across instances: computed aggregates, rendered fragments, anything expensive and reused.
Materialised views when the expensive thing is an aggregate over your own tables. It keeps the cache inside the database where the data already is; REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers, and requires a unique index to work.

Two failure modes to design for up front. The first is the stampede: a popular key expires, fifty requests miss simultaneously, and all fifty run the expensive query at once — often taking the database down at the exact moment the cache was supposed to protect it. Single-flight the fill so one request computes and the rest wait, and jitter your TTLs so keys populated together do not expire together.

The second is invalidation, which is the part that actually decides whether the cache is trustworthy. I wrote about doing it from the database rather than from application code in cache invalidation with Debezium.

Measuring the outcome

This is the part that makes the rest defensible, and the part most often done badly.

A number with no baseline is a claim. Capture the before on the same data, the same query and the same conditions as the after. “It feels faster” measured on a laptop against two hundred seeded rows is not evidence of anything — plans change with table size, and a query that index-scans at a thousand rows may switch strategy at ten million. Benchmark against production-shaped volume or do not quote the number.

Percentiles, not averages. An average is dragged around by whichever bucket has the most requests and hides exactly the requests that hurt — the customer with ten times the data, the cold cache, the page nobody tested. Track p50, p95 and p99. Improvements usually show up at p95 first, and so do regressions.

Measure at the layer the user experiences. Making a query 40% faster when it is 5% of request time is a 2% improvement. The query timing is how you verify the fix worked; the endpoint timing is whether it mattered. Both, in that order.

What is worth tracking, concretely:

endpoint latency at p50/p95/p99, before and after, under comparable load
queries per request — the number that catches a reintroduced N+1
pg_stat_statements total time per query, which tells you where to go next rather than where you just were
cache hit rate, since a cache below roughly 80% is usually caching the wrong thing
write throughput after adding indexes — the cost side of the ledger, and the one nobody checks

Then report it honestly. “p95 on the listing endpoint went from 1.4s to 380ms at the same concurrency, with insert throughput on that table down about 6% from two new indexes” is a sentence someone can act on. “Improved performance by 35%” is a sentence nobody can check, including you, in six months when it regresses.

The short version

Index for the query you actually run, with equality columns first. Collect identifiers and fetch in one round trip instead of looping. Reduce the number of trips before you reduce the cost of each one. Cache only what stayed expensive after all that, and plan for the stampede and the invalidation before you turn it on. Then measure at the percentile your users live at, against a baseline you can reproduce.