{
  "markdown": "# Postgres Scout MCP\n\nScout your PostgreSQL databases with AI - A production-ready Model Context Protocol server with built-in safety features, monitoring, and data quality tools.\n\n[![npm](https://img.shields.io/npm/v/postgres-scout-mcp)](https://www.npmjs.com/package/postgres-scout-mcp) [![License](https://img.shields.io/badge/License-Apache_2.0-blue.svg)](LICENSE)\n\n## What You Get\n\nYou ask:\n\n> *\"How healthy is my production database? Any urgent issues?\"*\n\nPostgres Scout returns:\n\n---\n\n### Overall Health Score: 78/100\n\n**Component Breakdown**\n| Component | Score | Status |\n|-----------|-------|--------|\n| Cache Performance | 94/100 | Healthy |\n| Index Efficiency | 82/100 | Good |\n| Table Bloat | 61/100 | Needs Attention |\n| Connection Usage | 75/100 | Fair |\n\n**Issues Found**\n- **HIGH** — Table `orders` has 34% bloat (2.1 GB wasted). VACUUM FULL recommended.\n- **MEDIUM** — 3 unused indexes on `sessions` consuming 890 MB.\n- **LOW** — Cache hit ratio for `analytics_events` is 71% (target: >90%).\n\n**Recommendations**\n- Run `VACUUM FULL orders` during maintenance window\n- Drop unused indexes: `idx_sessions_legacy`, `idx_sessions_old_token`, `idx_sessions_temp`\n- Consider adding `analytics_events` to shared_buffers or partitioning by date\n\n---\n\nThat's `getHealthScore` — one of 38 tools covering exploration, diagnostics, optimization, monitoring, data quality, and safe writes.\n\n## Quick Start\n\n### Claude Code\n\n```bash\nclaude mcp add postgres-scout -- npx -y postgres-scout-mcp postgresql://localhost:5432/mydb\n```\n\nThen ask: *\"Show me the largest tables and whether they have any bloat issues.\"*\n\n<details>\n<summary>Claude Desktop</summary>\n\nAdd to your Claude Desktop config (`~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):\n\n```json\n{\n  \"mcpServers\": {\n    \"postgres-scout\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"postgres-scout-mcp\", \"postgresql://localhost:5432/mydb\"],\n      \"type\": \"stdio\"\n    }\n  }\n}\n```\n\n</details>\n\n<details>\n<summary>Cursor / VS Code</summary>\n\nAdd to your MCP settings:\n\n```json\n{\n  \"postgres-scout\": {\n    \"command\": \"npx\",\n    \"args\": [\"-y\", \"postgres-scout-mcp\", \"postgresql://localhost:5432/mydb\"]\n  }\n}\n```\n\n</details>\n\n<details>\n<summary>Read-Only vs Read-Write</summary>\n\nThe server runs in **read-only mode by default**. For write operations, run a separate instance:\n\n```json\n{\n  \"mcpServers\": {\n    \"postgres-scout-readonly\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"postgres-scout-mcp\", \"--read-only\", \"postgresql://localhost:5432/production\"],\n      \"type\": \"stdio\"\n    },\n    \"postgres-scout-readwrite\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"postgres-scout-mcp\", \"--read-write\", \"postgresql://localhost:5432/development\"],\n      \"type\": \"stdio\"\n    }\n  }\n}\n```\n\n- **postgres-scout-readonly**: Safe exploration, no risk of data modification\n- **postgres-scout-readwrite**: Write operations when explicitly needed\n\n</details>\n\n## Tools\n\n### Explore — understand your database\n\n- `listDatabases` — databases the user has access to\n- `getDatabaseStats` — size, cache hit ratio, connection info\n- `listSchemas` — all schemas in the current database\n- `listTables` — tables with size and row statistics\n- `describeTable` — columns, constraints, indexes, and more\n\n### Query — run and analyze\n\n- `executeQuery` — run SELECT queries (or writes in read-write mode)\n- `explainQuery` — EXPLAIN plans for performance analysis\n- `optimizeQuery` — optimization recommendations for a specific query\n\n### Diagnose — find problems before they find you\n\n- `getHealthScore` — overall health score with component breakdown\n- `detectAnomalies` — anomalies in performance, connections, and data\n- `analyzeTableBloat` — bloat analysis for VACUUM planning\n- `getSlowQueries` — slow query analysis (requires pg_stat_statements)\n- `suggestVacuum` — VACUUM recommendations based on dead tuples and bloat\n\n### Optimize — make it faster\n\n- `suggestIndexes` — missing index recommendations from query patterns\n- `suggestPartitioning` — partitioning strategies for large tables\n- `getIndexUsage` — identify unused or underused indexes\n\n### Monitor — watch it live\n\n- `getCurrentActivity` — active queries and connections\n- `analyzeLocks` — lock contention and blocking queries\n- `getLiveMetrics` — real-time metrics over a time window\n- `getHottestTables` — tables with highest activity\n- `getTableMetrics` — comprehensive per-table I/O and scan stats\n\n### Data Quality — trust your data\n\n- `findDuplicates` — duplicate rows by column combination\n- `findMissingValues` — NULL analysis across columns\n- `findOrphans` — orphaned records with invalid foreign keys\n- `checkConstraintViolations` — test constraints before adding them\n- `analyzeTypeConsistency` — type inconsistencies in text columns\n\n### Relationships — follow the connections\n\n- `exploreRelationships` — multi-hop foreign key traversal\n- `analyzeForeignKeys` — foreign key health and performance\n\n### Time Series — temporal analysis\n\n- `findRecent` — rows within a time window\n- `analyzeTimeSeries` — window functions and anomaly detection\n- `detectSeasonality` — seasonal pattern detection\n\n### Export — get data out\n\n- `exportTable` — CSV, JSON, JSONL, or SQL\n- `generateInsertStatements` — INSERT statements for migration\n\n### Write (read-write only) — safe modifications\n\n- `previewUpdate` / `previewDelete` — see what would change before committing\n- `safeUpdate` — UPDATE with dry-run, row limits, empty WHERE protection\n- `safeDelete` — DELETE with dry-run, row limits, empty WHERE protection\n- `safeInsert` — INSERT with validation, batching, ON CONFLICT support\n\n## Security\n\n- **Read-only by default** — write operations must be explicitly enabled\n- All queries use parameterized values\n- SQL injection prevention with input validation and pattern detection\n- Identifier sanitization for table/column names\n- Rate limiting on all operations\n- Query timeouts to prevent long-running queries\n- Response size limits to prevent memory exhaustion\n\n## Examples\n\n> *\"What are the largest tables and do they have bloat?\"*\n\n```\nlistTables({ schema: \"public\" })\nanalyzeTableBloat({ schema: \"public\", minSizeMb: 100 })\n```\n\n> *\"Find duplicate emails in the users table.\"*\n\n```\nfindDuplicates({ table: \"users\", columns: [\"email\"] })\n```\n\n> *\"Which queries are slowest and how can I speed them up?\"*\n\n```\ngetSlowQueries({ minDurationMs: 100, limit: 10 })\nsuggestIndexes({ schema: \"public\" })\n```\n\n> *\"Show me what's happening on the database right now.\"*\n\n```\ngetCurrentActivity()\ngetLiveMetrics({ metrics: [\"queries\", \"connections\", \"cache\"], duration: 30000, interval: 1000 })\ngetHottestTables({ limit: 5, orderBy: \"seq_scan\" })\n```\n\n> *\"Find orphaned orders that reference deleted customers.\"*\n\n```\nfindOrphans({ table: \"orders\", foreignKey: \"customer_id\", referenceTable: \"customers\", referenceColumn: \"id\" })\n```\n\n## Configuration\n\n| Variable | Default | Description |\n|----------|---------|-------------|\n| `QUERY_TIMEOUT` | `30000` | Query timeout in milliseconds |\n| `MAX_RESULT_ROWS` | `10000` | Maximum rows returned per query |\n| `ENABLE_RATE_LIMIT` | `true` | Enable rate limiting |\n| `RATE_LIMIT_MAX_REQUESTS` | `100` | Requests per window |\n| `RATE_LIMIT_WINDOW_MS` | `60000` | Rate limit window (ms) |\n| `PGMAXPOOLSIZE` | `10` | Connection pool max size |\n| `PGMINPOOLSIZE` | `2` | Connection pool min size |\n| `PGIDLETIMEOUT` | `10000` | Idle connection timeout (ms) |\n| `ENABLE_LOGGING` | `false` | Enable file logging |\n| `LOG_DIR` | `./logs` | Log file directory |\n| `LOG_LEVEL` | `info` | Log verbosity: debug, info, warn, error |\n\nCLI flags: `--read-only` (default), `--read-write`, `--mode <mode>`\n\n## Logging\n\nFile logging is disabled by default. Set `ENABLE_LOGGING=true` to enable. Two log files are created in `LOG_DIR`:\n\n- **tool-usage.log** — every tool call with timestamp, name, and arguments\n- **error.log** — errors with stack traces\n\nConnection strings are automatically redacted in all output.\n\n## Development\n\n```bash\ngit clone https://github.com/bluwork/postgres-scout-mcp.git\ncd postgres-scout-mcp\npnpm install\npnpm build\npnpm test\n```\n\n## License\n\nApache-2.0\n",
  "bytes": 8140,
  "sha": "67a89e25cde181303b6603015163bc34c983a02e71ccb233b1aa940c667dc812",
  "repo_slug": "bluwork/postgres-scout-mcp",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_bluwork_postgres_scout_mcp_7362a17e/readme"
}