System Design103 min total · 14 parts
System Design Fundamentals for Interviews: Scalability, Trade-offs, and the Framework Interviewers Actually Grade
Part 10 of 14 · ~3 min
Storage and Indexing at Scale
SQL vs. NoSQL: What Actually Differs
"NoSQL is just faster" misses what's actually going on — a properly tuned relational query and a thoughtfully modeled NoSQL lookup can both scream along just fine. Where they genuinely diverge is in what each one is structurally set up to guarantee you.
| Relational | NoSQL (document / key-value / wide-column) | |
|---|---|---|
| Shape of the data | Fixed columns, enforced at the database layer | Loose — one record's fields can differ from the next |
| Relationships | First-class, via joins | Usually avoided — related data gets denormalized to sit together |
| Transactions | Real ACID guarantees, often spanning tables | Usually solid within one document; weaker across several |
| Scaling out | Harder — joins and multi-row transactions resist sharding | Easier — many of these stores are built to shard from day one |
| Where Fanline uses it | Orders, payments, seat inventory, where money and seat state need genuine transactional integrity | The "shows you might like" feed, where a flexible per-user record beats a normalized join |
Fanline's core booking data lives in Postgres on purpose: a seat hold and its payment charge need to succeed or fail together as one unit, and that's exactly what a relational transaction hands you for free. The recommendation feed lives somewhere else entirely — a document store — because that access pattern is naturally "give me this one user's precomputed list," which is already shaped like a single document, and rebuilding it via a join on every page load would cost more for no upside.
Indexing, Briefly
Put an index on a column (usually a B-tree, or a hash index when all you need is exact-match lookups) and you're paying in write cost and disk space to buy back read speed — the database keeps a second, ordered structure on the side that points straight at each value's real location, so a lookup becomes a quick walk down a tree instead of a crawl through every row in the table. That cost isn't hypothetical: any write touching an indexed column has to update every index built on it, and Fanline's inventory table is the last place that should carry an index "just in case," because it's already the busiest table in the system during an on-sale rush — every hold, every release, is one more write that each of that table's indexes has to keep pace with on top of the actual job it's doing.
When Denormalization Is the Right Call
Normalization — storing every fact exactly once, referenced elsewhere by foreign key — is the sane default for a relational schema, because it keeps data consistent: there's only one place to fix a fact when it changes. Denormalization goes the other way on purpose, duplicating data across records to avoid rebuilding it via a join on every single read.
Fanline copies a venue's name and city straight onto every event row instead of joining the venues table on every browse request, simply because browsing happens millions of times a day while a venue changing its own name happens maybe once a decade. That's a defensible call precisely because someone measured it first, not because duplication is automatically good — once a fact is copied in two places, both copies now have to be kept honest, and renaming a venue in the source table means a background job has to walk every one of that venue's event rows and update them too, or the storefront starts showing two different names for the same building depending on which code path happened to render the page.
Common mistake: duplicating a field "for speed" before anyone actually confirmed the join it replaces was ever slow to begin with. Denormalization earns its place as a targeted answer to one measured hot spot, not as a habit you reach for by default — every time you use it, you're signing up for a small, ongoing tax on keeping writes honest.