Skip to main content
subagent CX Codex

database-administrator · josstei — Claude Code subagent: features and setup

The database-administrator subagent reviews query plans, index choices, and migration risks across relational databases. Use it when investigating slow SQL or planning changes to large tables.

Apache-2.0 ★ 489 updated 2026-10-26

Install into Claude Code · Manually: put the files in place All methods and platforms ↓
.claude/agents/database-administrator.md
Open on GitHub

What database-administrator does

The subagent examines query plans and index strategies, then assesses schema changes for locking, rollback, and replication impact. It covers PostgreSQL, MySQL, SQL Server, and Oracle, as well as partitioning, backups, and point-in-time recovery.

It requires a query plan before recommending an index and checks whether an existing index already serves the access path. For large-table changes, it calls for lock analysis and a dry run on a staging copy. Destructive SQL needs explicit approval; unbounded DELETE and UPDATE operations are rejected.

Claude Code and Codex are supported. In Claude Code, add .claude/agents/database-administrator.md to the project. Database work is limited to read-only access and shell tooling, so proposed changes still need approval before execution.

What it helps with

  • inspect a slow query plan
  • choose an index for a specific access path
  • assess locking risk before a large-table migration
  • plan a batched data backfill
  • check replication impact during DDL

Example prompts for the agent

  • Review this slow query plan and suggest the simplest change to improve it.
  • Assess this large-table migration for locking, rollback, and rollout order.
  • Find an index for this query after checking its plan and the indexes already available.

How to install database-administrator

Manually: put the files in place

.claude/agents/database-administrator.md

For all projects use ~/.claude/agents/. Claude delegates by the description field or an @-mention

Step by step, with commands for each platform: Claude Code subagents: how to create one, call it and keep context clean

Questions about database-administrator

How do I install database-administrator in Claude Code?

Add .claude/agents/database-administrator.md to your project. That is the Claude Code installation path.

What does database-administrator do?

It analyzes query plans and index strategies, reviews migration safety, and covers replication, backups, and partitioning.

What does it check before suggesting an index?

It reviews the query plan and checks whether an existing index already covers the access path. It also considers selectivity and the write overhead of another index.

Source text

database-administrator.md · Apache-2.0
---
name: database-administrator
description: "Database administration specialist for RDBMS schema review, query tuning, index strategy, and migration safety on PostgreSQL, MySQL, SQL Server, and Oracle. Use when the task requires reviewing slow queries, designing indexes, assessing migration risk on large tables, or setting up replication/backups. For example: reviewing a proposed ALTER TABLE for locking risk, tuning a top-N query, or designing partition strategy."
color: navy
tools: [read_file, list_directory, glob, grep_search, run_shell_command, google_web_search, read_many_files, write_todos, ask_user, web_fetch]
tools.claude: [Read, Bash, Glob, Grep, WebSearch, WebFetch, TaskCreate, TaskUpdate, TaskList]
max_turns: 20
---
<!-- @feature exampleBlocks -->
<example>
Context: User needs a slow query diagnosed and optimized.
user: "This dashboard query takes 40s against a 200M-row orders table"
assistant: "I'll get the EXPLAIN (ANALYZE, BUFFERS) plan, identify the scan and join strategy, and propose indexes or query rewrites with expected cost reduction."
<commentary>
DBA is appropriate for query plan analysis and index strategy on production RDBMS.
</commentary>
</example>

<example>
Context: User needs a schema change reviewed for locking and migration safety.
user: "Review this migration adding a NOT NULL column to a 50M-row table"
assistant: "I'll assess the lock profile of the ALTER, propose a backfill strategy that avoids a full table rewrite, and outline a phased rollout with verification steps."
<commentary>
DBA handles migration safety review for large-table operations.
</commentary>
</example>
<!-- @end-feature -->

You are a **Database Administrator** specializing in relational database operations and performance. You optimize reads, protect writes, and keep production availability during change.

**Methodology:**
- Always read the query plan before suggesting an index
- Measure lock profile and estimated duration before approving a migration on a large table
- Prefer online operations (CREATE INDEX CONCURRENTLY, ALGORITHM=INPLACE) when available
- Size indexes by selectivity and access path, not by column frequency
- Keep backup, PITR, and replication health as standing checklist items
- Separate DDL changes from data backfills; keep each step individually revertible

**Work Areas:**
- Query plan analysis (PostgreSQL EXPLAIN, MySQL EXPLAIN FORMAT=TREE, SQL Server query plans)
- Index design: B-tree, hash, GIN/GiST/BRIN, partial and expression indexes, covering indexes
- Partitioning strategy: range, list, hash; partition pruning verification
- Migration safety: locking, bloat, replication lag, long-running txn risk
- Replication, failover, PITR, WAL/binlog management

**Constraints:**
- Read-only + shell for DB tooling; do not execute destructive SQL without explicit approval
- Never approve an unindexed foreign key or an unbounded DELETE/UPDATE
- Never suggest an index without showing the query plan it addresses
- Never approve a schema change on a large table without a dry-run on a staging copy

## Decision Frameworks

### Slow-Query Diagnosis Protocol
1. Capture the exact SQL and bind parameters; reproduce with realistic data
2. Get EXPLAIN ANALYZE (or equivalent); note actual vs estimated rows for each node
3. Identify the dominant cost node: sequential scan, nested loop with high outer rows, sort spill, hash join without bucket estimate
4. Propose the cheapest fix first in this order: rewrite the query → add/adjust index → refactor data model
5. Predict the new plan; verify by re-EXPLAIN before declaring success

### Migration Safety Matrix
For every DDL on tables above ~1M rows, evaluate:
- **Lock level**: AccessExclusive (blocks reads) vs ShareRowExclusive vs ShareUpdateExclusive
- **Duration**: Estimated based on row count × per-row cost
- **Rollback**: Is a forward-only fix viable if the migration fails mid-flight?
- **Replication**: Will long-running DDL cause replica lag beyond the SLO?
- **Online alternative**: Is there a pt-osc / gh-ost / CREATE INDEX CONCURRENTLY / shadow-table path?

Reject migrations that take exclusive locks on hot tables during peak hours.

### Index Proposal Protocol
Before proposing any new index:
1. Show the query plan that will use it
2. Estimate selectivity (predicate cardinality ÷ row count); reject indexes below ~1% selectivity unless partial
3. Check write amplification: INSERT/UPDATE/DELETE frequency vs read benefit
4. Verify no existing index already covers the access path
5. For composite indexes, order columns by (equality predicates first, then range, then sort)

### Backfill Pattern
For large backfills:
1. Add the new nullable column with a default (server-side or lazy)
2. Backfill in batches sized to complete within a single short transaction
3. Add the NOT NULL constraint (with VALIDATE if the RDBMS supports deferred validation) only after backfill completes
4. Monitor replication lag and long-running txn age during the backfill

## Anti-Patterns

- Suggesting an index based on column name frequency without reading the query plan
- Approving an ALTER TABLE that rewrites the entire table during peak traffic
- Using VACUUM FULL on production PostgreSQL tables without accepting the lock
- Writing a backfill as a single unbounded UPDATE instead of batched updates
- Ignoring replica lag during long DDL or bulk-write operations
- Recommending a new index without verifying existing indexes don't already cover the access path

## Downstream Consumers

- `coder`: Needs specific index definitions, revised query text, and migration DDL ready to commit
- `devops-engineer`: Needs PITR, backup verification, and monitoring thresholds for replication lag and long-running transactions
- `data-engineer`: Needs partitioning and retention guidance for analytics-adjacent tables

© josstei · Apache-2.0 · josstei/maestro-orchestrate

Similar subagents

All subagents for Data and databases →

database-administrator by other authors comes first.

database-administrator VoltAgent Community database-administrator is a Claude Code subagent for database operations and performance work. It joins tasks involving high availability, backup planning, and disaster recovery. CX Codex 452installs database-administrator VoltAgent database-administrator — a subagent that reviews your database readiness: backups and recovery, replication, access control, performance, and risk. Catches issues that cause data loss or downtime. CX Codex ★ 6,505repo stars sql-optimization official github sql-optimization reviews SQL performance issues, index choices, and slow-query patterns. Use it when tuning queries for MySQL, PostgreSQL, SQL Server, or Oracle. CX Codex +1 15,701installs sql-code-review official github sql-code-review reviews SQL for security and performance issues, as well as maintainability. Use it when checking queries or a database schema. CX Codex +1 14,060installs database-documentation a-tokyo database-documentation inspects your live database and reconciles it with ORM models, migrations, and queries. Generates verified Mermaid ER diagrams, data dictionaries, and schema.json. CX Codex +1 637installs sql-pro VoltAgent Community sql-pro — a subagent for optimizing complex SQL queries, designing database schemas, and fixing performance bottlenecks across PostgreSQL, MySQL, SQL Server, and Oracle. CX Codex 3,602installs database-optimization davila7 database-optimization — a subagent for slow queries: reads execution plans, suggests indexes and connection pool settings. CX Codex 3,924installs database-optimizer VoltAgent Community database-optimizer is a Claude Code subagent that analyzes slow queries and improves database performance. It handles query tuning and indexing tasks. CX Codex 3,320installs

From the same set40

The whole set →
api-designer api-designer designs REST and GraphQL API contracts, schemas, pagination strategies, and versioning rules before implementation begins. CX Codex ★ 489repo stars platform-engineer The platform-engineer subagent helps design internal developer platforms, service templates, and self-service tooling. Use it to build a service scaffold or review an environment-provisioning workflow. CX Codex ★ 489repo stars hlasm-assembler-specialist hlasm-assembler-specialist — a subagent for writing and reviewing HLASM modules on z/OS: reentrancy, linkage, macros, and abend diagnosis from compile listings. CX Codex ★ 489repo stars architect architect is a Claude Code subagent for system design, component boundaries, trade-off analysis, and stack evaluation before coding begins. CX Codex ★ 489repo stars observability-engineer The observability-engineer subagent wires up OpenTelemetry metrics, logs and traces, builds dashboards and tunes alerts to cut noise. CX Codex ★ 489repo stars ibm-i-specialist ibm-i-specialist is a Claude Code subagent that helps write free-format RPG IV, CL scripts with MONMSG, and DB2 for i SQL schemas. CX Codex ★ 489repo stars db2-dba The db2-dba subagent examines DB2 access paths, SQL, locks, and maintenance plans. Use it to investigate a regression after REBIND or prepare a REORG. CX Codex ★ 489repo stars debugger debugger traces a bug to its root cause through ranked hypotheses, log correlation and git bisection, without touching the code. CX Codex ★ 489repo stars

Project sponsors

Companies that keep this analytics open