postgres-mcp

Instructions

Args:
    table_name: Name of the table
    schema: Schema name (default: public)
    
Returns:
    Table statistics including row count, sizes, and vacuum info

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,
          )