PostgreSQL MCP Server

Instructions

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();
    }
  }