URL Shortener System Design

The requirements

• Shorten: POST / with a long URL returns a short code (e.g. aZ3x9).
• Redirect: GET /<code> 301-redirects to the original URL.
• Codes should be short (~6-7 chars), unique, and hard to guess is a bonus.
• Reads (redirects) hugely outnumber writes - optimize for fast lookups.

The key decision: generating the short code

Base62 of the row id is the usual default: store the row, take its integer id, encode it base62. Unique by construction - no collision check needed.

ALPHABET = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

def base62(n: int) -> str:
    if n == 0:
        return ALPHABET[0]
    s = []
    while n:
        n, r = divmod(n, 62)
        s.append(ALPHABET[r])
    return "".join(reversed(s))

The data model

One table is enough to start:

CREATE TABLE urls (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,  -- the id we base62-encode
    url   TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert the URL, read back the id, return base62(id) as the code. On redirect, decode the code back to the id (or just store the code and index it) and look up the URL.

The redirect flow

@app.get("/<code>")
def redirect_code(code):
    row = db.execute("SELECT url FROM urls WHERE code = ?", (code,)).fetchone()
    if not row:
        abort(404)
    return redirect(row["url"], code=301)   # 301 = permanent, cacheable

Use 301 so browsers and CDNs cache the redirect - that's what makes reads cheap.

How it scales

• Reads dominate -> put a cache (Redis) in front of the code->URL lookup; most redirects never touch the database.
• Distributed ids -> a single auto-increment is a bottleneck across many nodes; use a key-generation service or a sharded/range-allocated id scheme so each node mints ids without coordinating on every write.
• Storage -> the table is tiny per row; the real cost is read throughput, which the cache + CDN absorb.
• Custom aliases & analytics -> add a code column with a uniqueness constraint for vanity URLs; log redirects async for click stats.