CODEWITHSUNDEEP
cassandranosql
Kacy
Kacy

Reputation: 3430

Single update results in thousands of writes

I'm looking for a viable answer to this use case. There are music tracks, and users have playlists of tracks. Let's say a user uploads a track, then a week later decides to edit the name (or make the track private, etc). If the track has been added to ~10k different playlists, that single edit results in ~10k writes.

It takes a single query to get all the playlists the track has been added to using a reverse lookup table, then the application has to loop through all 10k results and perform the respective updates on the playlist table.

The only alternative I see to this is performing a join at the application level when retrieving playlists.

This is a common use case I keep running into and would like to know how best to handle it.

CREATE TABLE tracks (
    track_id timeuuid,
    url text,
    name text,
    PRIMARY KEY (track_id)
)

CREATE TABLE playlist_ordered_by_recently_added (
    playlist_id timeuuid,
    date_added_id timeuuid,
    track_id timeuuid,
    url text,
    name text,
    PRIMARY KEY (playlist_id, date_added_id)
) WITH CLUSTERING ORDER BY (date_added_id DESC)

CREATE TABLE playlist_ordered_by_recently_added_reverse_lookup (
    track_id,
    playlist_id,
    date_added_id,
    PRIMARY KEY (track_id, playlist_id)
)

Upvotes: 0

Views: 33

Answers (1)

Christophe Schmitz
Christophe Schmitz

Reputation: 2996

The "join" approach is the correct one, though I wouldn't call it "join". To retrieve the track list, you will need to issue a first query against playlist_ordedred_by_recently_added (which gives you all the track_id(s), which is expected to be reasonably small), followed by a bunch of parallel queries to retrieve the tracks.url and tracks.name from your tracks table. When you update, you only need to update the tracks table to change the name, once.

Upvotes: 1

Related Questions