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