pg_manage_indexes
Simplify PostgreSQL index management: list, create, drop, reindex, and analyze usage for optimized database performance. Supports concurrent operations and detailed usage insights.
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
| Name | Required | Description | Default |
|---|---|---|---|
| cascade | No | Include CASCADE clause (for drop operation) | |
| columns | No | Column names for the index (required for create operation) | |
| concurrent | No | Create/drop index concurrently (for create/drop operations) | |
| connectionString | No | PostgreSQL connection string (optional) | |
| ifExists | No | Include IF EXISTS clause (for drop operation) | |
| ifNotExists | No | Include IF NOT EXISTS clause (for create operation) | |
| includeStats | No | Include usage statistics (for get operation) | |
| indexName | No | Index name (required for create/drop) | |
| method | No | Index method (for create operation, defaults to btree) | |
| minSizeBytes | No | Minimum index size in bytes (for analyze_usage operation) | |
| operation | Yes | Operation: get (list indexes), create (new index), drop (remove index), reindex (rebuild), analyze_usage (find unused/duplicate) | |
| schema | No | Schema name (defaults to public) | |
| showDuplicates | No | Detect duplicate indexes (for analyze_usage operation) | |
| showUnused | No | Include unused indexes (for analyze_usage operation) | |
| tableName | No | Table name (optional for get/analyze_usage, required for create) | |
| target | No | Target name for reindex (required for reindex operation) | |
| type | No | Type of target for reindex (required for reindex operation) | |
| unique | No | Create unique index (for create operation) | |
| where | No | WHERE clause for partial index (for create operation) |
Input Schema (JSON Schema)
{
"$schema": "http://json-schema.org/draft-07/schema#",
"additionalProperties": false,
"properties": {
"cascade": {
"description": "Include CASCADE clause (for drop operation)",
"type": "boolean"
},
"columns": {
"description": "Column names for the index (required for create operation)",
"items": {
"type": "string"
},
"type": "array"
},
"concurrent": {
"description": "Create/drop index concurrently (for create/drop operations)",
"type": "boolean"
},
"connectionString": {
"description": "PostgreSQL connection string (optional)",
"type": "string"
},
"ifExists": {
"description": "Include IF EXISTS clause (for drop operation)",
"type": "boolean"
},
"ifNotExists": {
"description": "Include IF NOT EXISTS clause (for create operation)",
"type": "boolean"
},
"includeStats": {
"description": "Include usage statistics (for get operation)",
"type": "boolean"
},
"indexName": {
"description": "Index name (required for create/drop)",
"type": "string"
},
"method": {
"description": "Index method (for create operation, defaults to btree)",
"enum": [
"btree",
"hash",
"gist",
"spgist",
"gin",
"brin"
],
"type": "string"
},
"minSizeBytes": {
"description": "Minimum index size in bytes (for analyze_usage operation)",
"type": "number"
},
"operation": {
"description": "Operation: get (list indexes), create (new index), drop (remove index), reindex (rebuild), analyze_usage (find unused/duplicate)",
"enum": [
"get",
"create",
"drop",
"reindex",
"analyze_usage"
],
"type": "string"
},
"schema": {
"description": "Schema name (defaults to public)",
"type": "string"
},
"showDuplicates": {
"description": "Detect duplicate indexes (for analyze_usage operation)",
"type": "boolean"
},
"showUnused": {
"description": "Include unused indexes (for analyze_usage operation)",
"type": "boolean"
},
"tableName": {
"description": "Table name (optional for get/analyze_usage, required for create)",
"type": "string"
},
"target": {
"description": "Target name for reindex (required for reindex operation)",
"type": "string"
},
"type": {
"description": "Type of target for reindex (required for reindex operation)",
"enum": [
"index",
"table",
"schema",
"database"
],
"type": "string"
},
"unique": {
"description": "Create unique index (for create operation)",
"type": "boolean"
},
"where": {
"description": "WHERE clause for partial index (for create operation)",
"type": "string"
}
},
"required": [
"operation"
],
"type": "object"
}