I always used to do:
SELECT column FROM table ORDER BY random() LIMIT 1;
For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That’s why I started hunting for more efficient methods. People recommended:
SELECT column FROM table TABLESAMPLE BERNOULLI(1) LIMIT 1;
While fast, it also provides worthless randomness. It appears to always pick the same damn records, so this is also worthless.
I’ve also tried:
SELECT column FROM table TABLESAMPLE BERNOULLI(100) LIMIT 1;
It gives even worse randomness. It picks the same few records every time. This is completely worthless. I need actual randomness.
Why is it apparently so difficult to just pick a random record? Why does it have to grab EVERY record and then sort them (in the first case)? And why do the “TABLESAMPLE” versions just grab the same stupid records all the time? Why aren’t they random whatsoever? Who would ever want to use this “BERNOULLI” stuff when it just picks the same few records over and over? I can’t believe I’m still, after all these years, asking about grabbing a random record… it’s one of the most basic possible queries.
What is the actual command to use for grabbing a random record from a table in PG which isn’t so slow that it takes several full seconds for a decent-sized table?