database-optimizer
Optimizes database queries and improves performance across PostgreSQL and MySQL systems. Use when investigating slow queries, analyzing execution plans, or optimizing database performance. Invoke for index design, query rewrites, configuration tuning, partitioning strategies, lock contention resolution.
- Installs
- 6K
- Last 24h
- +34
- Trending rank
- #4158
- Security
- Fail
Install
Works with every agent the skills CLI supports.
npx skills add https://github.com/jeffallan/claude-skills --skill database-optimizer- 01
Install
Run the command in your project. The CLI asks which agents to install for.
- 02
Describe the task
Ask your agent for something this skill covers. It loads database-optimizer automatically when relevant.
- 03
Review the result
The agent follows the SKILL.md steps, scripts and references bundled with the skill.
Security audits
Independent scans of the skill's instructions and bundled files.
SKILL.md
The instructions your agent reads when the skill is triggered.
Database Optimizer
Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.
When to Use This Skill
- Analyzing slow queries and execution plans
- Designing optimal index strategies
- Tuning database configuration parameters
- Optimizing schema design and partitioning
- Reducing lock contention and deadlocks
- Improving cache hit rates and memory usage
Core Workflow
- Analyze Performance — Capture baseline metrics and run
EXPLAIN ANALYZEbefore any changes - Identify Bottlenecks — Find inefficient queries, missing indexes, config issues
- Design Solutions — Create index strategies, query rewrites, schema improvements
- Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next
- Validate Results — Re-run
EXPLAIN ANALYZE, compare costs, measure wall-clock improvement, document changes
⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.
Reference Guide
Load detailed guidance based on context:
| Topic | Reference | Load When |
|---|---|---|
| Query Optimization | references/query-optimization.md | Analyzing slow queries, execution plans |
| Index Strategies | references/index-strategies.md | Designing indexes, covering indexes |
| PostgreSQL Tuning | references/postgresql-tuning.md | PostgreSQL-specific optimizations |
| MySQL Tuning | references/mysql-tuning.md | MySQL-specific optimizations |
| Monitoring & Analysis | references/monitoring-analysis.md | Performance metrics, diagnostics |
Common Operations & Examples
Identify Top Slow Queries (PostgreSQL)
-- Requires pg_stat_statements extension
SELECT query,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(stddev_exec_time::numeric, 2) AS stddev_ms,
rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
Capture an Execution Plan
-- Use BUFFERS to expose cache hit vs. disk read ratio
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
AND o.created_at > now() - interval '7 days';
Reading EXPLAIN Output — Key Patterns to Find
| Pattern | Symptom | Typical Remedy |
|---|---|---|
Seq Scan on large table | High row estimate, no filter selectivity | Add B-tree index on filter column |
Nested Loop with large outer set | Exponential row growth in inner loop | Consider Hash Join; index inner join key |
cost=... rows=1 but actual rows=50000 | Stale statistics | Run ANALYZE <table>; |
Buffers: hit=10 read=90000 | Low buffer cache hit rate | Increase shared_buffers; add covering index |
Sort Method: external merge | Sort spilling to disk | Increase work_mem for the session |
Create a Covering Index
-- Covers the filter AND the projected columns, eliminating a heap fetch
CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
ON orders (status, created_at)
INCLUDE (customer_id, total_amount);
Validate Improvement
-- Before optimization: save plan & timing
EXPLAIN (ANALYZE, BUFFERS) <query>; -- note "Execution Time: X ms"
-- After optimization: compare
EXPLAIN (ANALYZE, BUFFERS) <query>; -- target meaningful reduction in cost & time
-- Confirm index is actually used
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';
MySQL: Find Slow Queries
-- Inspect slow query log candidates
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
-- Execution plan
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;
Constraints
MUST DO
- Capture
EXPLAIN (ANALYZE, BUFFERS)output before optimizing — this is the baseline - Measure performance before and after every change
- Create indexes with
CONCURRENTLY(PostgreSQL) to avoid table locks - Test in non-production; roll back if write performance or replication lag worsens
- Document all optimization decisions with before/after metrics
- Run
ANALYZEafter bulk data changes to refresh statistics
MUST NOT DO
- Apply optimizations without a measured baseline
- Create redundant or unused indexes
- Make multiple changes simultaneously (impossible to attribute impact)
- Ignore write amplification caused by new indexes
- Neglect
VACUUM/ statistics maintenance
Output Templates
When optimizing database performance, provide:
- Performance analysis with baseline metrics (query time, cost, buffer hit ratio)
- Identified bottlenecks and root causes (with EXPLAIN evidence)
- Optimization strategy with specific changes
- Implementation SQL / config changes
- Validation queries to measure improvement
- Monitoring recommendations
Maintained by @jeffallan, Principal Consultant at Synergetic Solutions
FAQ
What does the database-optimizer skill do?
Optimizes database queries and improves performance across PostgreSQL and MySQL systems. Use when investigating slow queries, analyzing execution plans, or optimizing database performance. Invoke for index design, query rewrites, configuration tuning, partitioning strategies, lock contention resolution.
How do I install database-optimizer?
Run npx skills add https://github.com/jeffallan/claude-skills --skill database-optimizer in your project. The skills CLI detects the agents you use and copies the skill into the right folder. For Claude Code you can also place the skill folder at ~/.claude/skills/database-optimizer/ (personal) or .claude/skills/database-optimizer/ (project).
Which AI agents can use database-optimizer?
Any agent that supports the open Agent Skills format (a folder with a SKILL.md file), including Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, Windsurf, Cline, OpenCode and more.
Is database-optimizer safe to install?
Security partners have reviewed it: Gen Agent Trust Hub flagged risks; Socket passed; Snyk passed; Runlayer flagged risks; ZeroLeaks passed. As with any skill, read the SKILL.md and bundled scripts before installing.
Who maintains database-optimizer?
It is published by jeffallan in jeffallan/claude-skills. It has 5,988 recorded installs.
More from jeffallan/claude-skills
laravel-specialist
jeffallan/claude-skills
Build and configure Laravel 10+ applications, including creating Eloquent models and relationships, implementing Sanctum authentication, configuring Horizon queues, designing RESTful APIs with API resources, and building reactive interfaces with Livewire. Use when creating Laravel models, setting up queue workers, implementing Sanctum auth flows, building Livewire components, optimising Eloquent queries, or writing Pest/PHPUnit tests for Laravel features.
golang-pro
jeffallan/claude-skills
Implements concurrent Go patterns using goroutines and channels, designs and builds microservices with gRPC or REST, optimizes Go application performance with pprof, and enforces idiomatic Go with generics, interfaces, and robust error handling. Use when building Go applications requiring concurrent programming, microservices architecture, or high-performance systems. Invoke for goroutines, channels, Go generics, gRPC integration, CLI tools, benchmarks, or table-driven testing.
php-pro
jeffallan/claude-skills
Use when building PHP applications with modern PHP 8.3+ features, Laravel, or Symfony frameworks. Invokes strict typing, PHPStan level 9, async patterns with Swoole, and PSR standards. Creates controllers, configures middleware, generates migrations, writes PHPUnit/Pest tests, defines typed DTOs and value objects, sets up dependency injection, and scaffolds REST/GraphQL APIs. Use when working with Eloquent, Doctrine, Composer, Psalm, ReactPHP, or any PHP API development.
flutter-expert
jeffallan/claude-skills
Use when building cross-platform applications with Flutter 3+ and Dart. Invoke for widget development, Riverpod/Bloc state management, GoRouter navigation, platform-specific implementations, performance optimization.
typescript-pro
jeffallan/claude-skills
Implements advanced TypeScript type systems, creates custom type guards, utility types, and branded types, and configures tRPC for end-to-end type safety. Use when building TypeScript applications requiring advanced generics, conditional or mapped types, discriminated unions, monorepo setup, or full-stack type safety with tRPC.
kubernetes-specialist
jeffallan/claude-skills
Use when deploying or managing Kubernetes workloads. Invoke to create deployment manifests, configure pod security policies, set up service accounts, define network isolation rules, debug pod crashes, analyze resource limits, inspect container logs, or right-size workloads. Use for Helm charts, RBAC policies, NetworkPolicies, storage configuration, performance optimization, GitOps pipelines, and multi-cluster management.
Popular data & databases skills
supabase-postgres-best-practices
supabase/agent-skills
Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pgcron, pgmq), vector/semantic search (pgvector), and restoring dumps (pgrestore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query.
prisma-client-api
prisma/skills
Prisma Client API reference covering model queries, filters, operators, and client methods. Use when writing database queries, using CRUD operations, filtering data, or configuring Prisma Client. Triggers on "prisma query", "findMany", "create", "update", "delete", "$transaction".
prisma-database-setup
prisma/skills
Deprecated compatibility name for prisma-orm-setup. Use only when a user or existing integration explicitly invokes prisma-database-setup; otherwise select prisma-orm-setup.
prisma-cli
prisma/skills
Prisma ORM 7 CLI reference for existing Prisma 7 applications or explicitly selected Prisma 7 workflows. Covers prisma init, generate, migrate, db, dev, studio, and mcp. For setup, connection repair, or Prisma 8, use prisma-orm-setup; not the Prisma Platform CLI.
prisma-postgres
prisma/skills
Deprecated compatibility name for prisma-postgres-setup. Use only when a user or existing integration explicitly invokes prisma-postgres; otherwise select prisma-postgres-setup.
prisma-driver-adapter-implementation
prisma/skills
Required reference for Prisma ORM 7 SQL driver adapter work. Use when implementing or modifying adapters, adding database drivers, or touching SqlDriverAdapter, Transaction, savepoint, result mapping, or DriverAdapterError behavior. Covers current transaction lifecycle, optional savepoint hooks, original database-error preservation, and verification.