TABLESAMPLE for random sampling in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TABLESAMPLE clause do in PostgreSQL?

It allows you to retrieve a random sample of rows from a table instead of the entire table, which is useful for quick analysis or testing.

beginner

How do you specify the size of the sample when using TABLESAMPLE?

You specify the sample size as a percentage depending on the sampling method, for example: TABLESAMPLE SYSTEM (10) samples approximately 10% of the table.

intermediate

What are the two main sampling methods supported by PostgreSQL's TABLESAMPLE?

The two main methods are SYSTEM and BERNOULLI. SYSTEM samples pages, while BERNOULLI samples rows individually.

intermediate

True or False: Using TABLESAMPLE SYSTEM guarantees the exact percentage of rows sampled.

False. SYSTEM sampling works at the page level, so the exact number of rows sampled can vary and is approximate.

beginner

Write a simple query to select approximately 5% random rows from a table named employees using TABLESAMPLE.

SELECT * FROM employees TABLESAMPLE SYSTEM (5);

Which TABLESAMPLE method samples rows individually in PostgreSQL?

A BERNOULLI

B SYSTEM

C RANDOM

D PAGE

BERNOULLI samples each row independently with the given probability, while SYSTEM samples pages.

What does TABLESAMPLE SYSTEM (10) do?

A Samples exactly 10 rows

B Samples approximately 10% of the table's rows

C Samples 10 random columns

D Samples 10 random tables

It samples approximately 10% of the table's rows by selecting pages randomly.

Can TABLESAMPLE guarantee the exact number of rows returned?

A Yes, always

B Only with BERNOULLI

C Only with SYSTEM

D No, it returns an approximate sample

Sampling methods return approximate results, not exact counts.

Which of the following is a valid TABLESAMPLE clause in PostgreSQL?

A TABLESAMPLE SYSTEM (20)

B TABLESAMPLE RANDOM (20)

C TABLESAMPLE ROWS (20)

D TABLESAMPLE PERCENT (20)

SYSTEM and BERNOULLI are valid methods; RANDOM, ROWS, and PERCENT are not.

Why might you use TABLESAMPLE in a query?

A To update all rows in a table

B To sort data alphabetically

C To speed up queries by working with a smaller random subset

D To create a new table

Sampling reduces data size for faster analysis or testing.

Explain how TABLESAMPLE works in PostgreSQL and the difference between SYSTEM and BERNOULLI methods.

Think about how data is stored in pages and how sampling can happen at different levels.

Describe a practical scenario where using TABLESAMPLE would be helpful.

Imagine you want to test a query or get a quick insight without scanning the whole table.