A service keeps a per-tenant counter in a single row that is updated thousands of times per second. Read traffic scales fine, but write latency spikes and throughput plateaus. Explain why multi-version concurrency control does not help this workload, and how you would weigh the options for fixing it.

## Why the read-side win is irrelevant here MVCC eliminates read locks by keeping multiple versions. But a logical row has exactly one *current* version, and two transactions cannot both produce its successor without losing one of the updates. So writers take an exclusive row lock, held to end of transaction, and first-updater-wins. On a single hot row that produces a strict queue. The property everyone quotes — readers don't block writers — is simply not the axis under stress. Worse, hot-row updating interacts badly with the rest of the MVCC machinery. Each update creates a version, so a row updated thousands of times per second generates thousands of dead versions per second. Depending on the storage design, that means either a growing version chain the engine must walk, or a heavily churned page plus reclamation pressure and index maintenance. High-frequency single-row update workloads are exactly where the version-storage cost of MVCC shows up worst. ## Do the arithmetic first The ceiling is `1 / (time the lock is held)`. If the transaction touches the counter early and then does 4 ms of other work before commit, every other writer waits those 4 ms: roughly 250 updates/s. This is a *serial* resource, so Little's law applies — adding concurrency past the ceiling only grows the queue. A common symptom is a latency graph that is flat until a threshold and then hockey-sticks, with lock-wait time dominating. Measure the hold time before choosing a redesign; the fix is often much cheaper than people expect. ## Lever 1 — shorten the critical section The lock starts at the UPDATE and ends at COMMIT. So: - Do the counter update **last**, immediately before commit. - Get every external call — payment gateway, other service, message publish — **out of the transaction**. Holding a database row lock across a network call couples your write ceiling to someone else's p99. - Keep the transaction to what must be atomic; split reporting or auditing writes out. - Ensure a consistent update ordering across all code paths, since contention makes deadlocks far more likely. This usually buys an order of magnitude and costs nothing structurally. It should always be step one. ## Lever 2 — shard the counter Replace one row with N (say 16 or 64) per tenant. Each writer picks a shard by worker id, connection id, or random choice, and updates that row. Reads become `SELECT sum(count) FROM counter_shards WHERE tenant_id = ?`. Write concurrency multiplies by roughly N. Tradeoffs to state explicitly: reads get more expensive and touch N rows (fine if reads are rarer than writes, which is the premise here); decrement-with-a-floor logic gets harder because no single shard knows the total, so quota enforcement at an exact boundary needs care; and N must be chosen — too small does not relieve contention, too large makes reads and cache footprint worse. Sharding is the standard answer when the counter must stay transactional and reasonably fresh. ## Lever 3 — append then aggregate Insert one row per event instead of updating a shared row. Inserts to different rows contend for almost nothing (page allocation and index leaf pages, which spreads out), so write throughput scales close to the hardware. The aggregate becomes a periodic rollup job, a materialized view, or an on-demand SUM over a recent window plus a stored base. This buys the most write headroom and additionally gives you an audit trail. It costs storage, a retention/cleanup policy, and a rollup pipeline — real operational surface. It also changes read freshness semantics unless you sum the un-rolled-up tail at read time. ## Lever 4 — take it out of the database If the counter is telemetry-grade — rate limiting with soft edges, dashboards, usage estimates — the right answer may be that it does not belong in an ACID row at all. An in-memory counter with periodic flush, or a stream aggregated downstream, removes the contention entirely. This is only acceptable if losing a few increments on a crash and lagging by seconds is genuinely fine. Say that condition out loud rather than assuming it. ## The judgment call The question to answer before choosing is: **how exact and how fresh must this number be, and who reads it?** A billing counter that gates a hard quota, an at-most-once idempotency guard, and a usage dashboard have three different right answers. Also ask what else the transaction does — if the counter update is one line inside a transaction that also calls an external service, the redesign may be unnecessary once that call moves out. Finally, be explicit that this is a *design* limit, not a tuning knob. No isolation level, connection-pool size, or index changes the fact that a single row's updates serialize. Raising the pool size in response to hot-row contention makes the symptom worse and is a classic wrong move.