Case Study 3.4: How Spotify Wrapped serves a launch-day spike

Concept. Spotify Wrapped computes each user's yearly summary before launch and stores the result by user, so the launch-day spike serves precomputed records.

Intuition. In this example architecture, 700 million users request Wrapped on December 1. A request cannot scan 365 billion play events, so a batch job aggregates each user's listens before launch and stores one record keyed by user_id.

Case Study 3.4 Reading Time: 7 mins

Chronological storage scatters a year of raw listens across pages. Clustering groups each user's listens for a sequential batch read. The batch job writes one precomputed Wrapped row per user, and the December card fetches it with one hash-index lookup on user_id.

Figure 1. The design clusters Listens by (user_id, listen_time), so a batch job reads each user's year sequentially and writes one summary row per user. On December 1, the card fetch uses one hash-index lookup on user_id.

Two phases: batch and online

The batch phase aggregates a year of play events into per-user summaries before launch. The online phase serves those summaries to 700 million clients. Each phase needs an index that matches its access pattern.

Phase 1: offline aggregation

Before December, the batch job reads the Listens table to tally each user's top tracks. The worked example follows a user with 1,000 listens.

Option A: unclustered storage

Chronological storage scatters the example user's 1,000 plays across 1,000 pages. Even with an index on user_id, the engine performs 1,000 random seeks. Repeating that work for every user would take weeks.

Option B: clustered storage

The design orders the Listens table by (user_id, listen_time), creating a clustered index on those columns. The clustered versus unclustered page explains the mechanism.

The user's 1,000 plays now occupy one 64 MB page or a small set of neighboring pages. The engine reads them sequentially. The same B+Tree also supports date-specific lookups such as "what did I play on July 12 at 9 PM?" because the key includes listen_time.

Phase 2: online delivery

The batch job stores each summary in a Wrapped table. The online path uses two access patterns.

Wrapped card: hash index on user_id

When a user opens Wrapped, Spotify fetches one precomputed summary. A hash index on user_id provides an expected O(1) lookup.

Recently played: B+Tree on a clustered table

The raw Listens table serves range queries such as "show all songs I played in December." Its B+Tree finds the start of December, then the clustered layout reads the range from contiguous pages. A hash index cannot serve that range scan.

One clustering order per table

A table supports one physical clustering order, so the design must choose which access pattern to favor:

  • Clustering by (user_id, listen_time) makes Wrapped fast and personal-history deep-dives fast.

  • Clustering by (artist_id, user_id) would make "Top 10 Fans of Taylor Swift" fast instead, but Wrapped becomes unclustered (and therefore slow).

A large system can keep multiple physical copies of Listens, each clustered for a different feature. This design spends storage to reduce random IO at query time.

Takeaway: Wrapped moves aggregation before launch, then serves each card from a hash-indexed per-user summary while clustering turns the worked batch read from 1,000 random IOs into one sequential read.