Backend performance

How to spot and fix an N+1 query

Load a list, then loop and query once per row, and you have shipped an N+1. Fifty posts become fifty-one queries: one for the list, one per author. In the logs it shows up as the same SELECT ... WHERE id = ? repeating with a different id, over and over, inside one request.

wrong "add an index on the author table and the per-row lookups get fast enough." Every one of those lookups is already a primary-key hit. What hurts is the fifty separate round trips, each paying network latency and holding a pooled connection, not the cost of any single query. An index moves none of that.

The fix is to collapse the round trips. Collect the ids and fetch them in one shot:

SELECT id, name FROM users WHERE id IN (12, 7, 44, 91, ...);

Then join the names back in memory. A SQL JOIN works too. DataLoader (common in GraphQL) automates the same batching per request. Whichever you pick, the goal is a query count that stays flat as the list grows.

Why an unbounded connection pool hurts

In Postgres each connection is a separate backend OS process, roughly 1.3 MB plus the cost to fork it. Give every request its own connection and a traffic spike forks hundreds of processes that then fight over CPU and locks. A pool caps this: a fixed set of connections gets reused, and requests wait for a free one instead of opening more. Size it to what the database can actually run in parallel. Maxing max_connections makes throughput worse, not better.

Why deep pagination gets slow

LIMIT 20 OFFSET 100000 does not jump to row 100,001. The database reads the first 100,000 rows, throws them away, and returns the next 20, so the query gets slower the deeper you page. Keyset pagination remembers the last row you showed and filters on its sort key plus a unique tiebreaker (so rows sharing a timestamp are never skipped or repeated):

SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ($last_seen_created_at, $last_seen_id)
ORDER BY created_at DESC, id DESC LIMIT 20;

That stays fast at any depth because an index positions it directly, and rows inserted mid-scroll no longer shift the page under the user. The tradeoff: you cannot jump to an arbitrary page number, only walk forward and back.

Reading latency: p95 and p99, not the average

Latency is right-skewed. A few slow requests barely nudge the mean while defining what your unlucky users actually feel, so report p50, p95, and p99, not one average. When p50 is low but p99 is high, the tail is coming from garbage-collection pauses, lock contention, a cold cache, or a slow dependency, not from your median code path.

How to size a connection pool

Throughput does not climb with pool size; it peaks at a small number and falls past the knee, because more concurrent connections mean more lock contention and context switching inside the database. HikariCP's rule of thumb is ((core_count * 2) + effective_spindles), which lands most OLTP services in the single or low double digits. Little's Law gives the other estimate: connections needed is roughly target throughput times mean database time per request. At 200 req/s and 20 ms of DB time that is about 4.

wrong "we're seeing connection timeouts, so raise the pool to 200." Vlad Mihalcea's well-known result is the opposite: shrinking an oversized pool cut response times from ~100 ms to ~2 ms with no other change. A small pool queues excess work cheaply in the app instead of thrashing the database.

# pgbouncer.ini
pool_mode = transaction
default_pool_size = 20      # server conns per db/user, not per client
max_client_conn = 5000      # clients can vastly exceed server conns

What transaction pooling breaks

Transaction-mode pooling assigns a server connection only for the length of a transaction, so a handful of Postgres connections serve thousands of clients. The catch is a hard one, not a tuning knob: anything with session state breaks. Session-level prepared statements, SET that outlives a statement, session advisory locks, and LISTEN/NOTIFY all assume one client owns one connection, which transaction mode does not guarantee. Know this before you flip the mode.

Why percentiles do not average

You cannot recover a true p99 by averaging the p99 of each shard or each one-minute window; percentiles are not linear. Aggregate from histograms (HDR, t-digest) instead. Two more traps interviewers listen for. Fan-out: if a request hits 100 backends, the parent waits on the slowest, so each backend's p99 becomes the parent's common case. Coordinated omission: a closed-loop load generator that waits for a slow response before sending the next one stops issuing requests exactly when the system is worst, hiding the tail it should be measuring.

How to answer "the API is slow"

The graded move is a funnel, not a guess. Scope it first (which endpoint, which percentile, since when), then measure one slow request end to end with a trace or flame graph, where wide frames show where wall time actually goes and off-CPU time exposes I/O and lock waits that an on-CPU profile misses. Localize to database, app CPU, a downstream call, or the network before touching code. Then the smallest fix, then confirm the percentile you targeted actually moved, and add a guard (an SLO alert, or a test asserting query count per request). Candidates who reach for a rewrite before they have looked at a single trace are the ones who fail this question.