What is the best way to find a fixed number of RANDOM users in DB based on a parameter?

user_8921534 0
Viewed 409

I am developing a Spring Boot REST API in Kotlin. The underlying db is Postgresql and I am using Spring Data JPA for database access.

I have a table called "Users" , where I have some user data. One of the user properties is "gender". It can have one of two values: MALE or FEMALE.

I would like to have a feature in my app to find a random number (say 20 for example) of people of specified gender that I have not seen before. I mean - let's assume I have a table where I store the ids of users that I have already seen.

So now, what I want to do is basically get 20 random users from Users table, where gender is MALE and id is NOT IN [list of ids I have seen].

The randomness of the query initially led me to creating a native query of the kind:

SELECT *  FROM users WHERE gender = :gender ORDER BY random() LIMIT :number

However, I realized that it might be very inefficient, since the order by random() part will be sorting the entire table (or ~half of the table if I select one gender).

So my second idea was to take care of the randomness in the code. So I decided to make a db call to count the amount of users (to fetch the highest id), then generate some id values in the range from 0 to the highest, filter out the ones that I have seen and then fetch the users from DB by ids:

val numberOfUsersInDatabase = userRepository.count()
    val idsOfUsersVotedForBefore = voteService.findIdsOfUsersVotedFor(requestingUser.id!!)
    val excludedIds = idsOfUsersVotedForBefore.plus(requestingUser.id)

    val idsToFetch = random.longs(2*amountOfIds, 1L, numberOfUsersInDatabase)
            .boxed()
            .filter { num -> !excludedIds.contains(num) }
            .limit(amountOfIds)
            .collect(toSet())
   val randomUsers = userRepository.findUsersByIds(idsToFetch)

But in this case I have no way of knowing what the gender of the randomly selected user is, so there is no possibility for me to filter the results by gender before making the db call.

Can you please advice how to tackle this better?

spring-data-jpa kotlin spring java
4 Answers

I'm not wholly familiar with Kotlin language but I'll write out the logic in Java and hope it translates well to you from there.

Your goal is to fetch 20 of a single gender, but you can't figure out what gender it is until you fetch it. Since we're already fetching 20 rows from the database, we can grab more as spares.

We can use a cumulative distribution for an optimal count of how many rows we realistically need, using this tool: https://stattrek.com/online-calculator/binomial.aspx

Assuming 50/50 gender breakdown, probability is 0.5. You can adjust this if your gender distribution is different for your needs. If the gender breakdown is not 50/50, you can create separate bucket fetches for each gender to get an appropriate level of success. We want at least 20 successful matches.

With a sample size of 60, we get a 99.6% probability that we will have 20 or more matches for the gender.

So we can fetch 60 instead of 20, filter for the first 20 of selected gender. If we don't reach 20 (on a 0.4% chance), then redraw another set of 20 to fill out our group. So 99% of the time, 60 row fetches, on a bad one, probably 80 row fetches. This does away with any use of RAND on the DB side which should be suitable for extra-large databases.

Set<Long> idsToFetch = random.longs(2*amountOfIds, 1L, numberOfUsersInDatabase)
            .boxed()
            .filter { num -> !excludedIds.contains(num) }
            .limit(amountOfIds * 3)
            .collect(toSet());

List<User> randomUsers = userRepository.findUsersByIds(idsToFetch);

List<User> selectedUsers = randomUsers
                            .stream()
                            .filter(e -> e.gender == selectedGender)
                            .limit(amountOfIds)
                            .collect(toList());


if(selectedUsers.length < amountOfIds) { 
    //redo or single fetch operation
}
user_29580860

select 100 thousand id in your condition.the data about several MB in memory.just shuffle it.then select * from tables in(id1,id2...,id20)

user_69033900

Do not generate ids, instead generate row indexes.

Then in a loop you can do this

select top 1 start at :randomBase *
from users where gender = :gender
user_25802910

How about a materialized view with the query in your post. It can be scheduled to refresh at a schedule of your choosing (using cron job or perhaps other tools offered by Postgres)

user_51656780
Related