Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:191-234 (schema)

  Input schema and definition for the 'indexes' MCP tool
  { name: 'indexes', description: 'Index management: list, create, drop indexes and analyze index usage', inputSchema: { type: 'object', properties: { action: { type: 'string', enum: ['list', 'create', 'drop', 'analyze', 'reindex', 'unused'], description: 'Action: list (all indexes), create (new index), drop (remove index), analyze (index statistics), reindex (rebuild index), unused (find unused indexes)' }, schemaName: { type: 'string', description: 'Schema name (default: public)', default: 'public' }, tableName: { type: 'string', description: 'Table name (required for create, list by table)' }, indexName: { type: 'string', description: 'Index name (required for drop, reindex)' }, columns: { type: 'array', items: { type: 'string' }, description: 'Column names for index (required for create)' }, options: { type: 'object', properties: { unique: { type: 'boolean', default: false }, concurrent: { type: 'boolean', default: false }, ifNotExists: { type: 'boolean', default: false }, ifExists: { type: 'boolean', default: true }, method: { type: 'string', enum: ['btree', 'hash', 'gist', 'spgist', 'gin', 'brin'] } }, description: 'Index creation options' } }, required: ['action'] } },
• src/index.ts:634-636 (registration)

  Registration of all tool definitions, including 'indexes', for the ListToolsRequestSchema handler
  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: toolDefinitions, }));
• src/index.ts:652-653 (registration)

  Dispatch/registration of the 'indexes' tool handler in the main CallToolRequestSchema switch statement
  case 'indexes': return await this.handleIndexes(args);
• src/index.ts:912-1070 (handler)

  Core handler function executing all 'indexes' tool actions: list indexes, create/drop indexes, analyze usage, reindex, find unused indexes using direct PostgreSQL queries via this.queryClient.
  private async handleIndexes(args: any) { const { action, schemaName = 'public', tableName, indexName, columns, options = {} } = args; switch (action) { case 'list': let listQuery; let params: any[] = []; if (tableName) { listQuery = ` SELECT i.indexname as index_name, i.tablename as table_name, i.schemaname as schema_name, pg_get_indexdef(pgc.oid) as definition, CASE WHEN i.indexname ~ '^.*_pkey$' THEN 'PRIMARY KEY' WHEN idx.indisunique THEN 'UNIQUE' ELSE 'INDEX' END as index_type, pg_size_pretty(pg_relation_size(pgc.oid)) as size, idx.indisvalid as is_valid FROM pg_indexes i JOIN pg_class pgc ON pgc.relname = i.indexname JOIN pg_index idx ON idx.indexrelid = pgc.oid WHERE i.tablename = $1 AND i.schemaname = $2 ORDER BY i.indexname `; params = [tableName, schemaName]; } else { listQuery = ` SELECT i.indexname as index_name, i.tablename as table_name, i.schemaname as schema_name, pg_get_indexdef(pgc.oid) as definition, CASE WHEN i.indexname ~ '^.*_pkey$' THEN 'PRIMARY KEY' WHEN idx.indisunique THEN 'UNIQUE' ELSE 'INDEX' END as index_type, pg_size_pretty(pg_relation_size(pgc.oid)) as size, idx.indisvalid as is_valid FROM pg_indexes i JOIN pg_class pgc ON pgc.relname = i.indexname JOIN pg_index idx ON idx.indexrelid = pgc.oid WHERE i.schemaname NOT IN ('information_schema', 'pg_catalog', 'pg_toast') ORDER BY i.schemaname, i.tablename, i.indexname `; } const indexes = await this.queryClient.executeQuery(listQuery, params); return { content: [{ type: 'text', text: JSON.stringify(indexes.rows, null, 2) }] }; case 'create': if (!tableName || !columns || columns.length === 0) { throw new Error('Table name and columns are required for index creation'); } const indexNameToUse = indexName || `idx_${tableName}_${columns.join('_')}`; let createIndexSQL = `CREATE${options.unique ? ' UNIQUE' : ''} INDEX${options.concurrent ? ' CONCURRENTLY' : ''}${options.ifNotExists ? ' IF NOT EXISTS' : ''} ${indexNameToUse}`; createIndexSQL += ` ON ${schemaName}.${tableName}`; if (options.method) { createIndexSQL += ` USING ${options.method}`; } createIndexSQL += ` (${columns.join(', ')})`; await this.queryClient.executeQuery(createIndexSQL); return { content: [{ type: 'text', text: `Index '${indexNameToUse}' created successfully on ${schemaName}.${tableName}` }] }; case 'drop': if (!indexName) { throw new Error('Index name is required for drop action'); } const dropSQL = `DROP INDEX${options.concurrent ? ' CONCURRENTLY' : ''}${options.ifExists ? ' IF EXISTS' : ''} ${schemaName}.${indexName}`; await this.queryClient.executeQuery(dropSQL); return { content: [{ type: 'text', text: `Index '${indexName}' dropped successfully` }] }; case 'analyze': const analyzeQuery = ` SELECT schemaname, tablename, indexname, idx_tup_read, idx_tup_fetch, idx_scan, CASE WHEN idx_scan = 0 THEN 'UNUSED' WHEN idx_scan < 10 THEN 'LOW_USAGE' ELSE 'ACTIVE' END as usage_status FROM pg_stat_user_indexes WHERE schemaname = $1 ORDER BY idx_scan DESC `; const stats = await this.queryClient.executeQuery(analyzeQuery, [schemaName]); return { content: [{ type: 'text', text: JSON.stringify(stats.rows, null, 2) }] }; case 'reindex': if (!indexName && !tableName) { throw new Error('Either index name or table name is required for reindex'); } let reindexSQL; if (indexName) { reindexSQL = `REINDEX INDEX${options.concurrent ? ' CONCURRENTLY' : ''} ${schemaName}.${indexName}`; } else { reindexSQL = `REINDEX TABLE${options.concurrent ? ' CONCURRENTLY' : ''} ${schemaName}.${tableName}`; } await this.queryClient.executeQuery(reindexSQL); return { content: [{ type: 'text', text: `Reindex completed for ${indexName || tableName}` }] }; case 'unused': const unusedQuery = ` SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) as size, idx_scan as scans FROM pg_stat_user_indexes WHERE idx_scan = 0 AND schemaname = $1 AND indexname NOT LIKE '%_pkey' ORDER BY pg_relation_size(indexrelid) DESC `; const unused = await this.queryClient.executeQuery(unusedQuery, [schemaName]); return { content: [{ type: 'text', text: JSON.stringify(unused.rows, null, 2) }] }; default: throw new Error(`Unknown index action: ${action}`); } }