🩺 PgDoctor — Postgres Performance Copilot

Connects to any Postgres database, captures slow queries via pg_stat_statements, runs EXPLAIN ANALYZE, and uses AI to suggest indexes, query rewrites, and schema changes — with confidence scores and ready-to-run SQL.

---

Features

• Slow query detection via pg_stat_statements
• EXPLAIN ANALYZE runner with automatic $1 placeholder substitution
• AI-powered suggestions — indexes, query rewrites, and schema changes with confidence scores
• Two AI backends — Anthropic Claude (cloud) or Ollama (100% free, runs locally)
• HTML reports — shareable diagnostic report with all findings
• Slack notifications — post findings to a Slack channel
• CLI interface — built with Typer; scriptable and CI-friendly

Install

pip install pgdoctor

Or from source:

git clone https://github.com/DIYA73/PgDoctor.git
cd PgDoctor
pip install -e .

Quick start

1. Enable pg_stat_statements in Postgres

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- After restart, in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2. Configure

cp .env.example .env

# Postgres connection
PG_DSN=postgresql://user:pass@localhost:5432/mydb

# AI backend (choose one)
ANTHROPIC_API_KEY=sk-ant-...        # Claude
# OLLAMA_HOST=http://localhost:11434 # or Ollama (free, local)

# Optional: Slack alerts
SLACK_WEBHOOK_URL=https://hooks.slack.com/...

3. Run

# Analyse slow queries and print suggestions
pgdoctor analyse

# Generate an HTML report
pgdoctor analyse --report report.html

# Use Ollama instead of Claude
pgdoctor analyse --backend ollama --model llama3

Example output

🔍 Found 3 slow queries (avg > 500ms)

Query #1  avg: 1.2s  calls: 4821
  SELECT * FROM orders WHERE user_id = $1 AND status = $2

  EXPLAIN ANALYZE → Seq Scan on orders (cost=0.00..48320.12)

  Suggestions:
  ✅ [confidence: 0.95] Add composite index
     CREATE INDEX CONCURRENTLY idx_orders_user_status
       ON orders (user_id, status);

  ✅ [confidence: 0.80] Avoid SELECT *
     SELECT id, created_at, total FROM orders
       WHERE user_id = $1 AND status = $2;

CLI reference

pgdoctor analyse              # analyse slow queries
pgdoctor analyse --top 20     # analyse top 20 slowest queries
pgdoctor analyse --min-calls 100   # skip queries called < 100 times
pgdoctor analyse --report out.html # save HTML report
pgdoctor analyse --backend ollama  # use local Ollama
pgdoctor analyse --slack           # post to Slack webhook

Project structure

pgdoctor/
├── pgdoctor/
│   ├── cli.py        # Typer CLI entrypoint
│   ├── db.py         # Postgres connection + pg_stat_statements query
│   ├── analyzer.py   # EXPLAIN ANALYZE runner + AI suggestion logic
│   ├── models.py     # Pydantic models for queries and suggestions
│   ├── report.py     # HTML report generator
│   └── slack.py      # Slack webhook notifier
└── tests/
    ├── test_analyzer.py
    └── test_db.py

Contributing

Issues and PRs welcome. Run tests with:

pip install -e ".[dev]"
pytest

License

MIT