Search code examples
pythonsqldjangoredisfeed

Good way to make a SQL based activity feed faster

Need a way to improve performance on my website's SQL based Activity Feed. We are using Django on Heroku.

Right now we are using actstream, which is a Django App that implements an activity feed using Generic Foreign Keys in the Django ORM. Basically, every action has generic foreign keys to its actor and to any objects that it might be acting on, like this:

Action: (Clay - actor) wrote a (comment - action object) on (Andrew's review of Starbucks - target)

As we've scaled, its become way too slow, which is understandable because it relies on big, expensive SQL joins.

I see at least two options:

  1. Put a Redis layer on top of the SQL database and get activity feeds from there.
  2. Try to circumvent the Django ORM and do all the queries in raw SQL, which I understand can improve performance.

Any one have thoughts on either of these two, or other ideas, I'd love to hear them.

Solution

  • Went with an approach that sort of combined the two suggestions.

    We created a master list of every action in the database, which included all the information we needed about the actions, and stuck it in Redis. Given an action ID, we can now do a Redis look up on it and get a dictionary object that is ready to be returned to the front end.

    We also created action id lists that correspond to all the different types of activity streams that are available to a user. So given a user id, we have his friends' activity, his own activity, favorite places activity, etc, available for look up. (These I guess correspond somewhat to materialized views, although they are in Redis, not in PSQL.)

    So we get a user's feed as a list of action ids. Then we get the details of those actions by look ups on the ids in the master action list. Then we return the feed to the front end.

    Thanks for the suggestions, guys.