Scalability #

Almost every system that hits scalability problems experiences it the same way: at first everything feels fine. The database serves hundreds of users smoothly, response times under 100ms, nothing to worry about. Then traffic starts to rise. Queries that used to finish in 50ms now take 500ms. Reports that used to finish in seconds now take minutes and eat CPU. Connection pools start filling up. And at some point, the system starts timing out here and there for no clear reason.

This is the reality of database scaling: it isn’t felt until suddenly it’s felt very intensely. And once it’s felt, the solution is almost always more expensive and harder than it should have been — because the early design decisions didn’t account for growth.

Database scalability isn’t about choosing the most advanced technology from the start. It’s about understanding the bottlenecks coming your way, optimizing the cheapest layer first, and moving up to the next layer only when truly necessary. This article covers the entire ladder — from query optimization to sharding — along with when and why to move from one level to the next.

Scalability vs Performance: A Distinction Often Reversed #

Before discussing techniques, the fundamental difference between two concepts often used interchangeably even though they mean different things must be understood.

Performance is how fast the system serves a single request under current conditions. A query that finishes in 10ms is a performant query.

Scalability is how well the system maintains its performance when load increases — more users, more data, more concurrent queries. A scalable system isn’t just fast today; it stays fast when data grows tenfold and traffic rises twentyfold.

Illustrating the performance vs scalability difference:

  System A — fast but not scalable:
  10 users    → 30ms  ✓
  100 users   → 150ms ✓
  1,000 users → 2.5s  ✗
  10,000 users→ timeout ✗✗

  System B — scalable:
  10 users    → 50ms  ✓ (slightly slower than A)
  100 users   → 60ms  ✓
  1,000 users → 80ms  ✓
  10,000 users→ 120ms ✓

  System A is faster at the start.
  System B is far more valuable in the long run.

A scalable database doesn’t have to be the fastest under light conditions. What matters most is that it doesn’t degrade dramatically as load increases.

The Database Is Always the First Bottleneck #

In modern web architectures, application servers are stateless — adding a new instance is as easy as adding a container to Kubernetes. But the database isn’t. It stores state, and that state can’t simply be duplicated without coordination. This is why the database almost always becomes the bottleneck before the application server.

Why the database always bottlenecks first:

flowchart TD
    subgraph AppLayer["Application Server (Stateless - Scales Linearly)"]
        direction LR
        App1["App Instance 1"]
        App2["App Instance 2"]
        App3["App Instance 3"]
    end

    AppLayer -->|"All request load"| DB["Database<br/>(Stateful - Bottleneck Point)"]

Every time you add an app instance → the database is burdened more. Adding app instances without preparing the database = worsening the bottleneck.

This is also why the “add more servers” solution often doesn’t help when the bottleneck is the database. What increases is only the pressure on the database, not its capacity.

The Bottleneck Ladder: The Right Order #

The most common mistake in database scaling is jumping straight to complex solutions — sharding, distributed databases, CQRS — before optimizing the simpler, cheaper layers. Sharding done prematurely is one of the most expensive technical decisions to roll back.

The right approach is climbing the ladder one rung at a time, and only moving to the next rung when the current one has been truly optimized.

The Bottleneck Ladder — optimization order from cheapest to most complex:

flowchart TD
    Step6["Rung 6: Sharding<br/>(Only if Step 5 isn't enough)"]
    Step5["Rung 5: Multi-database architecture<br/>(CQRS, polyglot persistence)"]
    Step4["Rung 4: Read replicas + connection pooler<br/>(Horizontal read scaling)"]
    Step3["Rung 3: Caching layer + async processing<br/>(Redis/Memcached)"]
    Step2["Rung 2: Query optimization + indexes + partitioning"]
    Step1["Rung 1: Vertical scaling<br/>(Upgrade hardware - Starting point)"]

    Step1 --> Step2 --> Step3 --> Step4 --> Step5 --> Step6

Principle: don’t move to the next rung before the current one is truly optimized.

Rung 1: Vertical Scaling #

Vertical scaling means adding resources to the existing database server — more CPU, more RAM, faster storage. This is the easiest and fastest first step to implement.

Vertical scaling:

flowchart LR
    Before["Before:<br/>DB Server<br/>4 CPU, 16 GB RAM, HDD"] -->|"Vertical Scaling (Upgrade)"| After["After:<br/>DB Server (Upgraded)<br/>32 CPU, 128 GB RAM, NVMe SSD"]

Advantages:

• No architecture or code changes.
• Can be done in hours (cloud: resize the instance).
• Effective for bottlenecks caused by pure resource limits.

Limits:

• There’s a hardware ceiling — it can’t be upgraded forever.
• Prices rise exponentially with instance size.
• Doesn’t solve architectural problems (bad queries stay bad).
• The single point of failure remains.

Vertical scaling is most effective as a first step and as “buying time” — it gives room to optimize the next levels without incident pressure. But don’t make it a permanent strategy.

Rung 2: Query, Index, and Partitioning Optimization #

Before adding new infrastructure, make sure the existing database is being used correctly. Very often, performance problems that feel like scalability problems turn out to be bad-query problems — queries without indexes, N+1s, SELECT * on large tables, or very large OFFSETs.

Query optimization impact — before and after:

  A query without an index on a 50-million-row table:
  SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01'
  → Full table scan: 4.2 seconds, 50 million rows read

  The same query with the right index:
  CREATE INDEX idx_orders_status_created ON orders(status, created_at);
  SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01'
  → Index seek: 8ms, 12,000 rows read

  This single query optimization equals upgrading the database to a 10x faster server
  — but it's free and needs no downtime.

Three areas most often ignored and with the biggest impact:

The highest-impact optimization areas:

  1. N+1 queries — silently killing performance
     ✗ 1 query for the list + N queries for each item = 101 queries for 100 items
     ✓ JOIN or batch loading = 1-2 queries for 100 items

  2. SELECT * on wide tables
     ✗ SELECT * FROM users — fetches 40 columns when only 3 are needed
     ✓ SELECT id, name, email FROM users — 90% less data transfer

  3. Large OFFSET for pagination
     ✗ LIMIT 20 OFFSET 10000000 — the database scans 10 million rows then discards them all
     ✓ Keyset pagination: WHERE id > last_seen_id LIMIT 20 — O(log n)

Partitioning (covered in the previous article) also belongs on this rung — it’s a table-level optimization that can be very effective before having to move up to replication or sharding.

Rung 3: Caching and Async Processing #

The most effective way to reduce database load is not hitting the database at all for data that can be cached. A caching layer like Redis or Memcached can serve millions of requests per second with sub-1ms latency — far faster than even the fastest database query.

Architecture with a caching layer:

flowchart TD
    Req["Request"] --> App["Application"]
    App -->|"Check cache"| Cache["Cache (Redis)"]
    Cache --> Hit{"Cache hit?"}
    Hit -->|"YES (< 1ms)"| Return["Return from cache"]
    Hit -->|"NO (5-50ms)"| DB["Query Database"]
    DB -->|"Store in cache"| Cache
    DB -->|"Return result"| Req

Effects:

• 80-95% of requests are served from cache without touching the database.
• The database only receives 5-20% of total requests.
• Effective throughput rises 5–20x without changing the database.

The right caching strategy depends on data characteristics:

Choosing a caching strategy:

  Cache-aside (lazy loading):
  → The app checks the cache first; on a miss, queries the DB, stores the result
  → Good for: data read more often than written
  → Trade-off: the first cache miss is still slow

  Write-through:
  → Every DB write also immediately updates the cache
  → Good for: data that must always be consistent between DB and cache
  → Trade-off: every write is slower (2 operations)

  Write-behind (write-back):
  → Write to the cache first, async flush to the DB later
  → Good for: counters, view counts, non-critical data
  → Trade-off: data loss risk if the cache goes down before flushing to the DB

  TTL-based expiry:
  → Cached data automatically expires after a certain duration
  → Good for: data that can be stale for a few seconds/minutes
  → Easiest to implement, most commonly used

Async processing also belongs on this level: operations that don’t need synchronous processing — sending emails, generating reports, calculating statistics — are moved to a queue and processed by background workers. The database isn’t burdened by operations that aren’t time-sensitive.

Rung 4: Read Replicas #

Most web systems are read-heavy: the read-to-write ratio is often 80:20 or even 95:5. Read replicas allow read load to be distributed to additional servers while all writes stay on the primary.

Read replica architecture:

flowchart TD
    App["Application"] -->|"Write"| Primary["Primary Database<br/>(All writes)"]
    App -->|"Read"| LB["Load Balancer<br/>(Round-robin / Least conn)"]
    Primary -->|"Replication"| Replica1["Replica 1<br/>(API reads)"]
    LB --> Replica1
    LB --> Replica2["Replica 2<br/>(reporting)"]
    LB --> Replica3["Replica 3<br/>(backup)"]

• Read capacity: 3x without changing the primary at all.
• Write capacity: the same (the primary doesn’t change).
• Complexity: moderate — need to handle read-after-write and replication lag.

Read replicas are the least invasive horizontal scaling step — no schema changes, no complicated applications, and they can be added at any time. This is usually the first horizontal step taken after query optimization has been maximized.

Rung 5: Separating OLTP and OLAP Loads #

Production systems often have two workload types with very different characteristics: OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing). Running both on the same database is one of the most common causes of unexplained performance problems.

OLTP and OLAP characteristic differences:

┌─────────────────────┬────────────────────────┬──────────────────────────┐
│ Aspect              │ OLTP                   │ OLAP                     │
├─────────────────────┼────────────────────────┼──────────────────────────┤
│ Purpose             │ Operational transactions│ Analysis and reporting  │
│ Queries             │ Simple, fast            │ Complex, slow            │
│ Data volume per query│ Few rows               │ Millions to billions     │
│ Frequency           │ Very high (thousands/s) │ Low (tens/day)           │
│ Latency required    │ < 100ms                 │ Seconds to minutes       │
│ Examples            │ Login, checkout, pay    │ Monthly reports, dashboards│
│ Optimal indexes     │ B-Tree per column       │ Columnar, bitmap         │
│ Optimal storage     │ Row-based               │ Column-based             │
└─────────────────────┴────────────────────────┴──────────────────────────┘

The problem when mixed:
→ An analytical query running 10 minutes locks CPU and IO resources
→ OLTP queries that should finish in 20ms are impacted too
→ User transaction latency rises unpredictably
→ Hard to debug because there's no error, just slowness

Solution: separate databases for OLTP and OLAP. For some systems, routing analytical queries to a dedicated read replica is enough. For larger systems, use a separate analytical database (ClickHouse, BigQuery, Redshift, or Snowflake) designed specifically for this workload.

OLTP/OLAP separation architecture:

flowchart LR
    OLTP["Transactions (OLTP)<br/>Queries < 100ms"] --> Primary["Primary DB"]
    Primary -->|"ETL / CDC Pipeline<br/>(Debezium, Airbyte)"| ETL["Data Sync"]
    ETL --> Analytical["Analytical DB<br/>(ClickHouse, BigQuery, etc.)<br/>Report queries (minutes)"]

Rung 6: CQRS and Polyglot Persistence #

CQRS (Command Query Responsibility Segregation) is an architectural pattern that separates the models for write operations (Command) and read operations (Query) fundamentally — not just at the connection level, but at the data model level and even in the databases used.

CQRS architecture:

flowchart TD
    subgraph WriteSide["Write Side (Command)"]
        CH["Command Handler<br/>(Create order, update status)"] --> WDB["Write Database<br/>(PostgreSQL, Normalized)"]
    end

    subgraph ReadSide["Read Side (Query)"]
        QH["Query Handler<br/>(Get order, list, search)"] --> RDB["Read Database<br/>(Elasticsearch, Redis, MongoDB)"]
    end

    WDB -->|"event"| RDB

Advantages:

• The read model can be optimized for queries without disturbing the write model.
• Different databases best suited to each use case can be used.
• The read model can be rebuilt from scratch if needed without downtime.

Disadvantages:

• Eventual consistency between the write and read models.
• Far higher operational complexity.
• Harder debugging — data may differ between the write and read stores.

CQRS only makes sense when reads and writes have very different needs that can’t be served well by a single database. Don’t use CQRS as a first step — its complexity is very high.

Recognizing When to Move to the Next Level #

One of the hardest decisions in scaling is determining when it’s time to move to the next level. Moving up too fast is over-engineering. Moving up too slowly is an incident.

Signals that you need to move to the next level:

  From vertical scaling to query optimization:
  → CPU and RAM already upgraded but latency stays high
  → The slow query log is full of optimizable queries
  → EXPLAIN shows full table scans on large tables

  From query optimization to caching:
  → Queries already optimal but traffic is too high for the DB alone
  → The same data is read repeatedly (popular products, configuration)
  → DB CPU is high because of query volume, not because queries themselves are heavy

  From caching to read replicas:
  → The DB is still overwhelmed even with a cache
  → Read traffic still dominates (> 80% of all queries)
  → Analytical traffic disturbs operational queries

  From read replicas to OLTP/OLAP separation:
  → Analytical queries burden replicas meant for APIs
  → A data warehouse need is emerging (historical analysis, BI tools)

  From OLTP/OLAP separation to CQRS or sharding:
  → The write rate is already too high for a single primary
  → Data volume already exceeds a single server's capacity (sharding)
  → Reads and writes need very different data models (CQRS)

Observability: The Foundation of Every Scaling Decision #

No scaling decision can be made correctly without data. Without observability, scaling is guesswork — and a wrong guess in this context can be very expensive.

Metrics that must exist before making scaling decisions:

  Database level:
  → Query latency (P50, P95, P99) — not just averages
  → Queries per second (QPS) — broken down per type (SELECT, INSERT, UPDATE, DELETE)
  → Slow query count and the slow query log
  → Threads_connected vs max_connections
  → Buffer pool hit rate — should be above 95% for InnoDB
  → Replication lag (if replicas exist)

  Table and index level:
  → Largest tables and their growth
  → Indexes never used
  → Queries with the highest rows_examined (EXPLAIN)

  System level:
  → CPU utilization (user vs system vs iowait)
  → Disk I/O: read/write throughput, IOPS, latency
  → Memory: RAM usage, swap usage
  → Network: throughput between app and DB

  Alerts that must exist:
  → Query latency P99 > threshold (match the SLA)
  → Slow query count exceeding N per minute
  → Connections approaching max_connections
  → Replication lag > X seconds
  → Disk usage > 80%
  → Buffer pool hit rate < 90%

-- Useful queries for observability without extra tools

-- Top queries by total execution time (MySQL Performance Schema)
SELECT
    digest_text AS query_pattern,
    count_star AS execution_count,
    ROUND(avg_timer_wait / 1000000000, 3) AS avg_seconds,
    ROUND(sum_timer_wait / 1000000000, 3) AS total_seconds,
    ROUND(sum_rows_examined / count_star) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text NOT LIKE '%performance_schema%'
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- Check the buffer pool hit rate (should be > 95%)
SELECT
    ROUND(
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') /
        ((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') +
         (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')) * 100,
    2) AS buffer_pool_hit_rate_pct;
-- If < 95%: consider increasing innodb_buffer_pool_size
-- If the buffer pool is too small, lots of data is read from disk → slow

-- The largest tables in the database
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 1) AS data_mb,
    ROUND(index_length / 1024 / 1024, 1) AS index_mb,
    table_rows AS estimated_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC
LIMIT 15;

Anti-Patterns to Avoid #

✗ Anti-pattern 1: upgrading hardware to cover for bad queries
  A 64-CPU, 512GB RAM server can still be destroyed by one full table scan
  query on a 1-billion-row table.
  ✓ Optimize queries and indexes before touching hardware.

────────────────────────────────────────────────────────────────────────────────

✗ Anti-pattern 2: running analytical queries on the transaction database
  A monthly report doing full scans of millions of rows for 10 minutes
  makes every user endpoint experience rising latency.
  ✓ Separate analytical workloads to a dedicated replica or analytical database.

────────────────────────────────────────────────────────────────────────────────

✗ Anti-pattern 3: adding read replicas without handling replication lag
  Reading from a replica right after a write → stale data.
  Users see the changes they just made not appearing.
  ✓ Handle read-after-write: read from the primary for critical operations,
    or wait for the replica to catch up before redirecting to it.

────────────────────────────────────────────────────────────────────────────────

✗ Anti-pattern 4: sharding too early
  Premature sharding adds very high operational complexity:
  cross-shard queries, distributed transactions, expensive resharding.
  ✓ Exhaust all simpler levels before sharding.
    Most systems don't need sharding until hundreds of millions of users.

────────────────────────────────────────────────────────────────────────────────

✗ Anti-pattern 5: scaling without observability
  Adding replicas, adding caches, changing configuration — all without
  measuring whether the change actually helps.
  ✓ Measure a baseline before changes. Measure again after changes.
    Scaling decisions must be data-driven, not assumption-driven.

────────────────────────────────────────────────────────────────────────────────

✗ Anti-pattern 6: over-engineering from the start
  Building a system with CQRS, sharding, and multi-database architecture
  for an application that just reached 1,000 users.
  ✓ Start simple. Scale when needed.
    Premature complexity is the productivity killer of teams.

Scalability Review Checklist #

FOUNDATION (must exist before thinking about scaling):
  □ The slow query log enabled and monitored regularly
  □ EXPLAIN run for all important, frequently executed queries
  □ Indexes optimal — no full table scans on large tables
  □ N+1 queries eliminated
  □ Pagination uses keyset (cursor-based), not large OFFSETs
  □ SELECT * replaced with only the needed columns

CACHING:
  □ Frequently read, rarely changed data is cached
  □ A cache invalidation strategy clearly defined
  □ Cache hit rate monitored (target > 80% for suitable data)

READ SCALING:
  □ Read replicas separated from the primary for heavy queries
  □ Analytical and reporting queries don't run on the primary
  □ Replication lag monitored with clear alerts
  □ Read-after-write handled in the application layer

OLTP VS OLAP:
  □ Heavy analytical queries routed to a dedicated replica or separate DB
  □ No query runs more than 30 seconds on the production primary

OBSERVABILITY:
  □ P50, P95, P99 query latency metrics available
  □ Buffer pool hit rate monitored (target > 95% for InnoDB)
  □ Database disk I/O, CPU, and memory monitored
  □ Alerts set for all critical metrics
  □ Performance baselines documented before every major change

SCALING DECISIONS:
  □ Decisions to move to the next scaling level based on data
  □ No sharding before all simpler levels are optimized
  □ The complexity of every scaling decision understood and documented

Summary #

• Scalability is the ability to maintain performance as load rises — not how fast the system is today, but how well it stays fast when data and users grow tenfold.
• The database always becomes the bottleneck first — because it’s stateful and can’t be scaled as easily as stateless application servers. Adding app instances without preparing the database worsens the bottleneck.
• Climb the ladder one rung at a time — vertical scaling → query optimization → caching → read replicas → OLTP/OLAP separation → CQRS/sharding. Don’t skip steps.
• Query optimization is the most often skipped rung — one right index can make a query 100x faster without any infrastructure changes.
• Caching is the easiest way to reduce database load — 80-95% of requests can be served from cache, leaving the database to serve only 5-20% of total traffic.
• Read replicas are for read scaling, not a write scaling solution — adding replicas increases read capacity, but writes remain the bottleneck on the primary.
• OLTP and OLAP must not be mixed — analytical queries running for minutes can impact all user-facing operational queries. Separate them to a dedicated replica or analytical database.
• Sharding is the last step, not the first — it solves problems no other approach can, but at a very high complexity cost. Most systems never need sharding.
• Observability is the foundation of every scaling decision — without P95/P99 latency, buffer pool hit rates, slow query logs, and disk I/O, all scaling decisions are guesses.
• Over-engineering is more dangerous than under-scaling — building complex distributed architectures prematurely kills team productivity without real benefit. Start simple, scale when needed, scale with evidence.

← Previous: Partitioning   Next: Sharding →