URL Shortener
Design the schema for a URL (Uniform Resource Locator) shortener (bit.ly clone) supporting custom short codes, per-link click analytics, optional expiry, and custom branded domains. Traffic is heavily skewed: 99% reads, 1% writes, with a long tail on click events.
Clarifying questions
- How long must short codes be? 6 chars gives ~56B combinations — is that the target lifetime volume?
- Do we need real-time analytics or batch (hourly aggregates fine)?
- What level of click detail — just counts, or per-click country / referrer / device?
- Can custom codes collide with auto-generated codes, and which wins?
- Do we honor robots and bot traffic, or are bot clicks first-class events?
Core requirements
- Resolve a short code to its long URL in under 10ms p99.
- Generate non-guessable short codes that don't collide.
- Record every click with enough context to power per-link dashboards.
- Support per-link expiry that returns 410 after the cutoff.
- Allow custom domains (acme.co/promo) mapped to the same code space.
- Aggregate clicks into daily summaries for the dashboard without scanning raw events.
Canonical schema (PostgreSQL)
CREATE TABLE domains (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
host TEXT UNIQUE NOT NULL,
owner_id UUID,
verified_at TIMESTAMPTZ
);
CREATE TABLE links (
id BIGSERIAL PRIMARY KEY,
domain_id UUID NOT NULL REFERENCES domains(id),
code TEXT NOT NULL,
target_url TEXT NOT NULL,
owner_id UUID,
expires_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at TIMESTAMPTZ,
UNIQUE (domain_id, code)
);
CREATE TABLE click_events (
id BIGSERIAL,
link_id BIGINT NOT NULL,
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
ip INET,
user_agent TEXT,
referrer TEXT,
country CHAR(2),
device TEXT,
PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);
CREATE TABLE click_daily (
link_id BIGINT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
day DATE NOT NULL,
country CHAR(2),
clicks BIGINT NOT NULL DEFAULT 0,
uniques BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (link_id, day, country)
);Key indexes
CREATE UNIQUE INDEX idx_links_lookup ON links(domain_id, code) WHERE deleted_at IS NULL
Hot read path — resolving (domain, code) to a target URL must be a single index hit on live rows only.
CREATE INDEX idx_links_owner_created ON links(owner_id, created_at DESC) WHERE deleted_at IS NULL
User dashboard 'my links' query paginates by recency.
CREATE INDEX idx_links_expiry ON links(expires_at) WHERE expires_at IS NOT NULL AND deleted_at IS NULL
Expiry sweeper marks links dead; partial index keeps the job bounded.
CREATE INDEX idx_click_daily_link_day ON click_daily(link_id, day DESC)
Dashboard chart 'last 30 days' is keyset-paged on the aggregated table, never the raw events.
Key decisions to defend
- Short codes are generated from a base62 encoding of a sharded counter, not random — guarantees collision-free issuance and lets you skip a SELECT-before-INSERT check.
- click_events is partitioned by occurred_at with weekly partitions — analytics queries are time-bounded, and old partitions DETACH for cold storage or deletion.
- Maintain click_daily aggregates asynchronously (batch or stream) — dashboards never touch the raw event table, which can be huge.
- The resolve path caches links aggressively in Redis with the row as the cache value; the DB is the cold backstop, not the hot path.
- Soft-delete links so analytics URLs in old emails still resolve to a useful 410 page rather than a generic 404.
Likely follow-ups
- · A link goes viral and gets 1M clicks/min — what protects the DB?
- · Add A/B redirects (50/50 split to two URLs) — schema change?
- · GDPR delete request for an IP — how do you scrub the partitioned events?
- · Custom domain owner wants to retire their domain — what happens to existing short links?
Dive deeper
URL shorteners are the canonical 'read-heavy, write-light, analytics-heavy' system. The interesting design moves are: code generation strategy (random vs counter), separation of hot resolve path from cold analytics, and the discipline to maintain aggregates instead of letting dashboards scan the event firehose. A strong candidate names the cache, names the partition strategy, and never proposes COUNT(*) over click_events.