pg-nl-copilot 🐘

A CLI AI copilot for PostgreSQL: ask your pgwatch metrics database questions in plain English.

This is a proof-of-concept companion project for the "AI Copilot for pgwatch" GSoC proposal, demonstrating the core NL → SQL → Explain pipeline in a small, self-contained Go CLI.

---

Architecture

User question
     │
     ▼
┌─────────────┐    schema context    ┌──────────────┐
│ LLM Provider│ ◄──────────────────  │  DB Client   │
│ (pluggable) │                      │  (pgwatch DB)│
└──────┬──────┘                      └──────┬───────┘
       │ generated SQL                      │ execute
       ▼                                    ▼
  extractSQL()  ──────────────────►     QueryResult
                                            │
                                            ▼
                                      LLM: explain()
                                            │
                                            ▼
                                    Plain-English output

Pluggable LLM providers: OpenAI · Anthropic · Ollama (local)

---

Prerequisites

• Go 1.22+
• A running PostgreSQL instance (your pgwatch metrics DB works perfectly)
• An API key for OpenAI or Anthropic — OR a local Ollama install

---

Installation

git clone https://github.com/yourusername/pg-nl-copilot
cd pg-nl-copilot
go mod tidy
go build -o pg-nl-copilot .

---

Usage

Environment variables (recommended)

export PG_DSN="postgres://user:pass@localhost/pgwatch?sslmode=disable"
export LLM_PROVIDER=openai          # openai | anthropic | ollama
export LLM_API_KEY=sk-...           # not needed for ollama

Interactive REPL

./pg-nl-copilot

  ✓ Connected  |  LLM: OpenAI (gpt-4o)
  ✓ Schema loaded. Ready.

╔══════════════════════════════════════════╗
║      pg-nl-copilot  🐘 AI Copilot       ║
║   Ask your pgwatch metrics in English   ║
╚══════════════════════════════════════════╝

pgwatch> Which tables have the most bloat?
pgwatch> Show me the top 10 slowest queries from the last hour
pgwatch> What is the cache hit ratio for each database?
pgwatch> Are there any long-running transactions right now?
pgwatch> Which indexes are never used?

Single question (non-interactive)

./pg-nl-copilot --ask "Show me table sizes sorted by largest first" --show-sql

Subcommands

# List tables the model can query
./pg-nl-copilot list-tables --schema public

---

Flags

Flag	Default	Description
--dsn	$PG_DSN	PostgreSQL connection string
--schema	public	Schema to introspect
--llm	openai	LLM backend: openai, anthropic, ollama
--api-key	$LLM_API_KEY	API key (not needed for ollama)
--model	provider default	Override model name
--ollama-host	http://localhost:11434	Ollama server URL
--ask	—	Single question, then exit
--show-sql	false	Print generated SQL before results

---

Example pgwatch Queries to Try

Plain English	What it checks
"Which tables grew the most in the last 24 hours?"	Bloat / growth
"Show databases with cache hit ratio below 95%"	Memory pressure
"List the top 5 slowest queries by mean time"	Query performance
"Are there any idle-in-transaction connections older than 5 minutes?"	Connection leaks
"Which indexes have zero scans?"	Index waste
"Show autovacuum activity for all tables"	Vacuum health

---

Relation to the GSoC pgwatch AI Copilot Project

This mini-project demonstrates the same core pipeline as the full proposal:

This project	Full pgwatch copilot
Pluggable LLM interface	Same interface, more providers
Schema introspection	pgwatch metric definitions
NL → SQL → Execute	NL → pgwatch API + SQL
Plain-English explanation	Root-cause analysis, alerts
CLI REPL	CLI + possible TUI/web
~500 lines of Go	~350 hours, production quality

---

Extending with a New LLM Provider

1. Create llm/myprovider.go implementing llm.Provider (two methods: Complete, Name)
2. Add a case to the switch in llm/provider.go
3. That's it. no other files change.