Data at Scale: Storage and Consistency

A relational database like PostgreSQL organizes data into tables with a fixed schema, and it earns that structure by giving you two things a lot of designs quietly depend on: joins across tables in a single query, and transactions that touch several rows and either all commit or none do. PostgreSQL keeps those transactions fast under concurrent load with MVCC (multiversion concurrency control): PostgreSQL's own documentation explains that each statement sees a snapshot of the data as it was at a point in time, so reading never blocks writing and writing never blocks reading. That's the mechanism, not a marketing line — it's what lets a relational database serve a mix of readers and writers without one queue stalling behind the other, while still giving every reader a consistent view.

A key-value store like DynamoDB starts from a different bet: you know your access patterns in advance, and you design the table around them rather than normalizing first and querying later. Every item has a partition key, and AWS's own guidance is explicit about what that key controls: its value is hashed to decide which physical partition stores the item, so a well-chosen key spreads requests evenly and a poorly chosen one creates a "hot" partition that throttles under load. In exchange for designing around a known access pattern, a key-value store scales writes across many machines far more easily than a single relational primary can — but it gives up the general-purpose join, and a query that wasn't anticipated at design time is often expensive or impossible without a new index.

The choice, then, isn't "relational is old, NoSQL is modern" — it's which bet fits the requirement you scoped earlier. Reach for a relational database when the data has real relationships you'll query across (a ledger, an inventory system, anything where a transaction must touch multiple rows atomically) and when the write volume fits on a primary you can still scale vertically or shard later. Reach for a key-value store when you have one or two access patterns you can name up front, write volume that a single node genuinely can't hold, and you can live with a secondary index or a cross-partition query being slower or eventually consistent. A URL shortener leans key-value — one access pattern, lookup by code; a ledger leans relational — it needs the transaction guarantee more than it needs horizontal write scale.

A single database node has a ceiling: one machine's disks, memory and CPU can only absorb so many writes a second, no matter how well-tuned the queries are. Sharding (or horizontal partitioning) gets past that ceiling by splitting one logical table across many physical machines, each holding a slice of the rows, so write throughput scales roughly with the number of shards rather than staying capped at one node's limit. The cost is real: a query that used to hit one machine may now need to fan out to several, and an operation that spans two shards — a join, a multi-row transaction — is no longer a single local operation. Sharding is therefore something you reach for once estimation shows write volume that one node genuinely can't hold, not a default.

The shard key is the whole design: it decides which physical shard a row lands on, and a bad choice creates a hot shard that absorbs a disproportionate share of traffic while the others sit idle. AWS's own guidance on DynamoDB partition keys makes the failure mode concrete: a status code with only a few possible values is a bad partition key because most items pile onto a handful of values, while a well-distributed key like a user ID, where the application has many users, spreads load evenly. The same logic applies to any sharded relational setup. A hash of a well-distributed key (user ID, not signup date rounded to the day) spreads writes evenly but makes range queries across shards expensive; a range-based key (sort by date) keeps ranges cheap but concentrates today's writes on whichever shard holds today's range.

Sharding solves a throughput ceiling; replication solves a different problem — a single copy of the data is also a single point of failure, and a single machine can only serve so many concurrent readers. Replication keeps one or more additional copies of a shard's data on separate machines, so a failed primary can be replaced and read traffic can be spread across replicas. The common shape is leader-follower (or primary-standby): one node accepts writes, and every other node applies the same stream of changes after the fact, so they converge on the same state without ever being written to directly.

How closely a follower tracks the leader is a choice, not a fixed property. PostgreSQL's documentation on streaming replication describes the default mode plainly: the primary streams its write-ahead log (WAL) to a standby as changes are generated, and by default this is asynchronous — the primary commits and returns to the client without waiting for the standby to confirm it received anything. Synchronous replication flips that: the primary's commit waits until at least one standby confirms it has the change, which closes the data-loss window at the cost of added commit latency, since every write now pays for a round trip to the standby before it can complete.

Failover is where the trade-off shows up concretely. If a primary using asynchronous replication crashes, any transaction it committed but hadn't yet streamed to a standby is gone the moment a standby is promoted to take its place — the standby simply didn't have it. The gap between what the primary committed and what a standby has applied is replication lag, and it grows under load or network trouble; a design that reads from replicas has to decide whether a slightly stale read is acceptable, or whether that particular read has to go to the primary. A synchronous replica removes the data-loss risk on failover, but only for the standby actually named as synchronous — the rest of the fleet, if any, stays asynchronous.

Gilbert and Lynch's formalization of what's known as the CAP theorem gives a precise shape to a trade-off every distributed system eventually faces: when a network partition splits nodes so they can't talk to each other, a system serving a write on one side has to choose between answering it locally — risking that side disagreeing with the other once the partition heals — or refusing to answer until the partition resolves. The first choice sacrifices consistency for availability; the second sacrifices availability for consistency. Outside of a partition, most real systems are also choosing along a related axis on every single request: how strong a guarantee does this particular read need, and what does that guarantee cost in latency.

DynamoDB's own documentation makes that per-request choice explicit rather than baking one answer into the whole system. The default is an eventually consistent read: it may not reflect a very recent write, but if you retry shortly after, it converges — and AWS states plainly that eventually consistent reads cost half as much as the alternative. A strongly consistent read asks for the most up-to-date data reflecting every successful prior write, at the cost of higher latency and more throughput capacity consumed; DynamoDB doesn't even offer it as an option on global secondary indexes, only on the base table and local secondary indexes. That's consistency treated as a per-read dial, not a single property the whole database either has or lacks.

The design move is to make that choice deliberately, per operation, rather than defaulting the whole system to the strongest guarantee everywhere. A dashboard showing a follower count can read eventually consistent — a count that's a few seconds stale costs nothing real. A checkout flow confirming that an item is still in stock before charging a card needs a strongly consistent read at that one point, even though every other read on the same page can stay eventual. Naming, out loud, which reads in a design need which guarantee is exactly the kind of non-functional detail an interviewer is listening for — it shows the trade-off was chosen, not defaulted into.