How YouTube Scales Watch History Using Bigtable

TL;DR: YouTube scales watch history for billions of users using Google Bigtable. Instead of running expensive SQL query sorts on the fly, they design a massive, sorted key-value store where the row keys are structured as UserID plus a Reverse Timestamp. This allows YouTube to fetch a user's most recent videos instantly via a high-performance prefix scan.

If you ask me, one of the coolest things about YouTube is clicking on your history page and seeing your recently watched videos load instantly. It feels like a trivial feature. But when I see developers talk about building this at scale—dealing with billions of active users writing and reading at the exact same time—they often overlook how quickly a traditional database will choke. You can't just throw a standard SQL database at this problem unless you want to melt your servers and leave your users staring at a loading spinner.

Why does SQL fail at YouTube-scale watch history?

When I see engineers struggle with scaling history feeds, they usually try to optimize SQL queries that just aren't meant for this volume. Relational databases fail at this scale because sorting billions of rows on the fly using indexes is incredibly CPU-heavy. Under massive concurrent write-and-read loads, keeping index trees balanced while scanning them causes severe database locking.

Let's say we have a traditional SQL table tracking watch events. Every time a user watches a video, we append a row. When they want to see their last ten videos, the database has to search the index, sort those specific records by timestamp in descending order, and return the top ten.

Doing this for billions of users watching videos simultaneously is like trying to rebuild an airplane engine mid-flight. The database simply cannot keep up with the index maintenance and the sorting overhead. If you try to run this under real-world load, your database will quickly fall over.

What is Google Bigtable and how does it store data?

Google Bigtable is a distributed, NoSQL database designed to handle massive workloads by functioning like a giant, lexicographically sorted map. Instead of supporting complex relational joins, it stores data as simple key-value pairs sorted alphabetically by their row keys.

From what I've seen, if you want absolute speed at scale, you have to sacrifice relational flexibility—there's no way around it. Bigtable doesn't do joins, and it doesn't do complex query execution plans. Instead, it acts as a massive, distributed, sorted map.

Because the keys are pre-sorted, if you scan a range of keys, Bigtable reads them sequentially off the storage medium. This means if we can design our keys correctly, we can guarantee that a user's history is stored together, pre-sorted, and ready to read in a single disk seek.

What is a reverse timestamp and how does it solve sorting?

To me, the cleverest part of this entire design is how they avoid sorting altogether using a reverse timestamp. A reverse timestamp is simply the maximum possible epoch integer value minus the current epoch timestamp, which naturally forces newer records to the top of an alphabetical index.

Let's look at how the math works. In a normal timeline, larger numbers represent more recent events. But in an alphabetically sorted map, we want the most recent events to appear first. By subtracting the current time from a fixed maximum, a newer, larger timestamp yields a smaller reverse timestamp.

When Bigtable sorts these keys alphabetically, the smallest keys naturally rise to the top—meaning your absolute newest watch history is always at the very beginning of your data range. Here is how you generate this key structure:

function generateHistoryKey(userId, timestampMs) {
  const MAX_EPOCH = 9223372036854775807n; // 64-bit max integer
  const reverseTimestamp = MAX_EPOCH - BigInt(timestampMs);
  return `user#${userId}#${reverseTimestamp}`;
}

How does a prefix scan retrieve watch history instantly?

A prefix scan allows the database to locate the starting boundary of a user's data instantly and read only the first few sequential rows. Because the row keys are pre-sorted in reverse chronological order, the database simply reads the first 10 rows and terminates the query.

Let's look at how Bigtable stores this on disk for a hypothetical user, user_123:

Row Key	Video ID	Actual Time
user_123#9223372036854575807	vid_999	3 seconds ago (Most Recent)
user_123#9223372036854675807	vid_888	2 minutes ago
user_123#9223372036854775807	vid_777	1 hour ago

To fetch the history, the application queries Bigtable for keys starting with the prefix user_123# with a limit of 10. Bigtable jumps directly to the first key starting with user_123#, reads the first 10 rows sequentially, and returns them. No sorting, no heavy index scanning, and absolutely no loading spinners.

FAQ

Can you use reverse timestamps in relational databases like PostgreSQL?

While you can physically store reverse timestamps in a relational database, it is generally unnecessary. Relational databases use B-Tree indexes which can be scanned backward just as quickly as forward. Reverse timestamps are a design pattern optimized specifically for distributed, NoSQL key-value stores that rely heavily on forward-only prefix scanning.

What happens when the maximum epoch timestamp is reached?

If you use a standard 64-bit integer as your maximum epoch value, the maximum timestamp is so far in the future (the year 292,277,026,596) that the Earth's sun will have run out of hydrogen before your keys roll over. For all practical software applications, the limit is functionally infinite.

Why not use an in-memory cache like Redis for each user's history?

While Redis is incredibly fast, keeping the entire watch history of billions of users in-memory is prohibitively expensive. Bigtable balances the high write-throughput of an in-memory system with the cost-effective storage of persistent disk, pulling data blocks into memory only when they are actively queried.