Example: News Forum
To illustrate these concepts, we will walk through an example of using Readyset for a news forum application inspired by HackerNews.
Schema
First we define two tables to keep track of HackerNews stories and votes.
CREATE TABLE stories (id int, author text, title text, url text);
CREATE TABLE votes (user int, story_id int);
Query
Next, we'll write a query that computes the vote count for each story and joins the vote counts with other story metadata such as the author, title, and ID.
SELECT id, author, title, url, vcount
FROM stories
JOIN (SELECT story_id, COUNT(*) AS vcount
FROM votes GROUP BY story_id)
AS VoteCount
ON VoteCount.story_id = stories.id WHERE stories.id = ?;
Caching with Readyset
Traditional databases would compute the results of this query from scratch every time it was issued. Readyset takes a different approach and instead precomputes and incrementally maintains the results of this query for commonly read keys. To accomplish this, Readyset creates a streaming dataflow graph, as described in the previous section. At a high level, the dataflow graph would look as follows:
Under the hood, here's what some of the internal state might look like for this graph.
Now, we’ll take a look at what happens when the data changes. Let’s say that we add a record to the Votes table to
reflect the fact that Justin voted for the story with ID 2
. This update would first be applied to the Votes base table at
the root of the graph, and then be propagated through the graph, updating all children nodes along that way.
The figure below shows the modified state at each node as the result of this write.
When performing a read (i.e., executing the prepared statement), we're essentially just doing a key-value lookup for the parameter value on the relevant column of the reader node.
In this case, we're doing a lookup for key 2
on the ID
column of the reader node. These lookups are blazingly fast, since reader
nodes are lock-free, in-memory data structures.