How TABLESAMPLE picks rows

Most exploratory questions against a large table only need a rough answer, but without an index on status, even "roughly how many shipped orders" costs a full scan. On the two-million-row orders table, Postgres has to read every single 8 kB page, all 18,085 of them (141 MB), just to count matching rows.

On a primary server handling production traffic is shared with everything else.

When all you need is exploratory confirmation "roughly how many" or "are there any representative samples" reading the whole terabyte is too expensive.

SQL Standard comes with TABLESAMPLE to help with that.

It goes in the FROM clause right after the table name, and the rest of the query runs against the subset of pages it picks.

If you need an exact number of rows, tsm_system_rows in contrib adds a SYSTEM_ROWS (n) method.

The rows come back in physical order, which is why these five are neighbours, ids 429 to 433. And whatever you compute is computed over the sample, so totals have to be scaled back up by hand, count(*) and sum() times 100, while averages, ratios and extremes are read as they come.

For counts like this one it holds up.

The rows are inserted one per second in created_at order, though, so the oldest row sits at the very start of the table. Ask the same 1% sample for it: SELECT min(created_at) FROM orders TABLESAMPLE SYSTEM (1); Six runs: 2024-01-01 02:51:13+00 2024-01-01 01:36:19+00 2024-01-01 01:27:24+00 2024-01-01 04:41:47+00 2024-01-01 02:51:13+00 2024-01-01 00:44:36+00 The real answer is 00:00:01. The sample is off by anything from 44 minutes to almost five hours, and runs one and five agree to the second, which a sample of random rows would almost never do. Everything odd about TABLESAMPLE comes from one fact: it doesn't pick rows, it picks addresses. Setup Everything in this article was captured on PostgreSQL 18.6 using the postgres:18 image.shared_buffers = 1GB and a warm cache, so the timings are for comparing methods against each other, not for predicting yours. CREATE EXTENSION IF NOT EXISTS pageinspect; CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL, amount numeric(10,2) NOT NULL ); SELECT setseed(0.42); INSERT INTO orders (customer_id, status, created_at, amount) SELECT (random() * 50000)::bigint + 1, CASE WHEN i <= 1300000 THEN 'delivered' WHEN i <= 1800000 THEN 'shipped' WHEN i <= 1900000 THEN 'cancelled' ELSE 'pending' END, timestamptz '2024-01-01 00:00:00+00' + i * interval '1 second', round((random() * 500)::numeric, 2) FROM generate_series(1, 2000000) AS i; VACUUM ANALYZE orders; -- the same rows in random physical order CREATE TABLE orders_shuffled AS SELECT * FROM orders ORDER BY random(); VACUUM ANALYZE orders_shuffled; CREATE TABLE customers AS SELECT id FROM generate_series(1, 50001) AS id; ALTER TABLE customers ADD PRIMARY KEY (id); -- a small copy with room on every page, for the pageinspect step CREATE TABLE h (LIKE orders INCLUDING ALL) WITH (fillfactor = 70); INSERT INTO h OVERRIDING SYSTEM VALUE SELECT * FROM orders WHERE id <= 1000 ORDER BY id;Every row has an address The pages are numbered from 0, and right after its header each page keeps an array of line pointers, one per row, numbered from 1 (Inside PostgreSQL's 8KB Page takes one apart byte by byte). Page number and line pointer together are the row's ctid: SELECT ctid, id, created_at FROM orders ORDER BY id LIMIT 3; ctid | id | created_at -------+----+------------------------ (0,1) | 1 | 2024-01-01 00:00:01+00 (0,2) | 2 | 2024-01-01 00:00:02+00 (0,3) | 3 | 2024-01-01 00:00:03+00 Both built-in sampling methods hash part of that address together with a seed and keep whatever hashes under a 1% cutoff. They differ only in which part. SYSTEM, in src/backend/access/tablesample/system.c, hashes the page number (trimmed to the lines that matter): hashinput[0] = nextblock; /* page */ hashinput[1] = sampler->seed; hash = DatumGetUInt32(hash_any((const unsigned char *) hashinput, (int) sizeof(hashinput))); if (hash < sampler->cutoff) /* keep the whole page */ BERNOULLI, in bernoulli.c, hashes the page and the line pointer number: hashinput[0] = blockno; /* page */ hashinput[1] = tupoffset; /* line pointer */ hashinput[2] = sampler->seed; So SYSTEM flips one coin per page and takes every row on a winning page. BERNOULLI flips one coin per line pointer, which means it has to open every page to see how many line pointers there are. That's the whole performance story: on this table SYSTEM answers in 1.2 ms and BERNOULLI in 10.4 ms. Pages come as bundles A page here holds about 107 rows, and since the rows went in by time, one page is 107 consecutive seconds of orders. Group a SYSTEM sample by the page half of ctid and the bundles are right there: SELECT (ctid::text::point)[0]::int AS page, count(*) AS rows, min(created_at)::time AS first, max(created_at)::time AS last FROM orders TABLESAMPLE SYSTEM (1) REPEATABLE (7) GROUP BY 1 ORDER BY 1 LIMIT 4; page | rows | first | last ------+------+----------+---------- 4 | 107 | 00:07:09 | 00:08:55 38 | 107 | 01:07:47 | 01:09:33 57 | 107 | 01:41:40 | 01:43:26 265 | 107 | 07:52:36 | 07:54:22 The sample isn't 20,000 scattered seconds. It's about 200 short strips of the timeline, and min() returns the start of whichever strip the hash kept first. Runs one and five above kept the same first page. Shuffle the table and the problem disappears. orders_shuffled holds the same rows written in ORDER BY random() order, so every page is a handful of seconds from all over the three weeks, and the same query lands within a few minutes of the truth every time: 2024-01-01 00:00:06+00 2024-01-01 00:01:20+00 2024-01-01 00:01:43+00 2024-01-01 00:01:24+00 2024-01-01 00:02:50+00 2024-01-01 00:05:07+00 BERNOULLI on the original, time-ordered table does just as well, because a coin per line pointer never bundles anything. Nobody samples for a minimum, but the bundles hurt averages the same way. Across 200 seeds, avg(created_at) from a SYSTEM sample wanders ten times as far as the same average from BERNOULLI. The reason is plain once you think in pages: 107 rows from one page tell you about as much about the timeline as one row from that page would, so a SYSTEM sample of 20,000 rows has roughly the precision of a 200-row one. A column that has nothing to do with where rows sit, like a random amount, averages identically under both methods. Time columns, ever-increasing ids, and anything loaded in batches are the columns where it matters. REPEATABLE remembers addresses REPEATABLE (42) fixes the seed. Same seed, same hash, same addresses. Whether those addresses still hold the same rows depends on what happened to the table in between, and the address model predicts every line of this table. Each run captures a sample with seed 42, changes the table, captures again, and counts how many rows from the first sample are still in it: afterSYSTEM keptBERNOULLI kept append 200k rows17,595 of 17,59519,800 of 19,800 delete half the rows8,795 of 8,79510,003 of 10,003 VACUUM8,795 of 8,79510,003 of 10,003 UPDATE every row8,740 of 8,795109 of 10,003 VACUUM FULL113 of 8,795113 of 10,003 CLUSTER on a reversed index0 of 8,79595 of 10,003 Appends create new addresses and deletes empty old ones; no surviving row moves, so nothing changes. VACUUM marks dead line pointers unused and compacts the tuple data inside each page, but it never renumbers a line pointer that still points at a live row (VACUUM at the Page Level walks through each phase). VACUUM FULL and CLUSTER build a fresh copy of the table, every row gets a new address, and both samples are effectively redrawn.

UPDATE is the interesting line.