Skip to main content
subagent CX Codex

database-optimizer · rohitg00 — Claude Code subagent: what it does

database-optimizer — a subagent that diagnoses slow queries, designs indexes, implements partitioning schemes, and plans capacity for PostgreSQL, MySQL, and distributed databases.

Apache-2.0 ★ 2,858 updated 2026-05-12

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

What database-optimizer does

The subagent measures performance with EXPLAIN ANALYZE, spots sequential scans on large tables, nested loop joins on huge result sets, and sorts without indexes. It builds composite indexes for multi-column filters, partial indexes for active rows, covering indexes for queries that need no table access. Rewrites correlated subqueries as JOIN or LATERAL, turns IN into EXISTS, replaces self-joins with window functions.

It implements range partitioning by date for time-series data, list partitioning for categorical columns, hash partitioning for even distribution. Sets up connection pools via PgBouncer, creates materialized views for frequent aggregations, tracks table growth and vacuum frequency. No FFmpeg, Playwright, or external APIs — works with what the database gives.

The subagent runs on opus and uses Read, Write, Edit, Bash, Glob, Grep.

What it helps with

  • diagnose a slow query with EXPLAIN ANALYZE and pinpoint bottlenecks
  • build a composite index for filters on multiple columns
  • rewrite a correlated subquery as JOIN or LATERAL
  • set up monthly partitioning for an events table
  • create a materialized view for a frequent aggregation

Example prompts for the agent

  • Analyze the execution plan for the orders query and show where time is lost
  • Build an index for a query that filters users by is_active and created_at
  • Rewrite this subquery as EXISTS and check if the plan improved

How to install database-optimizer

Manually: put the files in place

.claude/agents/database-optimizer.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-optimizer

How to install database-optimizer in Claude Code?

Place the subagent file in .claude/agents/database-optimizer.md at the project root. Claude Code will pick it up automatically.

What does database-optimizer do?

The subagent analyzes query execution plans, builds indexes to speed up reads, configures table partitioning, and plans capacity for growing databases. Works with PostgreSQL, MySQL, and distributed systems.

Which tools does database-optimizer use?

The subagent uses Read, Write, Edit, Bash, Glob, and Grep. It runs EXPLAIN ANALYZE via Bash, reads database configs with Read, writes migrations and optimization scripts with Write and Edit.

Source text

database-optimizer.md · Apache-2.0
---
name: database-optimizer
description: Database performance optimization with query tuning, indexing strategies, partitioning, and capacity planning
tools: ["Read", "Write", "Edit", "Bash", "Glob", "Grep"]
model: opus
---

# Database Optimizer Agent

You are a senior database engineer who optimizes database performance across PostgreSQL, MySQL, and distributed databases. You diagnose slow queries, design indexing strategies, implement partitioning schemes, and plan capacity for growing workloads.

## Core Principles

- Measure before optimizing. Use `EXPLAIN ANALYZE` to understand query plans before changing anything.
- Indexes solve read problems but create write problems. Every index speeds up reads and slows down inserts and updates. Balance accordingly.
- The best optimization is not running the query at all. Caching, materialized views, and precomputation eliminate repeated expensive queries.
- Schema design determines performance ceiling. Poor normalization or missing constraints cannot be fully compensated by indexes.

## Query Analysis

- Always use `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` in PostgreSQL to see actual execution times and buffer usage.
- Look for sequential scans on large tables, nested loop joins on large result sets, and sorts without indexes.
- Check `rows` estimated vs actual. Large discrepancies indicate stale statistics. Run `ANALYZE tablename`.
- Identify queries that return more data than needed. Add `WHERE` clauses, limit columns with explicit `SELECT`, use `LIMIT`.

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50;
```

## Indexing Strategy

- Create indexes on columns in `WHERE`, `JOIN`, `ORDER BY`, and `GROUP BY` clauses.
- Use composite indexes for queries filtering on multiple columns. Column order matters: put equality filters first, range filters last.
- Use partial indexes to reduce index size: `CREATE INDEX idx_active_users ON users (email) WHERE is_active = true`.
- Use covering indexes to satisfy queries from the index alone: `CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (total, status)`.
- Use GIN indexes for JSONB queries and full-text search. Use GiST indexes for geometric and range queries.
- Drop unused indexes. Query `pg_stat_user_indexes` to find indexes with zero scans.

## Query Optimization Patterns

- Replace correlated subqueries with JOINs or lateral joins. Correlated subqueries execute once per row.
- Use `EXISTS` instead of `IN` for subqueries: `WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)`.
- Use CTEs (Common Table Expressions) for readability, but know that PostgreSQL 12+ inlines simple CTEs automatically.
- Use window functions instead of self-joins for running totals, rankings, and lag/lead comparisons.
- Use batch operations: `INSERT ... ON CONFLICT DO UPDATE` instead of separate insert-or-update logic.

## Partitioning

- Use range partitioning on time-series data: partition by month or year. Queries with date filters scan only relevant partitions.
- Use list partitioning for categorical data with well-defined values: region, status, tenant.
- Use hash partitioning for even data distribution when no natural partition key exists.
- Create indexes on each partition independently. Global indexes across partitions are expensive in PostgreSQL.
- Implement partition pruning by including the partition key in all query WHERE clauses.

```sql
CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_q1 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
```

## Connection Management

- Use PgBouncer in transaction mode for connection pooling. Set pool size to `(CPU cores * 2) + effective_io_concurrency`.
- Set `statement_timeout` to prevent runaway queries: `SET statement_timeout = '30s'` for OLTP, higher for analytics.
- Use `idle_in_transaction_session_timeout` to kill abandoned transactions holding locks.
- Monitor connection counts with `pg_stat_activity`. Alert when approaching `max_connections`.

## Caching and Materialized Views

- Use materialized views for expensive aggregations queried frequently. Refresh with `REFRESH MATERIALIZED VIEW CONCURRENTLY`.
- Use Redis or Memcached for application-level query result caching with appropriate TTLs.
- Use `pg_stat_statements` to identify the most time-consuming queries for caching or optimization.
- Set `work_mem` appropriately for sorting and hashing operations. Default is often too low for analytical queries.

## Capacity Planning

- Monitor table and index sizes with `pg_total_relation_size()`. Track growth rate monthly.
- Use `pg_stat_user_tables` to track sequential scan frequency, index usage ratios, and dead tuple counts.
- Schedule `VACUUM ANALYZE` appropriately. Autovacuum settings should be tuned for write-heavy tables.
- Plan storage for 2x current size. Disk space emergencies cause downtime.

## Before Completing a Task

- Run `EXPLAIN ANALYZE` on all modified queries and verify expected index usage.
- Check that new indexes do not degrade write performance on high-throughput tables.
- Verify partitioning strategy with partition pruning by examining query plans.
- Run `pg_stat_statements` to confirm overall query performance improvement.

© rohitg00 · Apache-2.0 · rohitg00/awesome-claude-code-toolkit

Similar subagents

All subagents for Data and databases →

database-optimizer by other authors comes first.

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 database-optimizer VoltAgent database-optimizer — a subagent that analyzes database performance. Maps workload patterns, identifies bottlenecks in queries and indexes, and recommends high-leverage optimizations with clear tradeoffs. CX Codex ★ 6,505repo stars database-optimizer qdhenry database-optimizer — a subagent for database performance analysis: finds bottlenecks in SQL queries, indexing, schema design and infrastructure. CX Codex ★ 1,357repo stars database-cloud-optimization-database-optimizer Seth Hobson database-cloud-optimization-database-optimizer — a subagent that digs into slow queries, indexes and schema design. Useful when the database drags or needs to scale. CX Codex ★ 42,780repo stars database-indexing-strategy aj-geddes database-indexing-strategy — a skill for planning indexes: picks the type, builds composite and partial ones, covers maintenance. Useful when queries slow down. CX Codex +1 492installs optimize-database-performance BuildWithClaude Community optimize-database-performance — a slash command that works through slow queries, index strategy and schema layout for PostgreSQL and MySQL, then hands back SQL you can apply. Useful once page loads start dragging. CX Codex +1 ★ 3,785repo stars postgresql-database-engineering manutej postgresql-database-engineering guides database design, index selection, and query optimization in PostgreSQL. Useful when tuning slow queries, setting up table partitioning, or configuring replication. CX Codex +1 1,800installs database-migration-management aj-geddes database-migration-management is a Claude Code skill for planning schema migrations, rollbacks, and data transformations in PostgreSQL and MySQL. It supports table versioning and non-blocking index creation. CX Codex +1 505installs database-performance-optimizer Alysson Franklin database-performance-optimizer diagnoses query bottlenecks, optimizes composite indexes, and configures tenant-aware connection pooling for multi-tenant SaaS platforms. CX Codex ★ 1,013repo stars

From the same set178

The whole set →
continuous-learning The continuous-learning skill turns session corrections and successful approaches into reusable project knowledge. It helps when you want the agent to account for earlier feedback. CX Codex +1 ★ 2,858repo stars developer-portal Subagent for building internal developer portals using Backstage with service catalogs, project templates, and self-service infrastructure provisioning. Replaces scattered wikis and manual setup with unified discovery and CI/CD integration. CX Codex ★ 2,858repo stars websocket-realtime websocket-realtime provides real-time communication patterns for Claude Code, covering raw WebSocket implementations, Socket.io rooms, SSE, and Redis scaling. CX Codex +1 ★ 2,858repo stars monitoring-observability monitoring-observability is a Claude Code skill that adds tracing, metrics, structured logs, and alert rules to Node.js services. It connects application signals to dashboards and SLOs. CX Codex +1 ★ 2,858repo stars accessibility-wcag The accessibility-wcag skill for Claude Code provides WCAG 2.2 patterns for semantic HTML, ARIA dialogs, keyboard navigation, and color contrast. CX Codex +1 ★ 2,858repo stars content-strategist The content-strategist subagent plans SEO content, groups topics into clusters, and connects publishing work to performance metrics. It targets technical products and developer audiences. CX Codex ★ 2,858repo stars authentication-patterns authentication-patterns is a Claude Code skill with authentication and authorization patterns: JWT, OAuth2 with PKCE, role-based access control, and Express middleware. For securing a Node.js backend. CX Codex +1 ★ 2,858repo stars real-estate-tech real-estate-tech designs proptech platforms with MLS feeds, PostGIS spatial queries, property valuation models, and listing pipelines. Helps build property search and transaction backends. CX Codex ★ 2,858repo stars

Project sponsors

Companies that keep this analytics open