Analyze with AI
Get AI-powered insights from this Mad Devs article:
A production database is approaching saturation. Latency rises under load, CPU or I/O stays elevated, and the architecture discussion quickly narrows to a larger instance, replicas, or sharding. The database layer has already been confirmed as the constraint. The remaining question is more precise: which database change will remove that constraint?
If the limiting layer is still uncertain, start with Application Performance Optimization: How to Find the Real Bottleneck Before You Fix It. This guide begins at the next decision. It maps a measured database constraint to an intervention, the metric that should move, the problems left unsolved, and the operational cost introduced.
Key takeaways
- Treat repeatable database saturation as the entry condition for scaling, not a general feeling that the system is slow.
- Compute, read, write, and data-volume pressure lead to different architecture branches even when they produce the same user-facing latency.
- Require a proposed change to name the limiting metric it should improve and the constraints it cannot affect.
- Accept a scaling change only after measuring both the recovered capacity and its new cost in lag, complexity, reliability, or operations.
Classify the database constraint before choosing a scaling strategy
Classification does not require another end-to-end performance investigation. It requires enough production evidence to distinguish one database constraint from another under a representative workload.
Compute or memory pressure appears when database CPU remains saturated, queued database work grows, memory pressure causes eviction or swapping, or the working set no longer fits available memory. Break the evidence down by query class. A high aggregate CPU figure cannot distinguish useful work from a small number of inefficient scans.
Read pressure is visible when reads dominate throughput and resource consumption, read latency degrades with concurrency, and writes still have headroom. Measure operations per second, I/O, buffer-cache behavior, and latency separately for read and write paths. If a few repeated requests create most demand, reducing those requests may be more appropriate than adding database nodes.
Write or transaction pressure appears when commits, lock contention, log or journal throughput, storage latency, or serialization points limit progress on the primary. A workload can be modest in query count yet expensive because every transaction updates several indexes or contends for the same records.
Data-volume, working-set, or storage pressure is a different constraint again. Evidence includes sustained storage growth, a working set larger than practical memory, indexes that no longer remain hot, or maintenance operations that exceed their window. Capacity and access locality matter more than raw row count. There is no universal number of rows that triggers a topology change.
Two secondary categories often clarify the branch. Connection saturation may reflect pool sizing, long transactions, or a concurrency model rather than insufficient database capacity. Analytical queries may compete with OLTP for CPU, cache, and I/O even though each workload performs adequately in isolation.
Database scaling decision matrix
| Confirmed constraint | Candidate intervention | Expected effect | Does not solve | New trade-off |
|---|---|---|---|---|
| Single-node CPU or memory ceiling | Vertical scaling | More local compute or memory headroom | Inefficient workload or unlimited future growth | Higher instance cost and an eventual hard ceiling |
| Read-heavy primary saturation | Read replicas | Move eligible reads away from the primary | Primary write throughput | Replication lag and routing complexity |
| Repeated expensive reads | Cache or read-path optimization | Reduce database read demand | Write or storage bottlenecks | Invalidation and stale-data risk |
| Large tables with ineffective pruning | Partitioning | Scan less data and simplify lifecycle operations | Node-level capacity by itself | Partition design and maintenance |
| Sustained write or storage ceiling | Sharding or a distributed database | Spread data and write load | A poor shard key or inefficient queries | Routing, rebalancing, and distributed-system complexity |
| Analytics competing with OLTP | Workload separation or a reporting replica | Isolate analytical demand | The primary transactional write ceiling | Data freshness limits and extra infrastructure |
The matrix identifies candidates. The database engine, consistency requirements, workload shape, and ownership model determine the final design.
Use vertical scaling when the constraint is still local to one node
Vertical scaling is a rational choice when the measured limit is local CPU, memory, storage bandwidth, or IOPS and a larger instance provides enough headroom for the planning horizon. The expected result must be concrete: lower CPU saturation at the same request rate, a higher buffer-cache hit ratio, lower storage queueing, or improved tail latency under the same workload.
Scale-up preserves a single-node application model. It usually avoids new query routing, replication semantics, and distributed transactions. Google Cloud lists unchanged application code and easier single-server maintenance among its advantages, while also noting the physical ceiling and possible downtime of vertical scaling. Those properties can make a larger instance the lowest-risk and most economical option for a stable or moderately growing workload.
Validate more than the new resource total. Run the representative workload against the target class, confirm that additional memory improves working-set residency, and verify whether the storage configuration actually supplies the expected IOPS. Some managed tiers couple CPU, memory, network, and storage performance; others do not.
Scale-up will not correct an unbounded scan, a missing access boundary, or contention on one logical record. It also retains an eventual provider or hardware limit. Cost efficiency may diminish at high tiers, and a resize may require a restart or failover. These are planning constraints, not reasons to dismiss vertical scaling as merely temporary.
Add replicas when reads are the limiting workload
Read replicas fit a primary that is saturated by reads while its write path remains within capacity. Eligible reads move to replicas, reducing primary CPU and I/O and increasing aggregate read throughput. AWS documents this exact use case: RDS read replicas reduce source-instance load for read-heavy workloads.
The application still needs a routing policy. Product catalogs, historical views, search support, or reporting may tolerate slightly stale data. A request that writes a value and immediately reads it back may require primary routing, a session guarantee, or a lag-aware fallback. Measure primary read CPU and read latency before and after the change, then measure how much traffic actually reaches replicas. An idle replica is infrastructure, not read scaling.
Replication lag is part of the product behavior. In Amazon RDS, source updates are copied asynchronously to conventional read replicas. A routing design therefore needs explicit freshness requirements, lag monitoring, and behavior for a replica that falls behind. Connection-pool capacity and failure handling also change when the application gains multiple database endpoints.
Replication is not equivalent to write scaling. Writes still reach the primary and generate the replication stream. A replica cannot remove primary commit, log, lock, or write-I/O limits. High availability is also a separate objective: a read replica may be promotable, but read offload, synchronous standby, failover policy, recovery point, and recovery time are distinct design decisions.
Separate or reshape workloads before distributing data
A topology can remain single-primary while the team changes which work reaches it and how much data each operation touches. This branch is appropriate when contention between workloads or poor data locality is the measured constraint.
Analytical and reporting queries can evict hot transactional pages, consume I/O, and occupy connections for much longer than OLTP requests. Moving them to a reporting replica or an analytical store should reduce OLTP tail latency and resource contention. The cost is delayed data, another data path, and additional operational ownership. Verify the result with workload-specific latency and resource metrics rather than aggregate database CPU alone.
Partitioning addresses a different shape. It splits a logical table into physical parts so the engine can prune irrelevant data or manage data lifecycle in units. PostgreSQL documents that partition pruning can exclude partitions that cannot satisfy a query, and that adding or removing partitions can simplify bulk lifecycle operations.
Test partitioning with the actual predicates that should align with the partition key. Compare partitions and rows scanned, query duration, plan time, index size, and maintenance duration. A date partition helps only if important access patterns allow the engine to exclude date ranges. Too many partitions can increase planning and maintenance overhead.
Partitioning does not automatically distribute traffic among independent database nodes. A partitioned table may still live on one server and share its CPU, memory, I/O, and failure domain. Sharding adds distribution and routing; partitioning primarily changes data organization within the database system.
Archiving cold data or defining hot and cold storage boundaries follows the same evidence-based logic. It is useful when inactive data expands indexes, backups, or maintenance windows. It does not help a hot key or sustained write bottleneck. If the evidence instead points to inefficient queries, indexes, or application access patterns, treat that as an optimization problem rather than making scaling carry the wrong responsibility.
Scale writes horizontally only when the constraint requires distribution
Horizontal write scaling becomes justified when a practical single-node configuration cannot provide the sustained write throughput, working-set capacity, or storage capacity the workload requires. The evidence should show a durable limit under a representative workload, not a transient spike or one inefficient statement.
Sharding distributes subsets of data and traffic across nodes. MongoDB describes sharding as support for very large datasets and high-throughput operations and notes that both read and write workloads can be distributed when operations route effectively. The principle applies beyond one product: useful horizontal capacity depends on requests being divisible along a sound data boundary.
The shard key is therefore an architecture decision, not a deployment setting. Evaluate its cardinality, write distribution, growth pattern, query coverage, and relationship to transaction boundaries. A monotonically increasing key may concentrate new writes. A low-cardinality key may create large, uneven shards. Queries without a routing key may fan out across nodes, trading one local scan for distributed scatter-and-gather work.
The expected improvement should appear in per-node write utilization, commit latency, storage distribution, and hotspot rate at the target aggregate throughput. Validate cross-shard queries and transactions separately because their coordination cost can dominate even when average throughput improves. Also exercise rebalancing while the system serves traffic; distribution that works only in a static benchmark is incomplete.
Horizontal distribution introduces routers, metadata, balancing, failure modes, backup coordination, and more demanding observability. A topology change may also require a staged data migration and cutover. That is a separate implementation risk; use a dedicated zero-downtime database migration plan rather than treating migration mechanics as evidence that sharding is the right architecture.
There is no database-independent trigger such as a row count or QPS threshold. Workload cost, access locality, consistency, hardware, and engine behavior determine the ceiling.
Consider a hypothetical service whose primary database is near saturation. Reads account for 80% of its workload, write latency remains within its objective, and profiling shows read CPU and I/O consume the available headroom. Sharding could distribute the workload, but it would also distribute the write path and introduce routing the evidence does not require. A read replica can offload eligible reads, and success can be checked through primary read CPU, replica traffic, lag, and tail latency.
Change one condition: the primary is now limited by sustained commit throughput while reads remain inexpensive. The symptom visible to users may still be rising latency, but a read replica will not move the limiting metric. The architecture now needs a write-path change, workload boundary, or horizontal distribution supported by the data model.
Treat autoscaling as capacity automation, not architecture selection
Autoscaling changes allocated capacity in response to demand within rules configured for a platform. It can reduce manual resizing for variable workloads and provide headroom for recurring growth. MongoDB Atlas, for example, documents reactive and predictive cluster tier autoscaling as mechanisms that adjust compute or storage based on usage and forecast patterns.
Automation still operates on the existing architecture. It cannot select a shard key, make cross-node coordination inexpensive, turn a primary-only write path into a distributed one, or repair a query that scans unnecessary data. It also takes time to detect demand and provision capacity, so a sudden event can exceed the current tier before scaling completes.
Define the autoscaling metric, range, reaction time, and cost boundary. Then load-test whether scaling occurs early enough and whether latency remains acceptable during the transition. If the workload repeatedly reaches the configured maximum, autoscaling has exposed a planning or structural limit rather than removed it.
Define success before changing the topology
Write the acceptance criteria before implementation. Record the limiting metric and its baseline under a representative workload, the improvement expected from the intervention, the operational trade-off the team will accept, and the condition that should stop or roll back the change.
The mapping should be specific:
- For a read replica, measure primary read CPU or I/O, read latency, routed read volume, and replication lag.
- For vertical scaling, compare saturation and tail latency at the same workload and database state.
- For partitioning, compare partitions or rows scanned, query duration, plan time, and maintenance cost.
- For sharding, measure per-node write throughput, storage balance, hotspot rate, cross-shard latency, and rebalancing impact.
Test at the workload shape the system must support, not only at a higher average rate. Preserve the read/write mix, concurrency, transaction size, data distribution, hot keys, and slow analytical operations. Observe enough time to capture cache warm-up, checkpoints, replication, and engine-specific background maintenance.
After deployment, inspect the original metric and the new failure surfaces together. Tail latency, error rate, lag, failover behavior, operator workload, and cost may reveal that capacity was gained at an unacceptable price. Check the next bottleneck as well: removing database read pressure can expose application CPU, network, or downstream limits.
A scaling change is successful when the original limiting metric improves without creating a new constraint that costs more than the gain.
When the database constraint already affects product growth but several interventions remain plausible, Mad Devs' Backend Development Services can help connect production evidence to backend performance, data-access, and topology decisions.
Conclusion
Take the database constraint your team is currently discussing and write down three things before selecting a scaling mechanism: the metric that proves the constraint, the metric the proposed change should improve, and the new operational trade-off the team is willing to accept.
Backend performance assessment Choose the right scaling path Map the database constraint, candidate fixes, and trade-offs before changing topology. Review the bottleneck |
Latest articles here
