Write throughput is saturating your primary database while the read replicas sit idle. Walk through how you would decide among batching, queueing, bigger hardware, and splitting the data across multiple primaries.

## Why the ordering matters Each step down this list is roughly an order of magnitude more expensive in engineering time and permanent operational complexity than the one above it. Teams that jump to splitting the data first pay that cost forever, often to solve a problem that three redundant indexes were causing. ## Step 1: find the actual bottleneck Candidate bottlenecks, and how they present: - **Commit / fsync latency.** Throughput is a multiple of 1/commit-latency per session; storage flush time dominates. Symptom: high wait time on log flush, low CPU. Fix: faster storage, group commit, batching several logical writes into one transaction. - **Log volume.** Every write generates log records; full-page writes after a checkpoint multiply it. Symptom: log throughput near device limits, checkpoint spikes. Fix: fewer/narrower updates, fewer indexes, checkpoint tuning, better storage. - **Index maintenance amplification.** A table with 8 indexes turns one row insert into 9 structure modifications, most of them random writes. Symptom: insert cost far above row size, high buffer churn. Fix: delete unused indexes — the single highest-yield write optimisation in practice. - **Lock/latch contention.** Many sessions serialising on the same row or the same index leaf. Symptom: throughput flat or falling as concurrency rises, lock waits climbing. Fix: contention-specific (see the hot-row topic) — batching, sharded counters, append-only ledgers. - **Connection oversubscription.** Thousands of connections thrash the scheduler and memory. Symptom: high CPU with low useful throughput. Fix: a pooler with a sane pool size, not more hardware. - **Replication overhead / sync commits.** Every commit waits on a network round trip. Fix: reconsider synchronous replica placement and count. Without this step, every later decision is a guess. ## Step 2: reduce work per write - **Batching.** 1,000 single-row inserts, each its own transaction, pay 1,000 round trips and 1,000 commit flushes. One statement inserting 1,000 rows pays one of each. Order-of-magnitude gains are routine. Cost: latency for the batching window, larger failure blast radius, and bigger chunks for replicas to replay (which spikes lag), so size batches deliberately — typically hundreds to low thousands of rows. - **Index diet.** Audit index usage statistics and delete what is unused or redundant (a prefix of another index). Also consider whether some indexes only exist for a report that belongs on a replica. - **Narrower, fewer updates.** Wide updates rewrite more; in MVCC engines an update that touches an indexed column forces index maintenance that an update to non-indexed columns can avoid. Splitting a hot mutable counter out of a wide row is a classic win. - **Transaction hygiene.** Short transactions release locks sooner and shrink replay chunks. Never call an HTTP API inside a transaction. - **Durability tuning, selectively.** Group commit amortises fsync across concurrent commits at no correctness cost. Relaxing synchronous commit trades a window of possible loss for throughput — appropriate for telemetry, unacceptable for payments. Make it a per-workload decision, never a global switch flipped in a hurry. ## Step 3: change the shape of the write - **Queue non-critical writes.** Put audit events, activity feeds, denormalised aggregates and search-index updates onto a durable log/queue; a consumer applies them in batches. The database sees far fewer, larger transactions. Requirements: the write must be safe to apply later (the user need not see it immediately), consumers must be idempotent (retries are certain), ordering rules must be explicit, and the queue must have backpressure and a monitored lag — otherwise you have moved the outage rather than removed it. - **Append-only instead of update-in-place.** Inserting events and rolling them up periodically avoids contention and turns random updates into sequential appends. Costs read-side complexity. - **Staging + merge.** Bulk-load into an unlogged or temporary table, then merge in one pass. ## Step 4: buy capacity Storage IOPS and latency, more memory to keep the working set and index upper levels cached, more CPU for commit work. Modern single nodes handle very large write rates; buying a year of runway is often correct and lets you fix the real problem deliberately rather than in an incident. Partitioning also belongs here: smaller per-partition indexes are cheaper to maintain, and retention becomes a partition drop instead of a mass delete that generates enormous log volume. ## Step 5: split the data across primaries When one node's write ceiling truly is the limit, splitting data across independent primaries multiplies write capacity — and permanently imposes cross-node queries, distributed transactions or their avoidance, rebalancing, and per-node operations. It is the right answer at sufficient scale and the wrong answer as a first move. The gate I would apply: the write path has been measured and optimised, a bigger box has been costed and rejected, growth projections show the ceiling arriving within the planning horizon, and there is a natural key along which the data separates cleanly. ## The judgement being tested This question is not looking for a list of techniques; it is looking for **ordering discipline** — measure, then reduce work, then change the consistency contract deliberately, then spend money, then accept permanent architectural complexity — and for candour about what each step costs. The strongest answers also state explicitly which data may lose durability or immediacy and which may not, because that classification is what makes queueing and durability relaxation safe.