Scalability & Capacity Planning Questions

What I'd want to know about the workload first

• What the writes actually look like: single-row inserts versus batches, and whether the current bottleneck is disk I/O throughput, lock contention, or the overhead of committing (fsyncing) every individual write.
• Consistency requirements: can writes tolerate being asynchronous or eventually consistent, or does every write need to be immediately readable?
• Whether the data has a natural partition key (tenant ID, user ID, region) that writes could be split across, which is the precondition for horizontal scaling to even be viable here.
• Whether this is a one-time ceiling to clear or a trajectory that will keep climbing, since that changes how much re-architecture effort is worth investing now.

The decision framework

• Vertical scaling (a bigger instance, faster disks, more provisioned I/O): the fastest to implement and a reasonable choice if the headroom needed is modest and short-term, but it has a hard ceiling and doesn't fix a bottleneck that's inherently serial, like a single-writer commit log.
• Horizontal scaling (sharding writes across multiple database instances by a partition key): the sustainable choice if growth is ongoing and a good partition key exists, but it's a real re-architecture: a routing layer, and any query that spans shards gets harder.
• Changing the write pattern itself (batching writes, moving to an async queue, or a storage engine built for write-heavy workloads): often the highest-leverage option when the writes are small and frequent, because it reduces the cost per write instead of adding infrastructure.

A worked example

Say the primary is doing 4,000 writes/sec against a disk I/O ceiling of about 4,500 IOPS (input/output operations per second), with each write triggering its own commit. Batching writes into groups of 50 before committing turns 4,000 individual writes into roughly 80 batched commits/sec, which is far under the ceiling and buys significant headroom without touching hardware. That's why I'd usually check whether the write pattern itself can be improved before reaching for sharding.

What could go wrong with each approach

• Vertical: hits a cost or instance-type ceiling eventually, and the single instance stays a single point of failure the whole time.
• Horizontal: a poorly chosen partition key creates a hot shard that's just as bottlenecked as before, and operational complexity goes up.
• Batching/async: introduces a delay before a write is visible (staleness), needs idempotent retry handling (idempotent: safe to retry, because re-running the same write produces the same end result as running it once), and if the downstream queue consumer falls behind, the bottleneck just moves to the queue instead of being fixed.

Data sources

Historical resource utilization at fine enough granularity to see daily and weekly cycles (not just daily averages, which smooth away the actual peak), the business calendar for known seasonal events specific to this service, the product roadmap for planned launches or marketing pushes that would shift the baseline, and, because this is a stateful service, the data growth rate itself: storage size and replication lag trends, since state doesn't reset the way a stateless tier's load does.

Forecasting method

A plain trend line on raw traffic would smooth the seasonal peak away and under-forecast it. Instead I'd decompose trend and seasonality separately: take last year's comparable peak, apply this year's underlying growth rate to it. For example, if last year's seasonal peak was 40,000 requests per second (RPS) and the trend growth since then has run about 25% year over year, this year's forecast peak = 40,000 x 1.25 = 50,000 RPS. I'd cross-check that 25% growth figure against the last several weeks of baseline traffic to make sure the trend is still holding before betting the forecast on it.

Headroom policy

Target ceiling = forecast peak plus a buffer for forecast error, say 25%: 50,000 x 1.25 = 62,500 RPS. Because it's a stateful service, I'd also decide how much of that headroom needs to be pre-warmed ahead of time (cache populated, connection pools ramped, replication caught up) rather than scaled into reactively during the spike, since a stateful tier generally can't absorb a sudden load increase as fast as a stateless one can.

Cost trade-offs

Holding a 62,500 RPS-capable fleet year-round is expensive if the spike only lasts a couple of weeks a year. For example, if the non-peak fleet costs $10K/month and the full peak-ready fleet would cost $25K/month, holding it year-round costs roughly $180K/year extra, versus holding the extra capacity only for a 3-week window around the peak, which costs a small fraction of that. The middle path is usually a modestly larger always-on baseline plus a temporary reservation for the known spike window.

Testing the assumptions before the peak

I'd run a full load test at the 62,500 RPS ceiling several weeks before the actual peak window, leaving time to fix anything that fails and, if needed, procure more capacity. For a stateful service specifically, that test needs to cover cache warm-up time, replication catch-up under sustained write load, and connection pool exhaustion, since those are the failure modes that don't show up in a stateless load test but do show up here.

Metrics to track to forecast the spikes

• Request rate over time, to see the actual shape and onset speed of each spike, not just its peak magnitude.
• CPU utilization trend as a spike approaches, to confirm compute is tracking the traffic curve as expected.
• Latency percentiles (p95/p99, meaning the response time that the slowest 5% and 1% of requests exceed), an early sign of saturation before outright failures appear.
• Queue length or backlog depth, which for a data-serving fleet is often the earliest leading indicator: a queue growing faster than it's draining signals the fleet can't keep up before latency or error rates show it.

Pre-warming versus reserving on standby

Because these are recurring, predictable seasonal spikes with historical data behind them (unlike a one-off event with no track record), I'd favor pre-warming ahead of the known window over holding idle standby capacity year-round: scale up and warm caches shortly before the historically observed onset time, rather than paying for capacity that sits unused most of the year. Reserving idle standby makes more sense when the timing itself is unpredictable and there's no reliable lead-time signal to pre-warm against.

Cost versus availability risk, worked example

Say the baseline fleet costs $15K/month. Holding the full 10x capacity in standby year-round would add roughly $80K/month, about $960K/year. Pre-warming that same capacity only for the known spike windows, say 6 weeks total across the year, costs closer to $80K x (6/4.33), where 4.33 is the average number of weeks in a month (52 weeks / 12 months), which is about $110K for the year. Pre-warming into known windows is dramatically cheaper, but it only works if the timing is predictable enough to trust and there's enough lead time to actually warm the fleet (populate caches, ramp connection pools) before the spike arrives.

The availability risk to watch for

The main risk with pre-warming is being late: if warming starts even a little after the spike's real onset, the fleet hits the spike cold, and for a data-serving fleet specifically, a backlog that starts forming under a 10x load compounds quickly rather than draining. I'd build in a safety lead-time buffer, starting the warm-up a fixed amount of time before the earliest historically observed onset, not the average onset, so a slightly-early spike doesn't catch the fleet unprepared.