postgres-mcp

Instructions

Implementation Reference

• postgres_mcp/server.py:198-230 (handler)

  The primary handler function for the 'table_stats' MCP tool. Decorated with @mcp.tool() for registration and execution. Fetches stats via PostgresClient and formats the response dictionary.
  @mcp.tool() @handle_db_error def table_stats(table_name: str, schema: str = "public") -> dict: """Get statistics for a table (row count, size, bloat). Args: table_name: Name of the table schema: Schema name (default: public) Returns: Table statistics including row count, sizes, and vacuum info """ client = get_client() stats = client.get_table_stats(table_name, schema) if not stats: return not_found_response("Table", f"{schema}.{table_name}") return { "schema": schema, "table_name": table_name, "row_count": stats.get("row_count"), "row_count_formatted": format_count(stats.get("row_count")), "dead_tuples": stats.get("dead_tuples"), "total_size": stats.get("total_size"), "total_size_formatted": format_bytes(stats.get("total_size")), "table_size": stats.get("table_size"), "table_size_formatted": format_bytes(stats.get("table_size")), "index_size": stats.get("index_size"), "index_size_formatted": format_bytes(stats.get("index_size")), "last_vacuum": str(stats.get("last_vacuum")) if stats.get("last_vacuum") else None, "last_analyze": str(stats.get("last_analyze")) if stats.get("last_analyze") else None, }
• postgres_mcp/postgres_client.py:470-502 (helper)

  Helper method in PostgresClient that executes the SQL query against pg_stat_user_tables to retrieve raw table statistics, called by the tool handler.
  def get_table_stats(self, table_name: str, schema: str = "public") -> dict[str, Any]: """Get table statistics. Args: table_name: Table name schema: Schema name Returns: Dict with table statistics """ query = """ SELECT schemaname, relname AS table_name, n_live_tup AS row_count, n_dead_tup AS dead_tuples, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, pg_total_relation_size(schemaname || '.' || relname) AS total_size, pg_table_size(schemaname || '.' || relname) AS table_size, pg_indexes_size(schemaname || '.' || relname) AS index_size FROM pg_stat_user_tables WHERE schemaname = %s AND relname = %s """ with self.get_cursor() as cursor: cursor.execute(query, (schema, table_name)) row = cursor.fetchone() if row: return dict(row) return {}
• postgres_mcp/models.py:206-234 (schema)

  Pydantic model defining the structure for TableStats, including a from_row classmethod to parse database rows. Imported in server.py, provides type definitions for table statistics.
  class TableStats(BaseModel): """Table statistics.""" schema_name: str table_name: str row_count: Optional[int] = None total_size: Optional[int] = None table_size: Optional[int] = None index_size: Optional[int] = None toast_size: Optional[int] = None dead_tuples: Optional[int] = None last_vacuum: Optional[str] = None last_analyze: Optional[str] = None @classmethod def from_row(cls, row: dict) -> "TableStats": return cls( schema_name=row.get("schemaname", "public"), table_name=row.get("relname", row.get("table_name", "")), row_count=row.get("n_live_tup", row.get("row_count")), total_size=row.get("total_size"), table_size=row.get("table_size"), index_size=row.get("index_size"), toast_size=row.get("toast_size"), dead_tuples=row.get("n_dead_tup"), last_vacuum=str(row.get("last_vacuum")) if row.get("last_vacuum") else None, last_analyze=str(row.get("last_analyze")) if row.get("last_analyze") else None, )