System Design Methodology and Trade-off Analysis Questions

Direct answer

Match the store to the shape of the access pattern and the guarantees the component actually needs, not to whichever store the team already operates: relational for data needing multi-record transactions and ad hoc joins, key-value for simple point lookups by a known key at very low latency, document for semi-structured records usually fetched whole, wide-column for very high write throughput on wide, sparse rows accessed by a known key plus a range, and graph for data whose primary queries traverse relationships across multiple hops rather than filtering on attributes.

Structured elaboration

Guiding factors, in the order a competent decision usually walks through them: what is the dominant query, a lookup, a scan, a join, or a traversal; how many hops does a typical query need to walk, zero or one hop rarely justifies a graph database, several hops usually does; how strict does the transactional guarantee need to be; and what is the actual sustained write rate and row shape, not just the current team's default toolchain.

Worked example

A single e-commerce platform, choosing per component rather than one store for everything:

• Order and payment ledger: relational, because it needs multi-row transactions across order, inventory, and payment state that must all succeed or fail together.
• User session cache: key-value, point lookups by session identifier, no joins, and very low latency is the only real requirement.
• Product catalog: document, records vary by product category and are typically read whole on a product page.
• Clickstream and event ingestion: wide-column, an extremely high write rate queried later by user identifier plus a time range, exactly the access pattern wide-column engines are built for.
• "Customers who bought this also bought" and fraud-ring detection: graph, because the actual query traverses relationships (co-purchase edges, shared payment instruments) several hops deep, which is expensive to express as repeated joins in a relational engine.

Trade-offs & pitfalls

• Defaulting to whichever store the team already runs, then discovering the real query pattern needs relationship traversal or very high wide-row write throughput, and bolting it onto the wrong engine, shows up later as application code re-implementing joins or graph-walks, or a single store buckling under a write pattern it wasn't designed for.
• Choosing a graph database for data with only shallow, one-hop relationships is over-engineering, a foreign key in a relational table is simpler and has better tooling for that case.
• Choosing a wide-column store for workloads that need ad hoc filtering across arbitrary attributes works against its design, wide-column stores are fast specifically because queries are restricted to key plus range, arbitrary attribute filtering is its weakest fit.
• The same logical dataset can legitimately live in more than one store at once, for example a relational system of record with a derived graph or search index kept in sync, when no single store serves every access pattern well, but that adds an explicit synchronization problem that needs an owner, not an assumption that it will stay consistent on its own.

Direct answer

Strong consistency means every read after a write returns that write, as if there were only one copy of the data. Eventual consistency means replicas are allowed to disagree temporarily after a write, and are only guaranteed to converge if writes stop arriving. The trade-off: strong consistency gives simpler application logic at the cost of higher latency and lower availability during network trouble, while eventual consistency gives lower latency and higher availability at the cost of the application having to tolerate, or actively resolve, temporarily stale or conflicting data.

Structured elaboration

The consistency spectrum, not just two points

Why "eventual" has no built-in deadline
"Eventually" is not a duration; it is a promise that convergence happens once writes stop. The actual staleness window at any moment depends on the replication pipeline's replay capacity versus the current write rate (worked example below), which is a capacity problem, not a fixed property of the word "eventual."

Worked example

Suppose a replication pipeline can apply writes at 5,000 writes/sec sustained, and normal traffic sends 3,000 writes/sec. The replica keeps up, and staleness stays bounded by pure network propagation delay (milliseconds). If a promotional event spikes incoming writes to 6,000 writes/sec, the pipeline falls behind by:

$6{,}000 - 5{,}000 = 1{,}000 \text{ writes/sec of backlog}$

so after 60 seconds of that spike, the replica is:

$1{,}000 \times 60 = 60{,}000 \text{ writes behind}$

an ever-growing staleness window until the spike ends or the pipeline gets more replay capacity. This is the concrete question an eventual-consistency design has to answer: not "how long is eventually," but "what happens to staleness when write rate exceeds replay capacity, and what backpressure or scaling kicks in when it does."

Two scenarios where eventual consistency is acceptable

1. Social feed likes and comment counts: a like missing for a few seconds is invisible to the user experience and self-corrects on the next refresh.
2. Near real-time analytics dashboards: aggregates lagging by seconds is normal and expected, and nobody is making a financial decision off the exact current second.

Two where it is not

1. Financial account balances and transfers: a stale read can let a user believe funds are available when they aren't, or let two operations both proceed against the same balance.
2. Inventory reservation at checkout: a stale stock count can oversell an item, which is a customer-facing failure and an operational one (an order that can't actually be fulfilled).

The same distinction shows up at the feature level in machine learning. An online feature store computing a rolling 7-day purchase count can safely be eventually consistent; a few seconds of lag rarely changes a recommendation. But if the offline training pipeline computes that same rolling count from a different, out-of-sync snapshot than the one used at serving time, the model trains on a systematically different feature distribution than it sees in production. That training-serving skew is a strong-consistency requirement in disguise: the question isn't "is the count right," it's "was the count computed from the same consistent point-in-time snapshot on both sides."

Trade-offs & pitfalls

• Assuming "eventual" implies a short, informal default delay; there is no bound unless the team designs one, backed by a replay-capacity-versus-write-rate analysis like the one above.
• Reaching for causal consistency without realizing it requires tracking machinery (commonly version vectors: a small per-replica counter attached to each write that lets the system tell whether one write happened before, after, or concurrently with another, named here only) to know which writes are causally related; it is not a free upgrade over plain eventual consistency.
• Applying strong consistency everywhere "to be safe" and paying its latency and availability cost even for the majority of operations, like a social feed's like count, that never needed it.

Direct answer

Normalize when write correctness and storage efficiency matter most: each fact lives in exactly one place, so an update touches one row and there is no duplicate copy to drift out of sync. Denormalize when read speed matters most: copying a value into the table that needs it removes a join at read time, at the cost of extra storage and extra write work to keep every copy consistent. The decision is really about where you are willing to pay a cost: on the write path (normalized) or on the read path (denormalized).

Structured elaboration

What normalization buys you

• A single source of truth for each fact (a customer's name lives in one row in the customers table). Rename a customer once, and every order referencing that customer's ID sees the new name immediately, because nothing else stored a copy.
• No update anomalies: you cannot end up with two rows disagreeing about the same customer's e-mail address, because there is only one row.
• Smaller row sizes and less redundant storage, since each attribute is stored once.

What it costs

• Reads that need a full picture (an order plus the customer's name and the product's title) require joining across multiple tables. As the number of tables in the join grows, so does read latency and database load per request.

What denormalization buys you

• Fast reads: a single table scan or index lookup returns everything the page needs, no join required. This matters most for read-heavy, latency-sensitive paths (a product listing page, an order-history feed).
• Fewer round-trips and less join computation on the database, which matters at high read volume.

What it costs

• Duplicated data: the same fact (a product's name, a customer's e-mail) now lives in more than one row.
• Write amplification and staleness risk: change the source fact once, and every duplicate copy must also be updated, or the duplicates drift and become wrong. If you skip updating one copy, you now have silently inconsistent data.
• More total storage, since the same bytes are stored multiple times.

How to actually decide

1. Estimate the read:write ratio on the specific table or field in question, not the system as a whole. A field read a thousand times for every write is a strong denormalization candidate; a field written as often as it is read is not.
2. Ask how often the would-be-duplicated value actually changes. A product's category ID rarely changes; a live inventory count changes constantly. Denormalizing something that changes constantly multiplies your write cost and your staleness risk.
3. Ask how expensive staleness is if a duplicate briefly lags. A denormalized display name that is a few seconds stale is usually fine; a denormalized account balance is usually not.
4. Consider partial solutions before going fully one way: a materialized view or a cached read model gives you denormalized-shaped reads without hand-maintaining duplicate columns in the source tables, at the cost of a refresh lag you must define and tolerate.

Worked example

Take an orders schema. Normalized (third normal form): an orders table (order ID, customer ID, timestamp), an order_items table (order ID, product ID, quantity, unit price), a customers table, and a products table. Rendering an order-detail page means joining order_items to products (for the product name and image) and joining orders to customers (for the customer's name), a three- to four-way join.

Suppose the system processes 1,000,000 orders a month, averaging 3 line items per order, so 3,000,000 order_items rows are written per month. A normalized order_items row (order ID, product ID, quantity, unit price as fixed-width fields) is roughly 28 bytes. A denormalized version that also copies in the product name (about 24 bytes), product category (about 12 bytes), customer name (about 20 bytes), and customer e-mail (about 24 bytes) adds about 80 bytes per row:

$$28\ \text{bytes/row (normalized)} \quad\text{vs}\quad 28 + 80 = 108\ \text{bytes/row (denormalized)}$$

At 3,000,000 rows a month, that is:

$$3{,}000{,}000 \times 80\ \text{bytes} = 240{,}000{,}000\ \text{bytes} \approx 240\ \text{MB}$$

of pure duplicate data added every month, before counting index overhead or replication. That is the storage side of the cost. The write side shows up when a product gets renamed: if that product already appears in 50,000 historical order_items rows, a normalized schema needs a single row updated in products; a denormalized schema that copied the product name into order_items needs all 50,000 rows updated (or accepts that historical order rows show the old name, which is a legitimate choice for orders specifically, since an order should arguably show the name as it was at purchase time, not the current name).

$$50{,}000\ \text{rows updated} \quad\text{vs}\quad 1\ \text{row updated}$$

That last point is the real lesson: denormalizing an order line item's product name is often correct, not just a performance hack, because an order is a historical record and should not silently change when a product is renamed later. Denormalizing a customer's current e-mail address into the same row would be the wrong call, because you want that field to always reflect the customer's latest value, and a copy will drift.

Trade-offs & pitfalls

• Over-normalizing a read-heavy path (a product catalog page hit thousands of times a second) forces the database to redo the same multi-table join on every request, which is real, measurable load that a single denormalized read model would remove.
• Over-denormalizing a field that changes often multiplies write cost for a marginal read benefit, and creates a data-integrity bug class (stale duplicates) that is easy to miss in testing and expensive to debug in production.
• A common pitfall is denormalizing before measuring the actual read:write ratio, based on an assumption that reads are always dominant. Analytics and reporting schemas intentionally denormalize heavily (star-schema fact and dimension tables in an online analytical processing, OLAP, warehouse), because they are overwhelmingly read-heavy and batch-loaded; the live transactional path behind an online transaction processing (OLTP) system usually should not copy that pattern wholesale.
• The strongest senior answer treats this as a per-field decision, not a whole-schema philosophy: a single table can normalize some columns and denormalize others based on how each specific column is actually read and written.

Direct answer

Latency and throughput are not opposites by nature, they trade off through queueing: pushing more concurrent work through a fixed amount of processing capacity increases the time each request waits behind others, and holding latency low means keeping spare capacity in reserve rather than running it flat out. The right balance comes from setting an explicit target for both (a throughput floor and a tail-latency ceiling), then using queueing math plus load testing to find the utilization level where more throughput starts costing more latency than the business can absorb. What to measure at each load level: the full latency distribution, not just the average, including the 95th and 99th percentile (P95/P99), alongside the downstream business metric (conversion rate, task completion time) the latency target exists to protect.

Structured elaboration

Why the tension exists. Little's Law ties the three quantities together:

$L = \lambda W$

where $L$ is the average number of requests in the system (concurrency), $\lambda$ is the arrival rate (throughput), and $W$ is the average time a request spends in the system (latency). For a fixed amount of concurrency capacity $L$, pushing $\lambda$ up forces $W$ up. Throughput and latency are linked by whatever capacity sits between them, they only look independent at low load.

Decision criteria to walk through, in order:

• Is there a hard external constraint (a contractual service-level agreement, or SLA) versus a soft internal preference? Hard constraints bound the feasible region before you optimize anything.
• Is the load steady or bursty? A bursty workload needs headroom sized for the peak, not the average, or tail latency spikes during every burst.
• What is the true cost of extra capacity relative to the revenue or reliability cost of extra latency? If compute is cheap relative to the business impact of latency, buy headroom instead of accepting queueing.
• Which metric does the product actually care about, median latency almost never predicts user-visible pain, the tail does.

Process: baseline the current latency distribution and throughput, ramp load in steps while recording the full distribution at each step, locate the point where the P95 or P99 curve bends upward sharply (the "knee"), then correlate that knee to the business metric to decide whether operating past it is acceptable.

Worked example

Assume, for illustration, a single worker with an average service time of 10 ms per request ($S = 0.01\text{s}$), so its theoretical maximum throughput is $1/S = 100$ requests per second (RPS). Using the M/M/1 queueing approximation (a standard model for one server handling one request at a time, with randomly arriving requests and randomly varying service times, a common simplification for a single queue), the average wait time in queue at utilization $\rho = \lambda S$ is:

$W_q = \frac{\rho \cdot S}{1-\rho}$

Reproducing the middle row: $W_q = \dfrac{0.90 \times 0.01}{1-0.90} = \dfrac{0.009}{0.10} = 0.09\text{s} = 90\text{ms}$, so $W = 90 + 10 = 100\text{ms}$. Going from 70 to 90 RPS (a 29% throughput increase) roughly triples latency; the next 5.6% of throughput (90 to 95 RPS) roughly doubles it again. This is the shape of the trade-off: throughput gains near saturation cost latency disproportionately.

The same law sizes capacity to hit both targets at once. To sustain 5,000 RPS at an average latency target of 15 ms, the required in-flight concurrency is $L = \lambda W = 5000 \times 0.015 = 75$ concurrent request slots. If each server instance can hold 25 concurrent requests (its thread or connection budget), raw sizing needs $75 / 25 = 3$ instances, but running at 100% utilization guarantees queueing, so target roughly 65% utilization for headroom: $3 / 0.65 \approx 4.6$, round up to 5 instances.

Trade-offs & pitfalls

• Treating the median as the target metric hides exactly the users experiencing queueing delay, always instrument and alert on the tail, not the average.
• Adding raw compute capacity fixes queueing-induced latency but does nothing for latency caused by serialization cost or an inefficient algorithm, these are different bottleneck classes and need different fixes (see bottleneck-identification questions for the diagnostic process).
• Batching or coalescing requests can raise both average throughput and average latency-per-request while making the tail worse for whichever request lands first in a batch, batching trades individual completion time for aggregate efficiency and needs a separate tail-latency check.
• Autoscaling on CPU utilization alone can under-react to a pure queueing problem, alerting or scaling on the latency percentile itself, or on queue depth, catches the tension directly.
• Always tie the chosen operating point back to the business metric with real data (an A/B test or canary), a default like "P95 under 300 ms" is only correct if it is where the business metric actually degrades.

Direct answer

Match the pricing model to how predictable and interruptible the workload is: pay full price (on-demand) for unpredictable or short-lived work, commit to a discount (a reserved instance or savings plan) for the steady baseline you know you'll run for a year or more, and reach for the deep spot or preemptible discount only when the work can be killed and resumed without harm. Real workloads mix all three: a steady floor on committed pricing, a growth buffer on on-demand, and interruption-tolerant batch work on spot.

Structured elaboration

When each fits

Mixing for one workload

Split the workload into a steady floor, a variable middle, and interruption-tolerant batch, and price each piece with the model that fits it, rather than choosing one model for the whole workload.

Worked example (illustrative unit prices, not vendor-quoted rates)

Assume on-demand costs $0.10 per instance-hour, a committed baseline (reserved instance or savings plan) costs $0.06 per instance-hour (40% off), and spot averages $0.03 per instance-hour (70% off, an illustrative average across reclaim risk).

Baseline: 20 instances running 24/7 (730 hours/month):

$$\text{on-demand cost} = 20 \times 730 \times 0.10 = \$1{,}460/\text{month}$$ $$\text{committed cost} = 20 \times 730 \times 0.06 = \$876/\text{month}$$

Variable batch work: 10 additional instances running 8 hours/day on average (240 hours/month) on spot:

$$\text{spot cost} = 10 \times 240 \times 0.03 = \$72/\text{month}$$ $$\text{on-demand equivalent} = 10 \times 240 \times 0.10 = \$240/\text{month}$$

Blended monthly cost with the mix versus all on-demand:

$$\text{mixed} = 876 + 72 = \$948/\text{month}$$ $$\text{all on-demand} = 1{,}460 + 240 = \$1{,}700/\text{month}$$ $$\text{savings} = \frac{1{,}700 - 948}{1{,}700} \approx 44\%$$

Trade-offs & pitfalls

• Never put a single-master, stateful database on spot; there is no fallback for the primary if it is reclaimed mid-write.
• Overcommitting reserved or savings capacity beyond your actual steady floor turns a discount into waste; size the commitment to your measured baseline, not your hoped-for growth.
• Spot needs operational work to be safe: checkpointing, diversified instance types and zones so a single reclaim wave doesn't take out your whole batch fleet, and an automatic fallback to on-demand.
• Managing multiple pricing commitments has its own administrative overhead (tracking utilization, renewal timing); factor that into whether the discount is worth it at small scale.