πŸ’¬
βœ‰οΈ
πŸ“Έ
▢️

Loading…

Design a URL Shortener (TinyURL): Interactive System Design

URL ShortenerGuided Mode

Step 5 β€” Database Optimization

Even with caching, the Database still receives writes (new URLs) and cache-miss reads. Optimize the DB to handle these efficiently.

Request Flow

1

Every cache miss β†’ DB lookup

When the cache misses β€” first visit to a link β€” the API queries the Database by short code.

2

Without indexing: full table scan

SELECT * FROM urls WHERE code='abc123' with no index = scan every row. At 10M URLs: ~500ms per query.

-- No index: full scan
EXPLAIN SELECT long_url FROM urls
WHERE code = 'abc123';
-- β†’ Seq Scan, cost=0..250000 rows=10000000
3

Add a unique index on code column

B-tree index on 'code' β†’ O(log n) lookup. Same query at 10M rows: ~2ms. This is the single most impactful DB change.

-- Add index (run once, no downtime on Postgres < v12)
CREATE UNIQUE INDEX CONCURRENTLY idx_urls_code
ON urls(code);

-- Now: Index Scan, cost=0.56..8.58 rows=1
4

Separate read vs write paths

Add a read replica of the Database. Cache misses route to the replica. Writes (POST /shorten) go to the primary.

Key Insight

One index converts a 500ms full-table scan into a 2ms lookup. Database indexing is the highest ROI optimization in any read-heavy system.

Step 5 of 6