Autonomous DBA Agent for CockroachDB

Inspiration

Every DBA I've talked to describes the same loop: get paged about a slow query, look at the stats, find the missing index, create it, move on. The fix is routine — the remembering is not. Six months later, someone repeats the same investigation from scratch. LLM agents are good at the reasoning step, but without persistent memory they're just as amnesiac as the pager rotation. CockroachDB adding native vector indexing was the unlock: the same database the agent manages can be its long-term memory — operational tables and semantic recall in one system, one connection, transactional writes.

What it does

An autonomous DBA agent that runs a five-phase loop against a CockroachDB Cloud cluster, every hour, unattended:

1. Observe — pulls slow full-scan queries from crdb_internal.statement_statistics and cluster metadata via the ccloud CLI (REST API fallback in Lambda).
2. Recall — embeds the current symptoms (Bedrock Titan, 1024 dims) and vector-searches its own past incidents, stored in CockroachDB with a VECTOR column and vector index.
3. Reason — Claude Sonnet 4.6 on Bedrock receives observations + recalled incidents + measured action history + a CockroachDB performance Agent Skill, and returns a structured decision through a forced tool call.
4. Act — CREATE INDEX at high confidence auto-executes behind a strict SQL allowlist and a dedupe guard; anything riskier is only ever proposed.
5. Remember — the next run measures whether the fixed queries actually stopped full-scanning, records the verdict, and embeds resolved incidents into vector memory.

The agent's memory is on public display: it republishes a live dashboard built from its own memory tables after every run.

How we built it

Python + psycopg talking directly to CockroachDB Cloud; boto3 for Bedrock (Converse API with forced tool use, so decisions always arrive as schema-conforming JSON). The memory layer is four CockroachDB tables — observations, actions, outcomes, and incident_memory with a cosine vector index. The whole loop is packaged as a zip-based AWS Lambda (no Docker needed — manylinux wheels + bundled CA cert), invoked hourly by EventBridge, archiving run snapshots to S3 and publishing the dashboard as a public S3 object. During development, the CockroachDB Cloud managed MCP server (12 tools, OAuth) was wired into Claude Code for cluster introspection and schema work.

Challenges we ran into

• Measuring "did the fix work?" against cumulative stats. statement_statistics never forgets: a fixed query's old full-scan fingerprint stays visible forever. The insight that made outcome measurement work: under fresh traffic, a fixed query's full-scan execution count stops growing — new executions land under a different, non-full-scan fingerprint. A stalled counter under load is the success signal.
• ccloud CLI has no headless auth. Its OAuth session lives in the local keychain, so it can't run inside Lambda. I added a CockroachDB Cloud REST API fallback with a service-account key.
• Bedrock model gating. Anthropic models require a use-case form on new AWS accounts; I built the whole loop model-agnostic on Nova Pro first, then hot-swapped to Claude Sonnet 4.6 with a one-line env change once approved — the forced-tool-call pattern made the swap seamless.
• Least-privilege friction. Deploying under an IAM user with narrow permissions meant the deploy script had to be idempotent and degrade gracefully when IAM rights were removed after first deploy.

Accomplishments that we're proud of

It ran unattended for a week — 170+ hourly Lambda runs — with a 100% measured success rate on its auto-executed actions, and zero unsafe operations attempted. The agent has genuinely learned: on a healthy cluster it recalls the incidents it resolved (0.9+ similarity) and correctly no-ops at 0.9+ confidence rather than repeating work. And the reasoning got sharper with the model upgrade: Sonnet 4.6 independently spotted that the existing indexes lacked STORING clauses for projected columns and suggested ANALYZE — a diagnosis I hadn't scripted for.

What we learned

Memory is what turns a script with an LLM into an agent. The measured-outcomes table changed the agent's behavior more than any prompt engineering: once Claude could see "this exact intervention worked, this one didn't," its decisions became conservative in exactly the right way. Also: keeping semantic memory in the operational database — instead of bolting on a separate vector store — meant recall, action history, and outcome measurement were all one SQL query away, transactionally consistent.

What's next for Autonomous DBA Agent for CockroachDB

• Widen the safe-action allowlist: ANALYZE for stale statistics, covering-index upgrades (STORING clauses), and index cleanup for unused indexes.
• A human-approval channel (Slack) for the proposed queue, so high-risk actions get one-click review.
• Multi-cluster support — one agent, many clusters, shared incident memory so a fix learned on one cluster transfers to the rest.
• EXPLAIN-based verification before and after each index, so outcome measurement reasons about plans, not just counters.

Built With

• amazon-bedrock
• amazon-eventbridge
• amazon-iam
• amazon-web-services
• aws-lambda
• boto3
• ccloud
• claude
• cockroachdb
• mcp
• psycopg
• python
• titan-embeddings
• vector-search