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