Case: URL shortener

-- Postgres / MySQL — serve confortavelmente até ~1B rows
CREATE TABLE short_urls (
  short_code    VARCHAR(7) PRIMARY KEY,        -- PK compacta, index clustered
  long_url      TEXT NOT NULL,
  user_id       BIGINT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at    TIMESTAMPTZ,                   -- opcional; TTL
  click_count   BIGINT DEFAULT 0,              -- updated async
  last_clicked  TIMESTAMPTZ
);

CREATE INDEX idx_short_urls_user ON short_urls (user_id, created_at DESC);
CREATE INDEX idx_short_urls_expires ON short_urls (expires_at) WHERE expires_at IS NOT NULL;

# Redis cache-aside pattern
def resolve(short_code: str) -> str | None:
    cached = redis.get(f"url:{short_code}")
    if cached:
        return cached
    row = db.query("SELECT long_url FROM short_urls WHERE short_code = %s", short_code)
    if not row:
        return None
    redis.setex(f"url:{short_code}", 86400, row.long_url)  # TTL 1 dia
    return row.long_url

async def redirect_handler(short_code: str, request):
    long_url = await resolve(short_code)
    if not long_url:
        return Response(status=404)

    # Fire-and-forget
    asyncio.create_task(emit_click_event({
        "short_code": short_code,
        "ts": time.time(),
        "ip": request.headers.get("X-Forwarded-For"),
        "ua": request.headers.get("User-Agent"),
        "referer": request.headers.get("Referer"),
    }))
    return RedirectResponse(long_url, status_code=301)


async def emit_click_event(event: dict):
    await kafka_producer.send("url.click", event)
    # Worker offline enriquece (geo via IP), grava em BigQuery/Snowflake