Picking Random Data Efficiently: PostgreSQL Options Compared
A common approach is using ORDER BY RANDOM()
with LIMIT
. This sorts all rows randomly and then picks the first LIMIT
number of rows. This is slow because sorting a large table is expensive.
Fast Method: TABLESAMPLE BERNOULLI
PostgreSQL offers a built-in function called TABLESAMPLE
with the BERNOULLI
method. This is significantly faster for picking random rows. It scans a small sample of the table and uses that to estimate the random rows.
Here's how to use it to pick a single random row:
CREATE EXTENSION IF NOT EXISTS tsm_system_rows; -- Enable if not already
SELECT * FROM your_table TABLESAMPLE BERNOULLI (1);
Replace your_table
with your actual table name. This extension, tsm_system_rows
, is usually included by default in PostgreSQL.
Things to Consider:
TABLESAMPLE BERNOULLI
might not be perfectly random, but it's good enough for most cases and very fast.- If you need perfect randomness or a large number of random rows, alternative methods exist but might require more complex code.
Additional Tips:
- Make sure your table has an appropriate index for the column used in the
WHERE
clause if you have filtering conditions. - If you only need the ID of a random row, you can select just the ID instead of all columns (
SELECT id FROM your_table TABLESAMPLE BERNOULLI (1);
).
SELECT * FROM your_table
ORDER BY RANDOM()
LIMIT 1;
This code selects all rows, sorts them randomly, and then picks the first row (limited by LIMIT 1
).
Faster method using TABLESAMPLE BERNOULLI (recommended):
CREATE EXTENSION IF NOT EXISTS tsm_system_rows; -- Enable if not already
SELECT * FROM your_table TABLESAMPLE BERNOULLI (1);
This code enables the tsm_system_rows
extension (usually already there) and then selects a single random row from your_table
.
Selecting a random row based on a condition (TABLESAMPLE with WHERE):
CREATE EXTENSION IF NOT EXISTS tsm_system_rows; -- Enable if not already
SELECT * FROM your_table
WHERE category = 'Tech' -- Replace 'category' with your column name
TABLESAMPLE BERNOULLI (1);
This code picks a random row from your_table
where the category
column equals "Tech" (replace with your actual column name).
Selecting only the ID of a random row:
CREATE EXTENSION IF NOT EXISTS tsm_system_rows; -- Enable if not already
SELECT id FROM your_table TABLESAMPLE BERNOULLI (1);
This code retrieves only the id
of a random row from your_table
. This is efficient if you only need the unique identifier.
This method involves filtering rows based on a random value between 0 and 1. Here's the code:
SELECT * FROM your_table
WHERE random() < (SELECT count(*) FROM your_table) / desired_number_of_rows
LIMIT 1;
Explanation:
random()
: Generates a random number between 0 and 1.SELECT count(*) FROM your_table
: This subquery calculates the total number of rows in the table.- The
WHERE
clause filters rows where the random number is less than the ratio of total rows divided by the desired number of random rows. LIMIT 1
: This retrieves only the first row that meets the filter condition.
Drawbacks:
- This method scans the entire table to count rows, making it less efficient for large datasets compared to
TABLESAMPLE BERNOULLI
. - It might not be perfectly random, especially if there are gaps in row IDs.
Custom random aggregate function (For advanced users):
This method involves creating a custom function that aggregates random values and uses it for filtering. It's a complex approach and might be overkill for most cases. Refer to online resources for code examples of this method if you're interested in a deeper dive ().
Remember:
TABLESAMPLE BERNOULLI
is generally the preferred method for its speed and efficiency.- Use these alternatives only if
TABLESAMPLE BERNOULLI
doesn't meet your specific needs or for learning purposes.
sql performance postgresql