Beginner LevelPostgreSQL
How does TABLESAMPLE (SYSTEM, BERNOULLI) execute fast table sampling?
❓ Interview Prompt: Explain `TABLESAMPLE`, `SYSTEM` sampling (page-level fast sampling), `BERNOULLI` sampling (row-level uniform random), and seed configuration.
The 'TABLESAMPLE' clause returns a random sample of rows from a table:
•
'SYSTEM (percentage)': Samples entire physical 8KB data pages at random (fastest, but clustered results!).
•
'BERNOULLI (percentage)': Evaluates every row individually with a fixed probability (slower, but uniform random sampling!).
Code Implementation Example
sql
-- Fast page-level sampling of 5% of table rows
SELECT * FROM large_audit_logs TABLESAMPLE SYSTEM(5);
-- Uniform row-level sampling of 1% of table rows with fixed seed
SELECT * FROM large_audit_logs TABLESAMPLE BERNOULLI(1) REPEATABLE(42);💡Sampling table data using TABLESAMPLE SYSTEM and BERNOULLI methods.
🎯 Interview Key Takeaway
Use TABLESAMPLE SYSTEM for instant page-level data sampling on massive multi-gigabyte tables.
#TABLESAMPLE#SYSTEM#BERNOULLI#Sampling#Beginner
Back to all PostgreSQL Questions→