PostgreSQL's TABLESAMPLE samples addresses, not rows: why this can skew your analytical query

TABLESAMPLE promises fast answers on giant tables, but it works on physical page addresses, not on the rows themselves. Understanding this difference avoids wrong counts, averages, and test samples.

TABLESAMPLE promises fast answers on giant tables, but it works on physical page addresses, not on the rows themselves. Understanding this difference avoids wrong counts, averages, and test samples.

An exploratory question like "how many orders shipped" shouldn't require a full scan, but without an index on the column, that's exactly what PostgreSQL does. A technical article published on the boringSQL blog and republished on the Planet PostgreSQL aggregator uses a two-million-row orders table to show the cost of this: EXPLAIN (ANALYZE, BUFFERS) shows a read of 18,085 pages (141 MB) just to count rows with status = 'shipped'. On a 1 TB table, that means reading 134 million full pages from disk, even when the answer only needs to be "approximate".

The SQL standard has an answer for this kind of question: TABLESAMPLE. The clause goes right after the table name in the FROM, and the rest of the query runs only over the chosen subset of pages.

SELECT id, status, created_at, amount
FROM orders TABLESAMPLE SYSTEM (1) REPEATABLE (7)
LIMIT 5;

The value in parentheses is a percentage, not a fixed number of rows: in the author's material, five different seeds returned between 18,107 and 20,416 rows for the same 1. Anyone who needs an exact row count can turn to the tsm_system_rows contrib extension, which adds the SYSTEM_ROWS(n) method. And since the calculation runs over the sample, count(*) and sum() need to be scaled back up (by 100, in the case of 1%); averages, ratios, and extremes are read as they come.

What TABLESAMPLE Actually Chooses

The central point of the article, and the reason this piece deserves the attention of anyone running Postgres in production, is that neither of the two native methods chooses rows. Each 8 kB page stores, right after the header, an array of row pointers (one per record), and the pair page number + pointer number forms that row's ctid. SYSTEM and BERNOULLI hash different parts of that address:

• SYSTEM: hashes only the page number plus the seed. If the hash falls below the cutoff, the entire page enters the sample. One "coin flip" per page, none per row.
• BERNOULLI: hashes the page number, the row pointer, and the seed. One "coin flip" per row, which forces PostgreSQL to open every page to count how many pointers it has.

This difference in mechanism explains the cost observed in the article: on the test table, SYSTEM responded in 1.2 ms and BERNOULLI in 10.4 ms. SYSTEM is fast because it decides by whole page; BERNOULLI is more expensive because it always visits every page, even the ones it discards almost entirely.

Why the Count Works and the Minimum Doesn't

Redoing the count of shipped orders with TABLESAMPLE SYSTEM (1), the execution plan reads 177 pages instead of 18,085, and the scaled result (492,000) comes close to the real value (500,000). So far, TABLESAMPLE delivers on its promise.

The problem shows up when the sampled column correlates with the physical order of the rows. In the test table, records were inserted in created_at order, one per second, so each page of roughly 107 rows corresponds to 107 consecutive seconds. Asking min(created_at) of a SYSTEM (1) sample six times returned answers between 44 minutes and nearly five hours away from the real value (2024-01-01 00:00:01), because the sample isn't made up of 20 thousand scattered seconds: it's about 200 short, continuous stretches of the timeline, one per sampled page.

The author also shows that this bias disappears when the table is physically shuffled (a CREATE TABLE ... AS SELECT * FROM orders ORDER BY random()): with each page containing seconds scattered across the full three weeks, the same query returns to within minutes of the truth on every run. BERNOULLI on the original, time-ordered table also doesn't suffer the same problem, because a coin flip per row pointer never groups anything.

In summary: SYSTEM is faster, but any column correlated with the physical insertion order (an increasing timestamp, a sequential id, batch-loaded data) comes out skewed. For columns with no relation to physical position, such as a random monetary value, both approaches converge on the same result.

REPEATABLE Fixes the Address, Not the Row

REPEATABLE (42) fixes the seed, which fixes the hash and, therefore, the sampled addresses. What happens to the rows that occupied those addresses depends on what the table goes through afterward, and the article tests this against a sequence of maintenance operations:

Inserts and deletes don't move surviving rows, so nothing changes. VACUUM marks dead pointers as free and compacts the data within the page, but it never renumbers a pointer that still points to a live row. VACUUM FULL and CLUSTER rebuild the entire table; every address changes, and both samples are essentially redrawn from scratch.

The UPDATE case is the most revealing for anyone dealing with write-heavy workloads. PostgreSQL never overwrites a row in place: it writes a new version, and when the page has room and no indexed column changed, that's a HOT update, which doesn't touch any index. Since the new version stays on the same page, SYSTEM doesn't even notice the swap. But the new version gets a new row pointer, and that pointer is a fresh coin flip for BERNOULLI, which discards almost all the updated rows and draws others in their place.

When Stability Matters, Hash the Key

For anyone who needs a development or test subset that stays the same day after day, an address can't provide that guarantee, because updates and rebuilds move rows. A primary key doesn't move, so the way to go is to hash the key directly, outside of TABLESAMPLE:

SELECT * FROM orders
WHERE (hashint8extended(id, 42) & 1023) < 10;

hashint8extended hashes a bigint with a seed; keeping 10 of 1,024 buckets returns about 0.98% of the rows. In the article's test, this approach kept every surviving row at each step (9,916 of 9,916 after deletion, UPDATE, VACUUM FULL, and CLUSTER included), and its spread across 200 seeds on avg(created_at) matched that of BERNOULLI, without the page-grouping bias.

The cost is a full scan, since the hash has to be calculated for every row: 44.4 ms serially, 21.5 ms with two parallel workers in the tested material. For a subset queried frequently, an expression index on (hashint8extended(id, 42) & 1023) (14 MB on the test table) turns this into an 8.5 ms bitmap scan, with the seed fixed in the index definition itself.

Joins: The Sample Doesn't Propagate

TABLESAMPLE applies to a single table in the FROM; it can't be applied to a join or a subquery (it's a syntax error). Sampling only the orders side of a join with customers speeds up the scan of that table (202 pages instead of 18,085), but the hash over customers still reads all 222 of its pages, because TABLESAMPLE only shrinks the table it's written on.

In the article's example, the full query got 3.3 times faster, not 90 times, because the rest of the cost sits in the table the sample never touched. The sample scan also always runs on the leader process: the plan loses parallelism, no matter how large the table is.

Sampling both sides of the join at the same time seems like the obvious answer, but the author shows it's nearly useless: out of a real two-million-row join, independently sampling orders and customers with SYSTEM (1) kept only 288 pairs, because a sampled row only finds its match when both page samples happen to coincide for that record. When a pair of sampled tables needs to remain joinable by foreign key, hashing the foreign key on both sides, as in the technique from the previous section, is the way to preserve the relationship.

Which One to Use, and When to Use Neither

The article sums up the choice in three decision lines, and it's worth reproducing them because they cover most real-world cases:

• SYSTEM: for a quick number over a column with no relation to the physical insertion order.
• BERNOULLI: when that relation might exist and reading the whole table once is acceptable.
• Hashed key (outside of TABLESAMPLE): when the subset needs to stay the same tomorrow, or needs to keep foreign keys intact across tables.

None of the three works for a filtered subset: the sample is chosen before the WHERE runs, so the filter only trims a draw that's already been made, which explains why count(DISTINCT customer_id) over a 1% sample never recovers customers whose orders all landed on losing pages. It's also worth remembering that ANALYZE itself builds the pg_stats statistics from a block sampler with a seed the user doesn't control, which explains why plans shift slightly between runs even with no change to the data.