Database Connection Pooling & the N+1 Query Problem

Most backend performance problems aren't exotic — they're the same two or three mistakes wearing different clothes. Connection pooling and the N+1 query problem are two of the most common, and both share a trait that makes them dangerous: the code works fine in development, on a database with ten rows and zero concurrent users, and only falls apart once real traffic shows up.

The hidden cost of opening a connection

Opening a database connection is not free, even though a single line like createConnection() makes it look that way. Under the hood, the client and the database negotiate a TCP handshake, the database authenticates the credentials, and the server spins up the per-connection state it needs — memory buffers, a process or thread to service the connection, transaction bookkeeping. On Postgres, for instance, every connection gets its own backend process. None of that is instant, and all of it happens again if you open a fresh connection for every incoming request.

At low traffic this overhead is invisible. At real traffic — dozens or hundreds of requests per second — it stops being a rounding error and starts being the dominant cost of every request, on top of whatever the query itself takes.

Pooling: pay the setup cost once, not per request

A connection pool sidesteps this by keeping a fixed set of already-authenticated, already-open connections alive for the lifetime of the application. When a request needs to talk to the database, it borrows a connection from the pool, uses it, and returns it — it doesn't open or close anything. The TCP handshake and authentication happened once, when the pool was created, and every request after that amortizes that cost across thousands of reused connections instead of paying it again and again.

This is why virtually every production database client — Postgres'pg-pool, Prisma's connection management, HikariCP in the Java world — pools by default rather than opening connections on demand.

Sizing the pool: there is no "bigger is better"

A pool has a fixed size, and picking that number is a real trade-off, not a formality.

Too small, and requests start queuing for a free connection the moment concurrent traffic exceeds the pool size — every request behind the front of the queue pays extra latency waiting for one to free up, even though the database itself is idle and could easily do more work.

Too large, and you overwhelm the database rather than your application. Each open connection consumes real memory and, more importantly, competes for the database's CPU, locks, and I/O. Past a certain point, adding more concurrent connections doesn't add throughput — it adds context-switching and lock contention on the database server, and total throughput actually drops.

The N+1 query problem

Where pooling is about how connections are managed, N+1 is about how queries are shaped — and it's one of the easiest performance bugs to write without noticing.

The pattern: you fetch a list of N items with one query, then, for each item in that list, issue a separate query to fetch related data. Fifty blog posts plus each post's author looks like it should take two queries — one for the posts, one for the authors — but written naively it takes fifty-one: one for the posts, then one more per post to look up its author.

// 1 query to fetch the posts, then 1 more PER post — 51 queries total
const posts = await db.posts.findMany({ take: 50 });

const withAuthors = await Promise.all(
  posts.map(async (post) => {
    const author = await db.users.findUnique({ where: { id: post.authorId } });
    return { ...post, author };
  })
);

// Fixed: 1 query, author data joined in
const posts = await db.posts.findMany({
  take: 50,
  include: { author: true },
});

The fixed version replaces the per-row lookup with a single query that brings the related data along with it — a JOIN under the hood — so the total stays at one query no matter how many posts come back.

Why ORMs make this so easy to write by accident

N+1 rarely shows up as obviously as the loop above. Most ORMs support lazy-loaded relations, where accessing post.author looks like a plain property read but silently triggers a database query the first time it's touched. Loop over fifty posts and read .author on each one, and you've written an N+1 query without a single line of code that looks like a query. The bug hides behind ordinary-looking property access, which is exactly why it survives code review and only shows up once someone loads a page with a realistic amount of data.

The fix: eager loading and batching

The general fix is to stop letting related data get fetched lazily, one row at a time, and instead fetch it eagerly, up front. In SQL terms that means a JOIN, or — when a join would multiply and duplicate rows awkwardly — one batched follow-up query that fetches all the related rows for the whole list at once ("fetch these 50 posts, then fetch all authors whose id is in this set") and stitches them together in memory. Either way, the query count stops scaling with the size of the list.

GraphQL servers hit this problem constantly, since a resolver for a field like author runs once per item in a result list by design. The standard fix there is the same idea under a different name: DataLoader-style batching collects all the individual lookups requested during a single tick, deduplicates them, and issues one batched query instead of one query per resolver call — eager loading, just applied after the fact instead of at query-construction time.