Database Optimization Agent: Stop Guessing, Start Measuring
Every senior developer has been there: a query that worked fine in development starts choking in production. You’re staring at a wall of execution plan output, trying to figure out whether you need a composite index, a covering index, or a complete query rewrite. You pull up documentation across three browser tabs, cross-reference PostgreSQL internals docs, and spend two hours confirming what you suspected in the first fifteen minutes.
The Database Optimization agent for Claude Code exists specifically to eliminate that friction. It doesn’t just answer database questions — it approaches your schema, queries, and infrastructure the way a seasoned DBA would: profile first, measure actual performance, then optimize based on evidence. This agent understands execution plans, indexing strategies, connection pool tuning, and the tradeoffs between read-heavy and write-heavy workloads. It produces concrete, actionable output with before/after comparisons you can actually use to justify changes to your team.
For developers who live in backend systems, this agent is the equivalent of having a DBA available on demand — without the scheduling overhead.
When to Use This Agent
This agent is marked proactive for a reason. Don’t wait until your database becomes a crisis. The real value is catching problems early and establishing good patterns from the start. That said, here are the specific scenarios where this agent pays for itself immediately:
Slow Query Investigation
You’ve identified a query in your slow query log. You have the SQL, maybe a rough execution time, and a vague sense that something is wrong. Hand it to this agent with your table schema and current indexes. It will walk through the execution plan, identify sequential scans, bad join orders, or missing indexes, and give you a rewritten version with a projected improvement estimate.
Pre-Production Schema Review
Before you ship a new feature with complex queries, run the schema and anticipated query patterns through this agent. It will surface indexing gaps and normalization issues before they hit production traffic, when they’re cheap to fix.
Index Audits
Over time, codebases accumulate indexes that no longer serve their original purpose — or never did. This agent can analyze your index usage patterns, identify duplicates, unused indexes consuming write overhead, and recommend which ones to drop versus which ones to add.
Connection Pool Tuning
Misconfigured connection pools are a silent killer. If you’re seeing timeout spikes under load, or your database CPU is high while connections sit idle, this agent can help you calculate appropriate pool sizes based on your workload characteristics and database engine limits.
Read/Write Workload Optimization
OLTP and OLAP workloads have fundamentally different optimization strategies. If your application serves both — say, transactional writes alongside analytical queries — this agent can help you reason through caching layers, read replicas, materialized views, and query separation strategies.
Migration Planning
Restructuring a schema on a live database is risky. This agent will help you think through the migration path: which changes require table rewrites, how to add indexes concurrently without locking, and how to roll out changes incrementally with zero downtime.
Real-World Examples
Scenario: Orders Dashboard Timing Out Under Load
A backend developer is debugging a customer-facing orders dashboard. The main query aggregates orders by customer, joins to a products table, and filters by date range. In staging it runs in 200ms. In production it’s taking 8 seconds and occasionally timing out.
Developer: This query is killing us in production. It aggregates orders
by customer with a date filter and joins products. Under load it's hitting
8+ seconds. Here's the schema and the query:
SELECT c.id, c.name, COUNT(o.id) as order_count,
SUM(o.total) as revenue
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY c.id, c.name
ORDER BY revenue DESC;
Agent: Run EXPLAIN ANALYZE on this and share the output — I need to see
where the time is actually going. Based on the query structure, I'd guess
you're hitting a sequential scan on orders.created_at and the GROUP BY
is materializing a large intermediate result set.
While you grab that: do you have indexes on orders.customer_id,
order_items.order_id, and order_items.product_id? Those join columns
are the first place to check. Also, what's your orders table row count
in production?
Developer: Here's the EXPLAIN output [pastes plan]. Row count is 4.2M.
Indexes on customer_id and product_id but not on created_at.
Agent: There it is — sequential scan on 4.2M rows for the date filter.
Add a composite index on (created_at, customer_id) in that order. The
date range filter runs first and dramatically reduces the working set
before the join. Use CREATE INDEX CONCURRENTLY to avoid locking.
Also consider whether you need the full date range in one query or can
paginate by month at the application layer — that alone will drop your
result set size. Here's the rewritten query with an optimized GROUP BY
using a subquery to pre-aggregate orders before joining products...Scenario: Diagnosing Connection Pool Exhaustion
A developer is seeing intermittent timeout errors in their Node.js API under moderate load. Database CPU looks fine. The issue turns out to be pool exhaustion.
Developer: Getting "connection timeout" errors in our API under load.
DB CPU is only at 30%. We're using pg with pool max set to 10.
About 50 concurrent API requests at peak.
Agent: Your pool max of 10 is almost certainly the bottleneck, not the
database itself. With 50 concurrent requests and a pool of 10, requests
are queuing and hitting your idleTimeoutMillis or connectionTimeoutMillis.
What's your average query duration? If queries average 100ms and you have
50 concurrent requests, you need at minimum 5 connections to sustain that
throughput, but you have zero headroom for spikes.
A reasonable starting point: set pool max to 25–30 for your workload.
Also check your idleTimeoutMillis — if it's too aggressive you're
constantly tearing down and rebuilding connections.
More importantly: are these long-running queries or fast ones? If queries
are slow (500ms+), the real fix is query optimization first, then pool
sizing. Bigger pools on slow queries just delays the same problem.What Makes This Agent Powerful
Measurement-First Methodology
The agent is explicitly designed around the principle of profiling before optimizing. It will ask for EXPLAIN ANALYZE output, row counts, and actual timing data before making recommendations. This isn’t pedantry — it’s the only way to avoid optimizing the wrong thing. Developers who skip measurement often add indexes that hurt write performance without helping reads.
Engine-Specific Knowledge
PostgreSQL and MySQL have meaningfully different internals. The agent accounts for this: covering indexes, partial indexes, and concurrent index creation are PostgreSQL features. MySQL’s query planner behaves differently with composite indexes. You get recommendations that are actually valid for your specific database engine, not generic SQL advice.
Schema and Query Together
Most query optimization tools look at the query in isolation. This agent considers your schema design, normalization choices, and the relationship between your data model and query patterns. If the real problem is a denormalization opportunity or a missing materialized view, it will say so.
Before/After Framing
Every recommendation comes with projected performance impact. This is valuable not just for your own confidence, but for communicating changes to your team and stakeholders. “This index will reduce the query from 8 seconds to ~200ms based on the execution plan” is a much stronger argument than “I think we need an index.”
Production Safety Awareness
The agent understands the difference between changes that are safe on live databases and changes that require maintenance windows. It defaults to safe approaches — concurrent index creation, incremental migrations — and flags when a change carries locking risk.
How to Install the Database Optimization Agent
Setting up this agent in Claude Code takes about sixty seconds. Claude Code looks for sub-agents in the .claude/agents/ directory of your project. Create the file, paste the system prompt, and Claude Code loads it automatically.
Step 1: In your project root, create the directory if it doesn’t exist:
mkdir -p .claude/agentsStep 2: Create the agent file:
touch .claude/agents/database-optimization.mdStep 3: Open the file and paste the full agent system prompt (the content starting with “You are a database optimization specialist…” through the end of the agent body above).
Step 4: Save the file. Claude Code will automatically detect and load the agent on next invocation. No restart required.
You can invoke it directly by referencing it in your Claude Code session, or Claude Code will route relevant database performance queries to it automatically based on context.
If you’re working across multiple projects, consider adding this agent to a shared dotfiles setup or a team template repository so the whole team benefits.
Next Steps
Install the agent, then run it against your slowest queries first. Pull your slow query log (PostgreSQL’s pg_stat_statements or MySQL’s slow query log), grab the top five offenders by total time, and work through them one by one. The agent will ask for your schema and EXPLAIN ANALYZE output — have those ready.
From there, schedule a periodic index audit. Every quarter, review index usage stats and let the agent help you prune what’s no longer earning its write overhead. Databases that grow organically accumulate index debt the same way codebases accumulate technical debt — the difference is that index debt has a measurable cost on every write.
The goal isn’t to use the agent once for a crisis fix. It’s to build the habit of measuring and optimizing continuously, with a specialist-level tool available every time you need it.
Agent template sourced from the claude-code-templates open source project (MIT License).