In this post we are going to cover partitioning, VACUUM and maintenance in PostgreSQL: dead tuples, bloat, autovacuum, partition pruning, retention, transaction ID wraparound and disk usage. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.
Postgres needs housekeeping, because of the way MVCC works: old row versions stay in the table until something removes them. Most production incidents with Postgres, a table ten times bigger than its data, a disk that fills up, a database that refuses writes, come from this one topic. Every size and count in this post was measured, not estimated.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 10 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.
- Let's create some sample data
- Question 1: A nightly job updates the status of every row in a table. The row count never changes, but the table file doubled in size. Where did the space go?
- Question 2: After that job, you run VACUUM, but the table is still 18 megabytes on disk. A colleague suggests VACUUM FULL. What is the difference between VACUUM, VACUUM FULL and ANALYZE, and which do you run in production?
- Question 3: Autovacuum is on, but one busy table keeps bloating. You even run VACUUM by hand, and the dead rows are still there. What do you check?
- Question 4: A measurements table has billions of rows, and almost every query asks for one month. How does partitioning help, and how do you prove that Postgres reads only the partitions it needs?
- Question 5: You partitioned the table by month. Now three things surprise the team: the primary key on ID is rejected, an insert for next month fails, and deleting last year's data still takes hours. What is going on?
- Question 6: Monitoring raises an alert: transaction ID age is over one billion on the main database. The team has never heard of it. What is transaction ID wraparound, and why is it serious?
- Question 7: The disk on the database server is 85 percent full, and it grows every week. How do you find out which tables and indexes are using the space?
- Key points to remember
- The full SQL script
Let's create some sample data
This post uses a tickets table with 100,000 rows, where autovacuum is switched off so we can run every maintenance step by hand, and a measurements table that is partitioned by month. The pgstattuple extension gives exact counts of live and dead rows. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.
CREATE EXTENSION IF NOT EXISTS pgstattuple; -- exact live and dead row counts
-- 100,000 support tickets. Autovacuum is switched off for this table,
-- so that we can see every step by hand.
CREATE TABLE tickets WITH (autovacuum_enabled = false) AS
SELECT g AS ticket_id, 'open' AS status, repeat('x', 50) AS notes
FROM generate_series(1, 100000) AS g;
ALTER TABLE tickets ADD PRIMARY KEY (ticket_id);
ANALYZE tickets;
-- sensor readings, partitioned by month
CREATE TABLE measurements (
id BIGINT,
logdate DATE NOT NULL,
device INT,
reading NUMERIC(6,2)
) PARTITION BY RANGE (logdate);
CREATE TABLE measurements_2026_04 PARTITION OF measurements FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE measurements_2026_05 PARTITION OF measurements FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE measurements_2026_06 PARTITION OF measurements FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
INSERT INTO measurements
SELECT g, DATE '2026-04-01' + (g % 91), g % 50, (g % 400) / 10.0
FROM generate_series(1, 91000) AS g;
ANALYZE measurements;Question 1: A nightly job updates the status of every row in a table. The row count never changes, but the table file doubled in size. Where did the space go?
Into dead tuples. Because of MVCC, an UPDATE writes a new version of the row and leaves the old one in place, in case another transaction still needs it. Updating every row means two copies of every row until VACUUM cleans up.
Picture the table file as pages full of rows. 100,000 live rows fill the file. Now update every row. Postgres cannot overwrite them, so it adds 100,000 new versions at the end of the file. The old versions are now dead. Nobody will ever read them again, but they still occupy half of the file.
Let's measure it. Before the job: 100,000 live rows, zero dead rows, and the table is about 9 megabytes.
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');| live_rows | dead_rows | table_size | free_space |
|---|---|---|---|
| 100000 | 0 | 9216 kB | 81 kB |
Now the nightly job: one UPDATE that touches every row. And measure again. Still 100,000 live rows, but also 100,000 dead rows, and the table is 18 megabytes.
UPDATE tickets SET status = 'closed';SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');| live_rows | dead_rows | table_size | free_space |
|---|---|---|---|
| 100000 | 100000 | 18 MB | 153 kB |
A DELETE behaves the same way. It only marks rows as dead, so the file does not shrink. This is normal Postgres behaviour, and cleaning it up is the job of VACUUM. That is the next question.
DELETE also leaves dead tuples. The file does not shrink by itself.
Question 2: After that job, you run VACUUM, but the table is still 18 megabytes on disk. A colleague suggests VACUUM FULL. What is the difference between VACUUM, VACUUM FULL and ANALYZE, and which do you run in production?
VACUUM removes dead tuples and marks their space as reusable inside the file, without blocking reads or writes. VACUUM FULL rewrites the whole table into a new, smaller file, but holds an exclusive lock the entire time. ANALYZE only refreshes planner statistics.
Three commands, three jobs. Plain VACUUM cleans out the dead rows. The file keeps its size, but the freed space is used by the next inserts and updates. The table stays online. VACUUM FULL copies the live rows into a brand new file and deletes the old one. The disk space goes back to the operating system, but nobody can read or write the table while it runs. And ANALYZE does not clean anything. It samples the table and updates the statistics for the planner.
| VACUUM | VACUUM FULL | ANALYZE | |
|---|---|---|---|
| Dead tuples | removed | removed | not touched |
| File size on disk | same, space reusable | shrinks | same |
| Lock | reads and writes continue | ACCESS EXCLUSIVE: table offline | reads and writes continue |
| Extra disk needed | no | a full copy of the table | no |
| Use it | routinely (autovacuum) | rarely, in a maintenance window | after big data changes |
Run a plain VACUUM on our table. The dead rows are gone: zero. The table is still 18 megabytes, but look at the free space: 9 megabytes inside the file, ready to be reused.
VACUUM tickets;SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');| live_rows | dead_rows | table_size | free_space |
|---|---|---|---|
| 100000 | 0 | 18 MB | 9133 kB |
Now VACUUM FULL. The table is back to 9 megabytes, and the free space is almost nothing. The file was rewritten.
VACUUM FULL tickets;SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');| live_rows | dead_rows | table_size | free_space |
|---|---|---|---|
| 100000 | 0 | 9096 kB | 81 kB |
In production, plain VACUUM, through autovacuum, is the daily tool. A table that is the same size every day does not need shrinking. If you really must give space back on a live system, use an online tool like pg_repack instead of VACUUM FULL.
Shrink a table online: pg_repack. VACUUM FULL = downtime for that table.
Question 3: Autovacuum is on, but one busy table keeps bloating. You even run VACUUM by hand, and the dead rows are still there. What do you check?
Two things. First the trigger: by default autovacuum waits until 20 percent of the table is dead, which is far too late for a huge table, so lower it per table. Second, who holds an old snapshot: VACUUM cannot remove a row version that a long-running transaction might still see.
When does autovacuum start on a table? When the dead rows pass a threshold: 50 rows plus 20 percent of the table. For a table with 100 million rows, that means 20 million dead rows before anything happens. So for big, hot tables, set a much smaller scale factor on that table only. And the second cause: a transaction that has been open for hours. Every row version newer than its snapshot must be kept, so VACUUM runs, but removes nothing.
First the settings. Turn autovacuum back on for this table, with a scale factor of 1 percent and a threshold of 1,000 rows. The catalog shows the options stored on the table.
ALTER TABLE tickets SET (
autovacuum_enabled = true,
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000
);SELECT unnest(reloptions) AS table_setting
FROM pg_class
WHERE relname = 'tickets';| table_setting |
|---|
| autovacuum_enabled=true |
| autovacuum_vacuum_scale_factor=0.01 |
| autovacuum_vacuum_threshold=1000 |
Now the blocker. Session B opens a Repeatable Read transaction and reads the table. Its snapshot is now frozen. Session A updates 20,000 tickets and runs VACUUM. Look: 20,000 dead rows are still there. VACUUM ran, but it was not allowed to remove them.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM tickets;UPDATE tickets SET status = 'reopened'
WHERE ticket_id <= 20000;SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');| live_rows | dead_rows | table_size | free_space |
|---|---|---|---|
| 100000 | 20000 | 11 MB | 93 kB |
Session B commits, we vacuum again, and now the dead rows are gone.
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');| live_rows | dead_rows | table_size | free_space |
|---|---|---|---|
| 100000 | 0 | 11 MB | 1889 kB |
So when a table bloats, look for the oldest transaction in pg_stat_activity, for sessions that are idle in transaction, for forgotten replication slots, and for prepared transactions. Any one of them can hold cleanup back for the whole database.
Question 4: A measurements table has billions of rows, and almost every query asks for one month. How does partitioning help, and how do you prove that Postgres reads only the partitions it needs?
Partition the table by range on the date. Each month becomes its own smaller table. When the query filters on the partition key, the planner prunes the partitions that cannot match, and EXPLAIN shows only the ones that are scanned.
One logical table, three physical ones. Rows are routed to a partition by their date. A query for June carries a filter on the partition key. The planner compares it with the bounds of each partition, and throws April and May out of the plan before the query even starts. Only June is read.
SELECT ... FROM measurements WHERE logdate >= '2026-06-01' AND logdate < '2026-07-01'Here is how the rows are spread. The hidden column tableoid tells us which partition each row lives in: about 30,000 rows per month.
SELECT tableoid::regclass AS partition,
COUNT(*) AS rows
FROM measurements
GROUP BY 1
ORDER BY 1;| partition | rows |
|---|---|
| measurements_2026_04 | 30000 |
| measurements_2026_05 | 31000 |
| measurements_2026_06 | 30000 |
Now the June query. The plan has exactly one scan, on the June partition. April and May do not appear at all.
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM measurements
WHERE logdate >= DATE '2026-06-01'
AND logdate < DATE '2026-07-01';Aggregate
-> Seq Scan on measurements_2026_06 measurements
Filter: ((logdate >= '2026-06-01'::date) AND (logdate < '2026-07-01'::date))But filter on the device, which is not the partition key, and Postgres has to scan all three partitions. Partitioning only helps queries that filter on the key.
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM measurements
WHERE device = 7;Aggregate
-> Append
-> Seq Scan on measurements_2026_04 measurements_1
Filter: (device = 7)
-> Seq Scan on measurements_2026_05 measurements_2
Filter: (device = 7)
-> Seq Scan on measurements_2026_06 measurements_3
Filter: (device = 7)So choose the partition key from your most common WHERE clause, and from how you expire data. And do not over-partition. Thousands of tiny partitions slow down planning. A partition should hold millions of rows, not thousands.
Question 5: You partitioned the table by month. Now three things surprise the team: the primary key on ID is rejected, an insert for next month fails, and deleting last year's data still takes hours. What is going on?
A unique or primary key on a partitioned table must include the partition key. A row with no matching partition is rejected, unless a DEFAULT partition exists. And old data should be removed by detaching and dropping the partition, which is instant, not by DELETE.
Three rules. Each partition has its own index, and Postgres cannot check uniqueness across partitions, so the key must contain the partition column. Every row needs a home. If no partition covers its date, the insert fails. Create partitions ahead of time. And retention is where partitioning shines. Dropping a partition removes a whole month as one file operation: no row-by-row delete, no dead tuples, no vacuum.
PRIMARY KEY (id, logdate) ✓
create partitions ahead, or a DEFAULT
DROP partition: instant
Rule one. A primary key on ID alone is rejected. Add the partition column, and it is accepted.
ALTER TABLE measurements
ADD PRIMARY KEY (id);ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "measurements" lacks column "logdate" which is part of the partition key.ALTER TABLE measurements
ADD PRIMARY KEY (id, logdate);Result: ALTER TABLE
Rule two. Insert a reading for July. There is no July partition, so the row is refused.
INSERT INTO measurements
VALUES (999999, DATE '2026-07-15', 1, 20.5);ERROR: no partition of relation "measurements" found for row
DETAIL: Partition key of the failing row contains (logdate) = (2026-07-15).Rule three. Retention for April: detach the partition, then drop it. Two quick statements, and 30,000 rows are gone. Only May and June are left.
ALTER TABLE measurements
DETACH PARTITION measurements_2026_04;
DROP TABLE measurements_2026_04;SELECT tableoid::regclass AS partition, COUNT(*) AS rows
FROM measurements GROUP BY 1 ORDER BY 1;| partition | rows |
|---|---|
| measurements_2026_05 | 31000 |
| measurements_2026_06 | 30000 |
On a busy system, detach with the CONCURRENTLY option so queries are not blocked. And automate the creation of future partitions, with a scheduled job or the pg_partman extension, so rule two never wakes you up at midnight on the first of the month.
DETACH PARTITION ... CONCURRENTLY on busy tables. Automate new partitions (pg_partman).
Question 6: Monitoring raises an alert: transaction ID age is over one billion on the main database. The team has never heard of it. What is transaction ID wraparound, and why is it serious?
Transaction IDs are 32-bit numbers, so about 4 billion, used in a circle. A row is visible only to transactions after the one that created it, so very old rows must be frozen by VACUUM before the counter comes around. If that never happens, Postgres stops accepting writes to protect the data.
Think of the transaction counter as a clock face. Every row remembers the transaction that created it. Rows about two billion transactions behind the current one would suddenly look like they are in the future, and vanish. To prevent that, VACUUM freezes old rows: it marks them as visible to everyone, forever. Autovacuum forces this when a table's age reaches 200 million. If freezing is blocked for too long, Postgres first warns, and near the limit it refuses new write transactions until you vacuum.
(autovacuum_freeze_max_age)
vacuum this database
until VACUUM completes
Here is the number to monitor. The age of each database's frozen transaction ID. On this fresh server the ages are tiny. In production, alert long before one billion.
SELECT datname,
age(datfrozenxid) AS xid_age
FROM pg_database
WHERE datname = 'postgres' OR datistemplate
ORDER BY datname;| datname | xid_age |
|---|---|
| postgres | 1446 |
| template0 | 1446 |
| template1 | 1446 |
And per table, to find which table is holding the database back.
SELECT c.relname,
age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind = 'r'
AND c.relnamespace = 'public'::regnamespace
ORDER BY xid_age DESC
LIMIT 5;| relname | xid_age |
|---|---|
| measurements_2026_05 | 16 |
| measurements_2026_06 | 15 |
| tickets | 4 |
The usual causes are the same blockers as before: a transaction open for days, an abandoned replication slot, a prepared transaction, or autovacuum switched off on a big table. Fix the blocker, then run VACUUM FREEZE on the oldest tables.
Alert on age(datfrozenxid). Fix the blocker, then VACUUM FREEZE the oldest tables.
Question 7: The disk on the database server is 85 percent full, and it grows every week. How do you find out which tables and indexes are using the space?
Ask the catalog. pg_total_relation_size returns a table with its indexes and TOAST data, pg_relation_size the table alone, and pg_indexes_size its indexes. Sort by the total, and compare the table part with the index part.
One table on disk is really three things. The heap: the table's own rows. The indexes, which together can be bigger than the table. And TOAST, the side storage for large values like long text and JSON. The total relation size adds all three, and that is the number that matters for the disk.
pg_relation_size
pg_indexes_size
Here is the query I keep at hand. For every table in the schema: the total, the table alone, and the indexes, largest first.
SELECT c.relname AS table_name,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
pg_size_pretty(pg_relation_size(c.oid)) AS table_only,
pg_size_pretty(pg_indexes_size(c.oid)) AS indexes
FROM pg_class c
WHERE c.relkind = 'r'
AND c.relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 5;| table_name | total | table_only | indexes |
|---|---|---|---|
| tickets | 13 MB | 11 MB | 2648 kB |
| measurements_2026_05 | 2576 kB | 1584 kB | 968 kB |
| measurements_2026_06 | 2496 kB | 1536 kB | 936 kB |
Read the result like this. A table whose indexes are larger than its data probably has unused or duplicate indexes. A table that is far bigger than its row count explains is bloated, so check its dead rows and its autovacuum settings. And a table that simply has too much history needs a retention plan, which usually means partitions you can drop.
Key points to remember
- UPDATE and DELETE leave dead tuples. VACUUM makes that space reusable
- VACUUM FULL shrinks the file but locks the table. Plain VACUUM does not
- Tune autovacuum per table, and hunt long transactions that block cleanup
- Partition by the column you filter and expire by, and drop old partitions
- Monitor age(relfrozenxid) and the largest relations before they become incidents
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- PostgreSQL Interview Q&A #10 - Partitioning, VACUUM and maintenance
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================
-- Sample data
CREATE EXTENSION IF NOT EXISTS pgstattuple; -- exact live and dead row counts
-- 100,000 support tickets. Autovacuum is switched off for this table,
-- so that we can see every step by hand.
CREATE TABLE tickets WITH (autovacuum_enabled = false) AS
SELECT g AS ticket_id, 'open' AS status, repeat('x', 50) AS notes
FROM generate_series(1, 100000) AS g;
ALTER TABLE tickets ADD PRIMARY KEY (ticket_id);
ANALYZE tickets;
-- sensor readings, partitioned by month
CREATE TABLE measurements (
id BIGINT,
logdate DATE NOT NULL,
device INT,
reading NUMERIC(6,2)
) PARTITION BY RANGE (logdate);
CREATE TABLE measurements_2026_04 PARTITION OF measurements FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE measurements_2026_05 PARTITION OF measurements FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE measurements_2026_06 PARTITION OF measurements FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
INSERT INTO measurements
SELECT g, DATE '2026-04-01' + (g % 91), g % 50, (g % 400) / 10.0
FROM generate_series(1, 91000) AS g;
ANALYZE measurements;
-- Step 1 (question 1): stat before
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');
-- Step 2 (question 1): update all
UPDATE tickets SET status = 'closed';
-- Step 3 (question 1): stat after update
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');
-- Step 4 (question 2): vacuum plain
VACUUM tickets;
-- Step 5 (question 2): stat after vacuum
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');
-- Step 6 (question 2): vacuum full
VACUUM FULL tickets;
-- Step 7 (question 2): stat after full
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');
-- Step 8 (question 3): table settings
ALTER TABLE tickets SET (
autovacuum_enabled = true,
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000
);
-- Step 9 (question 3): show settings
SELECT unnest(reloptions) AS table_setting
FROM pg_class
WHERE relname = 'tickets';
-- Step 10 (question 3): session B old snapshot [run in session B]
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM tickets;
-- Step 11 (question 3): session A
UPDATE tickets SET status = 'reopened'
WHERE ticket_id <= 20000;
-- Step 12 (question 3): stat blocked
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');
-- Step 13 (question 3): stat unblocked
SELECT tuple_count AS live_rows,
dead_tuple_count AS dead_rows,
pg_size_pretty(table_len) AS table_size,
pg_size_pretty(free_space) AS free_space
FROM pgstattuple('tickets');
-- Step 14 (question 4): rows per partition
SELECT tableoid::regclass AS partition,
COUNT(*) AS rows
FROM measurements
GROUP BY 1
ORDER BY 1;
-- Step 15 (question 4): plan pruned
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM measurements
WHERE logdate >= DATE '2026-06-01'
AND logdate < DATE '2026-07-01';
-- Step 16 (question 4): plan not pruned
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM measurements
WHERE device = 7;
-- Step 17 (question 5): pk rejected
ALTER TABLE measurements
ADD PRIMARY KEY (id);
-- Step 18 (question 5): pk accepted
ALTER TABLE measurements
ADD PRIMARY KEY (id, logdate);
-- Step 19 (question 5): no partition
INSERT INTO measurements
VALUES (999999, DATE '2026-07-15', 1, 20.5);
-- Step 20 (question 5): drop april
ALTER TABLE measurements
DETACH PARTITION measurements_2026_04;
DROP TABLE measurements_2026_04;
-- Step 21 (question 5): partitions left
SELECT tableoid::regclass AS partition, COUNT(*) AS rows
FROM measurements GROUP BY 1 ORDER BY 1;
-- Step 22 (question 6): database age
SELECT datname,
age(datfrozenxid) AS xid_age
FROM pg_database
WHERE datname = 'postgres' OR datistemplate
ORDER BY datname;
-- Step 23 (question 6): table age
SELECT c.relname,
age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind = 'r'
AND c.relnamespace = 'public'::regnamespace
ORDER BY xid_age DESC
LIMIT 5;
-- Step 24 (question 7): largest tables
SELECT c.relname AS table_name,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
pg_size_pretty(pg_relation_size(c.oid)) AS table_only,
pg_size_pretty(pg_indexes_size(c.oid)) AS indexes
FROM pg_class c
WHERE c.relkind = 'r'
AND c.relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 5;
The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.