Module 3: Data Model and Short Code Generation

3. SQL vs NoSQL for Enlace

Description

"SQL or NoSQL?" is one of those questions that in interviews is answered badly by dogma —"NoSQL scales more", "SQL is more serious"— and well by analysis. In this lesson we answer it well, for Enlace's concrete case, and the method you use here will serve you for any system: first you name the access pattern, then you let that pattern choose the store. By the end you'll be able to defend a choice for Enlace without leaning on fashions, knowing exactly what each option gives you and what it takes from you.

Lesson 2 already left the work half done without telling you. You designed a thin record (five fields, ~1 KB) with short_code as the primary key, and you saw that 99% of the operations are "give me the row with this short_code". That profile —exact lookup by key, no joins, no ad-hoc queries, with a ratio of 100 reads per write— is the description of a dictionary. And when your problem is a dictionary, the SQL vs NoSQL discussion changes character: it's no longer "which is better in general?" (a question with no answer), but "which better serves a dictionary at this scale?" (a question with a defensible answer).

Connection to the module: this lesson closes the where that the what of lesson 2 opened. With the data model and the store decided, the module moves on to the algorithmic part: how the short_code is manufactured (lessons 4 to 7). But there's an important bridge built here: the store choice and the generation strategy talk to each other. For example, the "random + verification" strategy needs the store to be able to say quickly "does this code already exist?" —a uniqueness constraint or a GET by key—, and that's something both a relational table with a unique index and a key-value store do well. You'll see that connection again in lesson 7, when we bring store and strategy together into a single decision.

Two libraries, two ways of searching

Imagine two libraries. The first is a research library: it has an enormous cross-referenced catalog where you can ask rich questions —"give me all the physics books published between 1950 and 1970 by French authors, sorted by number of citations"— and the librarian, with their system of cards and references, assembles the answer for you by combining several tables of data. It's powerful and flexible: almost any question has an answer. The price of that power is structure and discipline: every book enters with its complete card, in a fixed format, and keeping the catalog consistent takes work. That library is a relational database (SQL): tables with a fixed schema, relationships between them, joins, and a query language (SQL) to ask almost anything.

The second is a theater coat check. You arrive, hand over your coat, and they give you a ticket with a number. On your way out, you return the ticket and they give you your coat. That's all the coat check knows how to do: number → coat, coat → number. You can't ask it "how many red coats are there?" or "give me the coats handed in after 8". It's very dumb compared to the library. But precisely for that reason it's very fast and trivial to scale: since the only thing it does is "ticket → thing", you can put ten identical coat checks, split the tickets among them, and each one serves its part without coordinating with the others. That coat check is a key-value store (a form of NoSQL): it stores a value under a key, returns it by that key, and almost nothing else.

The lesson's question, translated to this analogy, is: does Enlace need a research library, or is a coat check enough? And the answer jumps out as soon as you look at what questions people ask Enlace. The only question that matters, 4,000 times per second, is "number → thing": give me the URL of this ticket. Enlace doesn't ask its store "give me all the French links sorted by clicks". Enlace is, at its heart, a coat check.

The access pattern, spelled out in full

Before choosing, let's put Enlace's access pattern in a table, because that is the input for the decision, not the fashion. These are all the operations Enlace does against its store:

Operation	What it does	Frequency	Shape
`resolve(short_code)`	Reads a `short_code`'s row and returns `long_url`	~4,000/sec	Exact lookup by key
`shorten(long_url)`	Inserts a new row with a new `short_code`	~40/sec	Insertion by key
`exists(short_code)`	Is this code already taken? (for the generation strategy)	tied to writes	Exact lookup by key
increment `clicks`	Adds 1 to the counter (ideally in batches, not on each read)	deferrable	Update by key
expire/clean up	Deletes expired links	in batches, cold	Scan by `expires_at`

Look at the "Shape" column. Four of the five operations are by key —exactly what a coat check (key-value) does wonderfully—. The only one that isn't, the cleanup by expires_at, happens cold, in batches, off the hot path, and doesn't demand a sophisticated store: it can be resolved with a periodic scan or a native TTL if the store offers it. There's not a single operation that needs a join, a live aggregation, or an ad-hoc query over arbitrary columns. That's the finding: Enlace's access pattern uses almost none of what a relational database offers extra over a key-value.

Pause on the 100:1 ratio we've been carrying since module 2. It doesn't only say "there are far more reads than writes"; it says that the operation to optimize to the bone is resolve, a lookup by key. Any store that makes that lookup fast and that scales horizontally to absorb 4,000 (or 40,000, if Enlace grows) reads per second is a good candidate. And "lookup by key, horizontally scalable" is, almost word for word, the definition of a key-value store.

So, NoSQL always? Not so fast

It would be easy to close here with "it's a dictionary, use a key-value, done". But good system design is honest with both sides, and there's a strong reason why many real shorteners run on PostgreSQL (SQL) and do perfectly well. The reason is the scale of the problem compared to the capacity of a single modern database.

Do the math with Enlace's numbers. The hot path is ~4,000 reads per second of an indexed primary-key lookup, over a table that —with a cache in between, module 4— rarely touches the disk. A single well-tuned PostgreSQL instance handles on the order of tens of thousands of lookups by key per second without breaking a sweat. And the ~6 TB at 5 years, although not trivial, are within what a relational database handles (with partitioning) or what fits, growing, on the disk of a large server. In other words: at Enlace's scale, a single relational database is enough, above all with the cache absorbing most of the reads. The need to split the data across many machines —sharding— appears higher up, and it's the topic of module 5, not an obligation of today.

So, what does each option gain for this case? Here's the honest balance:

Criterion	Relational (SQL, e.g. PostgreSQL)	Key-value (NoSQL, e.g. Redis/DynamoDB)
Lookup by key	Fast (primary-key index)	Very fast (it's its only operation)
Horizontal scale	Requires work (partitioning, replicas) — module 5	Native: splitting by key is its nature
`short_code` uniqueness	Free: `UNIQUE`/`PRIMARY KEY` constraint	Free: the key is unique by definition
Rich queries / analytics	Yes, with SQL (but Enlace hardly uses them)	No (analytics would have to go elsewhere)
Transactions and strong consistency	Strong by default	Vary; many offer eventual consistency
Operation / familiarity	Very well known, mature tools	Depends on the product; very simple if pure KV

Reading the table: for Enlace's access pattern, both options work, and the choice is played on secondary factors, not the main pattern. The key-value wins on horizontal scale "out of the box" and on raw speed of the lookup by key. The relational wins in that you already know the tool, in that uniqueness and transactions come without effort, and in that if someday Enlace does need a rich query (an admin panel, for example) you have it there. And in both cases the short_code uniqueness —which the generation strategy will need (lessons 5 to 8)— comes free: in SQL as PRIMARY KEY, in KV because a key, by definition, is unique.

Worked example: the same operation in both worlds

Nothing clarifies the choice as much as seeing resolve written in both stores. It's the operation of 99% of the traffic, so if it looks simple in both, it confirms the access pattern is genuinely "dictionary-like".

In a relational store, resolve is a query by the primary key:

-- resolve(short_code) in SQL: lookup by primary key, one row.
SELECT long_url, expires_at
FROM links
WHERE short_code = 'aX9kR2q';

In a key-value store, resolve is, literally, requesting a key:

# resolve(short_code) in a KV (Redis style): GET by key.
GET link:aX9kR2q
# -> "https://example.com/article/very/long?utm=..."

To see that the two are the same operation —"key → value"—, let's simulate it in Python with a dictionary, which is the common essence of both. A Python dictionary is an in-memory key-value store; writing Enlace on top of it leaves the access pattern bare:

# The heart of Enlace is a dictionary: short_code -> record.
db = {}  # simulates the store (KV or table, doesn't matter for the pattern)


def shorten(long_url, short_code):
    """Insertion by key."""
    db[short_code] = {"long_url": long_url, "expires_at": None, "clicks": 0}


def resolve(short_code):
    """Exact lookup by key: the operation of 99% of the traffic."""
    record = db.get(short_code)
    return record["long_url"] if record else None


shorten("https://example.com/article/very/long", "aX9kR2q")
print("resolve('aX9kR2q') =", resolve("aX9kR2q"))
print("resolve('nope')    =", resolve("nope"))

What to expect. Running this with Python 3.14.0 gives:

resolve('aX9kR2q') = https://example.com/article/very/long
resolve('nope')    = None

That db.get(short_code) is all of Enlace's read logic. There's no join to simulate, no compound WHERE, no ORDER BY. When the central operation of your system is written with a dict.get, you have proof that the access pattern is "dictionary-like", and that's what makes the key-value such a natural fit. The point of the simulation isn't that Enlace is a Python dict (that doesn't persist or scale), but that its access shape is that of a dictionary, and that's why the SQL/NoSQL debate is decided on secondary factors, not on the access.

The decision for Enlace

With everything on the table, here's a defensible recommendation —and, more important, the reasoning that holds it up, which is what you have to be able to reproduce:

At today's scale (4,000 reads/sec, 6 TB at 5 years), Enlace runs comfortably on a relational database like PostgreSQL, with short_code as the primary key and a cache layer (module 4) absorbing the bulk of the reads. Reasons: the short_code uniqueness comes free with the primary key, you already know the tool, transactions cover your collision-free code generation, and a single instance (plus read replicas, module 5) handles this scale. There's no need for the operational complexity of a distributed store for a problem a relational database resolves with room to spare.

If Enlace grew a couple of orders of magnitude —hundreds of thousands of reads per second, tens of TB—, a distributed key-value store (DynamoDB, Cassandra) becomes the natural option, because it splits by key "out of the box" and avoids the manual work of sharding. In that regime, Enlace's "dictionary-like" access pattern is exactly what these stores do best, and their eventual consistency —which would be a problem for a bank— is perfectly acceptable for redirecting links (a just-created link that takes a second to propagate to all the replicas doesn't break anything; that tradeoff is seen in depth in module 7).

Notice the shape of the answer, because it's the shape of every good system-design decision: it's not "X is better", it's "X for this regime for these reasons, and Y when this number changes". That's the opposite of dogma. Dogma says "NoSQL scales more"; the analysis says "at this scale either of the two works, I choose the relational for simplicity and familiarity, and I know exactly which number (the reads/sec or the TB) would make me switch to a distributed KV". That second sentence is the one you want to be able to say.

Common mistakes

Choosing the store by fashion, not by access pattern. What happens: someone chooses a distributed key-value store for Enlace "because it scales" when a single PostgreSQL would have been enough, and carries the operational complexity (eventual consistency, less familiar tools, more pieces that can fail) without needing it. Or the reverse, they cling to relational when the scale already calls for splitting by key. Why it happens: the decision is made by the technology's reputation, not by the problem. How to detect it: if you can't name the access pattern and the scale number that justify your choice, you chose it by fashion. How to fix it: write the access pattern first (like this lesson's table), put the scale number next to it, and then choose. The technology is the conclusion, not the premise.

Believing that "NoSQL" is a single thing. What happens: someone treats "NoSQL" as one type of database, when it's an umbrella for very different families —key-value (Redis, DynamoDB), document (MongoDB), wide-column (Cassandra), graph (Neo4j)—, each good for different access patterns. They choose a generic "a NoSQL" and end up with a document database for a problem that was pure key-value. Why it happens: the term groups things that don't resemble each other. How to detect it: if you say "let's use NoSQL" without saying which family, you haven't decided anything. How to fix it: name the family. For Enlace, the "lookup by key" pattern points to key-value specifically, not document or graph. The family is chosen, again, by the access pattern.

Forgetting that the cache changes the equation. What happens: someone sizes the store to handle the 4,000 reads/sec by itself, and concludes it needs a huge distributed architecture, when in reality the cache (module 4) is going to absorb ~90% of those reads and the store will see a fraction. Why it happens: they forget that Enlace is 100:1 read and that the reads are very repetitive (a few hot links concentrate the traffic), which is the ideal scenario for a cache. How to detect it: if your store design doesn't mention what load reaches it after the cache, you're oversizing. How to fix it: remember that the store decision and the cache decision go together; the store only sees the reads the cache doesn't catch. That's exactly what module 4 opens, and it's why "a single PostgreSQL" reaches further than it seems.

Exercises

Exercise 1 — Name the pattern, then choose. For each of these three systems, write its dominant access pattern in one sentence and say whether it pushes toward relational, toward key-value, or toward another NoSQL family, with a reason. (a) Enlace (shortener). (b) A shopping cart that stores "for this user, which products and quantities" and is read/written per user. (c) An admin panel that asks "give me the 100 links with the most clicks created this month, by country".

See solution

(a) Enlace — pattern: "exact lookup by short_code → long_url", one key, no joins. Pushes toward key-value (or simple relational with a unique index), because it's a pure dictionary.
(b) Cart — pattern: "give me/store the cart of this user_id", one key (the user) that leads to an object (the list of items). It's also "dictionary-like", so key-value fits (the key is user_id); a document database also works if the cart is a rich nested document. What it does not ask for is joins or ad-hoc queries.
(c) Admin panel — pattern: "filter by month and country, sort by clicks, bring the top 100" — a rich query with filters, ordering, and aggregation. That pushes toward relational (SQL) or an analytical store, because it needs exactly what a key-value doesn't do. Design note: this pattern is not on Enlace's hot path; if it existed, it would live in a separate system (analytics), not competing with resolve.

The lesson: the same method —name the pattern first— gives different answers for different problems, and that's why it's a method and not a dogma.

Exercise 2 — What number would change your mind? We recommend PostgreSQL for Enlace at today's scale. Write two concrete conditions (with numbers) that, if met, would make you migrate to a distributed key-value store, and explain why each one.

See solution

Two defensible conditions (there are more):

The reads exceed what one instance + read replicas absorbs, even with a cache. For example, if Enlace went from 4,000 to ~200,000 reads/sec after the cache, splitting the load by key among many nodes (which a distributed KV does out of the box) would be simpler than orchestrating dozens of PostgreSQL replicas. The key number is "reads/sec that reach the store after the cache".
The data exceeds what fits/operates comfortably on a single machine. If the retention or growth took the dataset to, say, tens or hundreds of TB, sharding becomes inevitable, and a distributed KV does it natively while in PostgreSQL it's manual work (partitioning, routing). The key number is the total dataset size against a node's capacity.

Why it matters to be able to say this: an architecture decision isn't complete if it doesn't include what would reverse it. "I use PostgreSQL, and I'd migrate to a distributed KV if the post-cache reads exceed ~X or the dataset exceeds ~Y" is an adult decision; "I use PostgreSQL because I like it" isn't. Module 5 (sharding) and module 7 (consistency) develop exactly the regime these conditions lead to.

Exercise 3 — Uniqueness, in both worlds. The code generation strategy (lessons 5 to 8) needs to guarantee that two links never share a short_code. Explain how that uniqueness guarantee is obtained (a) in a relational store and (b) in a key-value store, and why in both cases it comes "almost for free".

See solution

(a) Relational: short_code is the primary key (or has a UNIQUE constraint). The database rejects any INSERT that repeats an existing short_code, with a uniqueness-violation error. The application doesn't have to check anything beforehand: it tries to insert and, if it collides, the database tells it. It comes free because uniqueness is a property of the schema, not code you write.
(b) Key-value: the key is unique by the nature of the store —there can only be one value per key—. The guarantee is obtained with a conditional write of the type "write only if the key doesn't exist" (SET key value NX in Redis, or PutItem with attribute_not_exists in DynamoDB). If the key already exists, the write doesn't happen and the store indicates it. It comes free because "one key, one value" is the very definition of a key-value.

In both cases, the "random + verification" strategy (lesson 5) rests on this guarantee: it generates a code, tries to write it conditioned on it not existing, and if the store rejects the write, it retries. The store does the collision check for you, atomically, instead of you doing it with a SELECT followed by an INSERT (which would have a race condition). That uniqueness is cheap in both worlds is one of the reasons the store choice doesn't depend on the generation strategy —they talk, but neither forces the other.

Summary and next step

You answered "SQL or NoSQL?" for Enlace with the method that serves any system: name the access pattern, and let it choose the store. Enlace's pattern is "exact lookup by short_code", with a 100:1 read ratio and no joins or ad-hoc queries —that is, a dictionary—, and you verified it by writing resolve as a dict.get in Python. You saw that at today's scale both options work: a single PostgreSQL with short_code as the primary key and a cache in front is comfortably enough, and the code uniqueness comes free (primary key in SQL, unique key by nature in KV). And you saw that the adult answer isn't "X is better" but "X for this regime for these reasons, and Y when such a number changes" —with the concrete numbers (post-cache reads, dataset size) that would trigger the migration to a distributed key-value.

Before moving on you should be able to: write Enlace's access pattern in a table and point out that almost everything is "by key"; explain why that makes the key-value a natural fit without being mandatory; defend PostgreSQL for today's scale and name the number that would make you change; and explain how the short_code uniqueness is guaranteed in both worlds.

With the what (the record, lesson 2) and the where (the store, this lesson) resolved, the module enters its algorithmic part: how the short_code is manufactured. Lesson 4 starts with the most intuitive strategy —deriving the code from a hash of the URL— and measures, by running a simulation, its Achilles' heel: collisions. You'll see a collision rate of 29.80% come out in the terminal and understand, with the number in front of you, why the pure hash isn't enough.

Resources

Designing Data-Intensive Applications, Martin Kleppmann — Chapter 3: "Storage and Retrieval" — the canonical treatment of why different engines fit different access patterns, including the hash-type indexes that are the essence of a key-value store. It's the theoretical basis of this whole lesson.
Redis documentation — "Data types" and the GET/SET command — the living example of a pure key-value store. Seeing how very few operations it offers (essentially by key) is the best way to feel why it fits Enlace and what renunciation (rich queries) it implies.
PostgreSQL documentation — "Constraints" (UNIQUE and PRIMARY KEY) — how the relational store gives you the short_code uniqueness at the schema level, the guarantee the code-generation strategy of the coming lessons rests on.
Amazon DynamoDB — "Core components" (tables, items, keys) — a real distributed key-value, with its conditional write (attribute_not_exists) that makes the collision check atomic. Useful for seeing the regime Enlace would migrate to if it grew two orders of magnitude.

← Previous2. The `Link` record Next →4. Generating the code with a hash: collisions measured