io.github.bluwork/postgres-scout-mcp
Scout your PostgreSQL databases with AI - safety features, monitoring, and data quality
Open source Open in the app JSON README (API)
About
Scout your PostgreSQL databases with AI - safety features, monitoring, and data quality
Details
- Kind
- MCP servers
- Topic
- Databases
- Publisher
- bluwork
- Origin
- official
- Category
- ferramentas
- Transport
- local
- Version
- 1.0.3
- Last push
- 2026-03-07T12:29:38Z
- Repository state
- ativo
- Language
- TypeScript
- License
- Apache-2.0
- Added
- 2026-08-29 03:02:31
- Updated
- 2026-08-29 03:02:31
- Origin id
io.github.bluwork/postgres-scout-mcp
README
# 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.
[](https://www.npmjs.com/package/postgres-scout-mcp) [](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**
| Component | Score | Status |
|-----------|-------|--------|
| Cache Performance | 94/100 | Healthy |
| Index Efficiency | 82/100 | Good |
| Table Bloat | 61/100 | Needs Attention |
| Connection Usage | 75/100 | Fair |
**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
```bash
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."*
<details>
<summary>Claude Desktop</summary>
Add to your Claude Desktop config (`~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):
```json
{
"mcpServers": {
"postgres-scout": {
"command": "npx",
"args": ["-y", "postgres-scout-mcp", "postgresql://localhost:5432/mydb"],
"type": "stdio"
}
}
}
```
</details>
<details>
<summary>Cursor / VS Code</summary>
Add to your MCP settings:
```json
{
"postgres-scout": {
"command": "npx",
"args": ["-y", "postgres-scout-mcp", "postgresql://localhost:5432/mydb"]
}
}
```
</details>
<details>
<summary>Read-Only vs Read-Write</summary>
The server runs in **read-only mode by default**. For write operations, run a separate instance:
```json
{
"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
</details>
## 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
| Variable | Default | Description |
|----------|---------|-------------|
| `QUERY_TIMEOUT` | `30000` | Query timeout in milliseconds |
| `MAX_RESULT_ROWS` | `10000` | Maximum rows returned per query |
| `ENABLE_RATE_LIMIT` | `true` | Enable rate limiting |
| `RATE_LIMIT_MAX_REQUESTS` | `100` | Requests per window |
| `RATE_LIMIT_WINDOW_MS` | `60000` | Rate limit window (ms) |
| `PGMAXPOOLSIZE` | `10` | Connection pool max size |
| `PGMINPOOLSIZE` | `2` | Connection pool min size |
| `PGIDLETIMEOUT` | `10000` | Idle connection timeout (ms) |
| `ENABLE_LOGGING` | `false` | Enable file logging |
| `LOG_DIR` | `./logs` | Log file directory |
| `LOG_LEVEL` | `info` | Log verbosity: debug, info, warn, error |
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
```bash
git clone https://github.com/bluwork/postgres-scout-mcp.git
cd postgres-scout-mcp
pnpm install
pnpm build
pnpm test
```
## License
Apache-2.0