Rails—get a random record from db?

Using Postgresql or SQLite, using RANDOM():

User.order("RANDOM()").take

Presumably the same would work for MySQL with RAND()

User.order("RAND()").take

Note: From Rails 6.1, this won't run any more. Use .order(Arel.sql('RAND()')) instead (passing literals to .order is deemed unsafe and has been deprecated for quite some time now.)

Well after lot of trials and errors i've found this solution to be helpful and error free.

Model.find(Model.ids.sample)

Model.ids will return an array of all ids in the database. we then call sample method on that array that will return a random item in the list.

You can find the maximum user id in the table and find a user given a random id limited to this maximum. Example:

max_id = User.order(id: :desc).limit(1).pluck(:id).first
user = User.find_by('id > ?', rand(max_id))

These two queries are extremely fast because you are using the index for the primary key (id).

I decided to run some simple benchmarks, using PostgreSQL, Rails 7 and a table with 65k records. In my case I needed a random record after a scope.

Update: Now using Arel.sql('RANDOM()') instead of 'RANDOM()' to avoid deprecation warning mentioned by @MDickten.

TL;DR in order from best to worst

1) Model.scope.order(Arel.sql('RANDOM()')).first 
2) Model.find(Model.scope.ids.sample)
3) Model.scope.sample

For a more detailed answer here's the snippet I ran

require 'benchmark'

N = 1000
Benchmark.bmbm do |x|
  x.report('sample') { N.times { Model.scope.sample } }
  x.report('find ids sample') { N.times { Model.find(Model.scope.ids.sample) } }
  x.report('RANDOM()') { N.times { Model.scope.order(Arel.sql('RANDOM()')).first } }
end

And the results were

                | user        system    total      real
sample          | 957.387427  11.710236 969.097663 (1054.653218)
find ids sample | 64.859209   3.164221  68.023430  (97.080131)
RANDOM()        | 0.819056    0.086088  0.905144   (11.837224)

for rails 6

you can pass records count to get how many records you want

User.all.sample(1)

Above Query will Return only one random record of the user

TLDR: Scroll to the bottom of the answer - strategy #5 is what I use today.

I've actually implemented this in an app, where I randomly need to pull a record out of a growing list of currently 1_000_000 records (for business reasons).

1. Use DB RANDOM for PostgreSQL/SQLite or RAND for MySQL/MariaDB

   • Pro: Most robust
   • Con: Slow. As the table grows, takes longer and longer (~260ms on my table)

       User.order('RANDOM()').limit(1)
     

2. Pluck/Select IDs and then use Ruby Array#sample to get a random id. Then look up that record in DB

   • Pro: faster (~50ms on my table)
   • Con: As your tables grows you it will start getting slower, and you simply may run out of resources, depending on the size of the machine. i.e.: you're running on a small VM

       User.find(User.pluck(:id).sample)
     

3. Get min & man value of IDs in the DB, then rand(min..max) and do a find_by (in case you hit a deleted record) in DB for that record:

   • Pro
     • Extremely fast, if no deleted data (~17ms on my sample data)
     • If the table grows, it doesn't affect the speed of the lookup
   • Con
     • Requires Numeric IDs, can't use GUID or Varchar IDs
     • Needs sequential IDs without missing IDs
     • Can be brittle if any records were deleted and ID's are missing.
       • Error handling & additional lookups can mitigate the problem, but as number of missing IDs becomes larger, this will slow as well

   User.find_by(rand(1..User.count))
   

4. There is another strategy that I haven't seen other answers mention - Random Offset. Just make sure it's in the range of your existing records.

• Pro: no error handling needed, doesn't depend on the type of ID
• Con: Not as fast - about 120ms on avg on my sample data, but depending on where the offset is in the table the actual queries can be anywhere between 20ms and 600ms. That's just how offset works.

  User.offset(rand(1..User.count)).limit(1)
  

Ultimately, I chose 3rd strategy - despite the cons, it is still faster on average for my use-case. Our deletes from that table are also rare, and we use soft deletes, that I can use in my error handling. However, since I came up with Strategy #4 - I've been using that sometimes. Works well.

Tips:

• If the actual current count is not critical: You can preload User.count and memorize it as User.total_count (for example) or keep in an app-load config, that gets loaded on app restart, and you can either invalidate this caches or restart your app on a regular basis. This will remove the extra 15-20ms needed to get User.count and will speed up option 3 to under 3ms, and option 4 to about 100ms

  def self.total_count
    @total_count ||= count
  end
  

  This is another reason I used stratagem #3. Even hitting "missing" records, and having to error handle it and do additional lookups, until I find a "working" random records - is very cheap. In the time I can look up using other strategies, I can do 5-10 look-ups using this approach and still be ahead of the curve on speed.

• Error handling can be done 2 ways:

  • Do lookups until you find one that exists
  • Do 3 (5, 10) lookups, and then select a random one from the result set. Still superfast (especially if you memoize User.count as User.total_count or just do User.count, before executing this statement below). In Rails find_by will return result or nil
  • Depending on your data User.maximum(:id) and User.count, maybe the same or close enough to not matter, but they can be also widely apart. So be careful. Do User.maximum(:id) or User.last.id, instead of User.count if you have a lot of gaps in your data. In my case, there are no gaps - so the result is the same.

  max = User.maximum(:id)
  ids = 5.times.map { rand(1..max) }
  User.where(id: ids).sample
  

Error handling should be chosen, depending on how many "missing" records you have in a sequence. If you have a lot, then strategy #3 may not be a good choice.

EDIT - new strategy #5:

Adding 5th strategy. I've come up with after finishing this post, and I think this is the fastest, while being the most robust

5. Get min & man value of IDs in the DB, then rand(min..max)and do a where lookup:

   • Pro
     • Extremely fast - I get 1-6ms results
     • If the table grows, it doesn't affect the speed of the lookup (actually faster than offset)
     • If records get deleted, it will just find the first match after the deleted one.
     • Can be modified into a scope, and used with other scopes - i.e User.published_today.random_record (Just keep in mind that it's essentially reimplementing an offset in a way, which may limit your data set for a lookup)
   • Con
     • Not as clear as using just offset, but offset will get slow er depending on how big is your data.

   # `random_id` Can be memoized / cached, but beware of scoping before caching!!!
   random_id = rand(User.minumum(:id)..User.maximum(:id)) 
   User.where("id >= ?", random_id).limit(1)
   

try this:

User.offset(0..(rand(User.count))).limit(1).sample

Enjoy it :)

Consider Disabling ActiveRecord Caching

It can sometimes be important to disable Rails caching, such as if you plan to run the same query multiple times within the same HTTP request:

random_user = User.uncached do
  # query is not cached
  User.order(Arel.sql("RANDOM()")).first
end

Any of the other, potentially more efficient queries in this thread will work as long as they're inside the uncached block.

Potential Bug

Here's a simplified example of the bug we hit. Suppose you have a method that returns a random user, and you call that method more than once:

def get_random_user
  User.order(Arel.sql("RANDOM()")).first
end

player_1 = get_random_user
player_2 = get_random_user

# In rails console...
player_1 == player_2 # => false (query not cached)

# If run in a Rails controller
player_1 == player_2 # => true (query IS cached)

As described above, the solution is to use ActiveRecord's uncached method to prevent caching:

def get_random_user
  User.uncached do 
    User.order(Arel.sql("RANDOM()")).first
  end
end

You can get a sample in Rails console using Model.all.sample.attribute.

E.g.:

Contact.all.sample.name
=> "Bob Mcmillan"