Случайная запись из таблицы в Postgresql

Google в помощь:
select random record from postgres table

Я уже оттуда и выписал что не так с предложенными там вариантами

Я уже оттуда и выписал что не так с предложенными там вариантами

Универсального супер-быстрого варианта, идеального в плане случайности, нет. Просмотрите пару страниц поисковой выдачи и выберите вариант, наиболее подходящий конкретно для вас. Вы не первый с такой проблемой - там полно публикаций именно на эту тему, где рассматриваются все возможные варианты и их «ЗА» и «ПРОТИВ».

Когда мне нужен был относительно равномерный семпл делал так:

select * from table where id % 200000 = 4;

У меня работает секунд 30 на таблице со 150 мл записей

выберите вариант, наиболее подходящий конкретно для вас

Пока не подходит ни один.

Универсального супер-быстрого варианта, идеального в плане случайности, нет

Я ожидал, что будет встроенная функция в postgres. Он же очень сложный и навороченный, а запрос вроде базовый. Придется костылить и искать компромиссы, пока в бд не завезут

Как идея появилось следующее:

Создать столбец вроде random_id, который следует для каждой строчки заполнять случайным числом от 0 до 1. Создать индекс для быстрого поиска по этому столбцу. При поиске генерировать случайное число от 0 до 1 и искать ближаший random_id

… Придется костылить и искать компромиссы, пока в бд не завезут

Посмотрите-проверьте еще вариант с tsm_system_rows extension:
https://dba.stackexchange.com/questions/259205/get-a-truly-random-row-from-a-postgresql-table-quickly/259210#259210

Но не подходит, т.к. нет гарантий, что id будут равномерно распределены

Можно поддерживать равномерную нумерацию

UPDATE table SET row_id = ROW_NUMBER();

Если таблица нечасто обновляется. А если часто, то можно делать это например каждую ночь. В течение дня равномерность рандома должна оставаться приемлемой.

Создать столбец вроде

Если приемлемы решения с изменением структуры БД - то, возможно, попробовать ключи сделать в UUIDv4, который по определению рандомный.

Налепил тут себе тестовых таблиц с 200 и 120М записей. Вполне прилично вроде выглядят оба:

SELECT * FROM small_table_uuid t WHERE t.uid > gen_random_uuid() ORDER BY t.uid LIMIT 1;
SELECT * FROM big_table_uuid t WHERE t.uid > gen_random_uuid() ORDER BY t.uid LIMIT 1;

Упс... Только при слишком малом количестве записей (например всего 2) t.uid > gen_random_uuid() может вообще не оказаться ни одной.

Ну, тогда так, например - гарантировано хоть одна будет (если их там есть):

WITH cte_r AS MATERIALIZED (
	SELECT * FROM small_table_uuid t WHERE t.uid > gen_random_uuid() ORDER BY t.uid LIMIT 1
)
SELECT * FROM cte_r r
UNION ALL
SELECT * FROM small_table_uuid t WHERE NOT EXISTS(SELECT FROM cte_r) LIMIT 1
;

:-)

SELECT * FROM table
OFFSET (floor(random() * (SELECT COUNT(*) FROM table)))
LIMIT 1

Вот тут count(*) заменить на примерное количество записей, которое можно выбрать из служебных таблиц. Иначе да, оно перечитывает всю таблицу. Что нибудь типа

SELECT reltuples
  FROM pg_class
 WHERE relname = 'items';

Опять же, offset-limit без указания order by… Ну такое

OFFSET делает фулскан от начал таблицы до офсета, так как считает реальные строчки

Ну это все равно минимум в 3 раза быстрее :)

Как вариант where id > randomId limit 1

Запрос писать не буду, но как идея

Получить MIN(id) и MAX(id), получить случайное число в этих границах, а потом найти ближайший id к этому случайному. Это конечно не запрос, а процедура будет, но вероятно, в итоге работать будет быстрее(всяко же на id есть индекс), чем один сложный запрос.

Всем спасибо за ответы. В качестве решения стал использовать UUIDv4 для id