postgres-mcp

Instructions

Args:
    table_name: Name of the table
    schema: Schema name (default: public)
    
Returns:
    List of indexes with name, columns, type, and size

Implementation Reference

• postgres_mcp/server.py:236-266 (handler)

  MCP tool handler for 'list_indexes'. Decorated with @mcp.tool() for automatic registration. Calls PostgresClient.list_indexes and formats the response dictionary with index details including name, uniqueness, type, size, and definition.

  @mcp.tool()
  @handle_db_error
  def list_indexes(table_name: str, schema: str = "public") -> dict:
      """List all indexes for a table.
      
      Args:
          table_name: Name of the table
          schema: Schema name (default: public)
          
      Returns:
          List of indexes with name, columns, type, and size
      """
      client = get_client()
      indexes = client.list_indexes(table_name, schema)
      
      return {
          "table_name": table_name,
          "schema": schema,
          "indexes": [
              {
                  "name": idx["index_name"],
                  "is_unique": idx.get("is_unique", False),
                  "is_primary": idx.get("is_primary", False),
                  "type": idx.get("index_type", "btree"),
                  "size": format_bytes(idx.get("size_bytes")),
                  "definition": idx.get("definition", ""),
              }
              for idx in indexes
          ],
      }

• postgres_mcp/postgres_client.py:299-330 (helper)

  Core implementation in PostgresClient class. Executes a SQL query joining pg_class, pg_index, pg_namespace, and pg_am to retrieve index details like name, uniqueness, primary status, type, definition, and size for the specified table and schema.

  def list_indexes(self, table_name: str, schema: str = "public") -> list[dict]:
      """List indexes for a table.
      
      Args:
          table_name: Table name
          schema: Schema name
          
      Returns:
          List of index dicts
      """
      query = """
          SELECT 
              i.relname AS index_name,
              t.relname AS table_name,
              ix.indisunique AS is_unique,
              ix.indisprimary AS is_primary,
              am.amname AS index_type,
              pg_get_indexdef(ix.indexrelid) AS definition,
              pg_relation_size(i.oid) AS size_bytes
          FROM pg_class t
          JOIN pg_index ix ON t.oid = ix.indrelid
          JOIN pg_class i ON i.oid = ix.indexrelid
          JOIN pg_namespace n ON n.oid = t.relnamespace
          JOIN pg_am am ON am.oid = i.relam
          WHERE n.nspname = %s
              AND t.relname = %s
          ORDER BY i.relname
      """
      with self.get_cursor() as cursor:
          cursor.execute(query, (schema, table_name))
          return [dict(row) for row in cursor.fetchall()]