Databases, caching and background jobs

Choosing a database

• Relational (PostgreSQL, MySQL): tables, joins, transactions, strong consistency. The default for most business apps: users, orders, applications.
• Document (MongoDB): flexible JSON documents; good when the shape varies and you read whole documents at once.
• Key-value (Redis): in-memory, extremely fast; caching, sessions, rate limits, queues, leaderboards.
• Search (Elasticsearch, OpenSearch): full-text search and filters.

"Use Postgres unless you have a reason not to" is a respectable answer if you can explain why.

Using a database from Node

• Use a connection pool: opening a connection per request is slow; too many connections overwhelm the DB.
• Use transactions when several writes must succeed or fail together (create order + reduce stock).
• Avoid the N+1 query problem: fetching 50 jobs and then running one query per job for its company. Use a join or one WHERE id IN (...) query.
• Index the columns you filter and sort by.

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE wallets SET balance = balance - $1 WHERE user_id = $2", [amt, from]);
  await client.query("UPDATE wallets SET balance = balance + $1 WHERE user_id = $2", [amt, to]);
  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();
}

ORMs (Prisma, Sequelize, TypeORM, Drizzle) speed up development and give type safety; you should still be able to read the SQL they produce.

Caching

Cache data that's read often and changes rarely.

• Cache-aside: read from Redis; on a miss, read the DB and store it with a TTL. On update, delete the cache key.
• HTTP caching: Cache-Control, ETag and CDNs for public responses.
• The hard part is invalidation: decide what may be stale and for how long.

async function getJob(id) {
  const key = `job:${id}`;
  const hit = await redis.get(key);
  if (hit) return JSON.parse(hit);
  const job = await repo.findJob(id);
  await redis.set(key, JSON.stringify(job), "EX", 300);
  return job;
}

Background jobs and queues

Anything slow or retryable shouldn't run inside the HTTP request: emails, PDF generation, image resizing, webhooks, AI processing. Put a job on a queue (BullMQ on Redis, RabbitMQ, SQS) and let workers process it with retries and backoff. The API responds immediately with 202 Accepted or the created resource.

Rate limiting

Protect login, OTP and expensive endpoints: e.g. 5 OTP requests per phone per hour, stored in Redis with expiring counters. Return 429 with a Retry-After header.

Check yourself

• What is the N+1 problem, and how do you fix it?
• When do you need a transaction?
• Why send emails from a queue instead of inside the request?