Chapter 3: DATABASE DESIGN - System Design Course: Complete Interview Preparation | PlayBook Library

SLIDE 20: Database Design Overview

Step 3: Database Schema Design

What We’ll Design:

Core Tables:

Users - User profiles and authentication
Posts - Post metadata (not media files)
Follows - User-to-user relationships (graph)
Likes - Post likes (junction table)
Comments - Post comments

SLIDE 20B: Database Design Overview (continued)

Design Considerations:

Aspect	Decision	Reasoning
Database Type	SQL (PostgreSQL)	Relational data, ACID needed, complex joins
Normalization	3NF (Third Normal Form)	Reduce redundancy, maintain integrity
Sharding Strategy	Horizontal by user_id	Distribute load, co-locate user data
Indexing	Composite indexes on query patterns	Optimize read performance

SLIDE 20C: Database Design Overview (continued)

Interview Expectations:

Show actual table schemas with column names and data types
Explain foreign key relationships
Justify indexing decisions
Discuss sharding strategy

Common Mistakes:

Hand-waving “we’ll use a database” without details
Storing media files in database (use object storage instead)
Not considering indexes (queries will be slow)
Arrays in SQL columns (doesn’t scale, hard to query)

SLIDE 21: Users Table Schema

Users Table

CODE

CREATE TABLE users (
    user_id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    username        VARCHAR(30) UNIQUE NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(100),
    bio             TEXT,
    profile_pic_url VARCHAR(500),
    follower_count  INTEGER DEFAULT 0,
    following_count INTEGER DEFAULT 0,
    post_count      INTEGER DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at DESC);

SLIDE 21B: Users Table - Column Explanations (Part 1)

Column Explanations:

Column	Data Type	Purpose	Notes
user_id	BIGINT	Primary key	Supports billions of users (2^63)
username	VARCHAR(30)	Unique username	Instagram’s actual limit is 30 chars
email	VARCHAR(255)	Email address	Standard email length
password_hash	VARCHAR(255)	Hashed password	Never store plaintext passwords
full_name	VARCHAR(100)	Display name	Can differ from username

SLIDE 21C: Users Table - Column Explanations (Part 2)

Column	Data Type	Purpose	Notes
bio	TEXT	User bio	Variable length, up to 150 chars in Instagram
profile_pic_url	VARCHAR(500)	URL to S3	Stored in object storage, not DB
follower_count	INTEGER	Denormalized count	Updated via trigger or async job
following_count	INTEGER	Denormalized count	Avoid COUNT(*) on large tables
post_count	INTEGER	Denormalized count	Performance optimization

SLIDE 21D: Users Table - Indexing Decisions

Indexing Decisions:

Index on username:

Used for login and user search
UNIQUE constraint enforces username uniqueness

Index on email:

Used for login and account recovery
UNIQUE constraint enforces email uniqueness

Index on created_at:

Useful for “newest users” queries (if needed)
DESC for reverse chronological order

SLIDE 21E: Users Table - Denormalization Rationale

Why Denormalize Counts:

Running COUNT(*) on millions of rows is expensive
Counts updated asynchronously (eventual consistency acceptable)
Trade-off: Storage space for query performance

SLIDE 22: Posts Table Schema

Posts Table

CODE

CREATE TABLE posts (
    post_id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id         BIGINT NOT NULL,
    image_url       VARCHAR(500) NOT NULL,
    thumbnail_small_url  VARCHAR(500),
    thumbnail_medium_url VARCHAR(500),
    thumbnail_large_url  VARCHAR(500),
    caption         TEXT,
    location        VARCHAR(100),
    like_count      INTEGER DEFAULT 0,
    comment_count   INTEGER DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,

    INDEX idx_user_created (user_id, created_at DESC),
    INDEX idx_created_at (created_at DESC)
);

SLIDE 22B: Posts Table - Column Explanations

Column Explanations:

Column	Data Type	Purpose	Notes
post_id	BIGINT	Primary key	Unique post identifier
user_id	BIGINT	Foreign key to users	Who created this post
image_url	VARCHAR(500)	S3 URL to original	Full-resolution image
thumbnail_*_url	VARCHAR(500)	S3 URLs to thumbnails	Pre-generated sizes
caption	TEXT	Post caption	Up to 2,200 characters in Instagram
location	VARCHAR(100)	Geographic location	Optional, user-provided
like_count	INTEGER	Denormalized	Avoid COUNT(*) on Likes table
comment_count	INTEGER	Denormalized	Avoid COUNT(*) on Comments table
created_at	TIMESTAMP	Post timestamp	Critical for feed sorting

SLIDE 22C: Posts Table - Critical Index

Critical Index: Composite Index on (user_id, created_at DESC)

Why This Index:

CODE

-- Common query: Get user's posts, newest first
SELECT * FROM posts
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;

How It Works:

Index first filters by user_id (narrow down to single user’s posts)
Index then sorts by created_at DESC (already sorted in index)
Result: Very fast query (seeks directly to correct rows)

SLIDE 22D: Posts Table - Index Importance

Without This Index:

Database scans entire Posts table
Filters for user_id
Sorts results (expensive)
At billions of posts, this is unusably slow

Foreign Key Cascade:

ON DELETE CASCADE: If user deleted, their posts also deleted
Maintains referential integrity
Prevents orphaned posts

SLIDE 23: Follows Table Schema

Follows Table (Graph Relationship)

CODE

CREATE TABLE follows (
    follower_id  BIGINT NOT NULL,
    followee_id  BIGINT NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (follower_id, followee_id),

    FOREIGN KEY (follower_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (followee_id) REFERENCES users(user_id)
        ON DELETE CASCADE,

    INDEX idx_follower (follower_id),
    INDEX idx_followee (followee_id),
    INDEX idx_created_at (created_at DESC)
);

SLIDE 23B: Follows Table - Purpose and Structure

Table Purpose:

Represents “who follows whom” relationship
Junction table for many-to-many relationship
User A can follow User B, and vice versa (not necessarily bidirectional)

Column Explanations:

Column	Purpose	Example
follower_id	User who is following	User 12345 follows…
followee_id	User being followed	…User 67890
created_at	When follow happened	2025-01-15 10:30:00

SLIDE 23C: Follows Table - Primary Key and Indexes

Composite Primary Key:

CODE

PRIMARY KEY (follower_id, followee_id)

Benefits:

Prevents duplicate follows (can’t follow same person twice)
Uniquely identifies each follow relationship
Automatically creates index on (follower_id, followee_id)

Indexing Strategy:

Index on follower_id:

CODE

-- Query: "Who does User 12345 follow?"
SELECT followee_id FROM follows
WHERE follower_id = 12345;

Index on followee_id:

CODE

-- Query: "Who follows User 67890?"
SELECT follower_id FROM follows
WHERE followee_id = 67890;

SLIDE 23D: Follows Table - Why Not Array

Why Not Store as Array in Users Table:

Wrong Approach:

CODE

-- BAD: followers as JSON array in users table
users (
    user_id BIGINT,
    followers JSON -- [12, 34, 56, 78, ...]
)

Problems:

Cannot index array elements efficiently
Query “find all posts from users I follow” becomes extremely slow
Array size unbounded (celebrities have millions of followers)
Cannot easily query “mutual followers”

Correct Approach:
Separate junction table with proper indexes enables fast bidirectional queries.

SLIDE 24: Likes Table Schema

Likes Table

CODE

CREATE TABLE likes (
    user_id     BIGINT NOT NULL,
    post_id     BIGINT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (user_id, post_id),

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,

    INDEX idx_post_id (post_id),
    INDEX idx_created_at (created_at DESC)
);

Likes Table Design:

Aspect	Decision	Reasoning
Primary Key	(user_id, post_id)	Prevents double-liking same post
Indexes	On post_id	Fast query: “who liked this post?”
Cascade Delete	Yes	If post deleted, likes also deleted

SLIDE 24B: Comments Table Schema

Comments Table

CODE

CREATE TABLE comments (
    comment_id  BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT NOT NULL,
    post_id     BIGINT NOT NULL,
    text        TEXT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,

    INDEX idx_post_created (post_id, created_at DESC)
);

Comments Table Design:

Aspect	Decision	Reasoning
Primary Key	comment_id (auto-increment)	User can comment multiple times on same post
Composite Index	(post_id, created_at)	Fast query: “get comments for post, newest first”
Text Storage	TEXT type	Comments up to 2,200 characters

SLIDE 24C: Likes and Comments - Query Patterns

Common Query Patterns:

Get all comments for a post (newest first):

CODE

SELECT * FROM comments
WHERE post_id = 789456
ORDER BY created_at DESC
LIMIT 50;

Served by idx_post_created index.

Check if user has liked a post:

CODE

SELECT 1 FROM likes
WHERE user_id = 12345 AND post_id = 789456;

Served by primary key lookup (instant).

Why Likes Uses Composite PK, Comments Uses Auto-Increment:

Likes: Each user can like a post exactly once (enforced by PK)
Comments: Users can comment multiple times (need unique ID per comment)

SLIDE 25: Entity-Relationship Diagram

Complete Database Schema - ER Diagram

CODE

#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .error-icon{fill:#552222;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .error-text{fill:#552222;stroke:#552222;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-thickness-normal{stroke-width:1px;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .marker{fill:#666;stroke:#666;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .marker.cross{stroke:#666;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 p{margin:0;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .entityBox{fill:#eee;stroke:#999;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .relationshipLabelBox{fill:hsl(-160, 0%, 93.3333333333%);opacity:0.7;background-color:hsl(-160, 0%, 93.3333333333%);}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .relationshipLabelBox rect{opacity:0.5;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .labelBkg{background-color:rgba(237.9999999999, 237.9999999999, 237.9999999999, 0.5);}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edgeLabel{background-color:white;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edgeLabel .label rect{fill:white;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edgeLabel .label text{fill:#000000;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edgeLabel .label{fill:#999;font-size:14px;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .edge-pattern-dashed{stroke-dasharray:8,8;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .node rect,#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .node circle,#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .node ellipse,#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .node polygon{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .relationshipLine{stroke:#666;stroke-width:1px;fill:none;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .marker{fill:none!important;stroke:#666!important;stroke-width:1;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look=neo].labelBkg{background-color:rgba(237.9999999999, 237.9999999999, 237.9999999999, 0.5);}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 .node .neo-node{stroke:#999;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].node rect,#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].cluster rect,#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].node polygon{stroke:url(#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].node path{stroke:url(#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633-gradient);stroke-width:1px;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].node circle{stroke:url(#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].icon-shape .icon{fill:url(#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-8688ce25-6156-44d8-9a0f-98f98f9f2633 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}USERSbigintuser_idPKvarcharusernameUKvarcharemailUKvarcharpassword_hashvarcharfull_nametextbiovarcharprofile_pic_urlintfollower_countintfollowing_countintpost_counttimestampcreated_attimestampupdated_atPOSTSbigintpost_idPKbigintuser_idFKvarcharimage_urlvarcharthumbnail_small_urlvarcharthumbnail_medium_urlvarcharthumbnail_large_urltextcaptionvarcharlocationintlike_countintcomment_counttimestampcreated_atFOLLOWSbigintfollower_idPK_FKbigintfollowee_idPK_FKtimestampcreated_atLIKESbigintuser_idPK_FKbigintpost_idPK_FKtimestampcreated_atCOMMENTSbigintcomment_idPKbigintuser_idFKbigintpost_idFKtexttexttimestampcreated_atcreatesfollows (follower)followed by (followee)likeswritesreceiveshas

SLIDE 25B: ER Diagram - Relationships

Relationship Cardinalities:

Relationship	Type	Description
User → Posts	One-to-Many	One user creates many posts
User → Follows (as follower)	One-to-Many	One user can follow many users
User → Follows (as followee)	One-to-Many	One user can be followed by many users
User → Likes	One-to-Many	One user can like many posts
User → Comments	One-to-Many	One user can write many comments
Post → Likes	One-to-Many	One post can have many likes
Post → Comments	One-to-Many	One post can have many comments

SLIDE 25C: ER Diagram - Constraints and Normalization

Foreign Key Constraints:

All foreign keys use ON DELETE CASCADE:

If user deleted → their posts, follows, likes, comments also deleted
If post deleted → likes and comments on that post also deleted
Maintains referential integrity automatically

Design Normalization:

This schema is in Third Normal Form (3NF):

No repeating groups (all atomic values)
All non-key attributes fully dependent on primary key
No transitive dependencies

Denormalization for Performance:

follower_count, following_count, post_count in Users table
like_count, comment_count in Posts table
Trade-off: Consistency for performance (updated asynchronously)

SLIDE 26: SQL vs NoSQL Decision - Comparison

Database Technology Choice: SQL (PostgreSQL) vs NoSQL

Why We Choose SQL for Instagram:

Criteria	SQL (PostgreSQL)	NoSQL (Cassandra/MongoDB)
Data Structure	Well-defined schema, relational	Flexible schema, document/wide-column
Relationships	Strong support (foreign keys, joins)	Limited (denormalization required)
ACID Transactions	Full ACID support	Limited or eventual consistency
Query Flexibility	Complex joins, aggregations	Primarily key-value lookups
Consistency Model	Strong consistency available	Typically eventual consistency
Maturity & Tooling	Decades of tools, ORMs, expertise	Newer, less tooling

SLIDE 26B: SQL vs NoSQL - Instagram’s Characteristics

Instagram’s Data Characteristics:

Relational Nature:

Users follow users (graph relationship)
Posts belong to users (one-to-many)
Likes and comments reference both users and posts
Complex joins needed for feed generation

Need for Transactions:

Liking a post must be atomic (can’t double-like)
Following a user must update both follower and followee records
ACID guarantees prevent data inconsistencies

SLIDE 26C: SQL vs NoSQL - Query Example

Query Patterns:

CODE

-- Example: Get feed (requires complex join)
SELECT posts.* FROM posts
JOIN follows ON posts.user_id = follows.followee_id
WHERE follows.follower_id = 12345
ORDER BY posts.created_at DESC
LIMIT 20;

This is natural in SQL, complex in NoSQL.

SLIDE 26D: SQL vs NoSQL - When NoSQL Makes Sense

When NoSQL Would Make Sense:

Use Case	Better Choice
Logging, metrics, timeseries	Cassandra, InfluxDB
Unstructured data (JSON documents)	MongoDB
Key-value cache	Redis, Memcached
Search functionality	Elasticsearch

Instagram’s Actual Technology:

Originally: PostgreSQL
Later migrated to: Cassandra for some workloads, PostgreSQL for others
Lesson: Start with SQL, migrate to NoSQL only when specific use cases demand it

SLIDE 26E: SQL vs NoSQL - Interview Answer

Interview Answer:
“I’m choosing PostgreSQL because Instagram’s data is inherently relational with complex relationships between users, posts, follows, and likes. SQL provides strong consistency guarantees for critical operations like liking a post, and supports complex joins needed for feed generation. While NoSQL can scale horizontally more easily, modern PostgreSQL with sharding can handle Instagram’s scale, and the relational model matches the problem domain better.”

SLIDE 27: Database Sharding - The Problem

Horizontal Sharding: Scaling the Database

The Problem:

Single database instance limitations:

Storage capacity: Limited to single machine disk size
CPU capacity: Limited to single machine cores
Network capacity: Single NIC bandwidth limit
At Instagram’s scale (billions of rows), single instance insufficient

Solution: Horizontal Sharding (Data Partitioning)

SLIDE 27B: Database Sharding - Strategy

Sharding Key: user_id

Why user_id as Shard Key:

Most queries are user-centric (“my feed,” “my posts,” “users I follow”)
Co-locates related data on same shard (user + their posts)
Evenly distributes load (user IDs roughly uniformly distributed)

Sharding Algorithm: Modulo (Hash-Based)

Structure & Concept Flow

shard_id = user_id % NUM_SHARDS

Example with 1,000 shards:

- User 12345 → Shard 345 (12345 % 1000)

- User 67890 → Shard 890 (67890 % 1000)

- User 999999 → Shard 999 (999999 % 1000)

SLIDE 27C: Database Sharding - Visual

CODE

#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;fill:#000000;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .error-icon{fill:#552222;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .error-text{fill:#552222;stroke:#552222;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-thickness-normal{stroke-width:1px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .marker{fill:#666;stroke:#666;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .marker.cross{stroke:#666;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 svg{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:16px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 p{margin:0;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .label{font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";color:#000000;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .cluster-label text{fill:#333;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .cluster-label span{color:#333;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .cluster-label span p{background-color:transparent;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .label text,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 span{fill:#000000;color:#000000;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node rect,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node circle,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node ellipse,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node polygon,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node path{fill:#eee;stroke:#999;stroke-width:1px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .rough-node .label text,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node .label text,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .image-shape .label,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .icon-shape .label{text-anchor:middle;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .rough-node .label,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node .label,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .image-shape .label,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .icon-shape .label{text-align:center;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node.clickable{cursor:pointer;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .root .anchor path{fill:#666!important;stroke-width:0;stroke:#666;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .arrowheadPath{fill:#333333;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edgePath .path{stroke:#666;stroke-width:1px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .flowchart-link{stroke:#666;fill:none;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edgeLabel{background-color:white;text-align:center;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edgeLabel p{background-color:white;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .edgeLabel rect{opacity:0.5;background-color:white;fill:white;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .labelBkg{background-color:rgba(255, 255, 255, 0.5);}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .cluster rect{fill:hsl(0, 0%, 98.9215686275%);stroke:#707070;stroke-width:1px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .cluster text{fill:#333;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .cluster span{color:#333;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:ui-sans-serif,-apple-system,BlinkMacSystemFont,"Segoe UI Variable Display","Segoe UI",Helvetica,"Apple Color Emoji","Noto Sans Arabic","Noto Sans Hebrew",Arial,sans-serif,"Segoe UI Emoji","Segoe UI Symbol";font-size:12px;background:hsl(-160, 0%, 93.3333333333%);border:1px solid #707070;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#000000;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 rect.text{fill:none;stroke-width:0;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .icon-shape,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .image-shape{background-color:white;text-align:center;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .icon-shape p,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .image-shape p{background-color:white;padding:2px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .icon-shape .label rect,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .image-shape .label rect{opacity:0.5;background-color:white;fill:white;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 .node .neo-node{stroke:#999;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].node rect,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].cluster rect,#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].node polygon{stroke:url(#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].swimlane.cluster rect{filter:none;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].node path{stroke:url(#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329-gradient);stroke-width:1px;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].node .outer-path{filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].node .neo-line path{stroke:#999;filter:none;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].node circle{stroke:url(#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].node circle .state-start{fill:#000000;}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].icon-shape .icon{fill:url(#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 [data-look="neo"].icon-shape .icon-neo path{stroke:url(#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329-gradient);filter:drop-shadow( 1px 2px 2px rgba(185,185,185,1));}#mermaid-346ee5cc-0dc4-4bde-bc2d-7ef347fa1329 :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}Query: user_id = 12345Shard RouterCalculates: 12345 % 1000 = 345Shard 0user_id % 1000 = 0~2M usersShard 345user_id % 1000 = 345~2M usersShard 999user_id % 1000 = 999~2M usersRoute toNot accessedNot accessed

SLIDE 27D: Database Sharding - Data Distribution

Data Distribution Across Shards:

Users Table:

Sharded by user_id directly
User 12345 lives on Shard 345

Posts Table:

Sharded by user_id (not post_id)
Post created by User 12345 lives on Shard 345
Co-location: User and their posts on same shard

Follows Table:

Sharded by follower_id
“User 12345 follows User 67890” lives on Shard 345
Allows fast query: “Who does User 12345 follow?”

Likes and Comments:

Sharded by post’s user_id
Like on post by User 12345 lives on Shard 345
Co-located with post metadata

SLIDE 27E: Database Sharding - Configuration

Shard Configuration:

Metric	Value
Number of Shards	1,000
Users per Shard	~2 million (2B users / 1000 shards)
Shard Growth Rate	+100K users/shard/year
Re-sharding Trigger	When shard exceeds 5M users

SLIDE 27F: Database Sharding - Cross-Shard Queries

Cross-Shard Queries:

Problem:

CODE

-- This query spans multiple shards
SELECT * FROM follows
WHERE followee_id = 67890;
-- "Who follows User 67890?"
-- Must query ALL shards (followers could be on any shard)

Solution:

Avoid cross-shard queries when possible
Denormalize data if needed (trade storage for query performance)
Use caching heavily (Redis for cross-shard aggregations)

SLIDE 27G: Database Sharding - Re-Sharding

Re-Sharding Strategy:

When to Re-Shard:

Individual shard exceeds capacity (storage or QPS)
Typically every 2-3 years as user base grows

How to Re-Shard:

Double number of shards (1,000 → 2,000)
Migrate data: user_id % 2000 might differ from user_id % 1000
Use consistent hashing for smoother migration (advanced topic)