Lab 11: Grow a table to six times its size, then measure exactly how much is waste

Objective

Bloat is easy to describe and hard to quantify. “The table is bloated” is not actionable; “the table is 74 MB and its live rows occupy 11 MB” is.

By the end of this lab you will have taken a clean table from 17 MB to 92 MB using nothing but UPDATE, measured precisely how much of that is waste, and applied three different remedies — observing that they recover different things, cost very different amounts, and take very different locks.

You will also learn the measurement you can afford to run. pgstattuple is exact and reads the entire table, which on a large table is absolutely not something you want to do casually. There are two cheaper options and this lab compares all three on the same table.

Architecture

One table, one primary key, one secondary index, with autovacuum disabled on the table so bloat accumulates instead of being cleaned up behind you.

flowchart TD
    T["orders, 200k rows\nautovacuum_enabled = off"] --> U["5 full-table UPDATEs"]
    U --> B["bloated: 92 MB total"]
    B --> M1["pgstattuple\nexact, full scan"]
    B --> M2["pgstattuple_approx\nsampled"]
    B --> M3["pg_class.relpages\ncatalog only"]
    B --> R1["VACUUM\nreusable space, same file size"]
    B --> R2["VACUUM FULL\nrewrite, AccessExclusiveLock"]
    B --> R3["REINDEX CONCURRENTLY\nindexes only, no exclusive lock"]

Requirements

A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab11.
The pgstattuple extension, in postgresql-contrib-18 on Debian.
Roughly 150 MB of free disk, since the point of the lab is to waste some.

Scenario

Storage alerts have fired on a database whose row counts have been flat for months. Before requesting more disk, you need to establish how much of the current usage is live data and how much is recoverable, and what recovering it would cost in downtime.

Tasks

Task 1 — Build a clean table and record the baseline

LAB="$HOME/rbpg-lab-11"
mkdir -p "$LAB"

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab11;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab11 <<'SQL'
CREATE EXTENSION pgstattuple;
CREATE TABLE orders(id int PRIMARY KEY, customer text, total numeric, status text)
  WITH (autovacuum_enabled = off);
INSERT INTO orders SELECT g, 'customer-'||(g%1000), g*1.25, 'new'
  FROM generate_series(1,200000) g;
CREATE INDEX orders_status_idx ON orders(status);
SQL

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "VACUUM ANALYZE orders;"

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "
  SELECT pg_size_pretty(pg_relation_size('orders'))            AS heap,
         pg_size_pretty(pg_relation_size('orders_pkey'))       AS pkey,
         pg_size_pretty(pg_relation_size('orders_status_idx')) AS status_idx,
         pg_size_pretty(pg_total_relation_size('orders'))      AS total;" | tee "$LAB/growth.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT * FROM pgstattuple('orders');" | tee "$LAB/pgstattuple.txt"

Read-only / Safewhat a freshly loaded table looks like— Captured from the lab run of 2026-08-28. 88% of the file is live tuples.

$ size functions, then pgstattuple('orders')

 heap  |  pkey   | status_idx | total 
-------+---------+------------+-------
11 MB | 4408 kB | 1368 kB    | 17 MB
(1 row)

table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
12050432 |      200000 |  10661952 |         88.48 |                0 |              0 |                  0 |      15512 |         0.13
(1 row)

tuple_percent = 88.48 is the number to remember. On a freshly loaded table it is high and the remainder is page headers, line pointers and the small amount of space the last row on each page could not use. It will never be 100.

pg_relation_size is the heap alone; pg_total_relation_size includes every index and the TOAST table. The 6 MB difference here is the two indexes.

Task 2 — Create the bloat, one pass at a time

for i in 1 2 3 4 5; do
  docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
    "UPDATE orders SET status = 'pass-$i';" > /dev/null
  SZ=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab11 -c \
        "SELECT pg_size_pretty(pg_relation_size('orders'))")
  TOT=$(docker exec -u postgres rbpg-lab01 psql -X -tAd lab11 -c \
        "SELECT pg_size_pretty(pg_total_relation_size('orders'))")
  printf "after pass %s: heap %-8s total %s\n" "$i" "$SZ" "$TOT"
done | tee -a "$LAB/growth.txt"

Service impact possiblethe same 200,000 rows, growing every pass— Captured from the lab run of 2026-08-28. Five full-table updates.

$ five UPDATE passes, measuring size after each

after pass 1: heap 24 MB    total 35 MB
after pass 2: heap 37 MB    total 49 MB
after pass 3: heap 49 MB    total 64 MB
after pass 4: heap 62 MB    total 78 MB
after pass 5: heap 74 MB    total 92 MB

Roughly 12 MB of heap per pass, plus index growth. The row count never changed. Every byte of the growth is versions of rows that already existed — exactly the mechanism Lab 9 showed on a single page, now applied 200,000 times over.

Note the index growth too: the total grew faster than the heap, because status is indexed and every update changed it, so none of these updates could be HOT.

Task 3 — Measure the damage exactly

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT * FROM pgstattuple('orders');" | tee -a "$LAB/pgstattuple.txt"

Read-only / Safe14% live, 62% free space— Captured from the lab run of 2026-08-28. Compare tuple_percent with the 88.48 from Task 1.

$ psql -X -d lab11 -c "SELECT * FROM pgstattuple('orders');"

 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
77766656 |      200000 |  11261952 |         14.48 |           200057 |       11265091 |              14.49 |   48262032 |        62.06
(1 row)

Read the three percentages together, because the interesting thing here is what they do not add up to in the way you might expect.

tuple_percent = 14.48 — live rows.
dead_tuple_percent = 14.49 — dead rows still occupying space.
free_percent = 62.06 — reusable space inside the file.

There were a million updates and only 200,057 dead tuples remain. Where did the other 800,000 go?

Opportunistic pruning already did most of the work

Vacuum is not the only thing that removes dead tuples. When any query reads a page and finds dead versions on it, it may prune them in place: the tuples become free space on that page immediately, without waiting for a vacuum and without touching indexes.

Each UPDATE pass in Task 2 scanned the whole table, so each pass pruned the dead tuples left by the previous one. What survives as dead_tuple_count is essentially just the last pass; everything earlier has already become free_space.

This is why dead_tuple_percent alone is a poor bloat metric. On a busy table it can look reassuringly small while the file is mostly holes. free_percent is where the space actually went, and tuple_percent — how much of the file is useful — is the number to watch.

Task 4 — What plain VACUUM recovers

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "VACUUM orders;"
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT * FROM pgstattuple('orders');" | tee -a "$LAB/pgstattuple.txt"
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "
  SELECT pg_size_pretty(pg_relation_size('orders'))       AS heap,
         pg_size_pretty(pg_relation_size('orders_pkey'))  AS pkey,
         pg_size_pretty(pg_total_relation_size('orders')) AS total;"

Configuration changedead tuples gone, table_len unchanged— Captured from the lab run of 2026-08-28. free_percent rose; table_len did not move at all.

$ VACUUM orders, then pgstattuple and the size functions

 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
77766656 |      200000 |  11261952 |         14.48 |                0 |              0 |                  0 |   64438200 |        82.86
(1 row)

heap  | pkey  | total 
-------+-------+-------
74 MB | 11 MB | 92 MB
(1 row)

dead_tuple_percent is now zero and free_percent has risen to 82.86. table_len is 77766656 in both readings — byte for byte identical.

Vacuum did exactly what it is supposed to do and the file is the same size. The 62 GB-equivalent of this on a real system is the moment people conclude vacuum is broken. It is not: the space is now available for new rows, which is the whole job.

Task 5 — Measure the index

Indexes bloat too, and pgstattuple is not the tool for them.

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "
  SELECT version, tree_level, index_size, internal_pages, leaf_pages,
         empty_pages, deleted_pages, avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('orders_pkey');" | tee "$LAB/index-bloat.txt"

Read-only / Safea B-tree at 36% density with 40% fragmentation— Captured from the lab run of 2026-08-28, on the bloated index.

$ psql -X -d lab11 -c "SELECT ... FROM pgstatindex('orders_pkey');"

 version | tree_level | index_size | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
---------+------------+------------+----------------+------------+-------------+---------------+------------------+--------------------
     4 |          2 |   11247616 |              6 |       1366 |           0 |             0 |            36.17 |              39.97
(1 row)

Two columns matter:

avg_leaf_density — how full the leaf pages are. A freshly built B-tree is around 90%. At 36%, this index is roughly two and a half times the size it needs to be.
leaf_fragmentation — how far the physical page order has drifted from the logical key order. High fragmentation means a range scan that should read sequentially reads all over the file instead.

An index at 36% density is not just wasting disk. It is wasting buffer cache — every index page read pulls in 8 kB that holds 36% useful data — and that is usually the more expensive half.

Task 6 — VACUUM FULL: the complete rewrite

START=$(date +%s%N)
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "VACUUM FULL orders;"
END=$(date +%s%N)
echo "VACUUM FULL took $(( (END-START)/1000000 )) ms"

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT * FROM pgstattuple('orders');" | tee -a "$LAB/pgstattuple.txt"
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "
  SELECT pg_size_pretty(pg_relation_size('orders'))            AS heap,
         pg_size_pretty(pg_relation_size('orders_pkey'))       AS pkey,
         pg_size_pretty(pg_relation_size('orders_status_idx')) AS status_idx,
         pg_size_pretty(pg_total_relation_size('orders'))      AS total;"
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT avg_leaf_density, leaf_fragmentation, index_size FROM pgstatindex('orders_pkey');" \
  | tee -a "$LAB/index-bloat.txt"

Destructive92 MB back to 18 MB, indexes rebuilt as a side effect— Captured from the lab run of 2026-08-28. 159 ms here, but this table is small.

$ VACUUM FULL orders, then measure everything again

VACUUM FULL took 159 ms

table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
13148160 |      200000 |  11261952 |         85.65 |                0 |              0 |                  0 |      72840 |         0.55
(1 row)

heap  |  pkey   | status_idx | total 
-------+---------+------------+-------
13 MB | 4408 kB | 1368 kB    | 18 MB
(1 row)

avg_leaf_density | leaf_fragmentation | index_size 
------------------+--------------------+------------
          89.95 |                  0 |    4513792
(1 row)

Everything is recovered: heap 74 MB to 13 MB, primary key 11 MB to 4408 kB, tuple_percent back to 85.65, index density back to 89.95 with zero fragmentation.

VACUUM FULL writes a brand new copy of the table and every index, then swaps them in and drops the originals. That is why it recovers everything, and it is also the source of both of its costs.

Task 7 — What VACUUM FULL costs

# A long reader, holding AccessShareLock.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab11 <<'SQL'
BEGIN;
SELECT count(*) FROM orders;
\\\\! sleep 60
COMMIT;
SQL\""
sleep 2
docker exec -d rbpg-lab01 bash -c \
  "su - postgres -c \"psql -X -d lab11 -c 'VACUUM FULL orders;'\""
sleep 3
for i in 1 2; do
  docker exec -d rbpg-lab01 bash -c \
    "su - postgres -c \"psql -X -d lab11 -c 'SELECT count(*) FROM orders;'\""
done
sleep 3

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "
  SELECT pid, state, wait_event_type, wait_event, left(query,40) AS query
  FROM pg_stat_activity WHERE datname='lab11' AND pid <> pg_backend_pid()
  ORDER BY query_start;"
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "
  SELECT pid, mode, granted FROM pg_locks
  WHERE relation='orders'::regclass AND locktype='relation'
  ORDER BY granted DESC, pid;"

Service impact possibleexactly the queue from Lab 7— Captured from the lab run of 2026-08-28. Two readers blocked behind the VACUUM FULL.

$ a long reader, a VACUUM FULL, then two ordinary readers

  pid  |        state        | wait_event_type | wait_event |            query             
-------+---------------------+-----------------+------------+------------------------------
11253 | idle in transaction | Client          | ClientRead | SELECT count(*) FROM orders;
11269 | active              | Lock            | relation   | VACUUM FULL orders;
11287 | active              | Lock            | relation   | SELECT count(*) FROM orders;
11293 | active              | Lock            | relation   | SELECT count(*) FROM orders;
(4 rows)

pid  |        mode         | granted 
-------+---------------------+---------
11253 | AccessShareLock     | t
11269 | AccessExclusiveLock | f
11287 | AccessShareLock     | f
11293 | AccessShareLock     | f
(4 rows)

The same shape as Lab 7, for the same reason. VACUUM FULL needs AccessExclusiveLock, so it queues behind the existing reader, and every query arriving afterwards queues behind it.

The two costs of VACUUM FULL, in order of importance

The table is unavailable for the whole rewrite. Not slow — completely blocked, for reads as well as writes. On a 200 GB table that is hours, and the queue behind it means the application stops rather than degrades.

It needs room for a second copy. The new table and its new indexes are built before the old ones are dropped, so peak usage is roughly the old size plus the new size. Running VACUUM FULL to recover space on a filesystem that is nearly full can fail for lack of space, having achieved nothing.

Use it when you can take the outage and the table is genuinely recoverable — after a one-off mass deletion, for example. For a table that must stay online, an external repack tool that builds the copy incrementally and takes the exclusive lock only for the final swap is the right answer, and index-only bloat has a built-in solution in the next task.

Task 8 — REINDEX CONCURRENTLY: online, but not free

Re-bloat the index and rebuild it without an exclusive lock:

for i in 6 7 8; do
  docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
    "UPDATE orders SET status = 'pass-$i';" > /dev/null
done
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "VACUUM orders;"
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT avg_leaf_density, leaf_fragmentation, pg_size_pretty(index_size::bigint) AS size
   FROM pgstatindex('orders_pkey');"

# A reader holding a transaction open for 25 seconds.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab11 <<'SQL'
BEGIN;
SELECT count(*) FROM orders;
\\\\! sleep 25
COMMIT;
SQL\""
sleep 2

START=$(date +%s%N)
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "REINDEX INDEX CONCURRENTLY orders_pkey;"
END=$(date +%s%N)
echo "REINDEX CONCURRENTLY took $(( (END-START)/1000000 )) ms"

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT avg_leaf_density, leaf_fragmentation, pg_size_pretty(index_size::bigint) AS size
   FROM pgstatindex('orders_pkey');" | tee -a "$LAB/index-bloat.txt"

Configuration changethe index rebuilt with the reader still connected— Captured from the lab run of 2026-08-28. Nothing was blocked, but note the elapsed time.

$ re-bloat, start a 25-second reader, then REINDEX INDEX CONCURRENTLY

 avg_leaf_density | leaf_fragmentation |  size   
------------------+--------------------+---------
          42.84 |               47.4 | 9264 kB
(1 row)

REINDEX
REINDEX CONCURRENTLY took 23033 ms

avg_leaf_density | leaf_fragmentation |  size   
------------------+--------------------+---------
          89.95 |                  0 | 4408 kB
(1 row)

The index went from 42.84% density and 9264 kB to 89.95% and 4408 kB with no query blocked at any point.

But look at the elapsed time: 23 seconds, on an index that VACUUM FULL rebuilt as part of a 159 ms operation. The reader was holding a transaction open for 25 seconds, and that is not a coincidence.

CONCURRENTLY does not block, but it does wait

REINDEX CONCURRENTLY builds the new index alongside the old one and then waits for every transaction that started before the build to finish, because those transactions may still be inserting rows the new index has not seen. Only then can it swap.

So it never blocks anybody, and it cannot finish while a long transaction is open. On a system with a reporting query that runs for forty minutes, the reindex takes at least forty minutes, and it holds a lock on the table’s catalog entry for that whole period.

Two consequences worth planning for:

A failed REINDEX CONCURRENTLY leaves an invalid index behind. Check for them with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; and drop them; they consume space and are maintained by writes without being usable by queries.

Run it when long transactions are not. The same is true of CREATE INDEX CONCURRENTLY. The operation is online in the sense that matters — nothing is blocked — but its duration is dictated by the longest transaction on the system, not by the size of the index.

Task 9 — Choose a measurement you can afford

pgstattuple reads every page of the table. That is fine here and unacceptable on a 500 GB table during business hours. Compare the three options:

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "UPDATE orders SET status='pass-9';" > /dev/null

START=$(date +%s%N)
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT table_len, tuple_percent, dead_tuple_percent, free_percent FROM pgstattuple('orders');"
END=$(date +%s%N); echo "pgstattuple (exact, full scan): $(( (END-START)/1000000 )) ms"

START=$(date +%s%N)
docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c \
  "SELECT table_len, scanned_percent, approx_tuple_percent, dead_tuple_percent, approx_free_percent
   FROM pgstattuple_approx('orders');"
END=$(date +%s%N); echo "pgstattuple_approx (sampled): $(( (END-START)/1000000 )) ms"

Read-only / Safethe sampled version reads half the pages and agrees to within 0.2%— Captured from the lab run of 2026-08-28. The gap widens on larger tables, where it matters more.

$ pgstattuple then pgstattuple_approx on the same table

 table_len | tuple_percent | dead_tuple_percent | free_percent 
-----------+---------------+--------------------+--------------
52584448 |         21.42 |              21.42 |        50.07
(1 row)
pgstattuple (exact, full scan): 68 ms

table_len |  scanned_percent  | approx_tuple_percent | dead_tuple_percent | approx_free_percent 
-----------+-------------------+----------------------+--------------------+---------------------
52584448 | 50.39725813989718 |   21.610648076024304 | 21.416887365633276 |    50.0490943634133
(1 row)
pgstattuple_approx (sampled): 56 ms

pgstattuple_approx scanned 50.4% of the pages and reported 21.61% against the true 21.42%. It skips pages the visibility map marks all-visible, so on a well-vacuumed table it can skip most of them.

And the third option, which touches the table not at all:

docker exec -u postgres rbpg-lab01 psql -X -d lab11 -c "
  SELECT c.relname,
         pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
         c.reltuples::bigint AS reltuples,
         c.relpages
  FROM pg_class c WHERE c.relname = 'orders';" | tee "$LAB/measurement-cost.txt"

Read-only / Safecatalog only, no table access at all— Captured from the lab run of 2026-08-28. relpages and reltuples come from the last vacuum or analyze.

$ psql -X -d lab11 -c "SELECT c.relname, pg_size_pretty(pg_relation_size(c.oid)), c.reltuples::bigint, c.relpages FROM pg_class c WHERE c.relname = 'orders';"

 relname | actual_size | reltuples | relpages 
---------+-------------+-----------+----------
orders  | 50 MB       |    200000 |     6419
(1 row)

Validation

test -s "$LAB/growth.txt"           && echo "OK growth"
test -s "$LAB/pgstattuple.txt"      && echo "OK pgstattuple"
test -s "$LAB/index-bloat.txt"      && echo "OK index-bloat"
test -s "$LAB/measurement-cost.txt" && echo "OK measurement-cost"

grep -q "92 MB"  "$LAB/growth.txt"      && echo "OK bloat created"
grep -q "14.48"  "$LAB/pgstattuple.txt" && echo "OK exact measurement captured"
grep -q "36.17"  "$LAB/index-bloat.txt" && echo "OK index bloat captured"
grep -q "89.95"  "$LAB/index-bloat.txt" && echo "OK index rebuilt"

Questions to answer without looking anything up:

A table has dead_tuple_percent = 2 and free_percent = 70. Is it bloated? Which column told you?
VACUUM completed successfully and pg_relation_size is unchanged. What went wrong?
You have 40 GB free and a 60 GB bloated table. Can you VACUUM FULL it?
REINDEX CONCURRENTLY has been running for 40 minutes on a 200 MB index. What is it waiting for?
You want a nightly bloat report across 4,000 tables. Which measurement do you use, and which must you not?

Expected Outcome

You have created a 6-fold bloat, measured it three ways, and applied three remedies with very different costs:

	Recovers	Lock	Duration governed by
`VACUUM`	reusable space inside the file	`ShareUpdateExclusiveLock`, does not block reads or writes	table size and cost budget
`VACUUM FULL`	everything, heap and indexes, file shrinks	`AccessExclusiveLock`, blocks everything	table size, plus needs double the space
`REINDEX CONCURRENTLY`	index bloat only	no exclusive lock for the build	the longest open transaction

And the operational judgement: routine bloat is prevented by autovacuum keeping up (Lab 10), not corrected by VACUUM FULL. Reach for the rewrite after a genuine one-off event, and monitor with pg_class.relpages so that you find out before the storage alert does.

Troubleshooting

ERROR: extension "pgstattuple" does not exist. It is in postgresql-contrib. Install it and CREATE EXTENSION pgstattuple; as a superuser.

The bloat does not appear. Autovacuum cleaned up between passes. Disable it on the table for the duration (ALTER TABLE ... SET (autovacuum_enabled = off)) as Task 1 does, and re-run the churn.

pgstattuple is very slow. It reads every page. On a large table use pgstattuple_approx for a sample, or work from pg_class.relpages against an expected size — Task 9 is about choosing a measurement whose cost you can afford.

free_percent is high and dead_tuple_percent is near zero. That is a vacuumed table with reusable space inside it, which is the normal resting state and not a problem. The two columns answer different questions: dead tuples are work not yet done, free space is work already done.

VACUUM FULL fails with could not extend file or a disk-full error. It writes a complete new copy before dropping the old one, so it needs free space equal to the live data on the same filesystem. Check before you start, not during.

VACUUM FULL appears to hang. It is waiting for AccessExclusiveLock behind an open transaction. It blocks everything once granted and waits for everything before that. Check pg_blocking_pids, and set lock_timeout before running it.

REINDEX CONCURRENTLY finishes but the old index is still there, marked invalid. The build failed or was cancelled. An invalid index is not used by the planner but is still maintained on every write — find them with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; and drop them explicitly.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='lab11' AND pid <> pg_backend_pid();"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab11;"

Production notes

Monitor pg_class.relpages per table over time. It is free — it is already in the catalogue — and a table whose page count is climbing while its row count is not is bloating. This finds the problem weeks before the storage alert.
VACUUM FULL is for a one-off event: a bulk delete, a mass update, a migration. Routine bloat means autovacuum is not keeping up, and the fix is in Lab 10’s thresholds, not in a rewrite that blocks the table.
REINDEX CONCURRENTLY is the online option for index bloat, and its duration is governed by the longest open transaction rather than by the index size. On a cluster with long-running reporting queries it can take far longer than the equivalent blocking rebuild.
Always set lock_timeout before VACUUM FULL or a blocking REINDEX. Without it, a statement that needs a brief exclusive lock queues behind one long reader and blocks everything arriving after it.
Budget the space before the rewrite. VACUUM FULL needs room for a second copy of the live data, and running out partway leaves the original intact but the filesystem full.

What You Learned

Bloat is measurable three ways, at three costs: pg_class.relpages (free, approximate), pgstattuple_approx (sampled), and pgstattuple (exact, reads every page).
free_percent and dead_tuple_percent mean different things. Dead tuples are work outstanding; free space is the result of work already done.
Plain VACUUM does not shrink the file. It makes space reusable inside it.
VACUUM FULL rewrites heap and indexes, returns the space, takes AccessExclusiveLock, and needs double the space while it runs.
REINDEX CONCURRENTLY avoids the exclusive lock but waits on the oldest open transaction, and a failed run leaves an invalid index that costs writes and serves no reads.
The cheap measurement is the one you will actually run. An exact figure you take once a year is worth less than an approximate one you trend weekly.

Objectives

Prerequisites