Database Reference
In-Depth Information
Read performance and write complexity
Let's now examine the performance characteristics of our new data. In the first query,
against the
home_status_update_ids
table, we are performing a range query across
a single partition: we know that this is an extremely efficient query, in the form we should
strive to always use in our application. The second query, however, accesses several parti-
tions, one for each status update author in the result set. If we're using a page size of ten,
we might read updates from as many as ten partitions, although it might be fewer if more
than one of the updates comes from the same author.
This is an improvement over our previous approach: the number of partitions that we need
to access is no longer proportional to the number of followers a user has; it is only propor-
tional to the page size.
The cost of improved read-time performance characteristics is additional write-time com-
plexity. Now, any time a status update is created, the application must determine who fol-
lows the author and write a row to the
home_status_update_ids
table for each fol-
lower. If the author later gains additional followers, the application might need to go
through all of the author's old status updates and add them to the new follower's home
timeline.
We also need to ensure that deletions are propagated to the
home_status_update_ids
table. If a status update is deleted, references to it should
also be deleted from the home timeline of anyone who follows its author. Let's say that
dave
decides to delete his most recent status update:
DELETE FROM "user_status_updates"
WHERE username = 'dave'
AND id = a05b90b0-2ada-11e4-8069-5f98e903bf02;
DELETE FROM "home_status_update_ids"
WHERE timeline_username IN ('alice')
AND status_update_id = a05b90b0-2ada-11e4-8069-5f98e903bf02;
In the second query, we use an
IN
query even though there is only a single
timeline_username
in play to demonstrate that this is possible: if
dave
had many
followers, we could simply add more usernames to that clause and still accomplish the de-
letion with a single query.