How to Analyze Table Bloat with MCP-PostgreSQL-Ops: Tools and Usage Guide

MCP-PostgreSQL-Ops provides two dedicated MCP tools—get_table_bloat_analysis and get_database_bloat_overview—that query pg_stat_user_tables to calculate dead-tuple ratios, estimate wasted space, and recommend VACUUM operations.

The call518/mcp-postgresql-ops repository exposes specialized database maintenance utilities through the Model Context Protocol (MCP). If you need to analyze table bloat with MCP-PostgreSQL-Ops, the toolkit offers precise instrumentation for identifying storage inefficiencies caused by dead tuples across individual tables or entire schemas.

Available Bloat Analysis Tools

Two primary functions handle bloat detection in src/mcp_postgresql_ops/mcp_main.py:

get_table_bloat_analysis: Located at line 1887, this tool calculates per-table bloat ratios by comparing dead tuples against live tuples in pg_stat_user_tables. It returns metrics including bloat_ratio_percent, estimated_bloat_size, and vacuum recommendations.

get_database_bloat_overview: Found at line 2140, this function aggregates bloat statistics across all non-system schemas. It summarizes total_dead_tuples, overall_bloat_percent, and identifies never_vacuumed_tables at the schema level.

Both tools are decorated with @mcp.tool() and automatically register with the MCP server upon startup. Helper utilities in src/mcp_postgresql_ops/functions.py handle query execution and result formatting, while src/mcp_postgresql_ops/prompt_template.md documents these tools for LLM discovery.

How to Analyze Table Bloat

Via Natural Language Prompts

When running the MCP server, invoke the tools through conversational queries. The MCP engine parses your intent and routes to the appropriate function.

Example prompts:

• Analyze table bloat for tables with more than 5000 dead tuples in the public schema of the inventory database.
• Show a database-wide bloat summary for the ecommerce database.

The server executes the underlying async function and returns formatted markdown tables showing bloat metrics.

Via Direct Function Calls

For custom Python integrations, import and call the async functions directly:

import asyncio
from mcp_postgresql_ops.mcp_main import get_table_bloat_analysis, get_database_bloat_overview

async def analyze_bloat():
    # Detailed table analysis

    table_report = await get_table_bloat_analysis(
        database_name="inventory",
        schema_name="public",
        min_dead_tuples=5000,
        limit=30
    )
    print(table_report)
    
    # Database-wide overview

    db_report = await get_database_bloat_overview(
        database_name="ecommerce",
        limit=10
    )
    print(db_report)

asyncio.run(analyze_bloat())

Key Parameters

Both functions accept these arguments:

• database_name: Target database (defaults to POSTGRES_DB environment variable)
• schema_name: Filter to specific schema (omitted scans all non-system schemas)
• table_pattern: SQL LIKE pattern for table name filtering (e.g., '%log%')
• min_dead_tuples: Threshold for inclusion (set to 1 to show all bloat)
• limit: Maximum results (1-100 for tables, 1-50 for schemas)

System schemas (information_schema, pg_catalog, pg_%) are automatically excluded.

Interpreting Bloat Analysis Results

The tools utilize format_table_data() from src/mcp_postgresql_ops/functions.py to render structured output.

Table-level metrics include:

• schema_name and table_name
• total_size / table_size (human-readable)
• dead_tuples vs live_tuples counts
• bloat_ratio_percent: Calculated as (dead tuples / total tuples) × 100
• estimated_bloat_size: Human-readable wasted space estimate
• vacuum_status: Indicates "Never vacuumed" or "Manual vacuum needed"

Database-level overview provides:

• total_tables and tables_with_bloat counts
• total_dead_tuples across the schema
• overall_bloat_percent and estimated_total_bloat
• never_vacuumed_tables requiring immediate attention

Advanced Filtering Examples

Filter by table name pattern to isolate specific workloads:

await get_table_bloat_analysis(
    database_name="ecommerce",
    table_pattern="user_%",  # Matches user_logs, user_sessions

    min_dead_tuples=100,
    limit=20
)

This queries only tables matching the SQL LIKE pattern while respecting the minimum dead tuple threshold.

Summary

• MCP-PostgreSQL-Ops exposes get_table_bloat_analysis and get_database_bloat_overview in src/mcp_postgresql_ops/mcp_main.py to analyze table bloat via dead-tuple ratios.
• Both tools exclude system schemas automatically and support filtering by schema_name, table_pattern, and min_dead_tuples.
• Results include bloat_ratio_percent, estimated_bloat_size, and vacuum status indicators formatted as markdown tables.
• You can invoke these through natural language prompts handled by the MCP server or direct async Python function calls.

Frequently Asked Questions

What PostgreSQL statistics do the bloat tools query?

The tools query pg_stat_user_tables to obtain live and dead tuple counts, then calculate ratios and estimate wasted space based on table sizes. This approach avoids the performance overhead of pgstattuple while providing actionable bloat estimates.

Can I analyze bloat in specific schemas only?

Yes. Pass the schema_name parameter to restrict analysis to a single schema. If omitted, the tools scan all non-system schemas (excluding information_schema, pg_catalog, and pg_% patterns).

What is the difference between table-level and database-wide bloat analysis?

get_table_bloat_analysis provides granular per-table metrics including individual vacuum status and precise bloat ratios, while get_database_bloat_overview aggregates statistics at the schema level to show total dead tuples, overall bloat percentages, and counts of never-vacuumed tables.

Do the tools require superuser privileges?

The tools require read access to pg_stat_user_tables and pg_class, which are typically available to all users. However, viewing size statistics for all schemas may require appropriate privileges or membership in the pg_read_all_stats role depending on your PostgreSQL configuration.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}