I agree with you, you don’t want to fetch 1000 friends of a user if only one in there is “online”.
But what do you mean by “online” ?
“Online” in the context of a graph or SQL DB might be very different than in the context of a Phoenix app.
I don’t think we can say a user is online with certainty because there’s been an update a few minutes ago, of a column “last_active” in a table, whose purpose is to approximately say, that a user was “recently” active…
But ok, let’s say each client periodically update a column like “last_active” with current datetime, so that the fetching friends query can filter out the ones that have been inactive for more than x minutes.
It’s true that with DynamoDB you cannot make join so there’s no way to gets friends of users and filter with “last_active” date, because that column would only be updated at the user level and not for each friends relationship.
Though my assumption is that since DynamoDB is way faster than SQL, it would not be a problem to fetch more users, especially because they would only be fetched once, and then cached.
The other things to consider in my mind are:
- Filtering users from “last_active” could be a waste of time. In the sense, that this column could be way further than the “truth” than we think. What do I want to know ? Whether an user is available or not right.
In the application I develop, people can quickly go from “available/online” to “busy” status and so with that “last_active” date, that we rely on initially, there will always be a delay.
It will never be as fast/accurate as Presence or Tracker so you could also say, “What’s the point of querying with a join in a SQL DB knowing the data it returns, might be obsolete when it arrives ?”
- You could have only 1 friend online at a T moment and 90% of your friends online at T+1, so you would end up, querying that user’s friends multiple times anyway, while you could have fetched the whole thing the first time and cache it.
In the end, what is returned by queries at some time could be far from the “truth”
My current architecture currently makes use of DynamoDb and streams some data to PostgresSQL.
I have considered using Postgres and fetch friends using that “last_active” date, but then, I thought, may be PostgreSQL would not be able to handle the load as good as DynamoDb.
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.
the leaving and joining of users every second would not be an issue at the app level I guess, because once they are fetched and cached, you can quickly go through them and check their status.
I’m currently tracking my users’s status in an ETS table.
I don’t think it would be a big issue to go through 1000 users in an ETS table to filter those that are away/busy
It would be actually way more complicated for a graph or SQL DB though… even with users periodically updating their status.
That’s tricky decision so that’s why I was asking.
Thanks
