Infrastructure Scaling, Capacity Planning, and High Availability Questions

What to check first

• Lock wait metrics and lock timeout/deadlock events, if the database exposes them directly, since these point straight at where contention is happening.
• The slow query log, specifically for long-running transactions, since a transaction that stays open longer than necessary holds its locks longer than necessary, and is one of the most common root causes of exactly this symptom.
• Index usage on the write paths: writes that trigger a full table scan (to find the row to update, or to maintain a poorly indexed secondary index) take longer and hold locks longer than an indexed write would.
• The transaction isolation level in use: stricter isolation levels generally take broader or longer-held locks to guarantee their stronger consistency promises, so an unnecessarily strict level can be directly responsible for contention that a looser, still-correct level would avoid.
• Whether contention is concentrated on a small number of specific rows (a hot row, like a shared counter or a single frequently-updated record) versus spread evenly across the table, since these have very different fixes.
• Whether write latency is genuinely lock-bound or actually disk/IO-bound underneath: if the underlying storage is slow, transactions simply take longer to complete, which mechanically extends how long they hold their locks, so an apparent "lock contention" problem can really be a storage-latency problem wearing a lock-contention costume.

Root causes to rule in or out

• Long-running transactions (application code holding a transaction open across something slow, like an external API call, instead of committing promptly).
• Hot-row contention (many concurrent updates converging on the same row).
• Missing or ineffective indexes extending the duration of the locking write.
• An isolation level stricter than the workload actually needs.
• Underlying storage latency inflating transaction duration and, with it, lock hold time.

Immediate mitigations

• Identify and terminate or time out any genuinely stuck or unnecessarily long-running transactions.
• Add missing indexes on the columns the write path filters or joins on.
• Shorten transaction scope in application code: commit as soon as the database work is done, and move any non-database work (an external call, heavy computation) outside the transaction boundary entirely.
• Where correctness allows, move from pessimistic locking to optimistic concurrency (a version or timestamp column checked at write time) for the specific hot rows causing the worst contention, since this avoids holding a lock at all for most of the operation's duration.
• Reduce isolation level if the workload's correctness requirements genuinely allow it, after confirming what guarantee you would be giving up.

Longer-term architecture changes to scale writes

• Partition or shard the data so a single hot table's write load spreads across multiple physical nodes, directly reducing contention on any one node.
• For a genuinely unavoidable hot row (a global counter, for example), move updates to that row through a queue that serializes and batches them asynchronously, rather than letting every request contend for the same row lock directly.
• Split read/write traffic so reporting or analytical queries run against a replica instead of competing for locks and resources on the primary alongside live writes.
• For the hottest, highest-contention write paths specifically, consider an append-only or event-sourced pattern (recording what happened rather than updating a row in place), which structurally avoids update-in-place lock contention for that path, at the cost of needing a separate process to derive current state from the event log.

Test types and why

Run a load test at expected peak traffic to confirm the release doesn't regress latency or error rate at normal load, and a short spike test to confirm any new code path handles a sudden burst. A major release is exactly the kind of change that can quietly regress performance in a way unit tests never catch.

Ramp pattern

Ramp from baseline to expected peak over roughly 10 to 15 minutes in steps, rather than an instant jump, holding each step for a few minutes, so a regression shows up at a specific traffic level you can point to rather than only "it's slower now."

Key metrics

p50, p95, p99 latency (the median, 95th-percentile, and 99th-percentile response times: the value below which 50%/95%/99% of requests complete) compared against the previous release's baseline numbers, not just an absolute threshold, since "did this get worse" is often the more actionable question before a release; error rate by class; and resource utilization (CPU, memory, connection-pool usage) on the tier the release actually changed.

Isolating from production traffic

Run against a staging or pre-production environment that mirrors production sizing and configuration, using synthetic traffic generation rather than real user traffic. If a production-scale staging environment isn't available, a safer alternative is a canary: route a small, controlled percentage of real production traffic, for example 1 to 5%, to the new release behind a feature flag (a toggle that turns a new code path on for only a chosen subset of traffic without a separate deploy), with the ability to instantly roll that percentage back to zero, and compare its metrics against the rest of the fleet still on the old release in real time.

Safe ways to run without impacting real users

Use a dedicated load-test account or clearly tagged synthetic requests so they can be filtered out of business metrics and don't pollute production analytics. Rate-limit the test traffic generator so a misconfigured test can't itself become an accidental denial-of-service against production. And always keep a manual kill switch to stop the test immediately, plus pre-agreed rollback criteria, for example "abort if error rate exceeds 1% for more than 2 minutes," decided before the test starts, not improvised mid-test.

Build ahead of time

• Pre-scale compute for the announced window instead of relying purely on reactive autoscaling, since reactive scaling has a detection-and-provision lag, the time to evaluate a metric plus instance boot and warmup time, that a sudden 5 to 10x spike can outrun before new capacity comes online.
• Add a caching layer, a CDN (content delivery network) for static or semi-static content and an application cache for expensive read paths, ahead of time, since the database is usually the hardest tier to scale on short notice and the one most likely to buckle.
• Load-test at the target multiple beforehand against a realistic traffic shape, not uniform load, to find the actual bottleneck before the real event does; it is rarely where you would guess.
• Set a budget-aware autoscaling ceiling and a cost alert, so absorbing the spike does not mean discovering a runaway bill after the fact.
• Coordinate with the marketing team on exact timing and expected volume; a known start time turns "unpredictable" into "scheduled," which unlocks pre-warming and scheduled scaling policies instead of purely reactive ones.

Rely on reacting in the moment

• Fine-grained autoscaling for anything beyond the pre-scaled baseline, since provisioning for the absolute worst case permanently is wasteful; pre-scale to a reasonable multiple, say the low end of the 5 to 10x range, and let autoscaling absorb overshoot above that.
• Load shedding and graceful degradation as the last line of defense if the spike exceeds even pre-scaled and autoscaled capacity, protecting the critical path, such as checkout, at the expense of non-critical features.
• Active human monitoring during the campaign window, not just passive alerting, since a known high-risk window justifies attention you would not staff for a random Tuesday.

Why the database needs the build-ahead half specifically

Compute and the load-balancer or CDN layer scale relatively fast and cheaply; the database usually does not, due to connection limits, replica lag under write load, or vertical scaling requiring downtime for some engines. That asymmetry is why preparing ahead should weight most heavily toward protecting the database, through caching that reduces load reaching it, read replicas provisioned in advance, and connection pooling sized for peak, rather than assuming you can react to database strain the same way you react to compute strain.

Keeping costs from spiraling

Use scheduled scaling for the known window, giving predictable cost provisioned only for the announced duration, rather than leaving autoscaling limits wide open indefinitely, and scale back down explicitly once the campaign window closes, confirming the autoscaler's scale-in policy will actually do it.

Direct answer

Give each account a home region so the large majority of payment writes never leave that region, replicate asynchronously to the other regions for read availability and disaster recovery, and resolve the rare concurrent-write conflict deterministically rather than blindly picking the latest timestamp, since blind last-writer-wins can silently drop a debit or credit.

Structured elaboration

Data partitioning: shard by account or customer identifier, with each account assigned a home region (typically its closest one). Writes for that account happen locally in its home region.

Conflict resolution: because each account has one home region, genuinely concurrent conflicting writes to the same account are rare, mostly limited to the window around a region failover. Use a monotonic version or vector clock (a per-replica counter set that lets the system tell whether one write happened before another, or genuinely at the same time) to detect conflicts, and resolve money-movement conflicts with a saga-style compensating transaction (reverse and reapply in a defined order) rather than a blind last-writer-wins rule, which can make one side of a transfer disappear.

Fraud prevention: score transactions in real time using locally replicated risk features with a short refresh window, and run an asynchronous global reconciliation job that cross-checks every region's ledger against the others on a tight schedule (for example hourly).

Reconciliation: a scheduled job compares double-entry ledgers across regions, flags mismatches, and routes them into a runbook for correction, never an automatic silent merge for financial data.

Capacity headroom for cross-region surges: size every region with standing spare capacity, not just for its own normal peak, since a lost region's traffic must land on the survivors.

Operational playbooks for partition heals: when a network partition between regions ends, run reconciliation before re-enabling cross-region writes for the affected accounts, and quarantine any account with a conflicting write history for manual review instead of auto-merging it.

Worked example

With three regions, if one is lost, the other two must absorb its share of load:

$$\text{extra\_load\_on\_survivors} = \frac{1/n}{(n-1)/n} = \frac{1}{n-1}$$

For n = 3, that is 1/2, or 50% extra load on the two survivors. A payment written in the failing region during the partition, but not yet visible to the others when it started, is exactly the case reconciliation must catch on heal: the system detects the pending transaction, checks whether any downstream ledger already recorded its effect, and either replays or reverses it, never approving it silently.

Trade-offs and pitfalls

Last-writer-wins is dangerous for money movement specifically; a saga-based or explicitly-ordered resolution costs more engineering effort but avoids silently losing a transaction.

Fully synchronous cross-region consensus buys strong consistency but makes every home-region write wait on a round trip to another region; for a payments team this trade is worth evaluating explicitly against the async approach rather than defaulting to either one.

Fraud scoring on stale cross-region signals during a partition can miss an attacker deliberately exploiting the reconciliation window (a double-spend-style attack). Put a hard hold or limit on outbound funds during a detected partition rather than trusting async reconciliation alone to catch it after the fact.

Goal

Catch capacity saturation while it is still a leading indicator, before it becomes a user-visible latency or error problem, by instrumenting every layer a request actually passes through, not just the infrastructure underneath it.

Metrics by layer

• App layer: in-process request queue depth (requests waiting for a worker thread or handler), thread pool or connection pool utilization, and garbage-collection pause time for managed runtimes. These often rise before CPU or memory looks unusual, since a service can be capacity-constrained on concurrency long before it is constrained on raw resources.
• Infra layer: CPU, memory, disk I/O, and network throughput, the traditional signals, still necessary but rarely sufficient on their own.
• Queueing layer: for any service that sits behind a message broker or work queue, consumer lag (how far behind the latest message the consumer group is) and broker-side queue depth. A queue that is quietly growing is one of the clearest leading indicators of saturation there is, since it shows demand is already outpacing processing capacity, even while current latency for messages already being processed still looks fine.

Recommended alert thresholds

Alert on trend, not just absolute level: a request queue depth or consumer lag that is growing steadily over a sustained window (say, 10 minutes) is a stronger, earlier signal than a single absolute threshold crossing, since the absolute number that indicates trouble varies a lot by service and by time of day.

Dashboards

A layered dashboard mirrors the layered instrumentation: app-layer queue and pool metrics on top, infra metrics in the middle, queueing-layer lag at the bottom, all on the same time axis, so an operator can see which layer moved first when investigating an incident.

Synthetic checks to validate capacity continuously

Real traffic metrics can hide a capacity problem during genuinely quiet periods, low traffic can mask that a single node is already struggling, or that a dependency has degraded, simply because there is not enough real load to expose it yet. Address this with:

• Continuous synthetic transactions: a scripted request against the critical path, run from outside the service on a fixed interval (for example, every minute), measuring latency and success independently of real user traffic, so a problem is visible even during a quiet 3am window.
• Periodic synthetic load tests: not continuous, but run on a schedule (say, monthly, or after any significant capacity or code change) to validate that the headroom assumptions from your capacity plan still hold, since the actual per-request cost of a workload drifts as code changes, and a headroom number computed six months ago can be stale.

Together, continuous synthetic checks catch a currently-broken system that real traffic is too light to expose, and periodic load tests catch a headroom assumption that has quietly become wrong.