Design a URL Shortener (TinyURL): Interactive System Design
URL Shortener
Guided ModeStep 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
Every cache miss β DB lookup
When the cache misses β first visit to a link β the API queries the Database by short code.
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
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
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
