bloat-and-vacuum-review

The text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.

Review bloat and autovacuum health for schema shop. 0. Call checkPrivileges. tableBloat and indexBloat are denied outright to a role without the scanning grant, and tableStats returns null statistics rather than an error when it cannot read a column -- which looks exactly like a table nobody has analyzed. 1a. Call listPartitions for shop first. Autovacuum runs per PARTITION, so a partitioned parent has no vacuum state of its own: its n_dead_tup is always zero and ranking parents finds nothing while one child falls behind. Where a parent exists, take its partitions from partitionDetails and rank those instead. Read default_rows while you are there -- rows in a DEFAULT partition matched no bound, which is a missing partition that has not failed loudly yet, and for a RANGE parent the highest upper bound against now() says whether the next period's partition exists at all. 1. Call listTableStats for shop. Rank by n_dead_tup and read last_vacuum and last_autovacuum beside it: a large dead-tuple count on a table vacuumed minutes ago is a busy table, not a neglected one. Read those two apart, because together they answer the question this review is actually for. A recent last_autovacuum means autovacuum is reaching the table and the settings are working. A recent last_vacuum beside a null or ancient last_autovacuum means the opposite: somebody is keeping this table alive by hand, autovacuum is not doing it, and the cron job is hiding the finding rather than being it. Both look identical if you only ask when the table was last vacuumed. last_analyze and last_autoanalyze divide the same way. Ranking by dead tuples alone misses the table most likely to hurt you, because an insert-only table never accumulates any. Read n_ins_since_vacuum as a second ranking: a large value beside an old last_vacuum is a table autovacuum reaches only through the insert threshold, and one that is never vacuumed is also never frozen, which makes it step 3's problem rather than this step's. Where the server reports it (PostgreSQL 16 and later), n_tup_newpage_upd against the update count says how often an update could not stay on its page. That is a cause rather than a symptom: it means HOT is failing, either because fillfactor leaves no room or because an index covers a column the workload keeps changing, and no amount of vacuuming fixes either. 2. Call replicationSlots before concluding anything. An inactive or lagging slot holds back the xmin horizon, which stops vacuum from removing dead tuples cluster-wide -- and no amount of autovacuum tuning fixes it. This is the single most common wrong diagnosis in this area. Then find out whether the slot will ever advance, because that decides whether to wait or to drop it. For a logical slot the consumer is a subscriber on another server: call subscriptionStats on that connection. A subscriber with a climbing apply_error_count or a table still copying is stuck but alive, and fixing it releases the horizon; one that reports nothing at all is gone, and the slot is abandoned. Those two look identical from this side. There is a third state that looks like abandonment and is not. Call listSubscriptions there too and read enabled beside disable_on_error. A subscription created with disable_on_error turns ITSELF off the first time apply fails, so what you see is an inactive slot, a disabled subscription, and WAL piling up behind a consumer that is neither broken nor gone -- it is waiting, exactly as configured. The fix is the apply error plus ALTER SUBSCRIPTION ENABLE, and dropping the slot here would destroy a subscriber that was one command from resuming. enabled false with disable_on_error false is the opposite: somebody disabled it by hand, and the question is who and whether they meant to leave it. 3. Call wraparoundStatus. If any database or table is approaching autovacuum_freeze_max_age, that outranks ordinary bloat. If a vacuum is running now, call progressStats before concluding that autovacuum is too slow. On PostgreSQL 18 and newer it reports delay_time_ms and delay_percent: the time that vacuum has spent ASLEEP on the cost-based delay. A vacuum that is mostly sleeping is being throttled by vacuum_cost_delay and vacuum_cost_limit, and it is indistinguishable in blocks-per-second from one on a slow disk. The two have opposite fixes -- raise the cost limit, or buy I/O -- and nothing else here tells them apart. 4. For the worst few tables, call tableBloat to measure rather than estimate. Leave exact at its default first; the approximation is usually enough to rank them. 5. For an index that looks redundant, duplicateIndexes says it is covered and idx_scan says nobody used it SINCE THE COUNTERS WERE RESET -- read counters_since before reading idx_scan at all. pg_stat_reset() zeroes idx_scan with everything else, so a zero on a database reset last week is an index unused for a week, which is what a month-end report, a quarterly job or a failover path looks like too. Where the window is short, idx_scan = 0 is not evidence and the honest answer is to say so and wait rather than to drop a large index on it. Note also that counters_since is a lower bound: pg_stat_reset_single_table_counters() zeroes one relation without moving it. And even a genuine zero does not prove the planner would not miss the index. Where hypopg is installed, evaluateIndex with 'hide' plans the query without the index and settles it. Dropping an index is easy; rebuilding one on a large table is not. 6. Only where bloat is confirmed, look at per-table storage parameters via tableDetails.reloptions and propose changes. Say which reading justifies each one. The autovacuum thresholds are the usual answer for a table that is vacuumed too rarely; fillfactor is the answer for one where n_tup_newpage_upd showed HOT failing, and it treats the cause rather than the residue.

bloat-and-vacuum-review

Synopsis

Arguments

What it asks the model to do

Example mocked arguments