Skip to main content
Glama
indiekitai

pg-dash

by indiekitai
OverviewSchemaSourceRelated ServersDiscussionsAdmin
TypeScript
Hybrid
README.md
[English](README.md) | [δΈ­ζ–‡](README.zh-CN.md)

# pg-dash

**The AI-native PostgreSQL health checker.** Three commands cover ~90% of day-to-day use β€” `check-migration`, `check`, `bloat`. Plus 26 MCP tools for deeper analysis (including pgvector health), Streamable HTTP transport for shared team servers, and a web dashboard for continuous monitoring.

πŸ“– **[Read the full writeup on Dev.to](https://dev.to/fan_yang_670d82db29664c9e/i-built-a-free-postgresql-health-checker-with-23-mcp-tools-and-ci-integration-2abc)**

## Daily drivers

Three commands cover the 80/20 case. Wire them into your workflow and you'll rarely need the rest:

| Command | When to run |
|---|---|
| `pg-dash check-migration <file> <conn>` | Before every migration deploy (CI or pre-commit) |
| `pg-dash check <conn>` (alias `health`) | Pre-deploy checklist |
| `pg-dash bloat <conn>` | Scheduled cleanup (unused indexes, dead tuples) |

For anything deeper β€” EXPLAIN analysis, schema diff, pgvector health, activity monitoring, query stats export β€” see the full command list below or call the 26 MCP tools from your AI agent.

Not another monitoring dashboard β€” pg-dash is built to fit into your **AI coding workflow**:

```
Developer writes a migration β†’ pg-dash check-migration (pre-flight) β†’
CI runs pg-dash check β†’ Finds missing indexes β†’
MCP tool suggests fix β†’ PR comment
```

```bash
# One-shot health check
npx @indiekitai/pg-dash check postgres://user:pass@host/db

# Check migration safety before running it
npx @indiekitai/pg-dash check-migration ./migrations/015_add_index.sql

# EXPLAIN ANALYZE a slow query in the terminal
npx @indiekitai/pg-dash explain "SELECT * FROM orders WHERE user_id = 1" postgres://...

# Real-time lock + long-query monitor (Ctrl+C to exit)
npx @indiekitai/pg-dash watch-locks postgres://...

# Analyze slow queries from pg_stat_statements
npx @indiekitai/pg-dash slow-queries postgres://... --limit 20 --min-calls 5

# Analyze table/index bloat
npx @indiekitai/pg-dash bloat postgres://...

# Compare two environments (local vs staging)
npx @indiekitai/pg-dash diff-env --source postgres://localhost/db --target postgres://staging/db

# AI assistant (Claude/Cursor) via MCP
pg-dash-mcp postgres://user:pass@host/db

# CI pipeline with diff
npx @indiekitai/pg-dash check $DATABASE_URL --ci --diff --format md
```

## Philosophy

**Developer tools are use-and-go.** You don't stare at a PostgreSQL dashboard all day. You run a check, fix the issues, and move on. pg-dash embraces this:

- **Health check** β†’ Find problems, get actionable SQL fixes, done
- **MCP tools** β†’ Let your AI assistant query and fix your database directly (unique β€” pganalyze/pgwatch don't have this)
- **CI integration** β†’ Catch issues automatically on every migration, not when production is on fire
- **Smart diff** β†’ See what changed since last run, track your progress

The Dashboard is there when you need it. But the real power is in the CLI, MCP, and CI.

## Why pg-dash?

| Tool | Price | Setup | AI-native | CI-ready |
|------|-------|-------|-----------|----------|
| pganalyze | $149+/mo | SaaS signup | ❌ | ❌ |
| Grafana+Prometheus | Free | 3 services | ❌ | ❌ |
| pgAdmin | Free | Complex UI | ❌ | ❌ |
| **pg-dash** | **Free** | **One command** | **25 MCP tools** | **`--ci --diff`** |


## Security: SQL Safety by Design

> **Note:** The [official Anthropic PostgreSQL MCP server](https://github.com/modelcontextprotocol/servers/tree/main/src/postgres) has a [known SQL injection vulnerability](https://invariantlabs.ai/blog/mcp-github-vulnerability) that bypasses its read-only transaction wrapper. pg-dash takes a different approach.

pg-dash enforces SQL safety at multiple layers:

- **Read-only by default** β€” All MCP query tools run inside `SET TRANSACTION READ ONLY` with parameterized queries. No raw SQL passthrough.
- **Strict write allowlist** β€” The `pg_dash_fix` tool only executes pre-approved operations: `VACUUM`, `ANALYZE`, `REINDEX`, `CREATE INDEX CONCURRENTLY`, `DROP INDEX CONCURRENTLY`. Everything else is rejected.
- **No arbitrary SQL execution** β€” Unlike servers that expose a generic `query` tool, pg-dash exposes purpose-built tools (`pg_dash_health`, `pg_dash_explain`, `pg_dash_bloat`, etc.) that construct their own SQL internally.
- **Connection string isolation** β€” Each MCP session is bound to the connection string provided at startup. No tool can connect to a different database.

## Features

### πŸ“Š Real-time Monitoring
- Live connection count, TPS, cache hit ratio, DB size
- Time-series charts with range selector (5m β†’ 7d)
- WebSocket-powered auto-refresh
- Active query list with cancel support
- 🌐 UI available in English and δΈ­ζ–‡ (simplified Chinese); per-browser preference saved to localStorage

### πŸ₯ Health Advisor
- **46+ automated checks** across performance, maintenance, schema, and security
- A-F health grade with category breakdown
- **One-click fixes** β€” not just "here's what's wrong" but "click to fix it"
- SQL allowlist (only safe operations: VACUUM, ANALYZE, REINDEX, etc.)

### πŸ“‹ Schema Browser
- Browse all tables, columns, indexes, constraints, foreign keys
- Sample data preview
- Index usage stats
- Extension and enum type listing

### πŸ”„ Schema Change Tracking
- Automatic schema snapshots (every 6 hours)
- Detects: tables added/removed, columns changed, indexes modified
- Timeline view with diff comparison
- The sticky feature β€” gets more valuable over time

### πŸ”” Alerts
- 7 default alert rules (connection utilization, cache ratio, long queries, etc.)
- Custom rules via API
- Cooldown support (no alert spam)
- Webhook notifications
- Alert history

### πŸ” EXPLAIN Plan Visualization
- Click any query in the Queries tab to see its execution plan
- Tree view of the EXPLAIN output for easy analysis

### πŸ“ˆ Query Time-Series Trends
- Trends tab with historical pg_stat_statements snapshots
- Track query performance over time

### πŸ’Ύ Disk Space Monitoring
- Disk tab with per-table size breakdown
- Growth prediction using linear regression
- "Days until disk full" estimate

### πŸ“£ Slack & Discord Notifications
- Webhook notifications for alerts
- Auto-detects Slack vs Discord webhook URLs
- Configure via `--slack-webhook` or `--discord-webhook`

### πŸ”¬ EXPLAIN ANALYZE CLI

```bash
pg-dash explain "SELECT * FROM orders WHERE user_id = 1" postgres://...
```

```
Query: SELECT * FROM orders WHERE user_id = 1

Limit cost=3.01..3.04 actual=0.060ms rows=10/10
└─ Sort cost=3.01..3.09 actual=0.057ms rows=10/32
  └─ Seq Scan on users cost=0.00..2.32 actual=0.023ms rows=32/32

─── Summary ────────────────────────────────────
  Execution time:  0.087ms
  Planning time:   0.756ms
  Seq Scans:       users

─── Recommendations ────────────────────────────
  β„Ή  Sort on [created_at DESC]. An index might eliminate this.
```

- Color-coded node types: πŸ”΄ Seq Scan, 🟒 Index Scan, 🟑 Hash Join, 🟣 Sort
- Shows actual vs estimated rows β€” catches bad planner estimates
- Flags Seq Scans > 1000 rows, Sort nodes, Hash Join memory spills
- `--no-analyze` for dry EXPLAIN (no actual execution)
- `--json` for scripting

### πŸ”’ watch-locks

```bash
pg-dash watch-locks postgres://...
```

Real-time lock wait monitor β€” refreshes every 3 seconds. Shows:
- Blocked queries with PID, wait time, and the blocking query
- Long-running queries (configurable threshold via `--long-query-threshold`)
- Table and lock type for each wait

### πŸ“Š query-stats (PG 18+)

```bash
# Export query statistics from production
pg-dash query-stats export postgres://prod-server/db --file prod-stats.json

# Import to development environment
pg-dash query-stats import prod-stats.json postgres://localhost/db
```

Export/import PostgreSQL query statistics (requires PG 18+). Enables "production query plans without production data" workflow:
1. Export stats from production: `pg-dash query-stats export prod`
2. Import to dev: `pg-dash query-stats import prod-stats.json dev`
3. Run `EXPLAIN` locally β€” now uses production statistics for accurate query plans

The exported JSON is typically <1MB regardless of database size.

### πŸ›‘οΈ Migration Safety Check
- Analyze a migration SQL file for risks before running it
- Detects: `CREATE INDEX` without `CONCURRENTLY` (lock risk), `ADD COLUMN NOT NULL` without `DEFAULT`, `ALTER COLUMN TYPE` (full table rewrite), `DROP COLUMN` (app breakage risk), `ADD CONSTRAINT` without `NOT VALID` (full table scan), `CREATE INDEX CONCURRENTLY` inside a transaction (runtime failure), `DROP TABLE`, `TRUNCATE`, `DELETE`/`UPDATE` without `WHERE`
- Dynamic checks: connects to DB to verify referenced tables exist, estimates lock time based on actual row counts
- CI-ready: `--ci` flag emits `::error::` / `::warning::` GitHub Actions annotations

### 🧠 Query Intelligence
- `pg_dash_analyze_query` β€” runs `EXPLAIN ANALYZE`, detects Seq Scans on large tables, auto-generates `CREATE INDEX CONCURRENTLY` suggestions with benefit ratings
- `pg_dash_query_regressions` β€” finds queries that got >50% slower vs historical baseline (requires `pg_stat_statements`)
- EXPLAIN Modal in dashboard shows index suggestions inline

### πŸ”„ Multi-Env Diff
- Compare schema and health between two PostgreSQL environments (local vs staging, staging vs prod)
- Detects: missing/extra tables, missing/extra columns, column type mismatches, missing/extra indexes, **foreign key and CHECK constraints**, **enum type differences**
- `--health` flag adds health score comparison and unique issues per environment
- `pg_dash_compare_env` MCP tool: ask your AI "what's different between local and staging?"

### πŸ”§ Production Readiness Audit
- **Unused indexes** β€” Find indexes with 0 scans since last stats reset; suggests safe `DROP INDEX CONCURRENTLY` SQL
- **Table bloat** β€” Dead tuple ratio per table (β‰₯10%); surfaces both `last_autovacuum` and `last_vacuum` timestamps
- **Autovacuum health** β€” Classifies each table as `ok` / `stale` / `overdue` / `never`; shows autovacuum settings with units
- **Lock monitoring** β€” Active lock-wait chains (who is blocking whom) + long-running queries >5s
- **Config recommendations** β€” Audits `shared_buffers`, `work_mem`, `checkpoint_completion_target`, `random_page_cost`, `idle_in_transaction_session_timeout`, and 5 more settings with severity-tagged recommendations

### 🧬 pgvector Health Check

```bash
pg-dash pgvector postgres://...
```

Checks pgvector installation and index health:
- Installed version and upgrade recommendations
- Vector columns with dimension analysis (flags >2000d for performance)
- Index types (IVFFlat vs HNSW) with parameter tuning suggestions
- IVFFlat `lists` vs row count validation (should be ~√rows)
- HNSW `ef_construction` vs `m` ratio check
- Missing vector indexes on large tables
- Index size vs table size ratio

MCP tool: `pg_dash_pgvector`

### πŸ€– MCP Server
- 26 tools for AI agent integration
- **Stdio** (default): `pg-dash-mcp postgres://...` β€” local use with Claude, Cursor, etc.
- **Streamable HTTP**: `pg-dash-mcp postgres://... --http` β€” shared team server, compatible with MCP 2026-07-28 stateless spec

```bash
# Remote mode β€” shared team server on port 8768
pg-dash-mcp postgres://... --http
# Custom port
MCP_PORT=9000 pg-dash-mcp postgres://... --http
```

MCP client config for remote mode:
```json
{
  "mcpServers": {
    "pg-dash": {
      "type": "streamable-http",
      "url": "http://your-server:8768/mcp"
    }
  }
}
```

### πŸ–₯️ CLI
```bash
# Start dashboard
pg-dash postgres://user:pass@host/db

# Health check (great for CI/CD)
pg-dash check postgres://user:pass@host/db
pg-dash check postgres://... --format json --threshold 70

# Migration safety check
pg-dash check-migration ./migrations/015_add_index.sql
pg-dash check-migration ./migrations/015_add_index.sql postgres://... --ci

# Multi-env schema diff
pg-dash diff-env --source postgres://localhost/db --target postgres://staging/db
pg-dash diff-env --source postgres://... --target postgres://... --health --format md

# Schema changes
pg-dash schema-diff postgres://user:pass@host/db
```

## Quick Start

```bash
# Using npx (no install needed)
npx @indiekitai/pg-dash postgres://user:pass@localhost/mydb

# Or install globally
npm install -g @indiekitai/pg-dash
pg-dash postgres://user:pass@localhost/mydb

# With individual options
pg-dash --host localhost --user postgres --db mydb --port 3480
```

Opens your browser at `http://localhost:3480` with the full dashboard.

## Documentation

- [Real-world example](docs/real-world-example.md) β€” pg-dash running against a production database
- [Migration safety guide](docs/migration-safety.md) β€” catching lock risks before they hit production
- [MCP setup guide](docs/mcp-setup.md) β€” connecting to Claude Desktop and Cursor
- [CI integration guide](docs/ci-integration.md) β€” automated checks in GitHub Actions

## CLI Options

```
pg-dash <connection-string>               Start dashboard
pg-dash check <connection-string>         Run health check and exit
pg-dash check-migration <file> [conn]     Analyze migration SQL for risks
pg-dash diff-env --source <url> --target <url>  Compare two environments
pg-dash schema-diff <connection-string>   Show schema changes

Options:
  -p, --port <port>      Dashboard port (default: 3480)
  --no-open              Don't auto-open browser
  --json                 Dump health check as JSON and exit
  --host <host>          PostgreSQL host
  -u, --user <user>      PostgreSQL user
  --password <pass>      PostgreSQL password
  -d, --db <database>    PostgreSQL database
  --pg-port <port>       PostgreSQL port (default: 5432)
  --data-dir <dir>       Data directory (default: ~/.pg-dash)
  -i, --interval <sec>   Collection interval (default: 30)
  --threshold <score>    Score threshold for check command (default: 70)
  -f, --format <fmt>     Output format: text|json|md (default: text)
  --query-stats-interval <min>  Query stats snapshot interval in minutes (default: 5)
  --slack-webhook <url>  Slack webhook URL for alert notifications
  --discord-webhook <url>  Discord webhook URL for alert notifications
  --ci                   Output GitHub Actions annotations (check, check-migration, diff-env)
  --diff                 Compare with last snapshot (check command)
  --ai-suggest           Use AI to generate fix suggestions (requires LLM config)
  --ai-explain           Use AI to explain schema diff business impact (requires LLM config)
  --snapshot-path <path> Path to snapshot file for --diff
  --health               Include health comparison (diff-env)
  -v, --version          Show version
```

## MCP Server

For AI agent integration:

```bash
# Start MCP server
pg-dash-mcp postgres://user:pass@host/db

# Or with env var (PG_DASH_CONNECTION_STRING or DATABASE_URL)
PG_DASH_CONNECTION_STRING=postgres://... pg-dash-mcp
```

### Available Tools (26)

| Tool | Description |
|------|-------------|
| `pg_dash_overview` | Database overview (version, uptime, size, connections) |
| `pg_dash_health` | Health advisor report with score, grade, and issues |
| `pg_dash_tables` | List all tables with sizes and row counts |
| `pg_dash_table_detail` | Detailed info about a specific table |
| `pg_dash_activity` | Current database activity (active queries, connections) |
| `pg_dash_schema_changes` | Recent schema changes |
| `pg_dash_fix` | Execute a safe fix (VACUUM, ANALYZE, REINDEX, etc.) |
| `pg_dash_alerts` | Alert history |
| `pg_dash_explain` | Run EXPLAIN ANALYZE on a SELECT query (read-only) |
| `pg_dash_batch_fix` | Get batch fix SQL for issues, optionally filtered by category |
| `pg_dash_slow_queries` | Top slow queries from pg_stat_statements |
| `pg_dash_table_sizes` | Table sizes with data/index breakdown (top 30) |
| `pg_dash_export` | Export full health report (JSON or Markdown) |
| `pg_dash_diff` | Compare current health with last saved snapshot |
| `pg_dash_check_migration` | Analyze migration SQL for lock risks, missing tables, destructive ops |
| `pg_dash_analyze_query` | Deep EXPLAIN analysis with automatic index suggestions |
| `pg_dash_query_regressions` | Detect queries that degraded >50% vs historical baseline |
| `pg_dash_compare_env` | Compare schema and health between two database environments |
| `pg_dash_unused_indexes` | Find unused indexes that waste space and slow down writes |
| `pg_dash_bloat` | Detect table bloat (dead tuples) that slow down queries |
| `pg_dash_autovacuum` | Check autovacuum health β€” which tables are stale or never vacuumed |
| `pg_dash_locks` | Show active lock waits and long-running blocking queries |
| `pg_dash_config_check` | Audit PostgreSQL configuration and get tuning recommendations |
| `pg_dash_pgvector` | pgvector health β€” installed version, vector columns, index types (IVFFlat/HNSW), dimension analysis, tuning |
| `fetch_db_context` | Comprehensive DB context for AI agents: all table structures, columns, types, PKs/FKs, indexes, business intent inference, and health summary (single call for full context) |
| `pg_dash_query_natural` | Query database using natural language β€” LLM converts your question to SQL and returns results |
| `ci_health_summary` | CI-friendly health summary with AI-powered prioritization |

## MCP Setup

Connect pg-dash to Claude Desktop or Cursor for AI-assisted database management.

### Claude Desktop

Add to `~/Library/Application Support/Claude/claude_desktop_config.json` (macOS) or `%APPDATA%\Claude\claude_desktop_config.json` (Windows):

```json
{
  "mcpServers": {
    "pg-dash": {
      "command": "npx",
      "args": ["-y", "-p", "@indiekitai/pg-dash", "pg-dash-mcp", "postgresql://user:pass@host/db"]
    }
  }
}
```

### Claude Code

Add to `~/.claude/mcp.json` (global) or `.mcp.json` (project-level):

```json
{
  "mcpServers": {
    "pg-dash": {
      "command": "npx",
      "args": ["-y", "-p", "@indiekitai/pg-dash", "pg-dash-mcp", "postgresql://user:pass@host/db"]
    }
  }
}
```

### Cursor

Add to `.cursor/mcp.json` in your project:

```json
{
  "mcpServers": {
    "pg-dash": {
      "command": "npx",
      "args": ["-y", "-p", "@indiekitai/pg-dash", "pg-dash-mcp", "postgresql://user:pass@host/db"]
    }
  }
}
```

### Example Conversations

Once connected, you can ask your AI assistant:

**Diagnosis:**
- "What's wrong with my database right now?"
- "Why is my `users` table slow? Check for missing indexes."
- "Show me the top 5 slowest queries this week."

**Optimization:**
- "Generate SQL to fix all missing FK indexes in one go."
- "EXPLAIN this query for me: SELECT * FROM orders WHERE user_id = 123"
- "Which tables are taking up the most space?"

**Pre-migration check:**
- "Run a health check and tell me if it's safe to deploy."
- "What changed in the schema since last week?"
- "Check if there are any idle connections blocking my migration."

## CI Integration

### GitHub Actions

Add `--ci` and `--diff` flags to integrate with CI pipelines:

```bash
# GitHub Actions annotations (::error::, ::warning::)
pg-dash check postgres://... --ci

# Markdown report for PR comments
pg-dash check postgres://... --ci --format md

# Compare with previous run
pg-dash check postgres://... --diff

# All together
pg-dash check postgres://... --ci --diff --format md
```

Sample workflow (`.github/workflows/pg-check.yml`):

```yaml
name: Database Health Check
on:
  push:
    paths: ['migrations/**', 'prisma/**', 'drizzle/**', 'supabase/migrations/**']
  pull_request:
    paths: ['migrations/**', 'prisma/**', 'drizzle/**', 'supabase/migrations/**']
  schedule:
    - cron: '0 8 * * 1'  # Weekly Monday 8am UTC
jobs:
  db-health:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      # Cache snapshot across ephemeral runners for --diff to work
      - name: Restore health snapshot
        uses: actions/cache@v4
        with:
          path: .pg-dash-cache
          key: pg-dash-snapshot-${{ github.ref }}
          restore-keys: pg-dash-snapshot-
      - name: Run pg-dash health check
        id: pg-check
        run: |
          mkdir -p .pg-dash-cache
          npx @indiekitai/pg-dash check ${{ secrets.DATABASE_URL }} \
            --ci --diff --snapshot-path ./.pg-dash-cache/last-check.json \
            --format md > pg-dash-report.md
          echo "exit_code=$?" >> $GITHUB_OUTPUT
        continue-on-error: true
      - name: Save health snapshot
        uses: actions/cache/save@v4
        if: always()
        with:
          path: .pg-dash-cache
          key: pg-dash-snapshot-${{ github.ref }}-${{ github.run_id }}
      - name: Fail if unhealthy
        if: steps.pg-check.outputs.exit_code != '0'
        run: exit 1
```

See [`examples/github-actions-pg-check.yml`](examples/github-actions-pg-check.yml) for a full workflow with PR comments.

## Health Checks

pg-dash runs 46+ automated checks:

**Performance**
- Missing indexes (high sequential scans on large tables)
- Bloated indexes (index size vs table size)
- Table bloat (dead tuple ratio)
- Cache efficiency per table
- Slow queries (from pg_stat_statements)

**Maintenance**
- VACUUM overdue
- ANALYZE overdue
- Transaction ID wraparound risk
- Idle connection detection
- Idle in transaction detection

**Schema**
- Missing primary keys
- Unused indexes (0 scans, >1MB)
- Duplicate indexes
- Missing foreign key indexes

**Security**
- Remote superuser connections
- SSL disabled
- Trust authentication (no password)

## CI/CD Integration

```bash
# Fail pipeline if health score < 70
pg-dash check postgres://... --threshold 70 --format json

# Example GitHub Actions
- name: Database Health Check
  run: npx @indiekitai/pg-dash check ${{ secrets.DATABASE_URL }} --threshold 70
```

## Data Storage

pg-dash stores metrics locally in `~/.pg-dash/`:
- `metrics.db` β€” Time-series metrics (7-day retention)
- `schema.db` β€” Schema snapshots and change history
- `alerts.db` β€” Alert rules and history

All SQLite. No external dependencies. Delete the folder to reset.

## Tech Stack

- **Backend**: Hono + Node.js
- **Frontend**: React + Tailwind CSS (bundled)
- **Storage**: SQLite (better-sqlite3)
- **Charts**: Recharts
- **Zero external services required**

## Requirements

- Node.js 18+
- PostgreSQL 12+ (some features require 15+)

## License

MIT

---

Built by [IndieKit](https://indiekit.ai) β€” open-source developer tools with MCP support.

## History

pg-dash has consolidated functionality from several earlier packages, all now archived:

- **[pg-inspect](https://github.com/indiekitai/pg-inspect)** β€” schema introspection (use `pg-dash schema-diff` or inspection routes)
- **[pg-top](https://github.com/indiekitai/pg-top)** β€” realtime activity monitor (use `pg-dash watch-locks`)
- **[pg-explain](https://github.com/indiekitai/pg-explain)** β€” EXPLAIN ANALYZE visualization (use `pg-dash explain`)
- **[pg-horizon](https://github.com/indiekitai/pg-horizon)** β€” MVCC horizon monitoring (covered by pg-dash's `Transaction ID Age` check)

If you've installed any of these as standalone npm packages, switch to pg-dash β€” same functionality, one install, one upgrade path.

This server cannot be deployed

One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

Maintenance

ActivityInactive
ResponsivenessNo issues

Resources