CPTRedHawk•3mo ago

High bandwidth consumption

Hello, I have a consumption problem in my application, I have a user ranking system, I need to rank them all, but the consumption is very high (currently 2k users)

25 Replies

Convex Bot•3mo ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

CPTRedHawkOP•3mo ago

export const usePositionById = query({
  args: {
    tgUserId: v.string(),
  },
  handler: async (ctx, args) => {
    const position = await ctx.db
      .query('user')
      .withIndex('by_points')
      .order('desc')
      .collect()
    return position.findIndex(p => args.tgUserId === p.tgUserId)
  },
})

export const usePositionById = query({
  args: {
    tgUserId: v.string(),
  },
  handler: async (ctx, args) => {
    const position = await ctx.db
      .query('user')
      .withIndex('by_points')
      .order('desc')
      .collect()
    return position.findIndex(p => args.tgUserId === p.tgUserId)
  },
})

lee•3mo ago

so there is a way to do this in O(log(n)) time and space, but there are some caveats since it's actively being worked on. see https://discord.com/channels/1019350475847499849/1254446033589633095/1254446033589633095 for previous discussion. do you expect the position to usually be small, compared to the total number of points? then you could walk the table incrementally and stop when you find the index

export const usePositionById = query({
  args: {
    tgUserId: v.string(),
  },
  handler: async (ctx, args) => {
    let index = 0;
    for await (const p of ctx.db
      .query('user')
      .withIndex('by_points')
      .order('desc')) {
      if (args.tgUserId === p.tgUserId) {
        return index;
      }
      index += 1;
    }
    return -1;
  },
})

export const usePositionById = query({
  args: {
    tgUserId: v.string(),
  },
  handler: async (ctx, args) => {
    let index = 0;
    for await (const p of ctx.db
      .query('user')
      .withIndex('by_points')
      .order('desc')) {
      if (args.tgUserId === p.tgUserId) {
        return index;
      }
      index += 1;
    }
    return -1;
  },
})

CPTRedHawkOP•3mo ago

But would this be efficient in the scenario with more than 10k users?

lee•3mo ago

that code ^ is linear in the returned index, whereas your code is linear in the total number of users. so no, it doesn't scale if the returned position can be large. the scalable version is trickier, involving storing separate data to more efficiently calculate offsets in the table for my own curiosity, can you describe the use-case for this query? is this for a leaderboard or something?

CPTRedHawkOP•3mo ago

yes ranking it just shows the position from largest to smallest. ?

ballingt•3mo ago

Consumption being very high here because the query function is running a lot, and it's an expensive function @CPTRedHawk?

CPTRedHawkOP•3mo ago

Hi Yes, it is executed every time the user goes to the ranking page. Today alone it consumed 5GB of my bandwidth This seems to be a solution, confirm?

ballingt•3mo ago

@CPTRedHawk Is it also running a lot of times reactively, while a user sits on that page? e.g. How many times is the query running, and how much bandwidth does it take each time? Once you get to this scale you can decide whether you need the reactivity of useQuery for this particular case; if this query is executing dozens-hundreds of times live, maybe you can e.g. calculate the rankings once a minute or once an hour, and use those stored rankings instead.

CPTRedHawkOP•3mo ago

they update in real time It's only supposed to work when the user is on the page! Hello

djbalin•3mo ago

You could implement "Your rank is updated every X minutes" and set up a cron job to calculate the entire leaderboard every X minutes and store each user's rank on their document. Maybe a rough idea could be this (I'm not very strong in time complexities, just take this as a guess): - Insert all scores into a HashMap, O(n) - Sort this HashMap by scores O(n*logn) - Iterate through this array once and set rank of user equal to the index O(n) Actually what the frick am I talking about, Convex maintains this automatically with the index on points??? Just traverse this: the first index would be rank 1, the last index would be the last rank? ok sorry I'm blind, @ballingt suggested this above..

CPTRedHawkOP•3mo ago

This solution seems to really work, but I believe it should be more optimized

jCtapuk•3mo ago

@CPTRedHawk 1) Select cingle user 2) Read point user and save 3) pick users points < current user point 4) array to size No for operation

CPTRedHawkOP•3mo ago

this example ?

jCtapuk•3mo ago

jCtapuk•3mo ago

here you can add an update time field to sort who has the same points And on the client side, make a request every 5 minutes and not subscribe to the update. For example, when you open a modal, you execute a request, and if the request is less than 5 minutes, return the old data to it. And the user can forcefully press the update button, etc. This is an optimization tip It was basic. And if you want, you can make sure that when you update the points, you make a change to all participants and shift the rating. And when requested, it returned the current rating of the position. It all depends on what approach you implement.

ballingt•3mo ago

I'm wondering if it updates many times while the user is on the page — if so, you could trade off cost for less reactivity Yeha what @jCtapuk is talking about

jCtapuk•3mo ago

you can refuse onUpdate and set the request manually if it has already received a request in the client that has not been set within 5 minutes. I mean that if you need to often see the rating in real time, then it will be expensive, and option 2 suggests saving the rating of users when the points change, we are pregnant with all users for those who have points less than the current points, and move their rating is +1. And this will be a fast query since it will immediately return the data without any calculations.

CPTRedHawkOP•3mo ago

It only updates when there are changes, it doesn't update every second or minute, do you have any practical examples? What the friend indicated above helped to mitigate consumption a lot, but it still consumes a lot.

CPTRedHawkOP•3mo ago

only today almost 1gb of consumption

CPTRedHawkOP•3mo ago

Good morning

djbalin•3mo ago

Good morning brother. What about the suggestion to calculate the entire leaderboard every X minutes and storing each user's rank directly on their document? Would that not be OK for you? I really think this could cut your bandwidth consumption dramatically Generate leaderboard:

export const generateLeaderboard = mutation({
  handler: async (ctx) => {
    let rank = 1;
    for await (const user of ctx.db
      .query("user")
      .withIndex("by_points")
      .order("desc")) {
      console.log(rank);
      await ctx.db.patch(user._id, {
        rank: rank,
      });
      rank += 1;
    }
  },
});

export const generateLeaderboard = mutation({
  handler: async (ctx) => {
    let rank = 1;
    for await (const user of ctx.db
      .query("user")
      .withIndex("by_points")
      .order("desc")) {
      console.log(rank);
      await ctx.db.patch(user._id, {
        rank: rank,
      });
      rank += 1;
    }
  },
});

Get rank of current user:

export const getRankByUserId = query({
  args: {
    userId: v.id("user"),
  },
  handler: async (ctx, { userId }) => {
    const user = await ctx.db.get(userId);
    if (user === null)
      throw new ConvexError(`User with id ${userId} not found`);
    return user.rank;
  },
});

export const getRankByUserId = query({
  args: {
    userId: v.id("user"),
  },
  handler: async (ctx, { userId }) => {
    const user = await ctx.db.get(userId);
    if (user === null)
      throw new ConvexError(`User with id ${userId} not found`);
    return user.rank;
  },
});

CPTRedHawkOP•3mo ago

In this case, would I have to create a task that updates it every x minutes? The problem is that I need it to be in real time, that's the idea of the scoreboard... This here drastically reduced my bandwidth cost, but it is still high (average of 1GB per day with 2.5k users)

djbalin•3mo ago

Yes I think you could use a cron job for this, see Convex docs, it's very easy to set up: https://docs.convex.dev/scheduling/cron-jobs But yes as you point out, the potential problem/downside is then that the scoreboard is not truly real-time/live. Are you making a real-time PvP game?

Cron Jobs | Convex Developer Hub

Convex allows you to schedule functions to run on a recurring basis. For

CPTRedHawkOP•3mo ago

It's not a game, but users follow the score in real time. This solution has proven to be extremely effective, however I believe that with an average of 10kk users the consumption would again be extremely high.