Write-Amplification Audit

A team facing a slow database usually asks how to make reads faster. When the workload is write-bound, that question leads to remedies that make it worse — read replicas add log-shipping work to the primary, and extra indexes add maintenance to every insert.

The audit asks what one logical write actually costs: the row, every index that must be updated, the write-ahead log, replication to every replica, any trigger, and — usually forgotten — the eventual deletion of that row when it ages out.

Why it matters

Write amplification is invisible in application code. An insert looks like one operation, and with eight indexes it is nine writes plus log plus replication. Teams add indexes to fix reads and quietly triple their write cost, then conclude the database cannot handle the volume.

Implementation patterns

• Diagnose the bound first. Write-ahead log flush, index maintenance, lock waits and vacuum pressure mean write-bound; buffer misses and long select times mean read-bound. A team that cannot name which should not be choosing a remedy.
• Batch. One statement inserting a thousand rows rather than a thousand statements is frequently a large win, because per-statement overhead dominates for small rows. Usually the biggest single improvement, and it needs no new infrastructure.
• Drop unused indexes. Analytics and event tables reliably carry indexes added for a query that no longer runs, and every one taxes every write.
• Buffer through a queue, so ingestion spikes reach the database as a smoothed rate rather than as a spike.
• Partition by time, so writes concentrate in the newest partition and old data is dropped wholesale. Row-by-row deletion of old data is itself a large write load that teams routinely forget to count.
• Separate the ingestion store from the query store. Events want an append-optimised columnar store while operational data wants a row store, and forcing both into one database is the actual architectural error that batching only postpones.

Industry example

Product-analytics platforms such as PostHog and Amplitude ingest very large event volumes while also serving arbitrary segmentation queries. The two workloads have opposite storage requirements, and the characteristic scaling failure is trying to serve both from one general-purpose database — at which point read replicas are added, the primary gets slower, and the team concludes it needs to shard when it needed to separate.

The same pattern appears in observability platforms, IoT telemetry ingestion and clickstream collection.

Failure scenarios

• Read replicas added to a write-bound system, making the primary marginally worse.
• Indexes accumulating with no review, silently multiplying write cost.
• Per-row inserts where batching was available.
• Deletion of old rows running as a large unaccounted write workload, often at the same time as peak ingestion.
• One database for ingestion and analysis, which caps both.

Trade-offs

Batching trades latency for throughput: an event is not durable until its batch commits, and a crash loses the in-flight batch unless it is first written somewhere cheaper. That is usually acceptable for telemetry and unacceptable for financial events, which is another argument for separating the two paths rather than tuning one.

Dropping indexes trades read latency for write throughput, and the honest version of that trade requires knowing which queries actually run — which is the measurement the audit starts with.

Interview question

"Your ingestion database is at 90% CPU. Someone proposes read replicas. Explain why that might make it worse, and tell me the three things you would measure before choosing anything."