Database Performance Bottlenecks: N+1 Queries, Missing Indexes, and Connection Pools | How to Center a Div

"Software engineering is more than just centering a div." — howtocenterdiv.com

TLDR: You profiled Node.js, added Redis, switched runtimes. Still slow. The database was the problem the whole time. Fix N+1 queries and missing indexes before you touch anything else.

Same story every time. App is slow. Someone swaps the framework. Still slow. Someone adds Redis. Still slow. Finally somebody opens up the query plan and there it is — a sequential scan across 4 million rows because nobody put an index on user_id. Four seconds per request. Was sitting there the whole time.

Nobody ever looks at the database first though. It's always "let's try Fastify" or "let's add a cache" because those feel like real engineering work. An index is one line of SQL. You can't demo one line of SQL in standup. A framework migration gets you a PR, a benchmark chart, maybe even a blog post. But it won't fix a missing index, obviously.

And honestly? Most backend devs have never been taught to read a query plan. Bootcamps don't go there, tutorials barely mention EXPLAIN, and you end up figuring it all out at 2am during a production fire. After that you never forget it. But it shouldn't take a production incident to learn this stuff.

N+1 Queries

We all learn about N+1 early on. We all still write one at some point:

javascriptCopy<br>1const orders = await db.query('SELECT * FROM orders LIMIT 50');<br>3for (const order of orders) {<br>4 order.user = await db.query(<br>5 'SELECT * FROM users WHERE id = $1',<br>6 [order.userId]<br>7 );<br>8}

51 round trips for 50 orders. PM says "can we do 500 per page?" and now it's 501. The page is slow because of a loop hitting the database on every iteration — nothing to do with Node being slow or not.

What's really annoying is that N+1 hides in dev. You've got 10 rows locally, it returns instantly, you move on. Deploys, data piles up, and six months from now somebody files a Jira ticket about the orders page taking 8 seconds. By then everyone forgot this code existed.

One JOIN:

sqlCopy<br>SELECT orders.*, users.name, users.email<br>FROM orders<br>JOIN users ON orders.user_id = users.id<br>LIMIT 50;

ORMs make it worse because the code looks clean:

javascriptCopy<br>1// Reads beautifully. Fires 51 queries.<br>2const orders = await prisma.order.findMany({ take: 50 });<br>3for (const order of orders) {<br>4 const user = await prisma.user.findUnique({ where: { id: order.userId } });<br>5}<br>7// This does the same thing in 1 query.<br>8const orders = await prisma.order.findMany({<br>9 take: 50,<br>10 include: { user: true },<br>11});

Nothing about that first version looks wrong. Prisma, Sequelize, TypeORM, all of them will do this to you if you don't pay attention. The whole selling point of an ORM is that it hides SQL. But it hides the query count too, and that's the part that bites.

Turn on query logging. Prisma has log: ['query'] on the client, Sequelize takes logging: console.log. Go look at what gets generated. I've opened up codebases where one endpoint was doing 200+ queries because somebody nested includes three levels deep. Endpoint worked, was just painfully slow, and nobody thought to check the logs until users started complaining.

GraphQL makes it uglier because resolvers fire per field, so a single query can spawn dozens of DB calls without anyone realizing. DataLoader was built for exactly this — batches and deduplicates calls within one tick. Retrofitting it later is painful so do it early if you can.

EXPLAIN ANALYZE

Slow query? Don't touch the code yet. Run this first:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

The output tells you how Postgres actually executed the query. Seq Scan is the bad one — it means Postgres read through the entire table, row by row, to find your data. On a table with 200 rows nobody cares. On a table with millions of rows that's where your response time went. Index Scan is what you want — Postgres found an index and skipped straight to the relevant rows.

sqlCopy<br>1-- Seq Scan: reading 4,999,999 rows to find 1. painful.<br>2Seq Scan on orders (cost=0.00..89234.00 rows=1 width=120)<br>3 Filter: (user_id = 42)<br>4 Rows Removed by Filter: 4999999<br>6-- One index fixes it<br>7CREATE INDEX idx_orders_user_id ON orders(user_id);<br>9-- Now look at the difference<br>10Index Scan using idx_orders_user_id on orders<br>11 Index Cond: (user_id = 42)

4 seconds to 4 milliseconds on a production table with real data. Not theoretical.

There's a third one you might run into: Bitmap Heap Scan . Postgres builds a map of which disk pages probably contain matching rows and reads those instead of scanning everything. Not usually a problem, but if your query should return like 3 rows and you see a bitmap scan, dig into it. Index might not match well or Postgres has outdated info about the data in that table.

Also look at estimated rows versus actual rows in the output. When those are wildly different (Postgres expected 10, actually...