Data utilities: crosstab, table sampling and UUIDs

conceptThe concept

tablefunc provides crosstab(source_sql, category_sql), which turns (row, category, value) triples into one row per row key with a column per category, plus normal_rand and connectby. The output columns must be declared in the query, and the two-argument form places values by category so missing combinations become NULL instead of shifting.

TABLESAMPLE is built in: SYSTEM (p) picks whole pages with probability p percent (fast, clustered), BERNOULLI (p) picks each row (slower, uniform), and REPEATABLE (seed) makes the sample reproducible. The tsm_system_rows extension adds SYSTEM_ROWS (n) for a fixed number of rows, and tsm_system_time adds SYSTEM_TIME (ms).

UUIDs: gen_random_uuid() (version 4) has been built in since PostgreSQL 13, and PostgreSQL 18 adds uuidv7(), which starts with a timestamp so new values go to the right edge of a B-tree index. uuid-ossp is now only needed for v1, v3 and v5 UUIDs.

whyWhy it matters

Reporting needs pivots, data profiling needs samples of tables too large to scan, and every new schema picks a key type. Knowing the right small tool avoids slow workarounds such as scanning a billion rows to estimate a distribution or random UUIDv4 keys bloating indexes.

Use caseReal-world scenario

A finance report shows order counts per status with a column per year (crosstab). A data quality job checks 10000 random rows from a 2-billion-row events table in milliseconds with SYSTEM_ROWS. A new service uses uuidv7() primary keys so inserts stay index-friendly.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE EXTENSION tablefunc;
SELECT *
FROM crosstab(
  $$SELECT status, extract(year FROM created_at)::int::text, count(*)
    FROM orders GROUP BY 1, 2 ORDER BY 1, 2$$,
  $$VALUES ('2024'), ('2025')$$
) AS ct(status text, y2024 bigint, y2025 bigint);
ROLLBACK;

SELECT status,
       count(*) FILTER (WHERE created_at < '2025-01-01') AS y2024,
       count(*) FILTER (WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01') AS y2025
FROM orders
GROUP BY status
ORDER BY status;

BEGIN;
CREATE EXTENSION tsm_system_rows;
SELECT count(*) AS rows_sampled FROM orders TABLESAMPLE SYSTEM_ROWS (100);
SELECT status, count(*) AS n
FROM orders TABLESAMPLE BERNOULLI (10) REPEATABLE (42)
GROUP BY status ORDER BY n DESC;
ROLLBACK;

SELECT uuid_extract_version(gen_random_uuid()) AS v4,
       uuid_extract_version(uuidv7()) AS v7,
       uuid_extract_timestamp(uuidv7()) <= now() + interval '1 second' AS v7_has_time,
       uuidv7() < uuidv7() AS v7_sorts_by_time;

CREATE EXTENSION "uuid-ossp";
-- name-based, deterministic: the same namespace and name always give the same UUID
SELECT uuid_generate_v5(uuid_ns_url(), 'https://shop.example.com/products/42');
-- v1 embeds the MAC address and time: avoid for anything public

internalsUnder the hood

SYSTEM sampling chooses blocks, so rows on the same page come together: fast but not independent. BERNOULLI visits every page and flips a coin per row, so it costs a full scan of I/O. UUIDv4 values are random, so inserts touch random index leaf pages, causing more page splits, a bigger working set and more full-page images in WAL; UUIDv7's time prefix makes inserts append-like, similar to a bigint sequence.

Avoid / preferDo this, not that

CREATE TABLE event (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), ...);  -- billions of inserts

CREATE TABLE event (id uuid PRIMARY KEY DEFAULT uuidv7(), ...);

SELECT * FROM events ORDER BY random() LIMIT 100;

SELECT * FROM events TABLESAMPLE SYSTEM_ROWS (100);

PitfallsCommon mistakes

• Using the one-argument crosstab with gaps in the data, so values shift into the wrong columns.
• Treating TABLESAMPLE SYSTEM as a uniform random sample of rows; it samples pages.
• Installing uuid-ossp just for uuid_generate_v4 when gen_random_uuid is built in.
• Exposing uuidv7 identifiers publicly without considering that they reveal creation time.

LimitsWhen not to use it

Skip crosstab for dashboards where the BI tool pivots anyway, and skip sampling when you need exact counts for billing or compliance. Keep bigint identity keys where IDs never leave the database; UUIDs are for distributed generation and public identifiers.

SecuritySecurity notes

UUIDv7 encodes the creation timestamp, and v1 the host MAC address; neither is a secret token. For anything that must be unguessable, such as password reset links, use a random token from gen_random_bytes or gen_random_uuid and store only a hash of it.

ChallengeTry it yourself

BEGIN;
CREATE EXTENSION tablefunc;
SELECT *
FROM crosstab(
  $$SELECT 'all'::text, rating::text, count(*) FROM reviews GROUP BY rating ORDER BY rating$$,
  $$SELECT generate_series(1, 5)::text$$
) AS ct(scope text, r1 bigint, r2 bigint, r3 bigint, r4 bigint, r5 bigint);
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• CREATE EXTENSION
• Additional supplied modules
• Packaging related objects