Database Scaling: A Practical Path from One Primary to Replicas, Partitioning, and Shards

Database Scaling: A Practical Path from One Primary to Replicas, Partitioning, and Shards

Database scaling is not one technique. It is a sequence of decisions about which resource is saturated and which architectural tool removes that specific bottleneck with the least added complexity.

A slow database may need an index, not sharding. A read-heavy database may need caching or replicas, not a larger write cluster. A huge event table may need partitioning for retention, while a write-saturated primary may eventually need shards or a distributed database.

This guide is a decision framework for choosing the next step.

Start with the bottleneck, not the architecture pattern

Before scaling anything, determine what is actually constrained.

Common database bottlenecks include:

• CPU
• memory / buffer-cache pressure
• storage latency or IOPS
• write-ahead/redo log throughput
• lock contention
• hot rows or indexes
• connection count
• expensive queries
• replication lag
• dataset size
• backup/maintenance windows

Two systems at the same request rate can need completely different fixes.

For example:

System A:
  CPU 25%
  storage healthy
  3 slow unindexed queries

System B:
  CPU 90%
  all important queries indexed
  primary write throughput saturated

Only System B has evidence that architecture-level scaling may be necessary.

Step 1: Fix query and schema problems first

Scaling hardware around inefficient SQL is expensive.

Start with:

• slow-query statistics
• EXPLAIN / execution plans
• missing or redundant indexes
• N+1 application queries
• full-table scans
• overly large result sets
• unnecessary joins
• repeated queries that could be cached

A well-designed index can turn an expensive scan into a small lookup without introducing another database node.

The first scaling tool should often be less work per request.

Step 2: Control connection pressure

Application instances can multiply faster than a database comfortably accepts backend sessions.

In PostgreSQL, each client connection is associated with a backend process. Too many active connections increase memory and scheduling pressure.

Connection pooling helps because thousands of application requests do not need thousands of simultaneously active database transactions.

A healthy architecture usually has:

many app requests
      |
      v
bounded connection pool
      |
      v
manageable active DB sessions

More connections are not the same as more throughput. Once CPU/storage/locks are saturated, extra concurrent transactions can increase queueing and latency.

Step 3: Scale vertically while it remains simple and economical

Vertical scaling means giving the database node more resources:

• more CPU
• more RAM
• faster storage
• higher storage throughput
• larger instance class

Advantages:

• minimal application changes
• no distributed routing
• normal transactions and joins remain local
• backups/operations stay simpler

This is often the best early scaling move.

Vertical scaling becomes less attractive when:

• hardware tiers become disproportionately expensive
• one node cannot hold the working set
• write throughput reaches an architectural limit
• failover/recovery time becomes too large
• maintenance on one enormous node becomes risky

Do not reject vertical scaling merely because horizontal scaling sounds more sophisticated.

Step 4: Cache repeated hot reads

If the database spends significant resources repeatedly producing the same data, caching can remove work entirely.

Common cache layers include:

• in-process cache
• Redis or another distributed cache
• CDN/HTTP cache for public API responses

A cache is especially useful when:

• reads repeat heavily
• the underlying data changes less often
• a bounded amount of staleness is acceptable

Caching introduces its own problems:

• invalidation
• stale data
• stampedes
• memory sizing
• cache outage fallback

Use it where the hit rate removes meaningful database work, not around every query automatically.

Step 5: Add read replicas when the workload is database-read heavy

Read replicas execute database queries against replicated copies of the data.

They help when:

• read CPU/I/O dominates the primary
• reporting competes with transactions
• stale-tolerant reads can be routed away from the primary
• HA replicas already exist and have spare read capacity

But read replicas introduce replica lag.

A user can write on the primary and immediately read old data from a replica.

Therefore routing must distinguish:

consistency-critical read -> primary
stale-tolerant read       -> replica

Replicas scale reads; ordinary primary-replica architecture does not horizontally scale authoritative writes.

Step 6: Separate OLTP from heavy analytics

Transactional databases are optimized for short business operations:

• create order
• update account
• read current state

Large scans, aggregations, dashboards, and historical reporting can compete with those operations for CPU, memory, and storage.

If analytics becomes a major workload, consider moving it to:

• a reporting replica
• a data warehouse
• a columnar analytical database
• materialized/precomputed tables

Use CDC, logical replication, or batch pipelines to feed the analytical system as appropriate.

This often scales the primary more effectively than continually increasing OLTP hardware.

Step 7: Scale writes by reducing write amplification first

Write scaling deserves separate treatment because writes involve durability, indexes, locks, constraints, and replication.

Before sharding writes:

• remove unnecessary updates
• batch ingestion where safe
• reduce redundant indexes
• fix hot-row contention
• move derived work out of synchronous transactions
• use bounded connection concurrency
• measure WAL/redo and commit latency

A write-heavy system may gain substantial headroom without adding shards.

Step 8: Partition very large tables when the data lifecycle supports it

Table partitioning splits one logical table into smaller physical pieces inside the database.

Common strategies:

• range by time
• list by category/region
• hash by key

Partitioning helps when:

• queries can prune most partitions
• old time ranges are removed regularly
• indexes have become very large
• maintenance/retention needs smaller units

It is particularly useful for event/log tables because old partitions can often be detached or dropped instead of deleting millions of rows.

Partitioning does not automatically add another database server or write owner.

Step 9: Shard only when independent write/storage ownership is needed

Sharding distributes different subsets of the data across separate database nodes.

Example:

tenants A-F -> shard 1
tenants G-M -> shard 2
tenants N-Z -> shard 3

Now independent tenants can write to different database primaries in parallel.

Sharding is useful when:

• dataset size exceeds practical single-node capacity
• one primary's write capacity is a proven limit
• tenants/domains have natural ownership boundaries
• failure isolation by shard is valuable

But it makes many things harder:

• cross-shard joins
• distributed transactions
• global indexes
• uniqueness
• routing
• rebalancing
• backups
• schema migration

Sharding is one of the last tools in the normal scaling sequence, not the first.

Step 10: Consider distributed SQL when you need stronger abstraction over many nodes

Distributed SQL databases attempt to provide SQL and transactional semantics across a distributed storage/replication layer.

They can simplify some application-level sharding problems, but distribution still has costs:

• network coordination
• quorum latency
• data locality decisions
• hotspot risk
• more complex failure behavior

A distributed database is valuable when its consistency, scale, geographic, and operational model matches the product.

It is not automatically faster than a well-run single-region primary.

Read scaling and write scaling are different axes

It helps to classify the workload explicitly.

Read-heavy system

Possible progression:

indexes -> cache -> read replicas -> analytics separation

Write-heavy system

Possible progression:

reduce writes -> batch -> fix contention -> faster primary -> partition -> shard/distribute

Storage-heavy system

Possible progression:

retention -> compression/archive -> partition -> shard/distributed storage

The right architecture depends on which axis is actually growing.

Scale the hot path, not the whole database

A database may contain hundreds of tables while only two create the scalability problem.

Examples:

• events grows by billions of rows
• orders gets the critical transactional write load
• users remains small

You do not necessarily need to shard the entire schema.

Possible targeted approaches:

• partition only events
• move analytics/event history elsewhere
• shard only tenant-heavy domains
• keep reference/config tables centralized

Scaling every table identically can create unnecessary distributed complexity.

Data ownership should stay clear

As the database architecture grows, avoid ambiguous sources of truth.

A healthy design answers:

• where is the authoritative record written?
• which stores are derived replicas/caches?
• which data can be stale?
• which copy can be rebuilt?
• which system owns retention?

For example:

PostgreSQL primary -> authoritative orders
read replica       -> stale-tolerant order reads
Redis              -> derived hot cache
ClickHouse         -> derived analytics history

Clear ownership makes recovery and consistency much easier.

Avoid dual writes as a scaling shortcut

When introducing a new store, it is tempting to write both systems directly from the request:

write primary database
write secondary database

If the process fails between those writes, the systems diverge.

Safer patterns include:

• transactional outbox
• change data capture
• database-native replication
• idempotent event consumers
• reconciliation

Scaling should not quietly weaken correctness.

Backpressure is a scaling mechanism

When downstream capacity is saturated, accepting unlimited concurrent work can cause collapse.

Use bounded:

• database connections
• worker concurrency
• queue depth
• request concurrency

A queue can smooth bursty asynchronous writes, but it does not increase the database's sustainable write rate by magic.

The system should degrade intentionally instead of creating an unbounded backlog or timeout storm.

Capacity planning needs a headroom target

A production database that runs at 95-100% of a critical resource during normal traffic has little room for:

• failover
• traffic bursts
• background maintenance
• backups
• replica catch-up
• schema migrations

Track headroom across:

• CPU
• memory
• storage space
• storage latency/IOPS
• WAL/redo volume
• connections
• lock waits
• replication lag

Scale before the resource ceiling becomes the incident.

Measure tail latency, not only averages

A database can have an average query latency of 10 ms while p99 is several seconds during contention.

Useful metrics include:

• p50/p95/p99 query latency
• transaction/commit latency
• throughput
• slow-query frequency
• lock waits
• connection-pool wait time
• storage latency
• cache hit ratio
• replication lag

Scalability is about predictable behavior under peak load, not only good averages during quiet periods.

Failure behavior changes as you scale

Every added component creates another failure mode.

Cache

What happens if Redis is unavailable?

Replica

What happens when lag becomes unacceptable?

Shard

Can unaffected tenants continue if one shard fails?

Distributed database

What happens during a network partition or quorum loss?

A scaling change is not production-ready until the failure path is defined and tested.

A practical scaling ladder

For many applications, this order keeps complexity proportional to need:

1. Measure the actual bottleneck.
2. Fix SQL, schema, indexes, and N+1 behavior.
3. Use bounded connection pooling.
4. Scale the primary vertically while economical.
5. Cache repeated expensive reads.
6. Add read replicas for stale-tolerant database reads.
7. Move heavy analytics away from OLTP.
8. Optimize/batch write paths and remove contention.
9. Partition large tables for pruning and lifecycle management.
10. Shard or adopt distributed storage when one ownership node is genuinely the limit.

Do not skip directly to step 10 because a diagram with many nodes looks more scalable.

Decision table

Database scaling is the process of moving the bottleneck without moving correctness out of sight. The best next step is usually the simplest mechanism that removes the resource constraint while preserving clear data ownership and predictable failure behavior.

References

• PostgreSQL — Performance Tips
• PostgreSQL — High Availability, Load Balancing, and Replication
• PostgreSQL — Table Partitioning
• PostgreSQL Wiki — Number of Database Connections