Decide whether bloat is real, and whether autovacuum is keeping up.
promptread-only tools
bloat-and-vacuum-review([schema])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. Before calling anything neglected, read cumulative_statistics_missing. Null vacuum and analyze times with n_live_tup at zero beside a row count in the millions are not a missing maintenance job: they are cumulative statistics that are not there, and the flag is true on exactly those tables. Crash recovery and an immediate shutdown discard them, a restore or an upgrade does not carry them over, and a replica never has them, because vacuum and analyze are recorded on the primary. Call tableStats on one flagged table: its note says which of those this server is, counters_since says how far back the counters reach, and counters_since_source names a server-wide view rather than pg_stat_database where the database itself has no reset time. Say that the counters cover only that window, and judge maintenance from the primary, or after autovacuum has had time to come round again -- not from these fields. 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 n_tup_upd says how often an update could not stay on its page; on every version n_tup_hot_upd against n_tup_upd says how often one did. 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. Where counters_since_source is not pg_stat_database the database carries no reset time and the figure is the earliest server-wide reset instead, which is what crash recovery leaves behind. 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.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "bloat-and-vacuum-review",
"arguments": {
"schema": "shop"
}
}
}