Chapter 10 — Database Scaling Basics

The why

Every database starts out handling everything perfectly — 100 users, 1000 rows, 10 queries per second. Then you launch. You get 10,000 users. Queries that took 5ms now take 500ms. The CPU is pegged at 100%. The database is the bottleneck. Welcome to scaling. This chapter covers the first things you do before reaching for microservices or sharding.

Core concepts

Vertical scaling (scale up)

Buy a bigger server. More CPU, more RAM, faster disks. This is the first and simplest scaling move.

When to scale vertically: Your database has enough capacity for the next 6 months, you just need a bit more. Most startups never outgrow vertical scaling — a well-tuned PostgreSQL instance on a large AWS RDS instance (96 vCPU, 768 GB RAM) can handle 50,000+ queries per second.

Connection pooling

The database can't handle unlimited connections. PostgreSQL forks a process per connection — 1000 connections = 1000 OS processes, each consuming ~5 MB. Your server runs out of memory before the database slows down.

PgBouncer sits between your application and PostgreSQL, maintaining a small pool of persistent connections and multiplexing application requests through them:

App (200 connections) → PgBouncer (20 connections) → PostgreSQL (20 processes)

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
 
[pgbouncer]
pool_mode = transaction    # Return connection to pool after each transaction
default_pool_size = 20     # 20 connections per pool
max_client_conn = 500      # Max incoming connections

Pool modes:

• Session mode: Connection tied to a client for the session duration. Like direct PostgreSQL.
• Transaction mode: Connection returned to pool after each transaction. Best for web apps — a client holds a connection only during the active query. 20 database connections can serve 200 concurrent users.
• Statement mode: Connection returned after each statement. Fastest but breaks transactions with multiple statements.

Read replicas

The most common scaling pattern: one primary handles all writes, multiple replicas handle reads.

              ┌──────────┐
   Writes →   │ Primary  │
              └────┬─────┘
                   │ replication (async, usually < 1 second lag)
        ┌──────────┼──────────┐
   ┌────┴─────┐ ┌──┴──────┐ ┌┴──────────┐
   │ Replica 1│ │Replica 2│ │ Replica 3  │
   └──────────┘ └─────────┘ └───────────┘
        ↑             ↑            ↑
    Reads (users)  Reads (reports) Reads (search)

How to use read replicas in your app:

import { Pool } from 'pg';
 
const primary = new Pool({ connectionString: process.env.PRIMARY_DB_URL, max: 10 });
const replicas = [
  new Pool(

What comes before sharding (the checklist)

Sharding (splitting data across multiple independent databases) is the nuclear option. Do these first:

1. ✅ Optimize queries. Add indexes. Rewrite slow queries. Use EXPLAIN ANALYZE.
2. ✅ Increase work_mem, shared_buffers, effective_cache_size.
3. ✅ Add connection pooling. PgBouncer or built-in pool.
4. ✅ Add read replicas. Move reads off the primary.
5. ✅ Vertical scaling. Bigger instance.
6. ✅ Caching layer. Redis in front of the database.
7. ✅ Partition large tables. PostgreSQL native partitioning by date/range.
8. ✅ Archive old data. Move rows older than N years to cold storage.
9. ⬜ Sharding. Only after 1–8 are exhausted and you've benchmarked the benefit.

Real-world example: How GitHub scaled MySQL (before moving to Vitess)

GitHub's database scaling journey:

1. 2008–2011: Single MySQL primary + read replicas. Vertical scaling. Worked until ~100k queries/sec.
2. 2012: Added PgBouncer-like connection pooling. Replicas for different query types (search replicas, reporting replicas, web replicas).
3. 2014: Partitioned large tables by repository ID range.
4. 2016: Introduced Vitess (sharding middleware) to horizontally shard by repository ID. Each shard is its own MySQL cluster.
5. 2020: Began migration to PostgreSQL for some workloads.

Lesson: GitHub was 8 years old before they needed sharding. Most companies never reach that scale. Do the simple things first.

Common mistakes

Production notes

• Set max_connections conservatively — 200–500 for a managed database, not 5000. Use PgBouncer to handle more clients.
• Monitor replication lag. SELECT pg_current_wal_lsn() - replay_lsn FROM pg_stat_replication; — if this grows beyond a few seconds, investigate.
• Automate failover. Cloud providers (RDS, Cloud SQL, Supabase) handle this. If self-hosting, use Patroni, repmgr, or pg_auto_failover.

Performance notes

• Read replicas reduce load on the primary by 60–90% in most web apps (reads dominate writes 10:1 to 100:1).
• Connection pooling reduces memory usage by 10–20x. 500 connections via PgBouncer (20 actual) uses ~100 MB vs ~2.5 GB without pooling.
• Replication lag adds latency, not throughput cost. The primary writes at full speed regardless of how many replicas are trailing.

Security notes

• Replicas contain all your data. They need the same security posture as the primary: encrypted at rest, TLS for connections, restricted IP access.
• Connection pooling doesn't bypass authentication. PgBouncer authenticates every client connection. Use auth_type = scram-sha-256.

Exercises

Beginner

1. Configure PgBouncer with transaction pooling. Connect through it. Run 100 concurrent queries and observe the number of PostgreSQL connections (SELECT count(*) FROM pg_stat_activity;).
2. Set up a PostgreSQL replica using pg_basebackup. Verify that writes on the primary appear on the replica.

Intermediate

1. Configure your application to route writes to primary and reads to a random replica. Simulate replication lag by introducing a delay on the replica. Observe stale reads. Implement "read your own writes" for user data.
2. Run a load test (1000 concurrent users) against a PostgreSQL instance with and without PgBouncer. Measure query latency, connection count, and memory usage.

Advanced

1. Set up PostgreSQL native partitioning by range on a events table. Insert 10M rows across multiple partitions. Compare query performance for "last 24 hours" with and without partition pruning.
2. Implement a simple sharding proxy that routes queries to different database instances based on tenant_id hashing. Handle cross-tenant queries with scatter-gather.

Interview questions

Beginner

1. What's the difference between vertical and horizontal scaling? Vertical scaling = bigger server (more CPU/RAM). Horizontal scaling = more servers (distributing data/work across multiple machines). Vertical is simpler but has a ceiling. Horizontal is complex but can scale nearly infinitely.

2. What is a read replica? A copy of the primary database that stays synchronized via replication. Reads go to replicas, writes go to the primary. This offloads read traffic and provides failover capability.

3. Why do you need connection pooling? Each database connection consumes memory and OS resources. Without pooling, high concurrency exhausts these resources. A pooler multiplexes many client connections through a small number of persistent database connections.

Senior

4. What is replication lag and how do you handle it? The delay between a write committing on the primary and that write being applied on the replica, typically 50–500ms. Handle it by: routing "read your own writes" to the primary, notifying users of a brief delay, using synchronous replication for critical data (at cost of latency), and designing UX that tolerates eventual consistency.

5. Walk through the steps you'd take before resorting to sharding. Optimize queries + indexes → tune PostgreSQL config (work_mem, shared_buffers) → add connection pooling → add read replicas → vertical scaling → add caching layer (Redis) → partition large tables → archive cold data → sharding as last resort. Each step should be backed by benchmarks showing the bottleneck.

6. What are the tradeoffs of using PostgreSQL native partitioning vs application-level sharding? Native partitioning: managed by PostgreSQL, transparent to queries (partition pruning is automatic), simpler to operate, limited to one server. Application-level sharding: full control over data distribution, can span multiple servers, but requires application logic to route queries, cross-shard operations are complex, and you lose many relational features (JOINs across shards, global uniqueness constraints).

Summary

Scaling a database is a ladder. Start with the simplest rung: optimize queries and indexes. Then add connection pooling. Then read replicas. Then vertical scaling. Then caching. Then partitioning. Only at the very top do you reach for sharding. Each rung adds complexity — make sure the previous rung is exhausted first. Connection pooling with PgBouncer (transaction mode) is the single highest-impact, lowest-effort scaling technique for most applications.

Quick recall

• Vertical scaling = bigger server. Horizontal = more servers. Start vertical.
• Connection pooling (PgBouncer, transaction mode) = 500 clients through 20 database connections.
• Read replicas: writes → primary, reads → replicas. Beware replication lag.
• Scaling ladder: indexing → pooling → replicas → vertical → caching → partitioning → sharding.
• Sharding is the last resort. GitHub didn't need it for 8 years.

Quiz

1. What is the simplest way to scale a PostgreSQL database? Vertical scaling — move to a larger instance with more CPU/RAM.
2. What PgBouncer pool mode is best for web applications? Transaction mode — returns the connection to the pool after each transaction.
3. What is replication lag? The delay between a write committing on the primary and being visible on replicas, typically 50–500ms.
4. Why shouldn't you write to a read replica? Writes to replicas are not replicated to other nodes. They create data divergence and are typically rejected by the database.
5. Name three things to do before sharding. Optimize queries/indexes, add connection pooling, add read replicas, vertical scaling, caching (Redis), table partitioning, archive cold data.
6. What's the risk of reading from a replica immediately after writing? Your write may not have replicated yet — you see stale data. This is the "read your own writes" problem.
7. How does PgBouncer reduce PostgreSQL memory usage? It replaces 500 individual PostgreSQL connections (each ~5 MB = 2.5 GB) with ~20 persistent connections (~100 MB).