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

> Analyze PostgreSQL table bloat with MCP-PostgreSQL-Ops tools. Learn how to estimate wasted space and optimize VACUUM operations using get_table_bloat_analysis and get_database_bloat_overview.

- Repository: [JungJungIn/mcp-postgresql-ops](https://github.com/call518/mcp-postgresql-ops)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/functions.py) handle query execution and result formatting, while [`src/mcp_postgresql_ops/prompt_template.md`](https://github.com/call518/mcp-postgresql-ops/blob/main/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:

```python
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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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:

```python
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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.