Trevor Turk

A chess-playing machine of the late 18th century, promoted as an automaton but later proved a hoax.

Reducing db load with cross-table caching (denormalization) using JSON in Rails

I'm working on a side project that lets people share embeddable videos with a follower/following system a la Twitter. You can see a rough example of the setup I'm using in this old demo app. The basic idea is that each user has a feed of posts created by other users that they follow. So far, I've found that having a feed_items table that stores posts on a per-user basis makes the most sense. If you drop an index in there, it seems speedy enough for my needs. Here's a simplified example of the relevant portion of the schema: Now, if you're going to show a user their "feed" you might do something like this: The only problem is that, even with those fancy includes, you're still querying 3 different tables: So, I started experimenting with storing attributes of the post and associated user information directly in the feed_items table. I played around with Active Record's serialize method (link), but it didn't seem like the right fit. Here's what I came up with yesterday. I think it's a pretty good start: Essentially, I'm just pushing a cached copy of the post (including the associated user) into the feed_items table, using JSON. This way, we can get away with a single query for the user's feed_items: I'm curious to hear what other people think about this, and if there are smarter ways to accomplish the same goal.

« Home page