How do I get random results from PostgreSQL?

We want to find results for a query, and then grab a random sample of them. To fetch the full data set and get a random selection from that set feels wrong and seems slow, and we throw away a bunch of data the database got us.

Original problematic code:

query
|> Repo.all()
|> Enum.take_random(2)

PostgreSQL can give you random results!

query
|> order_by(fragment("random ()"))
|> limit(2)
|> Repo.all()

The order by passes the SQL fragment "random ()" which will tell PostgreSQL to sort the results by a random value, then when combined with the limit(query, 2) call, will only return the first 2 of the randomized results.

This approach prevents loading all the results into memory and then taking a random set using Elixir, and puts that lift on the database which is better optimized for the effort.

A similar approach works in other languages, just use whatever mechanism is required by the database layer to send the "random ()" command into the ORDER BY clause.