Your Database Is the Bottleneck. Not Your Code

"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.

N+1 Queries

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

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

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

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

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

Indexes

CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

Connection Pools

codeCopy
1Pool size: 10
2Requests per second: 300
3Each request holds a connection for 50ms
4
5Connections needed: 300 * 0.05 = 15
6You have 10. Five requests queued at any moment.
7Waits compound. p99 latency climbs.

The Slow Query Log

ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

When Redis Actually Makes Sense

Transactions

javascriptCopy
1// Holds a lock while sendEmail does... whatever sendEmail does
2await db.transaction(async (trx) => {
3  await trx('orders').where({ id: orderId }).update({ status: 'confirmed' });
4  await sendEmail(order.userEmail); // could take 2+ seconds
5  await trx('inventory').where({ productId }).decrement('stock', 1);
6});

javascriptCopy
1await db.transaction(async (trx) => {
2  await trx('orders').where({ id: orderId }).update({ status: 'confirmed' });
3  await trx('inventory').where({ productId }).decrement('stock', 1);
4});
5
6await sendEmail(order.userEmail);

SELECT * Considered Harmful (Sometimes)

sqlCopy
-- Grabs everything including the giant description column
SELECT * FROM products WHERE category_id = 5;

-- Just what you need
SELECT id, name, price, thumbnail_url FROM products WHERE category_id = 5;

Before You Add Infrastructure

codeCopy
11. Run EXPLAIN ANALYZE on your slowest queries.
22. Hunt for N+1 patterns in your ORM code.
33. Add indexes on columns in WHERE, JOIN, ORDER BY.
44. Turn on the slow query log.
55. Check pool size against real traffic.
66. Nothing slow inside transactions.