io.github.bluwork/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.

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."

Claude Desktop

Add to your Claude Desktop config (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

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

Cursor / VS Code

Add to your MCP settings:

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

Read-Only vs Read-Write

The server runs in read-only mode by default. For write operations, run a separate instance:

{
  "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"
    }
  }
}

• postgres-scout-readonly: Safe exploration, no risk of data modification
• postgres-scout-readwrite: Write operations when explicitly needed

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.git
cd postgres-scout-mcp
pnpm install
pnpm build
pnpm test

License

Apache-2.0