How the MCP Server Handles Autovacuum Monitoring and Provides Operational Insights

The MCP PostgreSQL Operations server monitors autovacuum through three specialized asynchronous tools that query PostgreSQL's internal statistics views to calculate thresholds, track historical activity, and display live vacuum operations in human-readable formats.

The call518/mcp-postgresql-ops repository implements a Model Context Protocol (MCP) server that exposes database operational tools as callable functions. When handling autovacuum monitoring, the MCP server leverages asyncpg connections to inspect PostgreSQL's internal catalogs and compute urgency metrics without requiring manual SQL interpretation.

Data Retrieval Architecture

All autovacuum-related functions rely on the generic query executor defined in src/mcp_postgresql_ops/functions.py. The execute_query function establishes a lightweight asyncpg connection, runs the supplied SQL, and returns a list of dictionaries.

The helper get_current_database_name resolves the active database context so every response is explicitly labeled with the target database. For output formatting, format_table_data (lines 30-68 in functions.py) converts raw dictionaries into readable ASCII tables, automatically converting byte sizes to human-readable units and seconds to minutes or hours.

Autovacuum Status Analysis

The get_autovacuum_status function in src/mcp_postgresql_ops/mcp_main.py (lines 2228-2372) provides a comprehensive threshold-based assessment of table health.

Threshold Calculation and Urgency Classification

The function queries pg_stat_user_tables to retrieve live and dead tuple counts for each table. It computes the standard autovacuum threshold using the PostgreSQL default formula:

0.2 * n_live_tup + 50

Based on the ratio of dead tuples to this threshold, the function assigns one of four urgency levels:

• "NEEDS AUTOVACUUM NOW" – Dead tuples significantly exceed the threshold
• "APPROACHING THRESHOLD" – Dead tuples are within critical range of the threshold
• "MONITOR CLOSELY" – Elevated dead tuple count requiring observation
• "OK" – Dead tuples well below threshold

The output includes the percentage of threshold reached, hours elapsed since last_autovacuum, table size, and a formatted urgency flag.

Historical Activity Tracking

The get_autovacuum_activity function (lines 2376-2500 in mcp_main.py) focuses on execution history rather than current thresholds.

This tool examines autovacuum and autoanalyze activity over a configurable time window (defaulting to 24 hours). It reports:

• Count of autovacuum and autoanalyze runs per table
• Time elapsed since the last autovacuum or autoanalyze
• Activity classification levels including "NEVER AUTOVACUUMED", "NO RECENT ACTIVITY", "VERY RECENT", and "RECENT"

Results are ordered by inactivity duration, surfacing tables that have been neglected longest. This helps operators identify tables that may require manual intervention or configuration tuning.

Live Operations Monitoring

For real-time diagnostics, the get_running_vacuum_operations function (lines 2524-2625 in mcp_main.py) queries pg_stat_activity to capture active maintenance commands.

This function identifies currently executing VACUUM, ANALYZE, REINDEX, and CLUSTER statements, extracting:

• Process ID and database context
• User and client address
• Operation start time and elapsed duration
• Operation type and target table (when detectable from the query text)
• Impact level hints such as "HIGH (Exclusive Lock)" for operations holding restrictive locks

This provides immediate visibility into maintenance jobs that may be causing performance degradation or blocking other queries.

MCP Tool Integration and Usage

Each monitoring function is decorated with @mcp.tool(), making them automatically discoverable through the MCP CLI or API. The tools can be invoked programmatically or via command line:

# Programmatic usage example

import asyncio
from mcp_postgresql_ops.mcp_main import get_autovacuum_status

async def check_tables():
    report = await get_autovacuum_status(schema_name="public", limit=20)
    print(report)

asyncio.run(check_tables())

# CLI usage examples

mcp get_autovacuum_status --schema=public --limit=15
mcp get_autovacuum_activity --hours-back=48
mcp get_running_vacuum_operations

Summary

• The MCP server handles autovacuum monitoring through three specialized tools in mcp_main.py that query PostgreSQL's internal statistics views.
• get_autovacuum_status calculates standard PostgreSQL thresholds (0.2 × live tuples + 50) and classifies urgency into four levels from "OK" to "NEEDS AUTOVACUUM NOW".
• get_autovacuum_activity tracks historical execution patterns over configurable time windows, ordering results by inactivity to highlight neglected tables.
• get_running_vacuum_operations provides real-time visibility into active VACUUM, ANALYZE, REINDEX, and CLUSTER commands with lock impact assessments.
• All tools utilize the shared execute_query and format_table_data utilities from functions.py and are exposed via the @mcp.tool() decorator for CLI and API access.

Frequently Asked Questions

How does the MCP server determine when a table needs autovacuum?

The server applies PostgreSQL's default threshold formula (0.2 * n_live_tup + 50) to each table's live tuple count. It compares the current dead tuple count against this threshold and assigns urgency classifications ranging from "OK" to "NEEDS AUTOVACUUM NOW" based on how close the dead tuple ratio is to exceeding the calculated limit.

What is the difference between get_autovacuum_status and get_autovacuum_activity?

get_autovacuum_status evaluates current table health against autovacuum thresholds, showing dead tuple ratios and urgency flags. get_autovacuum_activity focuses on historical execution data, displaying when each table was last vacuumed or analyzed over a specified time period (default 24 hours) and highlighting tables with no recent maintenance activity.

Can the MCP server monitor manual vacuum operations in addition to autovacuum?

Yes. The get_running_vacuum_operations function monitors all active maintenance commands including manual VACUUM, ANALYZE, REINDEX, and CLUSTER statements by querying pg_stat_activity. It identifies the operation type, target table, elapsed time, and lock impact level, providing visibility into both automatic and manually triggered maintenance operations.

How are the monitoring results formatted for readability?

The server uses the format_table_data helper in functions.py (lines 30-68) to convert raw query results into ASCII tables. This utility automatically converts byte sizes to human-readable units (KB, MB, GB) and transforms seconds into minutes or hours, presenting urgency classifications and threshold percentages in a scannable tabular format suitable for CLI output or API responses.

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