Why a B-tree Index Never Shrinks

Where this is already explained

• Autovacuum Tuning and the Cost Budget Every Worker Shares

  That autovacuum_max_workers is a throughput setting, so a table that autovacuum cannot keep up with needs more workers. The cost limit is divided among the running workers: three workers each move at a third of the speed of one, the total I/O per second is unchanged, and on a large table adding workers makes each individual vacuum take proportionally longer and the table's peak bloat worse.

• The Free Space Map: Freed Is Not the Same as Available

  That once VACUUM reports the dead tuples removed, the space is back in play, so a table that keeps growing must mean vacuum is not running. Freeing space and advertising it are two separate steps. VACUUM records each page's new free space at the bottom of the map immediately, but GetPageWithFreeSpace descends from the root, and the root is refreshed only by FreeSpaceMapVacuumRange — once per index-vacuum cycle and once when the scan ends. On an 8 GB table at default settings that is a single refresh 233 seconds in, and every row inserted before it extends the file. On-access pruning never refreshes the map at all, deliberately.

• Heap-Only Tuple Updates and fillfactor

  That an UPDATE only writes to the indexes whose columns it changed, and that Postgres decides this per index. It is one all-or-nothing test per statement: change a single column that any non-summarizing index references and every index on the table gets a new entry, including the six that index columns you did not touch. And even an update that touches no indexed column falls back to that same cold path whenever the old row's page has no free space — which at the default fillfactor of 100 is most pages, most of the time.

• Index-Only Scans and the Visibility Map

  That an index-only scan does not read the table, so a covering index makes the heap irrelevant. It reads the heap for every row whose page is not marked all-visible in the visibility map, and only vacuum sets that bit while any write clears it — so on a table taking continuous writes an index-only scan does as many heap accesses as a plain index scan, in random order, and the planner picked it precisely because pg_class.relallvisible said it would not have to.

• MVCC, Dead Tuples and What VACUUM Does Not Do

  That VACUUM reclaims disk space and that running it more often fixes bloat. Plain VACUUM never shrinks the file except by truncating a wholly empty tail, and a single old transaction, replication slot or prepared transaction pins the removable cutoff so that no dead tuple newer than it can be removed at any frequency — the table bloats while every dashboard shows autovacuum succeeding.

• SELECT FOR UPDATE: Why Eight Workers Do the Work of One

  That adding FOR UPDATE to a queue poller makes N workers safe, so N workers do N times the work. They do not: every worker's scan reaches the same first eligible row, N-1 of them block on it, and throughput collapses to one transaction per hold time no matter how many workers you add — FOR UPDATE SKIP LOCKED is not a speed-up of that plan, it is a different plan that returns different rows. The second half of the belief is that the lock covers the condition you selected on. It covers tuples. Nothing stops a concurrent INSERT of a row that would have matched, and a FOR UPDATE lock on a parent row blocks INSERTs into any child table that references it, because a foreign-key check runs SELECT ... FOR KEY SHARE and FOR UPDATE is the one mode that conflicts with it.

• Getting the Disk Back: VACUUM FULL, pg_repack and What They Really Lock

  That VACUUM FULL costs you exactly as long as the rewrite takes, so a quiet window makes it safe, and that pg_repack is the lock-free version. Both halves are wrong. The ACCESS EXCLUSIVE request queues behind whatever query is already running, and because a request that conflicts with a waiting request must also wait, every query arriving after it queues too — so the outage starts before the rewrite does and lasts the running query plus the rewrite. pg_repack still needs ACCESS EXCLUSIVE twice, resolves the wait by cancelling and then terminating your backends, and needs twice the size of the table in free disk, which is the one thing you do not have.