you are going to be writing and reading from the DB probably way above what it can handle. but if you put something like memcached infront of your server that can easily handle 200k reads a second, or more if it’s on the app server. if you are careful with how you use memcached then you can probably avoid most DB queries for a scenario where someone signs up and logs in. if ETS is shared among all your servers then it should be a similar benefit, however you’ll have to add your own cleanup code i presume.
boxxxie
jaybe78
i am unsure of the scope of the problem. do you have data and usage patterns, expected active users per second?
The idea is for any users to see his list of friends “active”.
In my app an active player is not just a player online but a player available to play.
So basically the query I run fetch data with status != “away” and “busy” and “offline”
An user can go from “available” to “busy” really quickly, meaning he just found a player to play with.
Then, he’d stay “busy” for a little while, 1 hour or less before going back to “available”.
Multiple 10s of thousands of users can request their friends at the same time.
for example you can have a table that records the last time someone logged on/off the site
As described in my previous answer, I would use the Users table and batch update the “last_active_date” and “status” in order to do so.
how often is someone going to need to know about the 1000 friends statuses?
Among 1000 friends, first, I guess only a portion of them will be returned by the query with the status “available” from the DB.
So, let’s say I get 200 returned, the idea is to use a combination of PubSub and Phoenix Presence to track them afterwards.
My app is a mobile app, and since I only display something like 20/30 users on the screen at one time, I will have a pool of 200 users, but only subscribe a user to those on demand.
For example, when the user scrolls to see the next 30, I can unsubscribe from the ones that are not visible anymore on the screen, and subscribe to the new ones.
for example you can have a table that records the last time someone logged on/off the site
Yes except if I misunderstand, that’s more or less what I explained there Fetch online users's friends for large quantity of users - #18 by jaybe78
even if you aren’t doing predictions you only need active users in your graph + friend nodes, unless i misunderstand the problem.
That’s exactly what I want.
Except if I’m missing something I see that in 2 steps:
-
First step is to fetch a user friends that are most probably online and available
That is done in the SQL DB using “status”/ “last_active” columns joined with Friends table -
The second step is that I want user to see in real time when one of their friend goes from “available” to “unavailable”, and that’s why at this point, I want to use Phoenix Presence on a subset of all the friends returned from the query
you are going to be writing and reading from the DB probably way above what it can handle.
Well I understand that and that’s why I want to use something like broadway to get some back pressure on the SQL DB .
Are you saying that is not necessary if the work done in ETS table or memcach is good enough ?
if ETS is shared among all your servers then it should be a similar benefit
Well my idea was actually not to necessarily to duplicate the stored data in ETS across all nodes but do some rpc call/async_call to search for some friends on other nodes.
It would be a lot of broadcast around to publish the new relationships across all nodes and that would create duplicate data on all tables.
So the flow would be
- A user joins the friends screen
- We search for some relation in local ets tables and across the cluster
- if nothing is found or all users are busy we can query the DB to get new friends
- When we get some friends potentially available, we subscribe to them, not more than 40/50 at a time and return that to the UI
0
garrison
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.
1
garrison
Careful: if you retrieve and track only the online friends then users who come online after you look up the friends will never appear.
1
boxxxie
you could put this data in a different table that is partitioned on date. having a buffer that you then write out periodically should work. i think you should avoid that in the beginning.
i’m starting to see why you want to use something like dynamoDB, however postgres can handle 30k writes/s, though you may have to do some tuning. i think this is something you would have to test with any DB you use.
in postgres you can also make unlogged tables. if you are trying to get high writes and don’t care much about data loss in crashes.
if you think that you are going to be doing a heavy write load that is going to go beyond the limits of something like postgres or event the postgres providers (planetscale, aurora), then you could try something like rocksdb, which can handle 100k-200k writes. something that uses a LSM (log structured merge) should be fast. maybe you could do rocks for some data, or all the data, 1 DB is simpler than many.
1
boxxxie
I think it is worth it to try a simpler solution first. have a subscription for all of a users friends instead of a sub for each online friend. on the server you may want to use 2 lists per user for figuring out dispatch/rendering. a list of their friends, and a list of observers. maybe these 2 lists sound redundant, but you’ll be able to add a backoffice or followers without doing hacky stuff to the render list. when something changes with a user you iterate through the observers and dispatch status. if you get to the point where this kills your server then you can use something like a message queue for update dispatching, or use delays.
2