Tracking User Actions on Rails website

Question

Hi,

Users will each have a user profile, and he wants each user to know who has viewed their profile. The only way I can think of doing this involves doing a database INSERT every time someone views a profile page (probably using an Ajax call at the end of the page). This sounds like a massive performance issue, especially if the site builds up to any significant traffic.

Any ideas on how to achieve this in a performant way, or is it just the nature of tracking this kind of thing?

Thanks.

Answer 1

If you're using MySQL, I'm a big fan of using the "INSERT INTO...ON DUPLICATE KEY" feature which allows you to perform an update on a row if there's a key conflict. This means that instead of one row per profile view, there could be one row per user + profile pair, or for even more granularity, user + profile + day.

Depending on your traffic situation and choice of DB engine, this may work very well even for extended periods of time. Although there may be a very large number of rows in a tracking table like this, the content is very small, like a pair of ID fields and a numeric counter, which is on the order of 12-16 bytes per entry.

For example, a tracking table might be defined as:

def self.up
  create_table :user_profile_views do |t|
    t.integer :user_id
    t.integer :profile_user_id
    t.integer :count, :default => 0
  end

  add_index :user_profile_views, [ :user_id, :profile_user_id ], :unique => true
end

For the model:

def self.record_view_profile!(user, profile)
  connection.execute(sanitize_sql([ "
    INSERT INTO user_profile_views (user_id, user_profile_id, count)
      VALUES (%d,%d,1)
      ON DUPLICATE KEY UPDATE
       count=count+1
  ", user.id, profile.id ])
end

This will either insert a row, or update the count for an existing row.

Answer 2

1. Why add another ajax call to view?
   Do it When you call the user profile show action.. in the controller

2. Queue it to a background job (example: starling + workling)

3. Dont use acid DB for the viewed data, use some kind of key-value store db (cassandra, redis etc)