SQL, data modeling, data quality, EDA, visualization · insight-1067
Do not treat LIMIT or TABLESAMPLE SYSTEM as a random sample, and use reservoir sampling for streams
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
An analyst profiles a billion-row table with SELECT * LIMIT 100000 or TABLESAMPLE SYSTEM (1) and reports distributions from it.
Action
Use row-level sampling (BERNOULLI, or a random key with a threshold) for profiling, validate the sample against a few cheap full-table aggregates, and for unbounded streams use reservoir sampling to keep a fixed-size uniform sample. Record the seed.
Reason
Uniformity needs each row to have the same inclusion probability, which plan-order truncation and block sampling do not give.
Boundary
Profiling, quick estimates and building labelled sets from logs.
Symptom or risk
LIMIT without ORDER BY returns whatever the plan produces first, often one partition or time range, and block sampling inherits the clustering of the data, so the profile is biased.
Exceptions
Randomly ordered or very small tables, where any method is acceptable.
Trade-offs
Row-level sampling scans the table, which costs more than block sampling.
Pitfalls
Sorting by random() over the whole table to take a sample, which is a full sort.
Lessons and concepts
Claims and their sources
In PostgreSQL LIMIT without ORDER BY returns an unpredictable subset, TABLESAMPLE SYSTEM samples blocks and may be less random because of clustering, and BERNOULLI scans the table and keeps each row independently.
Reservoir sampling selects k items uniformly at random from a stream of unknown length in one pass; Vitter's 1985 paper presents faster variants.