English | 中文

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:

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

# 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?

Security: SQL Safety by Design

Note: The official Anthropic PostgreSQL MCP server has a known SQL injection 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

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

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+)

# 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

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

# 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:

{
  "mcpServers": {
    "pg-dash": {
      "type": "streamable-http",
      "url": "http://your-server:8768/mcp"
    }
  }
}

🖥️ CLI

# 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

# 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 — pg-dash running against a production database
• Migration safety guide — catching lock risks before they hit production
• MCP setup guide — connecting to Claude Desktop and Cursor
• CI integration guide — 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:

# 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)

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):

{
  "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):

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

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

# 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):

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

# 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 — 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.