0% completed
Step 6: Database Schema
On This Page
- UserLikes: one row per user per item
- ContentStats: the totals per item
Three decisions in this schema
"How many likes does this video have?" is asked about 250,000 times a second, and the schema must answer it without counting anything. "Has this user already liked this?" is asked on every click, and the schema must answer that with one lookup. Two NoSQL tables, one for each question, carry the whole design, as the diagram below shows.
1. UserLikes: one row per user per item
UserLikes holds each user's latest reaction to each video or comment. Its key is user_id and content_id together, a composite key, so each pair of user and item has at most one row. A second reaction from the same user overwrites that row, so the table can never hold two likes from one person on one video.
| Field | Data Type | Description |
|---|---|---|
| user_id | String | The user who reacted. Part of the composite primary key. |
| content_id | String | The video or comment reacted to. Part of the composite primary key. |
| action_type | String (enum) | The user's latest reaction: "like" or "dislike". |
| timestamp | DateTime | When the user last changed this reaction. |
| version | Integer | Goes up by one on every change. A write succeeds only if the version still matches the one the caller read, so two simultaneous toggles cannot both apply (Step 8). |
2. ContentStats: the totals per item
ContentStats serves the read path. It holds the like and dislike totals for each video or comment, keyed by content_id alone, so any item's totals come back from a single key lookup. The event stream described in Step 7 updates these rows.
| Field | Data Type | Description |
|---|---|---|
| content_id | String | The video or comment. Primary key, one row per item. |
| total_likes | Integer (64-bit) | Number of users whose current reaction is a like. |
| total_dislikes | Integer (64-bit) | Number of users whose current reaction is a dislike. |
| updated_at | DateTime | When the aggregation last wrote this row. Step 10's recount job uses it to find items the pipeline has stopped updating. |
The totals are 64-bit because YouTube's own view counter was once a signed 32-bit integer, with a ceiling of about 2.1 billion, and "Gangnam Style" came close enough that YouTube had to widen it.
Three decisions in this schema
Undo deletes the row. action_type holds only like or dislike, so removing a reaction deletes the row. In Cassandra, a delete writes a tombstone, a marker that stays on disk until compaction (the background merge of data files) removes it. A partition full of tombstones is slow to read, so measure undo traffic from day one.
Totals are stored, never computed on read. Counting the UserLikes rows for a popular video would scan millions of rows across many partitions on every page view. ContentStats costs one extra write per reaction, and in return a read costs the same whether the video has ten likes or ten million.
UserLikes is the source of truth, and ContentStats is derived from it. Every total can be recomputed from the per-user rows, so a wrong total is a temporary defect rather than permanent damage. This is why serving an eventually consistent count is safe, and Step 10 relies on it to repair drift.
Step 3 puts UserLikes at hundreds of billions of rows within five years, and because partitions are keyed by user_id, a very active user's partition keeps growing. The usual remedies are a time bucket in the partition key or moving old reactions to cheaper storage. Neither affects the count, which comes from ContentStats.
Reading Progress
0%
On This Page
- UserLikes: one row per user per item
- ContentStats: the totals per item
Three decisions in this schema