Lab 10: Work out exactly when autovacuum will run, and how fast

Objective

“Autovacuum is not keeping up” is one of the most confidently stated and least often verified claims in PostgreSQL operations. It is usually made without anybody having worked out when autovacuum was supposed to run on the table in question.

That number is computable. By the end of this lab you will have calculated it for a real table from that table’s own statistics, predicted the moment autovacuum would fire, and been right.

You will also find something the formula does not explain. On the first attempt in this lab autovacuum runs when it should not have, and chasing that down leads to a second trigger that has nothing to do with dead rows at all — one that will vacuum a table you have only ever inserted into.

Finally you will measure the cost budget, and see identical vacuum work take 69 milliseconds or 4.4 seconds depending on one setting.

Architecture

Two tables in one database: one updated heavily, one only inserted into, so the two triggers can be told apart.

flowchart TD
    S["autovacuum launcher\nevery autovacuum_naptime = 60s"] --> C1{"dead tuples >\nthreshold + scale_factor * reltuples\n(capped at max_threshold)"}
    S --> C2{"inserts since last vacuum >\ninsert_threshold + insert_scale_factor * reltuples"}
    C1 -->|yes| W["autovacuum worker"]
    C2 -->|yes| W
    W --> B["cost budget:\naccumulate cost, sleep\nwhen it exceeds cost_limit"]
    B --> R["pg_stat_user_tables\nautovacuum_count, last_autovacuum"]
    B --> L["log_autovacuum_min_duration\nfull per-run report"]

Requirements

A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab10.
Time. Several tasks wait a full autovacuum_naptime (60 seconds) so that the launcher gets a chance to act. Do not shorten it; watching the delay is part of understanding the behaviour.
log_autovacuum_min_duration = 0 so every run is logged. The lab sets it and resets it.

Scenario

A table is bloating. The team’s proposed fix is “make autovacuum more aggressive”, by which they mean lowering autovacuum_naptime cluster-wide.

Before changing anything you want to know three things: at what point autovacuum should be processing this table, whether it is in fact doing so, and if it is, why that is not enough.

Tasks

Task 1 — Read the settings that decide everything

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

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab10;"
docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET log_autovacuum_min_duration = 0;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, unit, context FROM pg_settings
  WHERE name LIKE 'autovacuum%' ORDER BY name;" | tee "$LAB/thresholds.txt"

Read-only / Safethe autovacuum settings on a PostgreSQL 18.6 cluster— Captured from the lab run of 2026-08-28. Defaults throughout.

$ psql -X -c "SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE 'autovacuum%' ORDER BY name;"

                 name                  |  setting  | unit |  context   
---------------------------------------+-----------+------+------------
autovacuum                            | on        |      | sighup
autovacuum_analyze_scale_factor       | 0.1       |      | sighup
autovacuum_analyze_threshold          | 50        |      | sighup
autovacuum_freeze_max_age             | 200000000 |      | postmaster
autovacuum_max_workers                | 3         |      | sighup
autovacuum_multixact_freeze_max_age   | 400000000 |      | postmaster
autovacuum_naptime                    | 60        | s    | sighup
autovacuum_vacuum_cost_delay          | 2         | ms   | sighup
autovacuum_vacuum_cost_limit          | -1        |      | sighup
autovacuum_vacuum_insert_scale_factor | 0.2       |      | sighup
autovacuum_vacuum_insert_threshold    | 1000      |      | sighup
autovacuum_vacuum_max_threshold       | 100000000 |      | sighup
autovacuum_vacuum_scale_factor        | 0.2       |      | sighup
autovacuum_vacuum_threshold           | 50        |      | sighup
autovacuum_work_mem                   | -1        | kB   | sighup
autovacuum_worker_slots               | 16        |      | postmaster
(16 rows)

Four of these are the trigger for ordinary vacuuming: autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor. autovacuum_vacuum_max_threshold caps the first pair, and Task 7 covers why.

autovacuum_vacuum_cost_limit = -1 means “inherit vacuum_cost_limit”, which defaults to 200.

Task 2 — Compute when autovacuum will run on your table

docker exec -i -u postgres rbpg-lab01 psql -X -d lab10 <<'SQL'
CREATE TABLE churn(id int PRIMARY KEY, payload text, updated_at timestamptz DEFAULT now());
INSERT INTO churn SELECT g, repeat('x',50), now() FROM generate_series(1,100000) g;
ANALYZE churn;
SQL

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT c.relname,
         c.reltuples::bigint AS reltuples,
         current_setting('autovacuum_vacuum_threshold')::int AS base_threshold,
         current_setting('autovacuum_vacuum_scale_factor')::float AS scale_factor,
         (current_setting('autovacuum_vacuum_threshold')::int
          + current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)::bigint
           AS fires_at_dead_tuples,
         s.n_dead_tup
  FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
  WHERE c.relname = 'churn';" | tee -a "$LAB/thresholds.txt"

Read-only / Safethe number this table has to reach— Captured from the lab run of 2026-08-28.

$ a query joining pg_class.reltuples to the autovacuum settings

 relname | reltuples | base_threshold | scale_factor | fires_at_dead_tuples | n_dead_tup 
---------+-----------+----------------+--------------+----------------------+------------
churn   |    100000 |             50 |          0.2 |                20050 |          0
(1 row)

20,050. That is the number. Below it, autovacuum will not touch this table for dead tuples, no matter how often the launcher wakes up.

Keep that query. It is the one to run before anybody proposes tuning autovacuum, and it answers “should this table have been vacuumed by now” in one round trip.

Task 3 — Churn below the threshold, and get a surprise

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c \
  "UPDATE churn SET updated_at = now() WHERE id <= 15000;"

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum,
         pg_size_pretty(pg_total_relation_size('churn')) AS size
  FROM pg_stat_user_tables WHERE relname='churn';"

# Wait a full naptime so the launcher definitely had its chance.
sleep 65

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT n_dead_tup, autovacuum_count, last_autovacuum
  FROM pg_stat_user_tables WHERE relname='churn';"

Read-only / Safe15,000 dead tuples, well under 20,050 — and it vacuumed anyway— Captured from the lab run of 2026-08-28.

$ update 15,000 rows, check the counters, wait 65 seconds, check again

 n_live_tup | n_dead_tup | autovacuum_count | last_autovacuum | size  
------------+------------+------------------+-----------------+-------
   100000 |      15000 |                0 |                 | 13 MB
(1 row)

n_dead_tup | autovacuum_count |        last_autovacuum        
------------+------------------+-------------------------------
        0 |                1 | 2026-08-28 00:56:50.630226+00
(1 row)

The prediction was wrong. 15,000 is below 20,050, and autovacuum ran.

Do not adjust the formula to fit. Find out what actually happened:

docker exec rbpg-lab01 grep -A12 'automatic vacuum of table "lab10.public.churn"' \
  /var/log/postgresql/postgresql-18-main.log | head -14

Read-only / Safethe run really did happen, and it really did remove the 15,000— Captured from the lab run of 2026-08-28.

$ grep the cluster log for the autovacuum report

2026-08-28 00:56:50.630 UTC [10589] LOG:  automatic vacuum of table "lab10.public.churn": index scans: 1
pages: 0 removed, 1307 remain, 1307 scanned (100.00% of total), 0 eagerly scanned
tuples: 15000 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 838, which was 0 XIDs old when operation ended
new relfrozenxid: 835, which is 1 XIDs ahead of previous value
visibility map: 1307 pages set all-visible, 170 pages set all-frozen (0 were all-visible)
index scan needed: 171 pages from table (13.08% of total) had 15000 dead item identifiers removed
index "churn_pkey": pages: 317 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 0.572 MB/s
buffer usage: 3161 hits, 0 reads, 3 dirtied
WAL usage: 1735 records, 3 full page images, 254712 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.04 s

The formula was not wrong. It was incomplete — there is a second trigger, and the 100,000 rows inserted in Task 2 satisfied it.

Task 4 — Isolate the insert trigger

Prove it with a table that has never been updated at all:

docker exec -i -u postgres rbpg-lab01 psql -X -d lab10 <<'SQL'
CREATE TABLE insert_only(id int PRIMARY KEY, payload text);
INSERT INTO insert_only SELECT g, repeat('y',50) FROM generate_series(1,100000) g;
SQL

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_dead_tup,
         n_ins_since_vacuum, autovacuum_count
  FROM pg_stat_user_tables WHERE relname='insert_only';" | tee "$LAB/insert-trigger.txt"

sleep 65

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT relname, n_dead_tup, n_ins_since_vacuum, autovacuum_count, last_autovacuum
  FROM pg_stat_user_tables WHERE relname='insert_only';" | tee -a "$LAB/insert-trigger.txt"

docker exec rbpg-lab01 grep -A4 'automatic vacuum of table "lab10.public.insert_only"' \
  /var/log/postgresql/postgresql-18-main.log | head -5 | tee -a "$LAB/insert-trigger.txt"

Read-only / Safezero updates, zero deletes, zero dead tuples — vacuumed— Captured from the lab run of 2026-08-28. Note index scans: 0 and tuples: 0 removed.

$ create an insert-only table, wait a naptime, then read the counters and the log

   relname   | n_tup_ins | n_tup_upd | n_tup_del | n_dead_tup | n_ins_since_vacuum | autovacuum_count 
-------------+-----------+-----------+-----------+------------+--------------------+------------------
insert_only |    100000 |         0 |         0 |          0 |             100000 |                0
(1 row)

 relname   | n_dead_tup | n_ins_since_vacuum | autovacuum_count |        last_autovacuum        
-------------+------------+--------------------+------------------+-------------------------------
insert_only |          0 |                  0 |                1 | 2026-08-28 00:58:50.646619+00
(1 row)

2026-08-28 00:58:50.646 UTC [10629] LOG:  automatic vacuum of table "lab10.public.insert_only": index scans: 0
pages: 0 removed, 1031 remain, 1031 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 841, which was 0 XIDs old when operation ended
new relfrozenxid: 840, which is 1 XIDs ahead of previous value

n_tup_upd = 0, n_tup_del = 0, n_dead_tup = 0, and autovacuum ran. The log confirms it did zero cleanup work: index scans: 0, tuples: 0 removed.

The trigger is n_ins_since_vacuum exceeding `autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor

reltuples, which here is 1000 + 0.2 × 100000 = 21000`. There were 100,000.

Why an insert-only table needs vacuuming at all

Two reasons, and neither is about reclaiming space.

The visibility map. Look at the log line: visibility map: 1031 pages set all-visible. A page marked all-visible can be skipped by later vacuums and can serve index-only scans without touching the heap at all. An insert-only table that is never vacuumed never gets those marks, so every index-only scan degrades into a heap fetch per row.

Freezing. new relfrozenxid advanced. Row versions must eventually be frozen so their transaction ids can be reused, and on a table that is only ever inserted into there is no update or delete activity to trigger that work. Without the insert trigger, such tables would sit untouched until they hit autovacuum_freeze_max_age — at which point the cluster would perform one enormous emergency freeze pass instead of many small ones.

The insert trigger exists to spread that cost out. It is the reason a large append-only table no longer surprises you with a multi-hour anti-wraparound vacuum.

Task 5 — Now confirm the update trigger on its own

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c \
  "UPDATE churn SET updated_at = now() WHERE id <= 25000;"
docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT n_dead_tup, n_ins_since_vacuum, autovacuum_count,
         pg_size_pretty(pg_total_relation_size('churn')) AS size
  FROM pg_stat_user_tables WHERE relname='churn';"

sleep 65

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT n_dead_tup, autovacuum_count, last_autovacuum,
         pg_size_pretty(pg_total_relation_size('churn')) AS size
  FROM pg_stat_user_tables WHERE relname='churn';"

Read-only / Safe25,000 dead tuples with the insert counter at zero — the update trigger fires— Captured from the lab run of 2026-08-28. Watch the size column across both readings.

$ update 25,000 rows, check, wait 65 seconds, check again

 n_dead_tup | n_ins_since_vacuum | autovacuum_count | size  
------------+--------------------+------------------+-------
    25000 |                  0 |                1 | 14 MB
(1 row)

n_dead_tup | autovacuum_count |        last_autovacuum        | size  
------------+------------------+-------------------------------+-------
        0 |                2 | 2026-08-28 00:59:50.654585+00 | 14 MB
(1 row)

n_ins_since_vacuum was 0, so this can only have been the update trigger, and 25,000 is above 20,050. The prediction holds.

Now look at the size column. The table was 13 MB in Task 3, grew to 14 MB under churn, and is still 14 MB after a vacuum that removed every dead tuple.

Vacuum makes space reusable; it does not give it back

This is the single most misunderstood thing about vacuum. It marks space inside the file as available for future rows. It does not return it to the filesystem, and df will not change.

That is the correct behaviour: returning the space would mean asking the operating system for it again on the next insert, which is slower and fragments the file. A table that stabilises at 14 MB and stays there is a table whose vacuuming is working.

What vacuum cannot do is undo growth that already happened. If a table reached 40 GB because vacuum fell behind for a week, it stays 40 GB even after the backlog clears — the free space is inside it. Shrinking the file needs VACUUM FULL (an AccessExclusiveLock and a full rewrite) or an online repack tool. Preventing the growth in the first place is much cheaper than either.

Task 6 — Override the threshold for one table

Cluster-wide autovacuum settings are a compromise across every table. A table with a specific problem gets specific settings:

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  ALTER TABLE churn SET (autovacuum_vacuum_scale_factor = 0.01,
                         autovacuum_vacuum_threshold = 100);"

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT relname, reloptions FROM pg_class WHERE relname='churn';" | tee "$LAB/per-table.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT c.relname,
         (100 + 0.01 * c.reltuples)::bigint AS now_fires_at,
         (current_setting('autovacuum_vacuum_threshold')::int
          + current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)::bigint
           AS cluster_default_would_be
  FROM pg_class c WHERE c.relname='churn';" | tee -a "$LAB/per-table.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c \
  "UPDATE churn SET updated_at = now() WHERE id <= 2000;"
sleep 65
docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT n_dead_tup, autovacuum_count, last_autovacuum
  FROM pg_stat_user_tables WHERE relname='churn';" | tee -a "$LAB/per-table.txt"

Configuration changethe same table now vacuums at 1,100 dead tuples instead of 20,050— Captured from the lab run of 2026-08-28. 2,000 updates were enough.

$ set reloptions, recompute the trigger, churn 2,000 rows, wait a naptime

 relname |                              reloptions                               
---------+-----------------------------------------------------------------------
churn   | {autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=100}
(1 row)

relname | now_fires_at | cluster_default_would_be 
---------+--------------+--------------------------
churn   |         1100 |                    20050
(1 row)

n_dead_tup | autovacuum_count |        last_autovacuum        
------------+------------------+-------------------------------
        0 |                3 | 2026-08-28 01:00:50.642918+00
(1 row)

Per-table beats cluster-wide, almost always

The instinct on discovering a bloating table is to change autovacuum_vacuum_scale_factor globally. That applies the change to every table in the cluster, including the thousand small ones where the default was fine, and it makes the total vacuum workload harder to reason about.

ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01) on the two tables that need it is a smaller, more defensible change, it is visible in pg_class.reloptions where the next engineer will find it, and it does not require a reload.

The one cluster-wide setting genuinely worth revisiting is the default scale factor of 0.2 itself: on a table of a hundred million rows, 20% is twenty million dead tuples before anything happens. Many sites run 0.05 or lower as their default and raise it for specific tables instead.

Task 7 — The cap that PostgreSQL 18 added

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, boot_val, short_desc FROM pg_settings
  WHERE name = 'autovacuum_vacuum_max_threshold';"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT n AS reltuples,
         (50 + 0.2*n)::bigint AS uncapped_threshold,
         least((50 + 0.2*n)::bigint,
               current_setting('autovacuum_vacuum_max_threshold')::bigint) AS effective_threshold
  FROM (VALUES (1000::numeric),(1000000),(100000000),(5000000000)) AS t(n);"

Read-only / Safethe scale factor stops mattering above half a billion rows— Captured from the lab run of 2026-08-28.

$ read the setting, then compute the capped and uncapped thresholds at four table sizes

              name               |  setting  | boot_val  |                         short_desc                          
---------------------------------+-----------+-----------+-------------------------------------------------------------
autovacuum_vacuum_max_threshold | 100000000 | 100000000 | Maximum number of tuple updates or deletes prior to vacuum.
(1 row)

reltuples  | uncapped_threshold | effective_threshold 
------------+--------------------+---------------------
     1000 |                250 |                 250
  1000000 |             200050 |              200050
100000000 |           20000050 |            20000050
5000000000 |         1000000050 |           100000000
(4 rows)

The scale factor is a percentage, so on very large tables it produces absurd thresholds — a five-billion-row table would need a billion dead tuples before the default settings did anything.

autovacuum_vacuum_max_threshold puts a ceiling on the computed value. It is a floor on how often very large tables get vacuumed, expressed as a cap on the threshold. On smaller tables it never binds.

Task 8 — Measure the cost budget

Autovacuum is deliberately slow. It accumulates a cost for every page it touches and sleeps when the accumulated cost exceeds its budget.

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, unit FROM pg_settings
  WHERE name IN ('autovacuum_vacuum_cost_delay','autovacuum_vacuum_cost_limit',
                 'vacuum_cost_limit','vacuum_cost_page_hit',
                 'vacuum_cost_page_miss','vacuum_cost_page_dirty')
  ORDER BY name;" | tee "$LAB/cost-budget.txt"

Read-only / Safethe cost model, in full— Captured from the lab run of 2026-08-28.

$ psql -X -c "SELECT name, setting, unit FROM pg_settings WHERE name IN (...) ORDER BY name;"

 autovacuum_vacuum_cost_delay | 2       | ms
autovacuum_vacuum_cost_limit | -1      | 
vacuum_cost_limit            | 200     | 
vacuum_cost_page_dirty       | 20      | 
vacuum_cost_page_hit         | 1       | 
vacuum_cost_page_miss        | 2       | 
(6 rows)

A page already in shared buffers costs 1, a page that must be read costs 2, and a page the vacuum dirties costs 20. When the running total exceeds vacuum_cost_limit, the process sleeps for vacuum_cost_delay and resets.

Measure the effect on identical work:

for LIMIT in 10000 200 20; do
  docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c \
    "UPDATE churn SET updated_at = now();" > /dev/null
  START=$(date +%s%N)
  docker exec -u postgres rbpg-lab01 psql -X -d lab10 \
    -c "SET vacuum_cost_limit = $LIMIT;" \
    -c "SET vacuum_cost_delay = '5ms';" \
    -c "VACUUM churn;" > /dev/null
  END=$(date +%s%N)
  printf "vacuum_cost_limit=%-6s -> %s ms\n" "$LIMIT" "$(( (END-START)/1000000 ))"
done | tee -a "$LAB/cost-budget.txt"

Read-only / Safethe same 100,000 dead tuples, three budgets, a 63-fold spread— Captured from the lab run of 2026-08-28. Timed from the shell around each psql invocation.

$ three VACUUM runs at different cost limits on identical work

vacuum_cost_limit=10000  -> 69 ms
vacuum_cost_limit=200    -> 377 ms
vacuum_cost_limit=20     -> 4368 ms

Identical work. The only difference is how much of it the process is allowed to do between sleeps.

Note the three separate -c flags

Writing this as one -c "SET vacuum_cost_limit = 20; VACUUM churn;" fails:

ERROR:  VACUUM cannot run inside a transaction block

psql wraps a multi-statement -c argument in a single implicit transaction, and VACUUM cannot run inside one. Separate -c flags share the session — so the SET still applies — but each runs as its own transaction.

The same trap catches ALTER SYSTEM, CREATE DATABASE, CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY, all of which cannot run inside a transaction block.

This is the setting that usually matters, not naptime

When autovacuum genuinely cannot keep up on modern hardware, the cause is almost always the cost budget rather than the schedule. The default limit of 200 was chosen when a good disk did a few hundred random I/Os per second; on NVMe storage it throttles vacuum to a small fraction of what the hardware would sustain.

Lowering autovacuum_naptime does not help here. The launcher already finds the table; the worker then crawls. Raising autovacuum_vacuum_cost_limit — 1000 to 2000 is a common starting point — or lowering autovacuum_vacuum_cost_delay is what changes throughput.

Make the change deliberately. The throttle exists so that vacuum does not compete with your workload for I/O, and removing it entirely on a storage-constrained system trades a bloat problem for a latency problem.

Task 9 — The question the whole lab was for

docker exec -u postgres rbpg-lab01 psql -X -d lab10 -c "
  SELECT relname, n_live_tup, n_dead_tup,
         round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
         autovacuum_count, vacuum_count,
         last_autovacuum, now() - last_autovacuum AS since_last
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;"

Read-only / Safeis autovacuum keeping up?— Captured from the lab run of 2026-08-28, after all the churn above.

$ the standard autovacuum health query over pg_stat_user_tables

   relname   | n_live_tup | n_dead_tup | dead_pct | autovacuum_count | vacuum_count |        last_autovacuum        |   since_last    
-------------+------------+------------+----------+------------------+--------------+-------------------------------+-----------------
churn       |     257338 |          0 |      0.0 |                5 |            3 | 2026-08-28 01:03:51.837834+00 | 00:00:25.819148
insert_only |     100000 |          0 |      0.0 |                1 |            0 | 2026-08-28 00:58:50.646619+00 | 00:05:27.010363
(2 rows)

Read it in this order:

dead_pct — how far behind the table is right now.
autovacuum_count — whether autovacuum is processing it at all. A large, churning table with a count of 0 is the real emergency.
since_last — whether it has been processed recently.

Note that n_live_tup for churn reads 257,338 against 100,000 actual rows. These counters are estimates maintained incrementally by the statistics collector, corrected at each ANALYZE. They are the right tool for spotting a problem and the wrong tool for an exact answer; count(*) is the exact answer when you need one.

Validation

test -s "$LAB/thresholds.txt"     && echo "OK thresholds"
test -s "$LAB/insert-trigger.txt" && echo "OK insert-trigger"
test -s "$LAB/per-table.txt"      && echo "OK per-table"
test -s "$LAB/cost-budget.txt"    && echo "OK cost-budget"

grep -q "20050"                "$LAB/thresholds.txt"     && echo "OK threshold computed"
grep -q "index scans: 0"       "$LAB/insert-trigger.txt" && echo "OK insert trigger isolated"
grep -q "autovacuum_vacuum_scale_factor=0.01" "$LAB/per-table.txt" && echo "OK per-table override"
grep -q "vacuum_cost_limit=20"  "$LAB/cost-budget.txt"   && echo "OK cost budget measured"

Questions to answer without looking anything up:

A table has 400,000 rows and 60,000 dead tuples on default settings. Should autovacuum have processed it? Show your working.
A table has only ever been inserted into. Why would autovacuum touch it, and what two things does that run accomplish?
Vacuum removed every dead tuple and the table is still the same size. Is that a fault?
Autovacuum runs on a large table every few minutes and it still bloats. Which setting would you look at first, and why not autovacuum_naptime?
Why does SET vacuum_cost_limit = 20; VACUUM t; in a single psql -c fail?

Expected Outcome

You can now compute, for any table, the number of dead tuples at which autovacuum will process it, and you know that number is not the only trigger.

The query to carry away:

SELECT c.relname,
       s.n_dead_tup,
       (coalesce((SELECT option_value::int FROM pg_options_to_table(c.reloptions)
                  WHERE option_name = 'autovacuum_vacuum_threshold'),
                 current_setting('autovacuum_vacuum_threshold')::int)
        + coalesce((SELECT option_value::float FROM pg_options_to_table(c.reloptions)
                    WHERE option_name = 'autovacuum_vacuum_scale_factor'),
                   current_setting('autovacuum_vacuum_scale_factor')::float)
          * c.reltuples)::bigint AS fires_at,
       s.autovacuum_count, s.last_autovacuum
FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
ORDER BY s.n_dead_tup DESC;

It respects per-table overrides, so it tells you the real threshold rather than the cluster default — which is exactly the number missing from most “autovacuum is not keeping up” conversations.

Troubleshooting

Autovacuum never runs on your table. Check the launcher is alive (ps -ef | grep autovacuum), and that both autovacuum and track_counts are on. Measured on 18.6 both have a boot_val of on, so a cluster where either is off has had it turned off deliberately — and track_counts is what feeds the statistics the thresholds are computed from, so with it off nothing ever triggers.

Autovacuum fired earlier than your arithmetic predicted. This is Task 3’s surprise and it is real: the insert threshold (autovacuum_vacuum_insert_threshold plus scale factor) is a separate trigger from the dead-tuple threshold, and on an insert-heavy table it fires first. Task 4 isolates it on a table with zero updates.

last_autovacuum is populated but the bloat is unchanged. That column records a start, not a completion. PostgreSQL exposes no column saying a vacuum finished. A worker that was cancelled — by a conflicting lock request — leaves the timestamp and keeps none of its progress.

Per-table reloptions appear to be ignored. They are stored on the table and shown by pg_options_to_table(c.reloptions). If the query still shows the cluster default, the ALTER TABLE named a different option than you think; the names are prefixed autovacuum_.

The cost budget makes no visible difference. Raise the churn. autovacuum_vacuum_cost_delay defaults to 2 ms and autovacuum_vacuum_cost_limit to -1 (meaning it inherits vacuum_cost_limit, 200). On a small table the whole vacuum finishes inside one budget window.

Statistics look stale. The statistics collector updates asynchronously. SELECT pg_stat_force_next_flush(); before reading, or allow a moment between the churn and the query.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET log_autovacuum_min_duration;"
docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET autovacuum_vacuum_cost_limit;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab10;"

Production notes

Compute the real threshold per table, with overrides applied, rather than quoting the cluster default. The query in Expected Outcome is the one to put in a dashboard; the cluster default is almost never the number that matters for the table you are worried about.
Treat last_autovacuum as “a worker started at this time”. If you need to know whether vacuum is keeping up, watch n_dead_tup over time — a value that returns to near zero is completion; a sawtooth with a rising floor is not.
Set per-table overrides on the few large, high-churn tables rather than lowering the cluster-wide scale factor. A global change alters behaviour on every table in the cluster, including the ones that were fine.
The insert threshold catches append-only tables that the dead-tuple threshold never would — which is what keeps their visibility maps current and their index-only scans working.
If autovacuum cannot keep up, establish which of the six causes it is before changing a setting. Cancellation by DDL and a pinned horizon both look like “autovacuum is too slow” and neither is fixed by tuning.

What You Learned

The threshold is computed from the table’s own statistics: threshold + scale_factor × reltuples, with per-table overrides taking precedence.
There is more than one trigger. The insert threshold fires on a table with no updates at all — measured here, and it is why autovacuum ran at 15,000 dead tuples against a computed 20,050.
last_autovacuum records a start. No column records a completion.
A cancelled vacuum keeps nothing. The next run begins at the start of the table.
The cost budget is a throttle, not a priority. cost_delay and cost_limit bound the I/O a worker may do, and on a large table they are what decides whether it finishes before the next one is due.
Per-table reloptions are the right tool for the handful of tables that need different behaviour from the cluster.

Objectives

Prerequisites