TABLESAMPLE for random sampling in PostgreSQL - Step-by-Step Execution

Concept Flow - TABLESAMPLE for random sampling

Start Query

↓

Identify Table

↓

Apply TABLESAMPLE Method

↓

Randomly Select Rows Based on Sample Size

↓

Return Sampled Rows

↓

End Query

The query starts by identifying the table, then applies the TABLESAMPLE method to randomly select a portion of rows, and finally returns those sampled rows.

Execution Sample

PostgreSQL

SELECT * FROM employees TABLESAMPLE SYSTEM (10);

This query selects approximately 10% of rows randomly from the employees table using the SYSTEM sampling method.

Execution Table

Step	Action	Evaluation	Result
1	Start Query	Parse SQL statement	Ready to execute
2	Identify Table	Table: employees	Table located
3	Apply TABLESAMPLE	Method: SYSTEM, Percentage: 10%	Sampling method set
4	Randomly Select Rows	Randomly pick ~10% rows	Subset of rows chosen
5	Return Sampled Rows	Output sampled rows	Rows returned to user
6	End Query	Query execution complete	Finished

💡 Query ends after returning the sampled rows from the employees table.

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Table	None	employees	employees	employees
Sampling Method	None	SYSTEM (10%)	SYSTEM (10%)	SYSTEM (10%)
Sampled Rows	None	None	Subset (~10%)	Subset (~10%)

Key Moments - 3 Insights

Why does the number of rows returned by TABLESAMPLE vary each time?

Because TABLESAMPLE uses random selection (see execution_table step 4), the exact rows and count can differ on each execution.

What does SYSTEM (10) mean in TABLESAMPLE?

It means approximately 10% of the table's rows are randomly selected using the SYSTEM method (execution_table step 3).

Does TABLESAMPLE guarantee exactly 10% rows?

No, it returns an approximate percentage, not an exact count, due to the random nature of sampling (execution_table step 4).

Visual Quiz - 3 Questions

Look at the execution_table, at which step are the rows randomly selected?

A Step 2

B Step 5

C Step 4

D Step 3

💡 Hint

Check the 'Action' column in execution_table for the step describing random row selection.

According to variable_tracker, what is the value of Sampling Method after Step 3?

A None

B SYSTEM (10%)

C Subset (~10%)

D employees

💡 Hint

Look at the Sampling Method row under 'After Step 3' in variable_tracker.

If you change SYSTEM (10) to SYSTEM (50), what happens to Sampled Rows in variable_tracker?

A Sampled Rows become a larger subset (~50%)

B Sampled Rows become a smaller subset

C Sampled Rows remain None

D Sampling Method changes to SYSTEM (10%)

💡 Hint

Consider how increasing the percentage affects the size of the sampled subset in variable_tracker.

Concept Snapshot

TABLESAMPLE lets you pick a random sample of rows from a table.
Syntax: SELECT * FROM table TABLESAMPLE method (percentage);
SYSTEM method picks approx percentage of rows randomly.
Result varies each time due to randomness.
Useful for quick data checks or testing.

Full Transcript

This visual execution trace shows how the TABLESAMPLE clause works in PostgreSQL. The query starts by parsing and locating the table. Then it applies the TABLESAMPLE SYSTEM method with a specified percentage, for example 10%. The database engine randomly selects approximately that percentage of rows from the table. These sampled rows are then returned as the query result. Because the selection is random, the exact rows and count can vary each time the query runs. The variable tracker shows the table name, sampling method, and the subset of sampled rows changing through the steps. Key moments clarify that TABLESAMPLE returns an approximate sample, not an exact count, and explain the meaning of SYSTEM (percentage). The quiz questions help reinforce understanding of the steps and variable changes during execution.

Practice

1.

What is the main purpose of using TABLESAMPLE in a PostgreSQL query?

easy

A. To retrieve a random subset of rows from a table

B. To sort the table rows in random order

C. To permanently delete random rows from a table

D. To create a backup of the table

Solution

1. Step 1: Understand the purpose of TABLESAMPLE

   TABLESAMPLE is used to get a random sample of rows from a table without scanning the entire table.

2. Step 2: Compare options with TABLESAMPLE usage

   Only To retrieve a random subset of rows from a table describes retrieving a random subset of rows, which matches TABLESAMPLE's purpose.

3. Final Answer:

   To retrieve a random subset of rows from a table -> Option A

4. Quick Check:

   TABLESAMPLE = random subset retrieval [OK]

Hint: TABLESAMPLE returns random rows, not sorting or deleting [OK]

Common Mistakes:

• Confusing TABLESAMPLE with ORDER BY RANDOM()
• Thinking TABLESAMPLE deletes rows
• Assuming TABLESAMPLE creates backups

2.

Which of the following is the correct syntax to sample approximately 10% of rows from a table named employees using the BERNOULLI method?

easy

A. SELECT * FROM employees TABLESAMPLE BERNOULLI(10);

B. SELECT * FROM employees TABLESAMPLE SYSTEM(10);

C. SELECT * FROM employees SAMPLE BERNOULLI(10);

D. SELECT * FROM employees TABLESAMPLE BERNOULLI 10;

Solution

1. Step 1: Recall correct TABLESAMPLE syntax

   The correct syntax uses TABLESAMPLE followed by the method name and percentage in parentheses.

2. Step 2: Check each option

   SELECT * FROM employees TABLESAMPLE BERNOULLI(10); matches the correct syntax exactly. SELECT * FROM employees TABLESAMPLE SYSTEM(10); uses SYSTEM instead of BERNOULLI. SELECT * FROM employees SAMPLE BERNOULLI(10); misses TABLESAMPLE keyword. SELECT * FROM employees TABLESAMPLE BERNOULLI 10; misses parentheses around 10.

3. Final Answer:

   SELECT * FROM employees TABLESAMPLE BERNOULLI(10); -> Option A

4. Quick Check:

   Correct TABLESAMPLE syntax = SELECT * FROM employees TABLESAMPLE BERNOULLI(10); [OK]

Hint: Use TABLESAMPLE METHOD(percentage) with parentheses [OK]

Common Mistakes:

• Omitting TABLESAMPLE keyword
• Using wrong method name
• Missing parentheses around percentage

3.

Given a table products with 1000 rows, what is the expected approximate number of rows returned by this query?

SELECT * FROM products TABLESAMPLE SYSTEM(5);

medium

A. Exactly 5 rows

B. About 50 rows

C. About 500 rows

D. All 1000 rows

Solution

1. Step 1: Understand SYSTEM sampling percentage

   SYSTEM(5) means approximately 5% of rows are sampled.

2. Step 2: Calculate expected rows from 1000 total

   5% of 1000 is 0.05 * 1000 = 50 rows approximately.

3. Final Answer:

   About 50 rows -> Option B

4. Quick Check:

   5% of 1000 = 50 [OK]

Hint: Percentage in TABLESAMPLE is approximate row count percent [OK]

Common Mistakes:

• Confusing percentage with exact row count
• Assuming SYSTEM returns fewer rows than BERNOULLI always
• Thinking TABLESAMPLE returns all rows

4.

Identify the error in this query that tries to sample 20% of rows using BERNOULLI:

SELECT * FROM orders TABLESAMPLE BERNOULLI 20;

medium

A. TABLESAMPLE keyword is misspelled

B. BERNOULLI method cannot be used with TABLESAMPLE

C. Percentage value must be between 0 and 1

D. Missing parentheses around the percentage value

Solution

1. Step 1: Check TABLESAMPLE BERNOULLI syntax

   The percentage must be inside parentheses, like BERNOULLI(20).

2. Step 2: Identify the error in the query

   The query misses parentheses around 20, causing syntax error.

3. Final Answer:

   Missing parentheses around the percentage value -> Option D

4. Quick Check:

   Percentage needs parentheses in TABLESAMPLE [OK]

Hint: Always put percentage in parentheses after method name [OK]

Common Mistakes:

• Omitting parentheses around percentage
• Using invalid percentage range
• Misspelling TABLESAMPLE keyword

5.

You want to quickly test a query on a large table sales but need a random sample that is more uniformly random than SYSTEM sampling. Which query is best?

Options:
A) SELECT * FROM sales TABLESAMPLE SYSTEM(10);
B) SELECT * FROM sales TABLESAMPLE BERNOULLI(10);
C) SELECT * FROM sales ORDER BY RANDOM() LIMIT 100;
D) SELECT * FROM sales TABLESAMPLE SYSTEM(100);

hard

A. Use SYSTEM sampling for speed but less randomness

B. Use ORDER BY RANDOM() for perfect randomness but slower

C. Use BERNOULLI sampling for better randomness

D. Use SYSTEM sampling with 100% to get all rows

Solution

1. Step 1: Compare randomness and speed of methods

   SYSTEM is faster but less random; BERNOULLI is slower but more uniformly random.

2. Step 2: Choose best method for uniform randomness and speed

   BERNOULLI(10) gives a good balance of randomness and reasonable speed for sampling.

3. Final Answer:

   Use BERNOULLI sampling for better randomness -> Option C

4. Quick Check:

   BERNOULLI = better randomness than SYSTEM [OK]

Hint: BERNOULLI is more random; SYSTEM is faster [OK]

Common Mistakes:

• Choosing SYSTEM when better randomness is needed
• Using ORDER BY RANDOM() on large tables (slow)
• Misunderstanding 100% SYSTEM returns all rows