Select my friends activities, nosql way

Related to:

Goal: understand is it makes sense to use redis here or continue using postgres is a right choice.

The task

We have two tables:

We need to get paginated list of friends activities for specified user_id. In SQL it looks like:

SELECT act.activity_id, act.timestamp from activities act
JOIN friends fr ON fr.friend_id=act.user_id AND fr.user_id='{user_id}'
WHERE act.timestamp < {last}
ORDER BY act.timestamp DESC
LIMIT {limit};

Let's try to use redis for this task. Simplified plan is next:

To do all this things on the redis side I wrote lua script:

def search(self, user, last, limit):
    SCRIPT = """"ZINTERSTORE", "test:tmp:" .. ARGV[1], 2, "test:last_user_activity", "test:friends:" .. ARGV[1], "AGGREGATE", "MAX")
    local users ="ZREVRANGE", "test:tmp:" .. ARGV[1], 0, -1, "WITHSCORES")
    if users == nil then
        return {}
    end"DEL", "test:tmp:" .. ARGV[1])
    local counter = 0
    local lastval = users[1]
    for k, v in pairs(users) do
        if (counter % 2 == 0) then
            lastval = v
  "ZUNIONSTORE", "test:tmp:" .. ARGV[1], 2, "test:tmp:" .. ARGV[1], "test:user_activities:" .. lastval, "AGGREGATE", "MAX")
  "ZREMRANGEBYSCORE", "test:tmp:" .. ARGV[1], ARGV[2], ARGV[3])
            if"ZCOUNT", "test:tmp:" .. ARGV[1], v, ARGV[2]) >= tonumber(ARGV[4]) then break end
        counter = counter + 1
    local users ="ZREVRANGE", "test:tmp:" .. ARGV[1], 0, ARGV[4] - 1)"DEL", "test:tmp:" .. ARGV[1])
    return users
    return self.conn.eval(SCRIPT, 0, user, last, get_timestamp(), limit)

Full script and it's output see on gist:

Briefly about results

Redis vs Postgresql, both were running on my laptop.

Postgres tables and indexes:

CREATE TABLE activities (
    id SERIAL,
    user_id VARCHAR(100),
    activity_id VARCHAR(100),
    timestamp BIGSERIAL
CREATE TABLE friends (
    id SERIAL,
    user_id VARCHAR(100),
    friend_id VARCHAR(100)
CREATE INDEX activities_user_id_index ON activities (user_id);
CREATE INDEX activities_timestamp_index ON activities (timestamp);
CREATE INDEX friends_user_id_index ON friends (user_id);
CREATE INDEX friends_friend_id_index ON friends (friend_id);

Activities count: 30000
Friends count: 25000
My friends count: 15000
Activities per page: 10
Page 1: 0.161883 s for postgres vs 0.025598 s for redis.
Page 2: 0.203902 s for postgres vs 0.026051 s for redis.
Page 10: 0.149319 s for postgres vs 0.048609 s for redis.

How You solve problems similar to described above? Is redis good for this task?
Does graph database may solve the problem?

UPD 2015-09-24

Each user needs its own stream.

Licensed under CC BY-SA 3.0