Postgres Scout Mcp

Postgres Scout MCP

Scout your PostgreSQL databases with AI - A production-ready Model Context Protocol server with built-in safety features, monitoring, and data quality tools.

[npm] [License]

What You Get

You ask:

"How healthy is my production database? Any urgent issues?"

Postgres Scout returns:

Overall Health Score: 78/100

Component Breakdown

Issues Found

• HIGH — Table orders has 34% bloat (2.1 GB wasted). VACUUM FULL recommended.
• MEDIUM — 3 unused indexes on sessions consuming 890 MB.
• LOW — Cache hit ratio for analytics_events is 71% (target: >90%).

Recommendations

• Run VACUUM FULL orders during maintenance window
• Drop unused indexes: idx_sessions_legacy, idx_sessions_old_token, idx_sessions_temp
• Consider adding analytics_events to shared_buffers or partitioning by date

That's getHealthScore — one of 38 tools covering exploration, diagnostics, optimization, monitoring, data quality, and safe writes.

Quick Start

Claude Code

claude mcp add postgres-scout -- npx -y postgres-scout-mcp postgresql://localhost:5432/mydb

Then ask: "Show me the largest tables and whether they have any bloat issues."

{  "mcpServers": {    "postgres-scout": {      "command": "npx",      "args": ["-y", "postgres-scout-mcp", "postgresql://localhost:5432/mydb"],      "type": "stdio"    }  }}

{  "postgres-scout": {    "command": "npx",    "args": ["-y", "postgres-scout-mcp", "postgresql://localhost:5432/mydb"]  }}

{  "mcpServers": {    "postgres-scout-readonly": {      "command": "npx",      "args": ["-y", "postgres-scout-mcp", "--read-only", "postgresql://localhost:5432/production"],      "type": "stdio"    },    "postgres-scout-readwrite": {      "command": "npx",      "args": ["-y", "postgres-scout-mcp", "--read-write", "postgresql://localhost:5432/development"],      "type": "stdio"    }  }}

Tools

Explore — understand your database

• listDatabases — databases the user has access to
• getDatabaseStats — size, cache hit ratio, connection info
• listSchemas — all schemas in the current database
• listTables — tables with size and row statistics
• describeTable — columns, constraints, indexes, and more

Query — run and analyze

• executeQuery — run SELECT queries (or writes in read-write mode)
• explainQuery — EXPLAIN plans for performance analysis
• optimizeQuery — optimization recommendations for a specific query

Diagnose — find problems before they find you

• getHealthScore — overall health score with component breakdown
• detectAnomalies — anomalies in performance, connections, and data
• analyzeTableBloat — bloat analysis for VACUUM planning
• getSlowQueries — slow query analysis (requires pg_stat_statements)
• suggestVacuum — VACUUM recommendations based on dead tuples and bloat

Optimize — make it faster

• suggestIndexes — missing index recommendations from query patterns
• suggestPartitioning — partitioning strategies for large tables
• getIndexUsage — identify unused or underused indexes

Monitor — watch it live

• getCurrentActivity — active queries and connections
• analyzeLocks — lock contention and blocking queries
• getLiveMetrics — real-time metrics over a time window
• getHottestTables — tables with highest activity
• getTableMetrics — comprehensive per-table I/O and scan stats

Data Quality — trust your data

• findDuplicates — duplicate rows by column combination
• findMissingValues — NULL analysis across columns
• findOrphans — orphaned records with invalid foreign keys
• checkConstraintViolations — test constraints before adding them
• analyzeTypeConsistency — type inconsistencies in text columns

Relationships — follow the connections

• exploreRelationships — multi-hop foreign key traversal
• analyzeForeignKeys — foreign key health and performance

Time Series — temporal analysis

• findRecent — rows within a time window
• analyzeTimeSeries — window functions and anomaly detection
• detectSeasonality — seasonal pattern detection

Export — get data out

• exportTable — CSV, JSON, JSONL, or SQL
• generateInsertStatements — INSERT statements for migration

Write (read-write only) — safe modifications

• previewUpdate / previewDelete — see what would change before committing
• safeUpdate — UPDATE with dry-run, row limits, empty WHERE protection
• safeDelete — DELETE with dry-run, row limits, empty WHERE protection
• safeInsert — INSERT with validation, batching, ON CONFLICT support

Security

• Read-only by default — write operations must be explicitly enabled
• All queries use parameterized values
• SQL injection prevention with input validation and pattern detection
• Identifier sanitization for table/column names
• Rate limiting on all operations
• Query timeouts to prevent long-running queries
• Response size limits to prevent memory exhaustion

Examples

"What are the largest tables and do they have bloat?"

listTables({ schema: "public" })analyzeTableBloat({ schema: "public", minSizeMb: 100 })

"Find duplicate emails in the users table."

findDuplicates({ table: "users", columns: ["email"] })

"Which queries are slowest and how can I speed them up?"

getSlowQueries({ minDurationMs: 100, limit: 10 })suggestIndexes({ schema: "public" })

"Show me what's happening on the database right now."

getCurrentActivity()getLiveMetrics({ metrics: ["queries", "connections", "cache"], duration: 30000, interval: 1000 })getHottestTables({ limit: 5, orderBy: "seq_scan" })

"Find orphaned orders that reference deleted customers."

findOrphans({ table: "orders", foreignKey: "customer_id", referenceTable: "customers", referenceColumn: "id" })

Configuration

CLI flags: --read-only (default), --read-write, --mode <mode>

Logging

File logging is disabled by default. Set ENABLE_LOGGING=true to enable. Two log files are created in LOG_DIR:

• tool-usage.log — every tool call with timestamp, name, and arguments
• error.log — errors with stack traces

Connection strings are automatically redacted in all output.

Development

git clone https://github.com/bluwork/postgres-scout-mcp.gitcd postgres-scout-mcppnpm installpnpm buildpnpm test

License

Apache-2.0