Skip to main content
Glama
deenesjakoruzh
HenkDz

PostgreSQL MCP Server

by HenkDz
OverviewSchemaRelated ServersScoreDiscussions
TypeScript
Remote

pg_manage_indexes

Manage PostgreSQL database indexes to list, create, remove, rebuild, and analyze performance. Optimize queries by identifying unused or duplicate indexes.

Instructions

Manage PostgreSQL indexes - get, create, drop, reindex, and analyze usage with a single tool. Examples: operation="get" to list indexes, operation="create" with indexName, tableName, columns, operation="analyze_usage" for performance analysis

Input Schema

TableJSON Schema
NameRequiredDescriptionDefault
connectionStringNoPostgreSQL connection string (optional)
operationYesOperation: get (list indexes), create (new index), drop (remove index), reindex (rebuild), analyze_usage (find unused/duplicate)
schemaNoSchema name (defaults to public)
tableNameNoTable name (optional for get/analyze_usage, required for create)
indexNameNoIndex name (required for create/drop)
includeStatsNoInclude usage statistics (for get operation)
columnsNoColumn names for the index (required for create operation)
uniqueNoCreate unique index (for create operation)
concurrentNoCreate/drop index concurrently (for create/drop operations)
methodNoIndex method (for create operation, defaults to btree)
whereNoWHERE clause for partial index (for create operation)
ifNotExistsNoInclude IF NOT EXISTS clause (for create operation)
ifExistsNoInclude IF EXISTS clause (for drop operation)
cascadeNoInclude CASCADE clause (for drop operation)
targetNoTarget name for reindex (required for reindex operation)
typeNoType of target for reindex (required for reindex operation)
minSizeBytesNoMinimum index size in bytes (for analyze_usage operation)
showUnusedNoInclude unused indexes (for analyze_usage operation)
showDuplicatesNoDetect duplicate indexes (for analyze_usage operation)

Implementation Reference

  • src/tools/indexes.ts:481-660 (handler)
    The pg_manage_indexes tool handler. This consolidated tool handles multiple index operations (get, create, drop, reindex, analyze_usage) by dispatching to internal helper functions defined in the same file.
    export const manageIndexesTool: PostgresTool = {
  name: 'pg_manage_indexes',
  description: 'Manage PostgreSQL indexes - get, create, drop, reindex, and analyze usage with a single tool. Examples: operation="get" to list indexes, operation="create" with indexName, tableName, columns, operation="analyze_usage" for performance analysis',
  inputSchema: z.object({
    connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
    operation: z.enum(['get', 'create', 'drop', 'reindex', 'analyze_usage']).describe('Operation: get (list indexes), create (new index), drop (remove index), reindex (rebuild), analyze_usage (find unused/duplicate)'),
    
    // Common parameters
    schema: z.string().optional().describe('Schema name (defaults to public)'),
    tableName: z.string().optional().describe('Table name (optional for get/analyze_usage, required for create)'),
    indexName: z.string().optional().describe('Index name (required for create/drop)'),
    
    // Get operation parameters
    includeStats: z.boolean().optional().describe('Include usage statistics (for get operation)'),
    
    // Create operation parameters
    columns: z.array(z.string()).optional().describe('Column names for the index (required for create operation)'),
    unique: z.boolean().optional().describe('Create unique index (for create operation)'),
    concurrent: z.boolean().optional().describe('Create/drop index concurrently (for create/drop operations)'),
    method: z.enum(['btree', 'hash', 'gist', 'spgist', 'gin', 'brin']).optional().describe('Index method (for create operation, defaults to btree)'),
    where: z.string().optional().describe('WHERE clause for partial index (for create operation)'),
    ifNotExists: z.boolean().optional().describe('Include IF NOT EXISTS clause (for create operation)'),
    
    // Drop operation parameters
    ifExists: z.boolean().optional().describe('Include IF EXISTS clause (for drop operation)'),
    cascade: z.boolean().optional().describe('Include CASCADE clause (for drop operation)'),
    
    // Reindex operation parameters
    target: z.string().optional().describe('Target name for reindex (required for reindex operation)'),
    type: z.enum(['index', 'table', 'schema', 'database']).optional().describe('Type of target for reindex (required for reindex operation)'),
    
    // Analyze usage parameters
    minSizeBytes: z.number().optional().describe('Minimum index size in bytes (for analyze_usage operation)'),
    showUnused: z.boolean().optional().describe('Include unused indexes (for analyze_usage operation)'),
    showDuplicates: z.boolean().optional().describe('Detect duplicate indexes (for analyze_usage operation)')
  }),
  // biome-ignore lint/suspicious/noExplicitAny: <explanation>
  execute: async (args: any, getConnectionStringVal: GetConnectionStringFn): Promise<ToolOutput> => {
    const { 
      connectionString: connStringArg,
      operation,
      schema,
      tableName,
      indexName,
      includeStats,
      columns,
      unique,
      concurrent,
      method,
      where,
      ifNotExists,
      ifExists,
      cascade,
      target,
      type,
      minSizeBytes,
      showUnused,
      showDuplicates
    } = args as {
      connectionString?: string;
      operation: 'get' | 'create' | 'drop' | 'reindex' | 'analyze_usage';
      schema?: string;
      tableName?: string;
      indexName?: string;
      includeStats?: boolean;
      columns?: string[];
      unique?: boolean;
      concurrent?: boolean;
      method?: 'btree' | 'hash' | 'gist' | 'spgist' | 'gin' | 'brin';
      where?: string;
      ifNotExists?: boolean;
      ifExists?: boolean;
      cascade?: boolean;
      target?: string;
      type?: 'index' | 'table' | 'schema' | 'database';
      minSizeBytes?: number;
      showUnused?: boolean;
      showDuplicates?: boolean;
    };

    try {
      switch (operation) {
        case 'get': {
          const result = await executeGetIndexes({
            connectionString: connStringArg,
            schema: schema ?? 'public',
            tableName,
            includeStats: includeStats ?? true
          }, getConnectionStringVal);
          const message = tableName 
            ? `Indexes for table ${tableName}` 
            : `Indexes in schema ${schema ?? 'public'}`;
          return { content: [{ type: 'text', text: `${message}\n${JSON.stringify(result, null, 2)}` }] };
        }

        case 'create': {
          if (!indexName || !tableName || !columns || columns.length === 0) {
            return { 
              content: [{ type: 'text', text: 'Error: indexName, tableName, and columns are required for create operation' }], 
              isError: true 
            };
          }
          const result = await executeCreateIndex({
            connectionString: connStringArg,
            indexName,
            tableName,
            columns,
            schema: schema ?? 'public',
            unique: unique ?? false,
            concurrent: concurrent ?? false,
            method: method ?? 'btree',
            where,
            ifNotExists: ifNotExists ?? true
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: `Index ${result.indexName} created successfully. Details: ${JSON.stringify(result)}` }] };
        }

        case 'drop': {
          if (!indexName) {
            return { 
              content: [{ type: 'text', text: 'Error: indexName is required for drop operation' }], 
              isError: true 
            };
          }
          const result = await executeDropIndex({
            connectionString: connStringArg,
            indexName,
            schema: schema ?? 'public',
            concurrent: concurrent ?? false,
            ifExists: ifExists ?? true,
            cascade: cascade ?? false
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: `Index ${result.indexName} dropped successfully. Details: ${JSON.stringify(result)}` }] };
        }

        case 'reindex': {
          if (!target || !type) {
            return { 
              content: [{ type: 'text', text: 'Error: target and type are required for reindex operation' }], 
              isError: true 
            };
          }
          const result = await executeReindex({
            connectionString: connStringArg,
            target,
            type,
            schema: schema ?? 'public',
            concurrent: concurrent ?? false
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: `Reindex completed successfully for ${result.type} ${result.target}. Details: ${JSON.stringify(result)}` }] };
        }

        case 'analyze_usage': {
          const result = await executeAnalyzeIndexUsage({
            connectionString: connStringArg,
            schema: schema ?? 'public',
            tableName,
            minSizeBytes,
            showUnused: showUnused ?? true,
            showDuplicates: showDuplicates ?? true
          }, getConnectionStringVal);
          return { content: [{ type: 'text', text: JSON.stringify(result, null, 2) }] };
        }

        default:
          return { 
            content: [{ type: 'text', text: `Error: Unknown operation "${operation}". Supported operations: get, create, drop, reindex, analyze_usage` }], 
            isError: true 
          };
      }

    } catch (error) {
      const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error));
      return { 
        content: [{ type: 'text', text: `Error executing ${operation} operation: ${errorMessage}` }], 
        isError: true 
      };
    }
  }
};
  • src/tools/indexes.ts:484-516 (schema)
    Input schema definition for the pg_manage_indexes tool using Zod, supporting parameters for all supported operations.
    inputSchema: z.object({
  connectionString: z.string().optional().describe('PostgreSQL connection string (optional)'),
  operation: z.enum(['get', 'create', 'drop', 'reindex', 'analyze_usage']).describe('Operation: get (list indexes), create (new index), drop (remove index), reindex (rebuild), analyze_usage (find unused/duplicate)'),
  
  // Common parameters
  schema: z.string().optional().describe('Schema name (defaults to public)'),
  tableName: z.string().optional().describe('Table name (optional for get/analyze_usage, required for create)'),
  indexName: z.string().optional().describe('Index name (required for create/drop)'),
  
  // Get operation parameters
  includeStats: z.boolean().optional().describe('Include usage statistics (for get operation)'),
  
  // Create operation parameters
  columns: z.array(z.string()).optional().describe('Column names for the index (required for create operation)'),
  unique: z.boolean().optional().describe('Create unique index (for create operation)'),
  concurrent: z.boolean().optional().describe('Create/drop index concurrently (for create/drop operations)'),
  method: z.enum(['btree', 'hash', 'gist', 'spgist', 'gin', 'brin']).optional().describe('Index method (for create operation, defaults to btree)'),
  where: z.string().optional().describe('WHERE clause for partial index (for create operation)'),
  ifNotExists: z.boolean().optional().describe('Include IF NOT EXISTS clause (for create operation)'),
  
  // Drop operation parameters
  ifExists: z.boolean().optional().describe('Include IF EXISTS clause (for drop operation)'),
  cascade: z.boolean().optional().describe('Include CASCADE clause (for drop operation)'),
  
  // Reindex operation parameters
  target: z.string().optional().describe('Target name for reindex (required for reindex operation)'),
  type: z.enum(['index', 'table', 'schema', 'database']).optional().describe('Type of target for reindex (required for reindex operation)'),
  
  // Analyze usage parameters
  minSizeBytes: z.number().optional().describe('Minimum index size in bytes (for analyze_usage operation)'),
  showUnused: z.boolean().optional().describe('Include unused indexes (for analyze_usage operation)'),
  showDuplicates: z.boolean().optional().describe('Detect duplicate indexes (for analyze_usage operation)')
}),
  • src/index.ts:225-257 (registration)
    Registration of the manageIndexesTool (pg_manage_indexes) in the central allTools array, which is passed to the PostgreSQLServer constructor to make it available to the MCP server.
    const allTools: PostgresTool[] = [
  // Core Analysis & Debugging
  analyzeDatabaseTool,
  debugDatabaseTool,
  
  // Schema & Structure Management (Meta-Tools)
  manageSchemaTools,
  manageFunctionsTool,
  manageTriggersTools,
  manageIndexesTool,
  manageConstraintsTool,
  manageRLSTool,
  
  // User & Security Management
  manageUsersTool,
  
  // Query & Performance Management
  manageQueryTool,
  
  // Data Operations (Enhancement Tools)
  executeQueryTool,
  executeMutationTool,
  executeSqlTool,
  
  // Documentation & Metadata
  manageCommentsTool,
  
  // Data Migration & Monitoring
  exportTableDataTool,
  importTableDataTool,
  copyBetweenDatabasesTool,
  monitorDatabaseTool
];
  • src/index.ts:26-32 (registration)
    Import statement for the manageIndexesTool from src/tools/indexes.js
    import { manageIndexesTool } from './tools/indexes.js';
import { manageQueryTool } from './tools/query.js';
import { manageUsersTool } from './tools/users.js';
import { manageConstraintsTool } from './tools/constraints.js';
import { executeQueryTool, executeMutationTool, executeSqlTool } from './tools/data.js';
import { manageCommentsTool } from './tools/comments.js';
  • src/tools/indexes.ts:40-101 (helper)
    Helper function executeGetIndexes used by pg_manage_indexes for the 'get' operation.
    async function executeGetIndexes(
  input: GetIndexesInput,
  getConnectionString: GetConnectionStringFn
): Promise<IndexInfo[] | IndexUsageStats[]> {
  const resolvedConnectionString = getConnectionString(input.connectionString);
  const db = DatabaseConnection.getInstance();
  const { schema, tableName, includeStats } = input;
  
  try {
    await db.connect(resolvedConnectionString);
    
    if (includeStats) {
      const statsQuery = `
        SELECT 
          schemaname,
          tablename,
          indexname,
          idx_scan as scans,
          idx_tup_read as tuples_read,
          idx_tup_fetch as tuples_fetched,
          pg_relation_size(indexrelname::regclass) as size_bytes,
          pg_size_pretty(pg_relation_size(indexrelname::regclass)) as size_pretty,
          indisunique as is_unique,
          indisprimary as is_primary,
          CASE 
            WHEN idx_scan = 0 THEN 0
            ELSE round((idx_tup_fetch::numeric / idx_tup_read::numeric) * 100, 2)
          END as usage_ratio
        FROM pg_stat_user_indexes psi
        JOIN pg_index pi ON psi.indexrelid = pi.indexrelid
        WHERE schemaname = $1 
          ${tableName ? 'AND tablename = $2' : ''}
        ORDER BY size_bytes DESC, scans DESC
      `;
      
      const params = tableName ? [schema, tableName] : [schema];
      const results = await db.query<IndexUsageStats>(statsQuery, params);
      return results;
    }
    
    const basicQuery = `
      SELECT 
        schemaname,
        tablename,
        indexname,
        indexdef,
        pg_size_pretty(pg_relation_size(indexname::regclass)) as size
      FROM pg_indexes 
      WHERE schemaname = $1 
        ${tableName ? 'AND tablename = $2' : ''}
      ORDER BY tablename, indexname
    `;
    
    const params = tableName ? [schema, tableName] : [schema];
    const results = await db.query<IndexInfo>(basicQuery, params);
    return results;
  } catch (error) {
    throw new McpError(ErrorCode.InternalError, `Failed to get indexes: ${error instanceof Error ? error.message : String(error)}`);
  } finally {
    await db.disconnect();
  }
}

Tool Definition Quality

B3.3/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries full burden but offers minimal behavioral disclosure. It mentions operations but doesn't cover critical aspects like authentication needs (connection string handling), potential data loss from drop/reindex, performance impact of concurrent operations, or error handling. The examples add some context but insufficient for a complex 19-parameter tool.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness4/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is appropriately sized with two sentences: a clear purpose statement followed by specific examples. It's front-loaded with the core functionality, though the example formatting could be slightly cleaner. Every sentence adds value without redundancy.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a complex tool with 19 parameters, no annotations, and no output schema, the description is inadequate. It doesn't explain return values, error conditions, or behavioral nuances across different operations. While the schema provides parameter documentation, the description fails to offer the holistic guidance needed for proper tool selection and invocation.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the baseline is 3. The description adds marginal value by mentioning examples like 'operation="create" with indexName, tableName, columns' and 'operation="analyze_usage" for performance analysis,' but doesn't provide additional semantic context beyond what's already documented in the comprehensive schema descriptions.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the tool's purpose: 'Manage PostgreSQL indexes - get, create, drop, reindex, and analyze usage with a single tool.' It specifies the exact operations and distinguishes this multi-operation index management tool from its siblings, which focus on other database aspects like constraints, functions, or queries.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides implied usage through examples (e.g., 'operation="get" to list indexes'), but lacks explicit guidance on when to use this tool versus alternatives. It doesn't mention prerequisites like database connection requirements or differentiate from sibling tools like pg_manage_constraints for related tasks.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Install Server

Other Tools

Latest Blog Posts

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/HenkDz/postgresql-mcp-server'

If you have feedback or need assistance with the MCP directory API, please join our Discord server