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.