Read-Heavy vs Write-Heavy System Design

Read-heavy means, for a given piece of data, reads vastly outnumber writes — a product catalog entry gets viewed thousands of times for every price update. Write-heavy means the reverse — a click-events table receives constant appends and is read only occasionally, in aggregate, much later. The distinction matters because the two demand almost opposite trade-offs: read-heavy design pays a little extra on writes to make reads fast and cheap; write-heavy design pays a little extra on reads to keep writes fast and never blocked.

The trap is judging the shape from what the product visibly does rather than from measured operation counts. A URL shortener "is" a read-heavy redirect service by product description — but its click-analytics table, silently written on every single redirect, can easily out-throughput every other table in the system combined. Two tables inside the same request can have completely different, even opposite, workload shapes, and applying one pattern uniformly across both is how a read optimization ends up damaging a write-heavy path, or vice versa.

Measure the actual read:write ratio per table or per data shape, not per feature — code_mappings (read-heavy: resolved constantly, written rarely) and click_events (write-heavy: appended constantly, read rarely and in aggregate) live in the same request but need entirely different treatment.

For code_mappings — millions of redirects against a small, rarely-changing set of short codes — the goal is serving as many reads as possible without touching the primary store at all. Caching (in-memory LRU at the edge, or a shared cache like Redis in front of the database) absorbs the overwhelming majority of lookups; cache hit ratio, not database query latency, becomes the metric that actually matters. Read replicas spread the remaining database load across multiple copies, since read-heavy data tolerates the read replicas' slight replication lag far better than it would tolerate a single primary buckling under read volume.

Denormalization — storing the short code and its resolved URL together, pre-joined, rather than normalized across multiple tables — trades some write-time complexity and storage for a read path that's a single, direct lookup instead of a join. For a table this read-dominant, that trade is almost always worth it: writes are rare enough that the extra write-side cost barely registers, while every read benefits.

None of this changes how code_mappings is written — creation stays simple and infrequent. The entire design effort goes into the read path, because that's where nearly all the operation volume actually is.

For click_events — an append on every single redirect, read rarely and only in aggregate — the goal inverts: keep the write path as cheap and unblocking as possible, and accept that reads (aggregate reporting queries) can be slower and run somewhere else entirely. Recording the click asynchronously through a queue, rather than synchronously inside the redirect's response path, means a slow or backed-up analytics write never adds latency to the actual redirect — the same backpressure discipline of bounding and decoupling a non-core call applies directly here.

Append-only writes (never updating a row in place) avoid the lock contention and read-modify-write cost that in-place updates carry at high volume. Batching — buffering a few hundred milliseconds of events and writing them in one batch — trades a small amount of durability latency for dramatically fewer, cheaper write operations. Partitioning by time (a new table or shard per day/hour) keeps each write target small and keeps old partitions out of the hot write path entirely.

The most consequential rule for a write-heavy table: every additional index makes every write more expensive, because the database has to maintain that index on every single insert. On a table receiving millions of writes, an index that would be nearly free on a read-heavy table can measurably slow every write, and that cost multiplies across the entire write volume.

Most real systems, like this one, are read-heavy in one place and write-heavy in another within the same request — and the ratio isn't fixed forever. A feature that adds a new write to a previously read-heavy table (or a new read against a previously write-only table) can flip its shape without anyone deciding to. Re-measure operation ratios periodically, especially after a new feature lands, rather than assuming the original architecture assessment still holds.

The most common cross-contamination mistake is applying a read pattern to a write-heavy path: adding a convenience index to click_events to support a new dashboard query, without accounting for what that index costs on every one of millions of daily writes. The mirror mistake — applying write-optimization (partitioning, async buffering) to a table that's actually read-heavy and rarely written — adds real complexity for a shape that data doesn't have, slowing reads for no benefit.

When a table's read needs and write needs genuinely conflict — as click_events' write-optimized shape conflicts with a reporting dashboard's read needs — the right answer is usually to serve reads from a separate, purpose-built store (a periodic aggregation job, a materialized view, or a dedicated read replica) rather than compromising the hot write path to make an occasional read faster. This is the same separation CQRS applies more formally when read and write models diverge enough to need it.

Trigger. A new "top links today" dashboard needs to query click_events by code and time range efficiently, so an engineer adds a composite index on (code, created_at) directly to the production table — a completely standard read-optimization move, applied without checking the table's write volume. Symptom. Within an hour, redirect latency (GET /r/{code}, on a completely different, read-heavy table) starts climbing, and eventually a fraction of redirects start timing out.

Root mechanism. click_events receives several million appends per hour. The new index has to be updated on every single one of those inserts, and that per-write cost — multiplied across the table's actual write volume — was enough to slow the insert path significantly. The async click-write queue, no longer able to drain as fast as events arrived, backed up; once the queue hit its bound, the backpressure mechanism protecting the redirect path did what it was designed to do, but the underlying cause (a write-side cost increase from a read-side change) was never diagnosed as the actual trigger, because the dashboard and the redirect endpoint looked like unrelated systems.

Evidence. Write-queue depth and insert latency on click_events both start climbing at the exact timestamp the index migration deployed, well before redirect p99 begins to move — the causal chain is visible once the two metrics are placed on the same timeline. Recovery: drop the index immediately, let the write queue drain, redirects recover. Prevention: route the dashboard's read to a separate aggregation pipeline or read replica that isn't in the hot write path, and require any index added to a high-write table to be evaluated for write-amplification cost, not just read benefit, before it ships.

Measure the actual read:write operation ratio per table or data shape before designing around it — not from what the product description implies. A single request can legitimately touch both a read-heavy and a write-heavy table, and each one needs its own treatment.

For read-heavy shapes: invest in caching, read replicas, and denormalization, and measure success by cache hit ratio. For write-heavy shapes: invest in async/batched writes, append-only design, and time-based partitioning, and treat every added index as a cost that multiplies across the table's full write volume — evaluate it against that volume explicitly, every time, not just against the read query it's meant to speed up.

When a table's read and write needs genuinely pull in opposite directions, separate them into different stores rather than compromising the hot path — a dashboard's occasional read does not get to set the design for a table receiving millions of writes an hour.