Have you considered using a relational database instead? They excel at solving this kind of problem.

It has everything to do with the database used: the fastest operation is the one that never runs, and with a relational/graph database you can avoid the reads altogether. You ask the database for the friends which are online and get only those in the result, deferring the retrieval of the less-interesting offline friends for later, which may very well be cached end-user-side (e.g. localStorage) on a long-term basis.

The moment that you start doing relational operations over data that you have retrieved from a NoSQL database, you have invented your own half-finished, slow, and bug-ridden relational database.

You will need to adapt the application to make good use of the relational model, but once you have, a sustained “50000 users with 1000 friends each leaving and joining every second” – and likely many of your other problems too – become a non-issue.

Not all your data needs to be in a relational database either, but you may as well put it there: in my experience every clever NoSQL project ends up needing relations everywhere sooner or later anyway.

You have two predicates.

• user has a friend relationship with current_user
• user has status=online

Assuming you have two indexes, for each of these predicates, you have two choices:

• scan user’s friends and then filter for status=online
• scan all online users and then filter for friendship

If you use a relational database it will pick one for you - almost certainly the first, because the set of friends is going to be a lot smaller than the set of all online users.

Of course, there is a third option: you could create an index of all online friends, essentially materializing the predicates.

But the crux of the issue here is: you seem to be storing the online status in ephemeral :ets tables instead of in the database (not necessarily a bad idea). So you are responsible for performing the filtering yourself. Meaning you are back to the same choices:

• scan all online users from the ets table and then filter for friends
• scan all friends from the database and then filter for online

The third option (indexing on both fields) is no longer available to you because you have no way to atomically update both the database and your ets table, so you risk corrupting the state. Of course maybe this is not a big deal since your online state is so ephemeral anyway.

I think you had the right idea from the beginning here - this is what you should do IMO. Also consider the following: it is common for a “friends list UI” to show both online and offline friends. I would go so far as to say this is the default. So you’re probably not losing much querying the whole friends list anyway - you may as well just display it!

Well yeah, you would sort the friends online-first in the UI. This is what literally every friends list UI I’ve ever seen does.

“Scanning” in my reply was a technical term referring to how the database executes your query. You would be writing a query, the database is scanning. You would of course want an index on the thing you’re scanning, which in this case would be the friends relation. You would want an index on (user_id, friend_id) to make that fast. I would just use that tuple as the primary key and kill two birds with one stone.

Obviously you would want that to be bidirectional so I guess you would either create two friends entries (one each way) for every pair of friends or create a second index and scan both. I’m not sure if there is a better way to model that.

Materialized views are not updated incrementally in Postgres IIRC but all at once, so obviously that creates performance problems. I can’t speak to partitioned tables, maybe someone else has experience with that.

What you are describing here is just caching, right? Sounds good to me.

Careful: if you retrieve and track only the online friends then users who come online after you look up the friends will never appear.