pg-bloat-detective: Postgres Bloat and VACUUM Diagnosis

    1

    Diagnose Postgres table and index bloat, find what is blocking VACUUM, and get the cheapest fix with PID and query evidence. Read-only, works with the pg-bloat-detective MCP server.

    Free

    1 useSecurity scanned

    Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    About this skill

    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

    Requires Python environment and pg-bloat-detective package. Default 1GB guard on large index scans. Needs pgstattuple extension for exact bloat metrics. Read-only access only.

    How to use

    1. 1

      Connect your agent to the Agensi MCP

      Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup

    2. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 3

      Ask for what you need

      Your agent loads this skill and uses it on your task. It always asks before any payment.

    Reviews

    No reviews yet

    Be one of the first to try it. Every listed skill goes through the trust checks below.

    Security scanned

    Checked by our 8-point scan before listing

    1 use

    Times used to date

    Free forever

    No account required to browse

    Trust & safety

    Security scan

    Security scan passed 1 month ago

    • Free with an Agensi account

    Requires

    Bloat MCP
    Python

    Permissions

    Network Access
    Terminal / Shell
    Environment Variables
    Listed1 month ago

    What's inside

    Frequently Asked Questions

    This skill automates the identification of disk-space-wasting 'bloat' by analyzing system catalogs and leaf density, saving you from writing manual, complex SQL queries to find hidden performance bottlenecks.

    Yes, the 'pg bloat detective' is designed to work without superuser privileges or write access, making it safe for use in production environments where security is a priority.

    The skill is compatible with PostgreSQL version 12 and newer; for the most precise leaf density metrics, having the `pgstattuple` extension pre-installed on your database is recommended.

    Unlike static prompts, this skill uses a SQLite-backed history to track bloat trends over time, helping you distinguish between a temporary spike in dead tuples and chronic, structural growth.

    It identifies specific PIDs, active queries, and stale replication slots that are pinning the `backend_xmin` and preventing Autovacuum from cleaning up dead tuples.

    To prevent performance degradation on your database, the skill automatically skips diagnostic scans for indexes larger than 1GB by default.

    New in Data & Databases

    Free