Multiple-choice synthesis across the storage unit: SQL vs NoSQL and the ACID/BASE trade, access-pattern-first design, blobs vs object storage with presigned URLs, durability vs availability, specialized stores, and the dual-write problem.
SDSenior◷ 13 min
Level
FoundationsJuniorMiddleSenior
Six questions that cut across the whole unit. Each is a decision you make at a design review or on a whiteboard — which store fits a workload, where the bytes live, what consistency you’re trading, and how two stores stay in sync — not a definition to recite.
Confirm you can place a workload on the SQL/NoSQL axis by the consistency it needs, spot when access-pattern-first design will bite, keep bytes out of the database, separate durability from availability, pick a specialized store on a measured limit, and avoid the silent dual-write divergence.
Quiz
Completed
A feature must atomically move money between two rows with no intermediate state visible to concurrent transactions. Which property is non-negotiable, and what store class typically provides it?
Heads-up Convergence 'eventually' is exactly the window you can't allow for money: one row debited, the other not yet, visible to a concurrent read. This needs ACID atomicity + isolation, not BASE.
Heads-up Write scale doesn't address a correctness invariant across two rows. A wide-column store tuned for write throughput typically relaxes the multi-row transaction you need here.
Heads-up Schema flexibility is irrelevant to atomic money movement. The non-negotiable is atomic, isolated multi-row transactions — an ACID guarantee.
Quiz
Completed
You modeled a key-value table around 'get item by id.' Six months later product needs 'list all items for a customer, newest first,' and customer isn't in the key. What does the access-pattern-first design predict?
Heads-up It doesn't. Without a matching key, the store can only scan. Access-pattern-first design is fast for planned patterns and punishes unplanned ones — the opposite of relational's ad-hoc flexibility.
Heads-up NoSQL secondary indexes aren't free relational B-trees: they add cost and are typically eventually consistent. The point stands — the new pattern wasn't designed in, so it's expensive.
Heads-up A list-by-customer query isn't a traversal; a graph store doesn't fit. The lesson is that the key design didn't anticipate this pattern, not that the family is wrong.
Quiz
Completed
A video app stores multi-GB uploads as BYTEA in Postgres 'to keep bytes and metadata in one transaction.' What breaks at scale, and what's the fix?
Heads-up That single 'benefit' is dwarfed by the costs: ballooning backups, a poisoned buffer cache, WAL bloat, and pool starvation. Databases are for small structured rows; large bytes belong in object storage.
Heads-up Replicas inherit the same bloat (and you ship every blob down the replication stream). The structural fix is removing blobs from the DB entirely — metadata in DB, bytes in object storage.
Heads-up Local disk attaches to one machine, isn't durable across machine death, and breaks the moment you add a second app node. Object storage is the machine-independent, durable home for large bytes.
Quiz
Completed
'S3 is eleven-nines durable, so our images survive any outage and we don't need cross-region replication.' What's the precise correction?
Heads-up Durability and availability are different guarantees. Eleven nines is about not LOSING data; reachability is a separate, lower number, and a region outage breaks reachability without losing a byte.
Heads-up Durability matters (it's why uploads aren't lost), but the claim conflated it with availability. The fix is to separate 'still exists' from 'reachable now,' then add cross-region replication for outages.
Heads-up That reintroduces every blob-in-DB failure and doesn't improve regional availability. The right answer is cross-region replication of the object store.
Quiz
Completed
A search box runs `WHERE description LIKE '%word%'` on a 3M-row table with a B-tree index on description, and it times out under load. What's the verdict and the right tool?
Heads-up No B-tree helps a leading-wildcard LIKE — there's no prefix to seek on, so it scans regardless. The fix is an inverted index that maps terms to documents, not another B-tree.
Heads-up A bigger box delays the wall, not removes it: it's still O(rows) per query and offers no ranking, stemming, or typo tolerance. LIKE isn't search; an inverted index is.
Heads-up Object storage has no query capability at all — no WHERE, no text matching. The workload needs a search engine's inverted index, not blob storage.
Quiz
Completed
You write an order to Postgres, then best-effort index it in Elasticsearch (log and ignore failures) to keep the request fast. Months later search is missing orders unpredictably. Root cause and fix?
Heads-up No vendor fixes this — it's architectural. Two independent writes with no shared transaction diverge on any crash or failure. Derive the index from the DB commit (CDC/outbox).
Heads-up Retries can't survive a crash between the two writes and risk double-indexing, while slowing the request you wanted fast. Derive the index from the commit log so committing the data guarantees indexing.
Heads-up No transaction spans Postgres and Elasticsearch — separate systems, no shared commit. You need an outbox row committed atomically with the order, or CDC off the WAL.
Recall before you leave
01
Why is 'eleven nines durable' not 'always available,' and what closes the gap?
02
How do you keep a search index consistent with the database without a dual write?
Recap
The through-line is that storage choice is matching a workload to the guarantees a store actually provides. Money needs ACID atomicity and isolation (relational), not BASE — convergence “eventually” is a window you can’t allow. Access-pattern-first NoSQL design is fast for planned patterns and brutal for unplanned ones, so a new query with no matching key degrades to a scan. Large bytes never go in the database (ballooning backups, poisoned buffer cache, WAL bloat, pool starvation) — keep metadata in the DB, bytes in object storage, served by presigned URLs. Durability is not availability: eleven nines means you won’t lose bytes, not that a region can’t go dark, which is what cross-region replication is for. A specialized store (time-series, search) earns its place on a measured limit and is treated as a derived index, with the dual-write problem solved by deriving that index from one authoritative commit via CDC or a transactional outbox — never two independent writes.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.