PostgreSQL Stories: Taking Random Samples from Big Tables

The text explores efficient methods for taking random samples from large datasets in PostgreSQL, particularly for data science and AI applications where model training, resource constraints, and exploratory data analysis benefit from such sampling. It critiques the inefficiency of the intuitive ORDER BY random() method, which involves scanning and comparing all rows, and proposes alternatives like Bernoulli sampling and TABLESAMPLE, each with its own trade-offs. Bernoulli sampling reduces runtime by avoiding complex comparisons, while TABLESAMPLE further improves performance by selecting entire heap blocks instead of individual rows, though it introduces non-determinism and potential bias in sampling. The text also discusses the implications of sampling on joined tables, emphasizing the importance of understanding join relationships and avoiding pitfalls such as independently sampling both tables in a join. Through simulations and examples, it provides insights into the practical application and efficiency of these sampling techniques, ultimately promoting an understanding of their impact on database performance.