What you'll learn

By the end of this you'll understand the database decisions that actually affect production systems: how to pick between SQL and NoSQL for a given problem, how indexing works and why it matters, what replication and sharding do and when you need each, and how connection pooling prevents your app from accidentally opening 10,000 database connections.

This isn't a database internals deep-dive. It's the design-level knowledge you need to make good decisions in a system design conversation or when you're actually architecting something that needs to handle real load.

Who this is for

  • Backend engineers who have used SQL databases for years but haven't needed to think about replication or sharding yet
  • Developers preparing for system design interviews where "design a Twitter" or "design a URL shortener" requires real database reasoning, not just a buzzword list
  • Engineers who've hit a database bottleneck in production and want a framework for diagnosing and fixing it

You can skip this if you've designed and operated a sharded, replicated database cluster in production and have made the SQL vs NoSQL call on multiple distinct product surfaces.

What is a database in system design?

In system design, the database is where your data actually lives — the source of truth that everything else in your architecture exists to protect and serve. Load balancers, caches, queues, and CDNs are scaffolding around the database.

Plain English: The database is the filing cabinet your application reads from and writes to. Every other piece of infrastructure — caching, replication, connection pooling — exists because the database has limits on how fast it can serve reads and writes, and how many connections it can handle at once.

Simple idea: pick the right database type for your data shape, index what you query, add read replicas before you shard, and pool your connections. Most systems never need to go beyond that.

Prerequisites

  • Basic SQL (SELECT, INSERT, JOIN, WHERE)
  • Experience running a web application backed by PostgreSQL, MySQL, or similar
  • Optional: EXPLAIN ANALYZE run on at least one slow query

Setup from zero

Step 1 — SQL vs NoSQL: make the decision deliberately

Choose SQL (relational) when:

  • Your data has clear relationships and you need referential integrity
  • You need ACID transactions across multiple tables
  • Your schema is relatively stable
  • You're doing complex ad-hoc queries and joins

PostgreSQL is the default choice for new projects and will handle far more load than most teams ever experience.

Choose NoSQL when:

  • Your data is document-shaped with varying schema (MongoDB)
  • You need extreme write throughput with eventual consistency (Cassandra, DynamoDB)
  • You're modeling graph relationships (Neo4j)
  • You're storing time-series data at high volume (InfluxDB, TimescaleDB)

The most common mistake is choosing NoSQL for "flexibility" when you actually just mean "I don't want to write migrations."

Step 2 — Index what you query, not everything

An index lets the database find rows quickly without scanning the entire table. Without an index on the column you're querying, the database does a full table scan — reads every row. On ten million rows, that's slow.

  • Create an index on every column you WHERE, JOIN, or ORDER BY frequently
  • Composite indexes work for multi-column queries — but column order matters
  • Foreign key columns should almost always be indexed

The trade-off: indexes make reads faster and writes slower. Index what you actually query, based on real query patterns.

Step 3 — Use read replicas before you shard, and pool your connections

Most database scaling problems are read problems. Read replicas are copies of your primary that serve read queries, relieving read pressure from the primary.

Sharding splits data across multiple database instances by a shard key. It scales write capacity but adds significant complexity. Most startups that think they need sharding actually have a missing index or an N+1 query problem.

Connection pooling maintains a set of open connections and lends them to requests as needed. Without pooling, you pay the TCP handshake and authentication cost on every query — and at scale, you run out of connections before you run out of anything else.

> Little tip: Run EXPLAIN ANALYZE on your slow queries before creating indexes. It shows you exactly what the database is doing — whether it's doing a sequential scan, using an existing index, or using an index inefficiently.

The mental model

The mental model for database design is where is the bottleneck, and what's the cheapest way to move it?

The bottleneck in most systems follows a predictable progression: first it's bad queries and missing indexes (fix the query), then it's read volume overwhelming one database (add replicas), then it's write volume (think about sharding), then it's connection exhaustion (add a pooler), then it's schema limitations (re-evaluate the database choice).

Most teams encounter the first two stages and never need to go further. Don't design for stage five when you're at stage one. Start with the simplest thing that works — Postgres, good indexes, a connection pool — and move up the complexity curve only when you have evidence that you need to.

Key terms

ACID — Atomicity, Consistency, Isolation, Durability. The four properties of reliable database transactions.

Index — a data structure that speeds up reads by allowing the database to find rows without a full table scan. Makes writes slightly slower.

Sequential scan — a full table scan that reads every row. Usually indicates a missing index.

Read replica — a copy of the primary database that receives writes via replication and can serve read queries.

Sharding (horizontal partitioning) — splitting data across multiple database instances by a partition key. Scales write capacity but adds significant complexity.

Connection pool — a cache of open database connections reused across requests.

CAP theorem — in a distributed database, during a network partition you can guarantee consistency or availability but not both.

Eventual consistency — all replicas will eventually converge to the same data, but there may be a brief window where different nodes return different values.

Step-by-step: diagnose and fix a slow query

Start with the query that's slow in production:

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

If the plan shows Seq Scan on users, you need an index:

CREATE INDEX CONCURRENTLY idx_users_status ON users(status);
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

Re-run EXPLAIN ANALYZE and confirm the plan now uses index scans.

Configure connection pooling in Node.js:

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  min: 5,
  max: 20,
  idleTimeoutMillis: 600_000,
});

export async function getActiveUsersWithOrderCounts() {
  const { rows } = await pool.query(`
    SELECT u.name, COUNT(o.id) AS order_count
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE u.status = 'active'
    GROUP BY u.id, u.name
    ORDER BY order_count DESC
    LIMIT 20
  `);
  return rows;
}

Working examples

Read replica routing:

Write queries → Primary database
Read queries  → Read replica (with replication lag awareness)
Read-after-write → Primary (user just saved something — route to primary)

Connection pool sizing:

pool size: min=5, max=20
→ 5 connections always open, ready to serve queries
→ up to 20 concurrent connections under load
→ connections idle for >10 minutes are closed

CAP theorem in practice:

Network partition occurs:
  CP system → refuses requests (consistency over availability)
  AP system → continues serving, may return stale data (availability over consistency)

Patterns / when to use each technique

Use indexes when: EXPLAIN ANALYZE shows sequential scans on large tables for queries that run frequently.

Use read replicas when: read volume overwhelms the primary but write volume is manageable. Route read-after-write queries to the primary.

Use sharding when: write volume genuinely cannot be handled by a single primary and caching plus replicas are exhausted. This is rare for most applications.

Use connection pooling always: every production application should pool connections. Use PgBouncer or RDS Proxy for serverless environments where each function instance creates its own pool.

> Little tip: If you're running serverless functions (AWS Lambda, Vercel Functions), each instance creates its own connection pool. At scale this means hundreds of permanently open connections. Use a connection proxy like PgBouncer, RDS Proxy, or Prisma Accelerate to manage actual connections at the proxy layer.

Common mistakes

Choosing NoSQL to avoid migrations — schema flexibility in NoSQL doesn't come free. You lose type enforcement, referential integrity, and complex joins. Make that trade knowingly.

Indexing everything by default — every index slows writes. Index based on actual query patterns from EXPLAIN ANALYZE, not intuition.

Sharding before fixing queries — most "we need to shard" situations are actually missing indexes or N+1 query problems. Fix the query first.

No connection pool in production — creating a new connection per request exhausts the database's connection limit long before CPU or memory become bottlenecks.

Troubleshooting

Queries suddenly slow after a data growth spike — a query that worked fine at 100K rows may sequential-scan at 10M rows. Run EXPLAIN ANALYZE and add indexes for new query patterns.

"Too many connections" errors — connection pool max is too high across many app instances, or there's no pool at all. Add a pooler and set max based on (total_connections_budget / num_instances).

Users see stale data after saving — read is hitting a replica with replication lag. Route read-after-write queries to the primary.

Cross-shard queries are painfully slow — sharding without a shard key that matches your query patterns forces scatter-gather across all shards. Redesign the shard key or denormalize.

Checklist

  • [ ] Chose SQL (PostgreSQL) as default unless a specific NoSQL use case applies
  • [ ] Ran EXPLAIN ANALYZE on the slowest production queries
  • [ ] Created indexes on columns used in WHERE, JOIN, and ORDER BY
  • [ ] Connection pool configured with appropriate min/max sizes
  • [ ] Read replicas added before considering sharding
  • [ ] Read-after-write queries routed to the primary
  • [ ] Serverless deployments use a connection proxy (PgBouncer, RDS Proxy)
  • [ ] Replication lag monitored on read replicas

Practice task

Take the slowest query in a project you have access to. Run EXPLAIN ANALYZE and identify whether it's doing a sequential scan. Add the appropriate index with CREATE INDEX CONCURRENTLY. Re-run the explain plan and measure the query time before and after. Document the index choice and explain why a composite index was or wasn't needed.

FAQ

PostgreSQL vs MySQL — which should I pick?

PostgreSQL has stronger support for complex queries, JSON columns, full-text search, and advanced indexing (GiST, GIN). MySQL is widely deployed and well understood. For new projects, PostgreSQL is the stronger default unless you have a specific MySQL requirement.

When do I actually need sharding?

When write volume on a single primary exceeds what vertical scaling and write optimization can handle, and you've already exhausted caching and read replicas. Most applications never reach this point. Treat sharding as a last resort with significant operational cost.

What does the CAP theorem mean for my daily work?

Less than people think. CAP describes behavior during a network partition, which is rare. The more common day-to-day trade-off is consistency vs latency — strong consistency from the primary is slower than eventually consistent reads from a nearby replica. Use strong consistency where it matters; use replicas everywhere else.

What to learn next

With database fundamentals solid: caching to reduce read load on your primary and replicas, message queues to decouple write-heavy async work from synchronous request paths, and CDN configuration for serving static assets without touching the database at all.

  • System design: caching — reduce read load before adding replicas or shards
  • System design: queues — decouple write-heavy async work from synchronous paths
  • System design: CDN and edge — serve static content without database round-trips

Takeaways

Most database scaling problems are read problems solved by indexes and read replicas, not sharding. Start with PostgreSQL, index what you query, pool your connections, and add complexity only when measurement shows you need it. The CAP theorem matters during partitions; consistency vs latency matters every day.

If you remember only one thing: run EXPLAIN ANALYZE on slow queries before reaching for sharding, replicas, or a new database — the fix is usually a missing index.