Learn to Generate Sample Data Using SQL

Author: Narendra Mandadapu
Posted: Jul 16, 2023

Learning to sample a subset of a table using SQL is a skill to master.

We have a total of 4 articles on this topic.

Learn to generate data using SQL
Learn to generate random data using SQL
Learn to generate sample data using SQL (You are reading now!)
Learn to split a dataset for testing and training using SQL

Extracting a small subset of a table is commonly referred to as sampling. There are several reasons to use sampling, such as:

Performing estimations on large datasets: When dealing with large tables, there are instances where we are willing to sacrifice precision for the sake of speed. By sampling a portion of the table, we can obtain quicker but less accurate results.
Producing a training set: During data analysis utilizing machine learning models, it is often crucial to train the model using a subset of the data known as a training set. Sampling the table allows us to generate this training set.

Sampling with LIMIT
Using TABLESAMPLE
- The SYSTEM sampling method:
- The BERNOULLI sampling method
Repeatable sampling
Performance comparison

Sampling with `LIMIT`

An dead simple approach to retrieve a random section of a table is by combining random with LIMIT:

# Sampling with LIMIT

WITH sample AS (
    SELECT *
    FROM products
    ORDER BY random()
    LIMIT 10000
)

SELECT count(*) FROM sample;

#output

 count
───────
 10000
(1 row)

Time: 205.765 ms

To obtain a sample of 10,000 random rows from the products table, follow these steps:

Randomly sort the table by utilising the random() function.
Retrieve the first 10,000 rows using the LIMIT 10000 clause.

It is important to note that this sampling method requires the database to sort the entire dataset and select the initial N rows. This method is suitable for smaller datasets, it may become highly inefficient and lead to excessive memory consumption and CPU usage when dealing with very large datasets.

Using `TABLESAMPLE`

PostgreSQL offers table sampling methods that are better suited for larger tables. To sample a table, employ the TABLESAMPLE keyword within the FROM clause and specify the sampling method along with its respective arguments.

The `SYSTEM` sampling method:

To utilise the SYSTEM sampling method and extract a 10% sample from the table, execute the following query:

# Using the SYSTEM sampling method

WITH sample AS (
    SELECT *
    FROM products TABLESAMPLE SYSTEM(10)
)
SELECT count(*) FROM sample;

#output

 count
───────
 95400
(1 row)

Time: 13.654 ms

The products table comprises 1 million rows, and the resulting sample yielded slightly fewer than 100,000 rows.

The SYSTEM sampling method operates by selecting blocks from the table. For instance, when sampling 10% from a table that is stored in 10 blocks, the SYSTEM sampling method only reads one block and retrieves the rows within it.

It is worth noting that when sampling 10% of the table using the SYSTEM sampling method, it proves significantly faster compared to the previous method employing random and LIMIT, completing in 205ms as opposed to 13ms. The SYSTEM sampling method operates at the storage level, resulting in its exceptional speed.

The `BERNOULLI` sampling method

Going forward, we have a choice, and we got one more sampling method called BERNOULLI. To sample 10% of the table using BERNOULLI, we can use the following query:

# Using the BERNOULLI sampling method

WITH sample AS (
    SELECT *
    FROM products TABLESAMPLE BERNOULLI(10)
)
SELECT count(*) FROM sample;

# output

 count
────────
 100364
(1 row)

Time: 54.663 ms

By utilising the BERNOULLI sampling method, we obtained 100,364 rows in 54ms. This method exhibits faster performance compared to random and LIMIT, but it is slower than the SYSTEM sampling method.

Unlike the SYSTEM sampling method, which selects a sample of the table blocks, the BERNOULLI sampling method scans the entire table and randomly selects rows. This implies that BERNOULLI is slower than SYSTEM, but it produces more evenly distributed random results.

Repeatable sampling

There are instances when it proves beneficial to generate the same sample from a specific dataset. This situation may arise when implementing an incremental rollout of an experiment, conducting A/B testing, or gradually enabling a feature flag.

Let's consider the following example:

# Creating a table to sample

DROP TABLE IF EXISTS products;

CREATE TABLE products AS
    SELECT n AS id, false AS blackfriday_sale
    FROM generate_series(1, 100000) AS n;

This will create a table with 100,000 products, each with a boolean field to indicate whether they are enrolled in the BlackFriday sales.

To gradually implement the blackfriday_sale, our initial step involves ensuring that none of the products are enrolled. This is achieved by setting the blackfriday_sale column to false for all products.

To enroll 5% of the products in the blackfriday_sale, execute the following UPDATE command:

# Enroll 5% of products to blackfriday_sale

UPDATE products
SET blackfriday_sale = true
WHERE NOT blackfriday_sale
AND id IN (
    SELECT id
    FROM products TABLESAMPLE BERNOULLI(5)
);

The command above uses the BERNOULLI sampling method to sample 5% of the products, and enroll them into the blackfriday_sale by setting blackfriday_sale = true.

Let's aim to expand the blackfriday_sale rollout to include 25% of the products. Given that we have already enrolled 5% in the blackfriday_sale, we need to enroll an additional 20%:

# Enroll 20% of products to blackfriday_sale, to reach a total of 25%

UPDATE products
SET blackfriday_sale = true
WHERE NOT blackfriday_sale
AND id IN (
    SELECT id
    FROM products TABLESAMPLE BERNOULLI(20)
);

Executing the above two commands will sample 25% of the products and enroll them in the blackfriday_sale if they are not already enrolled.

However, it is important to note that after executing the second query, there is no assurance that approximately 25% of the products will be enrolled in the blackfriday_sale.

# Attempting to enroll additional products into the blackfriday_sale

# 1. Add 5% to blackfriday_sale

UPDATE products
SET blackfriday_sale = true
WHERE NOT blackfriday_sale
AND id IN (
    SELECT id
    FROM products TABLESAMPLE BERNOULLI(5)
);

# 2. Add 20% to blackfriday_sale, to reach a total of 25%

UPDATE products
SET blackfriday_sale = true
WHERE NOT blackfriday_sale
AND id IN (
    SELECT id
    FROM products TABLESAMPLE BERNOULLI(20)
);

# check how many products are enrolled in the blackfriday sale

SELECT blackfriday_sale, COUNT(*)
FROM products
GROUP BY 1;

Output:

 blackfriday_sale	count
f	75898
t	24102

Observe that even after enrolling an additional 25% in the "blackfriday_sale," the overall count of enrolled products remains below 25%.

The BERNOULLI sampling method functions by randomly selecting rows if the second query happens to sample products already enrolled in the initial 5%; the resulting overall sample size will be less than 25%.

To be able to incrementally enroll products into the blackfriday_sale who are not already enrolled, we can use a technique similar to generating reproducible random data.

Similar to the random function, the BERNOULLI sampling method also accepts a seed value for sampling, utilising the REPEATABLE keyword:

# Enroll 5% of products to the blackfriday_sale using a seed value

# Enroll 5% to blackfriday_sale

UPDATE products
SET blackfriday_sale = true
WHERE NOT blackfriday_sale
AND id IN (
    SELECT id
    FROM products TABLESAMPLE BERNOULLI(5) REPEATABLE (5040)
);

# output:
UPDATE 5066

Executing this query will enroll 5% of the products in the blackfriday_sale. Have you noticed that we utilised BERNOULLI(5) REPEATABLE (5040) to specify a seed value for the random function. In this instance, the seed value is set as 5040, which can be any value.

If we repeat the execution of the same UPDATE command, using the same seed, it will sample the exact same rows. Should we execute the identical command again, it will not add any additional products.

# Enroll 5% to blackfriday_sale

UPDATE products
SET blackfriday_sale = true
WHERE NOT blackfriday_sale
AND id IN (
    SELECT id
    FROM products TABLESAMPLE BERNOULLI(5) REPEATABLE (5040)
);

# output: UPDATE 0

If we want to increase the rollout of the blackfriday sale to 25%, we can now simply set the sampling percent to 25%. Yes, that simple.

# Enroll 25% to blackfriday_sale

UPDATE products
SET blackfriday_sale = true
WHERE NOT blackfriday_sale
AND id IN (
    SELECT id
    FROM products TABLESAMPLE BERNOULLI(25) REPEATABLE (5040)
);

# output: UPDATE 19923

Since we applied a seed value for both UPDATE commands, the initial 5% of the products sampled are encompassed within the 25% that were sampled in the second command. YaY!

Performance comparison

Here is the timing for sampling 10% of a table containing 1M rows:

Sampling Method	Execution time
`random()`	205ms
`BERNOULLI`	54ms
`SYSTEM`	13ms

Which method should we choose?

If we are working with a small dataset where sample size is crucial, it is recommended to use random() in conjunction with LIMIT.

However, if we are dealing with a large dataset, it is advisable to start with the BERNOULLI method. Only consider switching to SYSTEM if the performance noticeably deteriorates.

Thanks a lot for reading. We have a total of 4 articles on this topic.

Learn to generate data using SQL
Learn to generate random data using SQL
Learn to generate sample data using SQL (You already read!)
Learn to split a dataset for testing and training using SQL

Post Tags:

Learn to Generate Sample Data Using SQL

Sampling with LIMIT

Using TABLESAMPLE

The SYSTEM sampling method: