pg-bloat-detective: Postgres Bloat and VACUUM Diagnosis

Teaches your agent to use the open-source pg-bloat-detective tool (MIT, on PyPI and the official MCP registry) to diagnose PostgreSQL bloat without writing to your database.

What it does

• Collects read-only snapshots (read-only transactions, 5s statement timeout) and returns one verdict per table or index.
• Table verdicts: normal, vacuum-starved, blocked by an idle transaction, replication slot, prepared transaction or active transaction, and needs-rewrite.
• Index verdicts: index-bloated (needs REINDEX) and index-unused (DROP candidate, confirm with pg_stat_statements first).
• Names the PID, query and xmin age holding VACUUM back, and gives the matching fix.

How it works

The skill walks your agent through installing the tool (pip install pg-bloat-detective), running the bloat-mcp server, and using its five tools: bloat_check, bloat_collect, bloat_timeline, bloat_blockers and bloat_live_diagnose. It never runs fixes for you. It shows the statement and the evidence and you decide.

Works with Postgres 12-17 on RDS, Neon, Supabase or self-hosted, per the project README. Source: https://github.com/r69shabh/pg-bloat-detective