Database Scaling Patterns in Practice

📘 Chapter 7: Scalability Patterns ⏱️ 10 min read 🏗️ Lesson 028

The database is almost always the first bottleneck you'll hit. Unlike stateless app servers that scale with a click, databases hold state — and state is hard to distribute. This lesson covers the practical patterns used in production, ordered from simplest to most complex.

Recap: Database Scaling Through the Scale Cube

  • X-Axis (Read Replicas): Clone the database — one primary handles writes, replicas handle reads
  • Y-Axis (Functional Partitioning): Separate databases per service — orders DB, users DB, products DB
  • Z-Axis (Sharding): Split one table's data across multiple databases by key (user_id, region)

Connection Pooling: The First Thing You Need

Why Every Production DB Needs a Connection Pooler

Database connections are expensive: each one consumes ~10MB of memory in PostgreSQL. A server with 16GB RAM can handle maybe 500 connections — but a busy app server might try to open thousands.

  • PgBouncer (PostgreSQL): Sits between app and DB, maintains a pool of reusable connections. One PgBouncer can multiplex 10,000 app connections into 100 real DB connections.
  • ProxySQL (MySQL): Connection pooler + query router. Can also do read/write splitting.
  • Application-level pooling: Libraries like HikariCP (Java), SQLAlchemy pool (Python) — good but don't help across multiple app instances.

Rule of thumb: Max DB connections = (CPU cores × 2) + effective_spindle_count. For a 4-core server, that's ~10 connections doing useful work. More connections = more context switching = slower.

Read/Write Splitting

Most applications are 80-95% reads. By routing reads to replicas and writes to the primary, you can scale read capacity almost linearly.

Read/Write Splitting Architecture App Server 1 App Server 2 App Server N Connection Pooler (PgBouncer/ProxySQL) 10K connections WRITES READS Primary Writes + strong reads replication Replica 1 Replica 2 Replica 3 → 50 real connections ⚠️ Replication lag: reads from replicas may be slightly stale (ms to seconds behind primary)
Figure 1: Write path goes to primary; read path fans out to replicas. Connection pooler multiplexes thousands of app connections into a small pool.

Denormalization: Trading Storage for Speed

Pre-Computing Joins

Normalized data (3NF) is great for writes and consistency, but reads often require expensive joins across many tables. Denormalization stores pre-joined data to eliminate joins at read time.

  • Example: Instead of joining orders + users + products on every page view, store a order_summary table with user_name and product_name already included.
  • Trade-off: Storage increases, writes become more complex (must update multiple places), but reads go from 50ms → 2ms.
  • When to use: Read-heavy workloads where the same join is done thousands of times per second.

Materialized Views

Database-Level Caching of Expensive Queries

A materialized view is a saved query result that the database stores as a table. Unlike regular views, it doesn't re-execute the query each time.

  • PostgreSQL: CREATE MATERIALIZED VIEW monthly_stats AS SELECT ... ; REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;
  • Refresh strategies: On schedule (every 5 min), on trigger (after writes), or manually
  • Advantage over app-level cache: The DB manages it, it's queryable with SQL, and indexes can be added
  • Limitation: Data can be stale between refreshes

CQRS Preview: Separate Read and Write Models

Command Query Responsibility Segregation

The ultimate evolution of read/write splitting — use completely different data models for reads vs writes:

  • Write model: Normalized, optimized for consistency and transactions (e.g., PostgreSQL)
  • Read model: Denormalized, optimized for queries (e.g., Elasticsearch, Redis, or a pre-computed view)
  • Sync mechanism: Events flow from write side to read side (event sourcing, CDC, or application-level)

CQRS is powerful but complex. Use it when your read and write patterns are fundamentally different — not as a default.

🏢 Real-World: How Slack Scaled MySQL

Slack's journey through database scaling patterns:

  1. Connection pooling: Introduced ProxySQL to manage thousands of connections from their Go services to MySQL. Reduced connection count from 10K to 200 per database.
  2. Read replicas: Added read replicas for channel history queries (which dominate traffic). Tolerable staleness — you might not see the very latest message for 100ms.
  3. Sharding by workspace: Each Slack workspace's data lives on one shard. New workspaces go to the least-loaded shard. Large workspaces (enterprise customers) get dedicated shards.
  4. Vitess: Eventually adopted Vitess (YouTube's MySQL sharding middleware) to manage shard routing, schema migrations, and rebalancing automatically.

Key insight: They applied these in order of increasing complexity, only moving to the next when the previous wasn't enough.

🏢 Real-World: How Pinterest Uses Denormalization

Pinterest's home feed must render fast — but the data is deeply relational (pins → boards → users → interests → followers).

  • Problem: Generating a personalized feed required joins across 5+ tables with billions of rows. Too slow for real-time.
  • Solution: Pre-compute each user's feed into a denormalized user_feed table. When someone pins something, fan out the update to all followers' feed tables.
  • Trade-off: Massive write amplification (one pin → thousands of feed writes), but reads are O(1) — just scan user's pre-built feed.
  • Result: Home feed renders in <100ms regardless of how many people you follow.

Interactive: Build a Database Scaling Strategy

Toggle scaling techniques to see how they affect your database's throughput and latency:

Scaling Techniques

Metrics

Read Throughput:
500 q/s
Write Throughput:
200 q/s
Read Latency (p99):
250ms
Complexity Score:
⭐