PostgreSQL DBA Part 13: Table and Index Bloat, Find and Fix

This is Part 13 of the PostgreSQL DBA series. Table bloat is disk space inside a table's files that no longer holds live rows, and index bloat is the same thing inside an index. You find it by measuring the files with the pgstattuple extension, and you fix it by rebuilding: REINDEX CONCURRENTLY for indexes, and VACUUM FULL or the pg_repack tool for tables.

In Part 11 you saw that every UPDATE and DELETE leaves an old row version behind, and in Part 12 (VACUUM and autovacuum) you saw how VACUUM cleans those versions up. This part covers what happens when cleanup falls behind. By the end you will be able to measure exactly how much of a table or index is wasted space, decide whether it is worth fixing, fix it with the least locking, and set up a table so it bloats less in the first place.

What you will learn

• What bloat is, why plain VACUUM does not remove it, and when a little bloat is normal
• How to measure table bloat with pgstattuple and pgstattuple_approx, and index bloat with pgstatindex
• Why n_dead_tup can show 0 on a table that is 80% empty
• How to rebuild bloated indexes online with REINDEX CONCURRENTLY and clean up after a failed run
• How to shrink a table with VACUUM FULL, and online with pg_repack
• How fillfactor and HOT updates prevent bloat

Prerequisites

• Parts 1 to 12 of this series, especially Part 11 (MVCC), Part 12 (VACUUM and autovacuum) and Part 10 (sessions and pg_stat_activity).
• One Ubuntu server (24.04 LTS) with PostgreSQL 18 from the PGDG repository, cluster 18 main running on port 5432, set up as in Part 2.
• A user with sudo rights. Every SQL command below is run as the postgres operating system user, so the database role is the superuser postgres.
• About 100 MB of free disk space for the lab.

How it works

Think of a table as a notebook. When you change a row, PostgreSQL does not rub out the old line. It crosses it out and writes the new version further down, because other transactions may still need to read the old line (that is MVCC from Part 11). A crossed-out line that nobody needs any more is a dead tuple. A tuple is PostgreSQL's word for one stored version of a row.

VACUUM rubs out the dead lines so new rows can be written in their place. But the notebook does not get thinner. The documentation says it plainly: standard VACUUM marks the space available for future reuse, but it will not return the space to the operating system, except when whole pages at the end of the table become empty. A page is the 8 kB block PostgreSQL stores data in.

So bloat is the empty or dead space inside the files: pages that are mostly blank, or full of dead tuples. Some of it is healthy. A busy table settles at its minimum size plus the space used between two vacuum runs, and that free space gets reused. Bloat becomes a problem when the empty space is much larger than the table will ever refill, because then every sequential scan, every backup and every page in memory carries air.

The usual causes are:

• Mass UPDATE or DELETE: a purge of old orders or a one-time update of every row doubles the table, and VACUUM only frees space inside it.
• Autovacuum falling behind: the table changes faster than autovacuum visits it (Part 12 showed how to tune that).
• Long-running transactions: an old snapshot stops VACUUM from removing any tuple that became dead after it started, so dead tuples pile up.

Indexes bloat for their own reasons. When an UPDATE cannot use the HOT (heap-only tuple) shortcut, every index on the table gets a new entry pointing at the new row version, even indexes whose columns did not change. And when you delete most but not all rows in a key range, B-tree pages are left with only a few entries each. The documentation notes that completely empty B-tree pages are reclaimed for reuse, but a page with only a few remaining keys stays allocated.

These are the tools this part uses:

Build a bloated table to practise on

You will create a small orders table, measure it, then bloat it on purpose so every number later in the article has a known cause.

Step 1: Create the lab database and an orders table

Where: on your PostgreSQL server, as the postgres user. Open psql with the first command, then run the SQL inside it.

sudo -u postgres psql

CREATE DATABASE lab13;
\c lab13

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer       NOT NULL,
    status      text          NOT NULL,
    amount      numeric(10,2) NOT NULL,
    updated_at  timestamptz   NOT NULL DEFAULT now()
) WITH (autovacuum_enabled = false);

INSERT INTO orders (customer_id, status, amount, updated_at)
SELECT (random() * 5000)::int + 1,
       'new',
       round((random() * 9000 + 100)::numeric, 2),
       now() - (g || ' minutes')::interval
  FROM generate_series(1, 200000) AS g;

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_updated  ON orders (updated_at);

ANALYZE orders;

autovacuum_enabled = false switches autovacuum off for this one table, so it cannot clean up in the middle of the demonstration. Never do this on a real table; you drop the whole database at the end. The two indexes are created after the load so they start out freshly built and compact.

CREATE DATABASE
You are now connected to database "lab13" as user "postgres".
CREATE TABLE
INSERT 0 200000
CREATE INDEX
CREATE INDEX
ANALYZE

Check: the INSERT reports 200000 rows.

Step 2: Record the baseline sizes

Where: in the same psql session, database lab13.

SELECT pg_size_pretty(pg_relation_size('orders'))       AS table_size,
       pg_size_pretty(pg_indexes_size('orders'))        AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_index
 WHERE indrelid = 'orders'::regclass
 ORDER BY 1;

 table_size | index_size | total_size
------------+------------+------------
 11 MB      | 10 MB      | 22 MB
(1 row)

     index_name      |  size
---------------------+---------
 orders_pkey         | 4408 kB
 idx_orders_customer | 1440 kB
 idx_orders_updated  | 4408 kB
(3 rows)

200,000 rows need 11 MB of table and 10 MB of indexes. Keep these numbers: they are the minimum size of this data.

Step 3: Create bloat with a full update and a purge

Where: same session. The first statement changes every row (like a one-time data fix). The second deletes 70% of the rows (like archiving old orders).

UPDATE orders SET status = 'paid', updated_at = now();
DELETE FROM orders WHERE order_id % 10 < 7;

UPDATE 200000
DELETE 140000

Now run the two size queries from Step 2 again.

 table_size | index_size | total_size
------------+------------+------------
 23 MB      | 17 MB      | 40 MB
(1 row)

     index_name      |  size
---------------------+---------
 orders_pkey         | 8792 kB
 idx_orders_customer | 2856 kB
 idx_orders_updated  | 5672 kB
(3 rows)

Only 60,000 rows are left, yet the table doubled to 23 MB. The UPDATE wrote a second version of every row. It could not use HOT because it changed updated_at, which is indexed, so all three indexes got 200,000 new entries too.

Finding table bloat

Step 4: Look at the dead row estimate

Where: same session. This is the cheap first check every DBA runs.

SELECT relname, n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd
  FROM pg_stat_user_tables
 WHERE relname = 'orders';

 relname | n_live_tup | n_dead_tup | n_tup_upd | n_tup_hot_upd
---------+------------+------------+-----------+---------------
 orders  |      60000 |     340000 |    200000 |             0
(1 row)

n_dead_tup is 340,000: 200,000 old versions from the UPDATE plus 140,000 deleted rows. n_tup_hot_upd is 0, which confirms none of the updates were HOT. The documentation calls both counts estimated; they come from activity counters, not from reading the table.

Step 5: Measure the table exactly with pgstattuple

Where: same session. pgstattuple is a contrib extension shipped in the postgresql-18 package, so there is nothing to install with apt. You create it once in each database where you want to use it.

CREATE EXTENSION pgstattuple;
\x on
SELECT * FROM pgstattuple('orders');
\x off

CREATE EXTENSION
Expanded display is on.
-[ RECORD 1 ]------+---------
table_len          | 24100864
tuple_count        | 60000
tuple_len          | 3360000
tuple_percent      | 13.94
dead_tuple_count   | 140000
dead_tuple_len     | 7840000
dead_tuple_percent | 32.53
free_space         | 11230256
free_percent       | 46.6
Expanded display is off.

Live data is only 13.94% of the file. The rest is dead tuples (32.53%) and free space (46.6%). The percentages do not add up to 100 because each page also has a header, a list of row pointers and alignment padding.

Why 140,000 dead tuples and not 340,000? While the DELETE read each page, PostgreSQL pruned the 200,000 old UPDATE versions on the fly (the page pruning you saw in Part 11), leaving free space and small line pointers behind. Only the freshly deleted rows were still whole dead tuples.

Step 6: Run VACUUM and measure again

Where: same session. This step shows what plain VACUUM can and cannot do about bloat.

VACUUM (VERBOSE) orders;

INFO:  vacuuming "lab13.public.orders"
INFO:  launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO:  finished vacuuming "lab13.public.orders": index scans: 1
pages: 0 removed, 2942 remain, 2942 scanned (100.00% of total), 0 eagerly scanned
tuples: 140000 removed, 60000 remain, 0 are dead but not yet removable
...
index scan needed: 2942 pages from table (100.00% of total) had 340000 dead item identifiers removed
index "orders_pkey": pages: 1099 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "idx_orders_customer": pages: 357 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "idx_orders_updated": pages: 709 in total, 549 newly deleted, 549 currently deleted, 0 reusable
...

pages: 0 removed, 2942 remain is the key line: VACUUM removed 140,000 dead tuples and 340,000 dead index pointers, but it gave no pages back, because the live rows are spread over the whole file. Now check both views again:

SELECT relname, n_live_tup, n_dead_tup
  FROM pg_stat_user_tables
 WHERE relname = 'orders';

SELECT table_len, tuple_percent, dead_tuple_count, free_percent
  FROM pgstattuple('orders');

 relname | n_live_tup | n_dead_tup
---------+------------+------------
 orders  |      60000 |          0
(1 row)

 table_len | tuple_percent | dead_tuple_count | free_percent
-----------+---------------+------------------+--------------
  24100864 |         13.94 |                0 |         82.5
(1 row)

This is the most important lesson in this part. n_dead_tup is 0, so a monitoring check that only looks at dead rows reports a healthy table. Yet 82.5% of the 23 MB file is empty. Dead tuples became free space, and free space is invisible to pg_stat_user_tables. To find bloat you must measure free space.

Step 7: Rank every table by wasted space with pgstattuple_approx

Where: same session, or any database where the extension is created. pgstattuple_approx skips pages the visibility map marks as all-visible and takes their free space from the free space map, so it reads far less data. Its dead tuple figures are exact; live tuple and free space figures are estimates.

SELECT c.oid::regclass                          AS table_name,
       pg_size_pretty(s.table_len)              AS table_size,
       round(s.approx_free_percent::numeric, 1) AS free_pct,
       round(s.dead_tuple_percent::numeric, 1)  AS dead_pct,
       pg_size_pretty((s.approx_free_space + s.dead_tuple_len)::bigint) AS reclaimable
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
 CROSS JOIN LATERAL pgstattuple_approx(c.oid) AS s
 WHERE c.relkind = 'r'
   AND n.nspname NOT IN ('pg_catalog', 'information_schema')
   AND pg_relation_size(c.oid) > 1024 * 1024
 ORDER BY s.approx_free_space + s.dead_tuple_len DESC
 LIMIT 10;

 table_name | table_size | free_pct | dead_pct | reclaimable
------------+------------+----------+----------+-------------
 orders     | 23 MB      |     82.3 |      0.0 | 19 MB
(1 row)

relkind = 'r' keeps ordinary tables, and the size filter skips tables under 1 MB, where bloat does not matter. scanned_percent (not shown) was 0 here because VACUUM had just marked every page all-visible. The reclaimable column is an upper bound: a rewrite keeps some free space for page overhead and fillfactor.

How much is too much? The documentation gives no threshold. A practical rule many DBAs use: a large table with more than 20 to 30% free space that does not shrink back after normal traffic is worth a look, especially if it is scanned often. A table that refills its free space every day is not bloated, it is working.

Finding index bloat

Step 8: Measure B-tree indexes with pgstatindex

Where: same session.

SELECT i.indexrelid::regclass                         AS index_name,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
       s.leaf_pages,
       s.avg_leaf_density,
       s.deleted_pages
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  JOIN pg_am a    ON a.oid = c.relam
 CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
 WHERE i.indrelid = 'orders'::regclass
   AND a.amname = 'btree'
 ORDER BY 1;

     index_name      |  size   | leaf_pages | avg_leaf_density | deleted_pages
---------------------+---------+------------+------------------+---------------
 orders_pkey         | 8792 kB |       1093 |            13.71 |             0
 idx_orders_customer | 2856 kB |        355 |             18.5 |             0
 idx_orders_updated  | 5672 kB |        157 |            30.29 |           549
(3 rows)

A freshly built B-tree fills leaf pages to its fillfactor, which defaults to 90, so a healthy avg_leaf_density is close to 90. The primary key is at 13.71%: 1,093 leaf pages holding what fits in about 160. VACUUM did not fix this, because the pages are not empty, just sparse. idx_orders_updated shows the other case: its 549 pages became completely empty and were deleted, which is space the index can reuse but the file stays the same size.

The filter on btree matters: pgstatindex works only on B-tree indexes. For GIN there is pgstatginindex, and for hash indexes pgstathashindex. For other index types the documentation suggests watching their physical size over time.

Fixing index bloat

Step 9: Rebuild the indexes with REINDEX CONCURRENTLY

Where: same session, as the postgres role (or a role with the MAINTAIN privilege on the table).

REINDEX (VERBOSE) TABLE CONCURRENTLY orders;

INFO:  index "public.orders_pkey" was reindexed
INFO:  index "public.idx_orders_customer" was reindexed
INFO:  index "public.idx_orders_updated" was reindexed
INFO:  index "pg_toast.pg_toast_16424_index" was reindexed
INFO:  table "public.orders" was reindexed
DETAIL:  CPU: user: 0.07 s, system: 0.01 s, elapsed: 0.11 s.
REINDEX

Check: run the Step 8 query and the first size query again.

     index_name      |  size   | leaf_pages | avg_leaf_density | deleted_pages
---------------------+---------+------------+------------------+---------------
 orders_pkey         | 1328 kB |        164 |               90 |             0
 idx_orders_customer | 536 kB  |         65 |            89.89 |             0
 idx_orders_updated  | 424 kB  |         51 |            89.12 |             0
(3 rows)

 table_size | index_size | total_size
------------+------------+------------
 23 MB      | 2288 kB    | 25 MB
(1 row)

The indexes went from 17 MB to 2288 kB and every leaf density is back near 90. idx_orders_updated is now the smallest because every row has the same updated_at value, and a rebuild packs duplicates into one posting list entry (B-tree deduplication, on by default). The table itself is still 23 MB: REINDEX never touches table bloat.

Plain REINDEX blocks writes on the table, and because it takes an ACCESS EXCLUSIVE lock on the index while the planner tries to lock every index of the table, it blocks almost every query on the table too. CONCURRENTLY avoids that, at the price the documentation describes: two scans of the table per index, waits for running transactions that could use the index, and more total work. Other restrictions:

• It cannot run inside a transaction block (BEGIN ... COMMIT).
• Only one concurrent index build can run on a table at a time.
• System catalogs (REINDEX SYSTEM) and exclusion constraint indexes cannot be reindexed concurrently.
• While it runs, it holds back VACUUM on other tables like any long transaction.

Fixing table bloat

To give table space back to the operating system you must rewrite the table. The documentation names VACUUM FULL, CLUSTER and the table-rewriting forms of ALTER TABLE. All of them take an ACCESS EXCLUSIVE lock and temporarily need extra disk space about equal to the table plus its indexes, because the old files are kept until the new ones are complete.

Step 10: Check free disk space before any rewrite

Where: in a shell on the database server, as any user.

df -h /var/lib/postgresql/18/main

Filesystem      Size  Used Avail Use% Mounted on
/dev/vda        252G  9.0G   30G  24% /

Compare Avail with the total_size from Step 9 (25 MB): it must be comfortably larger than the table plus its indexes. If the table lives in another tablespace (Part 9), check that filesystem instead. Running out of space in the middle of a rewrite fails the command and, worse, can fill the disk for everyone else.

Step 11: Shrink the table with VACUUM FULL

To see the lock problem for yourself, open four terminals. Each runs sudo -u postgres psql -d lab13.

Terminal 1 opens a transaction that has read the table and stays open, like an application that forgot to commit:

BEGIN;
SELECT count(*) FROM orders;

Terminal 2 starts the rewrite. It hangs, waiting for Terminal 1:

VACUUM FULL orders;

Terminal 3 runs an ordinary read. It hangs too:

SELECT count(*) FROM orders;

Terminal 4 shows who is waiting for whom:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 30) AS query
  FROM pg_stat_activity
 WHERE datname = 'lab13'
   AND cardinality(pg_blocking_pids(pid)) > 0;

 pid  | blocked_by |            query
------+------------+------------------------------
 2773 | {2765}     | VACUUM FULL orders;
 2778 | {2773}     | SELECT count(*) FROM orders;
(2 rows)

The plain SELECT is blocked by VACUUM FULL, which has not even started yet. A waiting ACCESS EXCLUSIVE request blocks every lock request queued behind it, so one forgotten transaction plus one VACUUM FULL can stop all reads of a table. Type COMMIT; in Terminal 1 and both waiting commands finish at once. (Part 29 covers locks in depth; pg_blocking_pids was introduced in Part 10.)

Check: run the size query and pgstattuple again.

SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_indexes_size('orders'))  AS index_size;
SELECT tuple_count, dead_tuple_count, free_percent FROM pgstattuple('orders');

 table_size | index_size
------------+------------
 3536 kB    | 2288 kB
(1 row)

 tuple_count | dead_tuple_count | free_percent
-------------+------------------+--------------
       60000 |                0 |         0.28
(1 row)

The table went from 23 MB to 3536 kB and free space is 0.28%. VACUUM FULL wrote a new file and rebuilt every index, so you did not need Step 9 first; it was shown separately because often only indexes are bloated. Progress of a running VACUUM FULL appears in the view pg_stat_progress_cluster, not pg_stat_progress_vacuum.

Step 12: Shrink a table online with pg_repack

pg_repack is a third-party extension, not part of PostgreSQL, but it is packaged in the PGDG repository. It builds a compacted copy of the table in the background, records changes made meanwhile with a trigger, replays them, then swaps the files. Its documentation says it holds an ACCESS EXCLUSIVE lock only for a short period during initial setup and during the final swap-and-drop phase, so normal reads and writes continue for the rest of the run.

First bloat the table again so there is something to repack. Where: psql on lab13, as postgres:

UPDATE orders SET status = 'shipped', updated_at = now();
UPDATE orders SET status = 'delivered', updated_at = now();
VACUUM orders;

UPDATE 60000
UPDATE 60000
VACUUM

The size query now shows 11 MB of table and 5912 kB of indexes. Where: a shell on the server, with sudo. Install the package, then create the extension in the database:

sudo apt-get install -y postgresql-18-repack
sudo -u postgres psql -d lab13 -c "CREATE EXTENSION pg_repack;"

CREATE EXTENSION

Do a dry run first, then the real run. Where: shell, as the postgres user:

sudo -u postgres /usr/lib/postgresql/18/bin/pg_repack --dry-run -d lab13 -t orders
sudo -u postgres /usr/lib/postgresql/18/bin/pg_repack -d lab13 -t orders

INFO: Dry run enabled, not executing repack
INFO: repacking table "public.orders"
INFO: repacking table "public.orders"

Check: the size query now shows 4000 kB of table and 2288 kB of indexes. That is a little more than after VACUUM FULL because 'delivered' is a longer value than 'paid'. pg_repack requirements from its documentation:

• The table must have a PRIMARY KEY or a UNIQUE index on NOT NULL columns.
• Only superusers or owners of the tables and indexes can use it.
• It needs free disk space about twice the size of the table and its indexes.
• No DDL on the table while it runs, and it cannot process temporary tables.
• The pg_repack client and the extension version must match, so install it from the same repository as the server.

Preventing bloat with fillfactor and HOT updates

A HOT update writes the new row version on the same page and adds no index entries. The documentation lists two conditions: the update does not change any column used by an index (BRIN indexes excepted), and there is enough free space on the page that holds the old row. You control the second condition with the table's fillfactor: the percentage of each page INSERT fills, between 10 and 100, default 100.

Step 13: Measure HOT updates, then lower fillfactor

Where: psql on lab13, as postgres. First put the rows back in primary key order with CLUSTER, another table rewrite (same ACCESS EXCLUSIVE lock as VACUUM FULL), so the updated rows are spread evenly over the pages. Then reset the table's counters so the HOT numbers below count only this test. pg_stat_reset_single_table_counters is restricted to superusers by default.

CLUSTER orders USING orders_pkey;
SELECT pg_stat_reset_single_table_counters('orders'::regclass);

UPDATE orders SET status = 'returned' WHERE order_id % 50 = 7;

SELECT n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
  FROM pg_stat_user_tables
 WHERE relname = 'orders';

 n_tup_upd | n_tup_hot_upd | hot_pct
-----------+---------------+---------
      4000 |             0 |     0.0
(1 row)

The update did not touch an indexed column, yet 0% of updates were HOT: the pages were 100% full, so every new version went to another page and needed new entries in all three indexes. Statistics counters are sent at the end of a transaction, so if you see zeros, wait a second and query again.

Now leave 20% of each page free. Changing the setting does not move existing rows; the ALTER TABLE documentation says you need a rewrite such as VACUUM FULL or CLUSTER to apply it:

ALTER TABLE orders SET (fillfactor = 80);
CLUSTER orders;
SELECT reloptions FROM pg_class WHERE relname = 'orders';
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

SELECT pg_stat_reset_single_table_counters('orders'::regclass);
UPDATE orders SET status = 'refunded' WHERE order_id % 50 = 7;

                reloptions
------------------------------------------
 {autovacuum_enabled=false,fillfactor=80}
(1 row)

 table_size
------------
 5000 kB
(1 row)

CLUSTER orders without USING reuses the index chosen last time. Run the HOT query again:

 n_tup_upd | n_tup_hot_upd | hot_pct
-----------+---------------+---------
      4000 |          4000 |   100.0
(1 row)

Every update was HOT. Now change an indexed column instead (reset the counters first, then run the HOT query):

UPDATE orders SET updated_at = now() WHERE order_id % 50 = 7;

 n_tup_upd | n_tup_hot_upd | hot_pct
-----------+---------------+---------
      4000 |             0 |     0.0
(1 row)

Free page space does not help when an indexed column changes. The trade-off is clear in the numbers: fillfactor 80 made the table 25% bigger (4000 kB to 5000 kB) to buy HOT updates. Use it on tables with frequent updates of non-indexed columns (status, counters, last_seen timestamps that are not indexed). Leave tables that are only inserted into at 100. And question every index on a frequently updated column: each one turns HOT updates into index bloat.

Undo: ALTER TABLE orders RESET (fillfactor); then rewrite the table if you want the pages packed again.

Step 14: Clean up the lab

Where: a shell, as the postgres user.

sudo -u postgres psql -c "DROP DATABASE lab13;"
sudo -u postgres psql -Atc "SELECT count(*) FROM pg_database WHERE datname = 'lab13';"

DROP DATABASE
0

If you do not need pg_repack elsewhere, remove it with sudo apt-get remove postgresql-18-repack.

Common mistakes and how to fix them

ERROR: function pgstattuple(unknown) does not exist

ERROR:  function pgstattuple(unknown) does not exist
LINE 1: SELECT * FROM pgstattuple('pg_class');
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Extensions are created per database. You created pgstattuple in lab13, then connected to another database. Fix: run CREATE EXTENSION pgstattuple; in the database you are checking.

ERROR: permission denied for function pgstattuple

Even the table owner gets this error. The documentation says only the role pg_stat_scan_tables has EXECUTE on these functions by default (superusers bypass it). Fix: give your monitoring role that predefined role instead of superuser.

GRANT pg_stat_scan_tables TO app_owner;

GRANT ROLE

ERROR: REINDEX CONCURRENTLY cannot run inside a transaction block

You ran it after BEGIN, or from a tool that wraps every script in a transaction. Fix: run it on its own, in autocommit mode. In psql, do not wrap it in BEGIN; in a migration tool, mark that migration as non-transactional.

An index ending in _ccnew marked INVALID

If a concurrent reindex is cancelled or fails (a statement_timeout, a lost connection, a duplicate in a unique index), it leaves its half-built copy behind. In the lab, a statement_timeout of 3s while another session held an open UPDATE produced this:

ERROR:  canceling statement due to statement timeout

Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
    "idx_orders_customer" btree (customer_id)
    "idx_orders_customer_ccnew" btree (customer_id) INVALID
    "idx_orders_updated" btree (updated_at)

The invalid index is not used by queries but is still updated on every write. Find every one in the database, drop it, and retry:

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
  FROM pg_index
 WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY idx_orders_customer_ccnew;
REINDEX INDEX CONCURRENTLY idx_orders_customer;

The documentation's rule: a name ending in _ccnew is the unfinished new copy, so drop it and run REINDEX CONCURRENTLY again. A name ending in _ccold is the old index that could not be dropped; the rebuild succeeded, so just drop it. A number may follow the suffix, such as _ccnew1.

WARNING: relation must have a primary key or not-null unique keys

WARNING: relation "public.order_events" must have a primary key or not-null unique keys

pg_repack skipped the table, and its exit status is still 0, so a cron job will not notice. Fix: add a primary key (or a unique index on NOT NULL columns) first, or use VACUUM FULL in a maintenance window. Check the output of scheduled pg_repack runs for WARNING lines.

Bloat comes straight back after a rewrite

A rewrite only removes the symptom. If a session sits idle in transaction for hours, VACUUM cannot remove anything that died after its snapshot started, as the lab showed:

tuples: 0 removed, 64000 remain, 4000 are dead but not yet removable

SELECT pid, state, age(backend_xmin) AS xmin_age, now() - xact_start AS xact_age
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY age(backend_xmin) DESC;

 pid  |        state        | xmin_age |    xact_age
------+---------------------+----------+-----------------
 3091 | idle in transaction |        1 | 00:00:01.111693
(1 row)

Once that transaction ended, the next VACUUM reported 4000 removed. Fix the cause: end or terminate the old session (Part 10), set idle_in_transaction_session_timeout, check old replication slots and prepared transactions, and tune autovacuum for the table (Part 12).

Running VACUUM FULL from a nightly cron job

Scheduled VACUUM FULL takes a full lock every night to shrink a table that will grow back the next day. The documentation's advice is to run standard VACUUM often enough that VACUUM FULL is never needed. Keep rewrites for one-time events such as a large purge.

Practice exercise

1. Create a table payments (payment_id bigint primary key, status text, amount numeric) with 100,000 rows. Delete every row where payment_id % 4 <> 0, run VACUUM, and use pgstattuple to report free_percent. Has the file shrunk?
2. Rebuild only the primary key index of payments without blocking writes, and confirm with pgstatindex that avg_leaf_density is close to 90.

Quick reference

# Sizes of a table and its indexes
sudo -u postgres psql -d mydb -c "SELECT pg_size_pretty(pg_relation_size('orders')), pg_size_pretty(pg_indexes_size('orders'));"

# Cheap check: estimated dead rows and HOT updates
sudo -u postgres psql -d mydb -c "SELECT relname, n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

# Install the measuring extension (once per database)
sudo -u postgres psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS pgstattuple;"

# Exact table bloat (full scan) and approximate (faster)
sudo -u postgres psql -d mydb -c "SELECT * FROM pgstattuple('orders');"
sudo -u postgres psql -d mydb -c "SELECT * FROM pgstattuple_approx('orders');"

# B-tree index density (healthy is near 90)
sudo -u postgres psql -d mydb -c "SELECT avg_leaf_density, leaf_pages, deleted_pages FROM pgstatindex('orders_pkey');"

# Let a monitoring role use pgstattuple
sudo -u postgres psql -d mydb -c "GRANT pg_stat_scan_tables TO monitor;"

# Rebuild indexes without blocking writes
sudo -u postgres psql -d mydb -c "REINDEX (VERBOSE) TABLE CONCURRENTLY orders;"

# Find invalid indexes left by a failed concurrent build
sudo -u postgres psql -d mydb -c "SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;"

# Check disk space before any table rewrite
df -h /var/lib/postgresql/18/main

# Rewrite the table (ACCESS EXCLUSIVE lock: maintenance window only)
sudo -u postgres psql -d mydb -c "VACUUM FULL orders;"

# Online rewrite with pg_repack
sudo apt-get install -y postgresql-18-repack
sudo -u postgres psql -d mydb -c "CREATE EXTENSION pg_repack;"
sudo -u postgres /usr/lib/postgresql/18/bin/pg_repack --dry-run -d mydb -t orders
sudo -u postgres /usr/lib/postgresql/18/bin/pg_repack -d mydb -t orders

# Leave room for HOT updates, then rewrite to apply it
sudo -u postgres psql -d mydb -c "ALTER TABLE orders SET (fillfactor = 80);"

Summary and what's next

• Bloat is free and dead space inside table and index files; plain VACUUM makes it reusable but does not shrink the file.
• n_dead_tup can be 0 on a table that is mostly empty, so measure free space with pgstattuple or pgstattuple_approx.
• pgstatindex shows B-tree leaf density; a fresh index is near 90%, and sparse pages stay allocated until a rebuild.
• REINDEX CONCURRENTLY rebuilds indexes without blocking writes; drop any _ccnew or _ccold INVALID index a failed run leaves.
• VACUUM FULL and CLUSTER shrink tables but take an ACCESS EXCLUSIVE lock and need free space about the size of the table plus indexes.
• pg_repack does the same rewrite online, needs a primary key, and locks exclusively only at the start and end.
• fillfactor below 100 plus fewer indexes on updated columns gives HOT updates and less bloat; long transactions bring bloat back.

In Part 14 we look at transaction ID wraparound and freezing.