DeepDBA

Autonomous PostgreSQL Database Reliability Engineer powered by LangChain DeepAgent.

DeepDBA is a multi-agent system that diagnoses, analyzes, and resolves PostgreSQL database issues. It serves as a reference implementation demonstrating all major DeepAgent capabilities applied to a real-world domain.

Features

• Performance Analysis — slow query identification, execution plan review, missing/unused index detection, vacuum health, table bloat, cache hit ratios
• Security Auditing — role privilege analysis, public grant detection, security configuration review
• Schema Quality — constraint validation, foreign key index checks, schema overview
• Capacity Planning — storage overview, database sizes, table growth trends, connection utilization
• Incident Investigation — structured root cause analysis with evidence gathering
• Health Reporting — weekly reports with domain scoring and trend tracking
• Human-in-the-Loop — DDL operations require explicit approval before execution
• Persistent Memory — incidents, recommendations, and baselines saved across sessions with automatic snapshot capture
• Beautiful Terminal Output — rich markdown rendering for tables, headers, code blocks, and lists
• PostgreSQL 18 Compatible — supports pg_stat_checkpointer for checkpoint statistics

Quick Start

Prerequisites

• Python 3.12+
• PostgreSQL instance (local or remote)
• LLM API key (Anthropic, DeepSeek, or Ollama)

Install

git clone https://github.com/pratims091/deep_dba.git && cd deep_dba
uv sync

Configure

export DEEP_DBA_DATABASE_URI="postgresql://user:pass@localhost:5432/mydb"
export ANTHROPIC_API_KEY="sk-ant-..."
# Or use DeepSeek:
export DEEPSEEK_API_KEY="sk-..."
# Or Ollama (local):
export DEEP_DBA_MODEL_NAME="ollama/llama3.1"

Run

# Full database analysis (all 4 subagents run concurrently)
deep-dba analyze

# Interactive investigation session with markdown rendering
deep-dba investigate

# Weekly health report
deep-dba weekly-report

# Index and performance recommendations
deep-dba recommend

# Review a migration file
deep-dba review-migration migration.sql

# Interactive REPL
deep-dba repl

Architecture

deep_dba/
├── agent.py              # Incident Commander (top-level agent)
├── cli.py                # Click CLI with REPL, HITL approval, rich markdown output
├── config.py             # Pydantic Settings from env vars
├── patterns.py           # Async concurrent delegation templates
├── logger.py             # Structured logging setup
├── tools/
│   ├── common.py         # Shared DB tools: explain_query, get_db_health, get_top_queries,
│   │                     # get_blocking_queries, get_bgwriter_stats, list_schemas,
│   │                     # list_tables, describe_table, get_tablespaces, get_index_usage
│   ├── performance.py    # slow queries, vacuum health, table bloat, unused indexes, cache ratios
│   ├── security.py       # role privileges, public grants, security config
│   ├── schema.py         # constraints, missing FK indexes, schema overview
│   ├── capacity.py       # storage, database sizes, growth stats, connections
│   ├── ddl.py            # execute_ddl (requires human approval)
│   └── reporting.py      # HTML report generation
├── subagents/
│   ├── performance_agent.py
│   ├── security_agent.py
│   ├── schema_agent.py
│   └── capacity_agent.py
├── memory/               # Auto-save & manual memory persistence tools
├── reports/              # Report generation module
└── AGENTS.md             # Persistent agent context

Agent Design

Incident Commander

The main agent orchestrates 4 specialized subagents for concurrent analysis:

Subagent	Domain	Tools
performance	Query optimization, vacuum, bloat, cache	7 tools
security	Roles, grants, config hardening	4 tools
schema	Constraints, FK indexes, naming	4 tools
capacity	Storage, growth, connections	5 tools

Tool-Only Approach

All database queries are predefined tools — the LLM cannot generate raw SQL. This prevents catalog errors and ensures PostgreSQL version compatibility. New tools can be added to extend functionality.

Memory

Uses CompositeBackend for hybrid storage:

• Ephemeral (StateBackend) — working files scoped to a thread
• Persistent (StoreBackend) — survives across threads

Memory tools automatically save snapshots (health, storage) and provide incident/recommendation/baseline tracking.

Human-in-the-Loop

DDL operations (ALTER TABLE, CREATE INDEX, DROP INDEX, etc.) are routed through execute_ddl which triggers an interrupt. The CLI presents the proposed SQL with formatted markdown and offers approve/reject/edit options.

Skills

6 on-demand skills loaded from SKILL.md files provide structured workflows for common DBA tasks like slow query investigation, index advisory, migration review, and incident response.

Configuration

Set via environment variables (prefix DEEP_DBA_):

Variable	Default	Description
DEEP_DBA_DATABASE_URI	postgresql://localhost:5432/deep_dba	PostgreSQL connection string
DEEP_DBA_MODEL_NAME	claude-sonnet-4-5-20250929	Claude model for the agent
DEEP_DBA_MCP_ACCESS_MODE	unrestricted	Tool access mode
DEEP_DBA_MEMORY_NAMESPACE	deep_dba	Memory namespace prefix

Development

# Run tests
uv run pytest

# Lint
uv run ruff check src/ tests/

# Format
uv run ruff format src/ tests/

Dependencies

Package	Purpose
deepagents	DeepAgent framework
langchain + langchain-anthropic	LLM agent orchestration
psycopg[binary]	PostgreSQL driver
pydantic + pydantic-settings	Configuration
click	CLI framework
rich	Terminal markdown rendering
plotly + pandas	Reporting & charts
jinja2	Report templates

License

MIT