This week we're excited to introduce a couple of major performance improvements via caching and swapping out N+1 queries for a database-driven design. Overall, these updates were relatively simple to build however they took time and detail to implement properly. The end result is a 50% improvement on common network calls (ie fetching a repository) that sideload a bunch of metadata such as total bookmarks, downloads, reviews, and more.

• The Caching Layer
• N+1 Query Updates

For starters, the new caching layer uses a private, self-hosted instance of Redis to store frequently accessed data for faster response times. For example, as an SSR-driven application, Playbooks has to fetch the repo anytime the user navigates to a nested page since we're rendering that HTML on the server and several pages share data. Considering then that the user probably wants to navigate around the repository quickly, the caching layer handles this with ease.

A diagram of the new caching layer As shown in the diagram above, the caching layer essentially checks Redis first and then, if it exists, immediately returns the data. If it does not, we perform the necessary database queries, store that data in the cache with a reasonable expiration date, and then return that data to the client. Behind the scenes, we then break the cache when data associated with the initial fetch, such as a bookmark or review, get's posted or the repository itself is updated. All in all, a pretty straight forward implementation that provides significant performance gains on specific endpoints.

The second thing we did was replace several of our N+1 queries with a single database query to an associated "meta" table that stores incremental data.

An diagram showing the network calls before the N+1 query changes For instance, when fetching a repository we often sideload a bunch of metadata such as total bookmarks, downloads, reviews, and more. Previously, as shown in the diagram above, we had to fetch that data one-by-one or bundle it into one giant SQL query which wasn't ideal for several reasons. So, instead, we've introduced a related "meta" database table where we increment and decrement associated data when it occurs.

A diagram showing the network calls after the N+1 query changes As shown in the new diagram, the result is a single database query for all the aforementioned data rather than 3-4 separate database queries. All in all, this requires much more work. But the end query is much faster and simpler versus the aggregate database calls mentioned before.

If you made it this far, thank you for taking the time to catch up on what's happening at Playbooks! To receive these updates in your inbox, simply create a account over on Playbooks. Or, feel free to follow along over on Twitter / X to receive updates there.