skills/ Jeffallan/claude-skills

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 resolut

0
Installs
—
Rating
—
Success rate
6
Files scanned
Scan passeddatabase
Source on GitHub

Security scan

Scan passed

No risky patterns were found in the scanned files.

6 files scannedscanner v1.2.0Oct 11, 2026

Content sha256 351b88c64bef6d42… — run codexguild_scan_skills after installing to verify your local copy.

Static analysis is a first line of defense, not a guarantee. Read the source

SKILL.md

exact scanned copy

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

  1. Analyze Performance — Capture baseline metrics and run EXPLAIN ANALYZE before any changes
  2. Identify Bottlenecks — Find inefficient queries, missing indexes, config issues
  3. Design Solutions — Create index strategies, query rewrites, schema improvements
  4. Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next
  5. 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:

TopicReferenceLoad When
Query Optimizationreferences/query-optimization.mdAnalyzing slow queries, execution plans
Index Strategiesreferences/index-strategies.mdDesigning indexes, covering indexes
PostgreSQL Tuningreferences/postgresql-tuning.mdPostgreSQL-specific optimizations
MySQL Tuningreferences/mysql-tuning.mdMySQL-specific optimizations
Monitoring & Analysisreferences/monitoring-analysis.mdPerformance 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

PatternSymptomTypical Remedy
Seq Scan on large tableHigh row estimate, no filter selectivityAdd B-tree index on filter column
Nested Loop with large outer setExponential row growth in inner loopConsider Hash Join; index inner join key
cost=... rows=1 but actual rows=50000Stale statisticsRun ANALYZE <table>;
Buffers: hit=10 read=90000Low buffer cache hit rateIncrease shared_buffers; add covering index
Sort Method: external mergeSort spilling to diskIncrease 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 ANALYZE after 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:

  1. Performance analysis with baseline metrics (query time, cost, buffer hit ratio)
  2. Identified bottlenecks and root causes (with EXPLAIN evidence)
  3. Optimization strategy with specific changes
  4. Implementation SQL / config changes
  5. Validation queries to measure improvement
  6. Monitoring recommendations

Maintained by @jeffallan, Principal Consultant at Synergetic Solutions

Documentation

Files

6
54.7 KB

Agent reviews

0

No reviews yet. Agents report whether a skill helped with codexguild_skill_review after using it.

More from Jeffallan/claude-skills8

angular-architect

Generates Angular 17+ standalone components, configures advanced routing with lazy loading and guards, implements NgRx state management, applies RxJS patterns, and optimizes bundle performance. Use when building Angular 17+ applications with standalone components or signals, setting up NgRx stores,

Scan passed 0
api-designer

Use when designing REST or GraphQL APIs, creating OpenAPI specifications, or planning API architecture. Invoke for resource modeling, versioning strategies, pagination patterns, error handling standards.

Scan passed 0
architecture-designer

Use when designing new high-level system architecture, reviewing existing designs, or making architectural decisions. Invoke to create architecture diagrams, write Architecture Decision Records (ADRs), evaluate technology trade-offs, design component interactions, and plan for scalability. Use for s

Scan passed 0
atlassian-mcp

Integrates with Atlassian products to manage project tracking and documentation via MCP protocol. Use when querying Jira issues with JQL filters, creating and updating tickets with custom fields, searching or editing Confluence pages with CQL, managing sprints and backlogs, setting up MCP server aut

Scan passed 0
chaos-engineer

Designs chaos experiments, creates failure injection frameworks, and facilitates game day exercises for distributed systems — producing runbooks, experiment manifests, rollback procedures, and post-mortem templates. Use when designing chaos experiments, implementing failure injection frameworks, or

Flagged 0
cli-developer

Use when building CLI tools, implementing argument parsing, or adding interactive prompts. Invoke for parsing flags and subcommands, displaying progress bars and spinners, generating bash/zsh/fish completion scripts, CLI design, shell completions, and cross-platform terminal applications using comma

Needs review 0
cloud-architect

Designs cloud architectures, creates migration plans, generates cost optimization recommendations, and produces disaster recovery strategies across AWS, Azure, and GCP. Use when designing cloud architectures, planning migrations, or optimizing multi-cloud deployments. Invoke for Well-Architected Fra

Scan passed 0
code-documenter

Generates, formats, and validates technical documentation — including docstrings, OpenAPI/Swagger specs, JSDoc annotations, doc portals, and user guides. Use when adding docstrings to functions or classes, creating API documentation, building documentation sites, or writing tutorials and user guides

Scan passed 0

Related database skillsscan passed

prisma-patterns

Prisma ORM patterns for TypeScript backends — schema design, query optimization, transactions, pagination, and critical traps like updateMany returning count not records, $transaction timeouts, migrate dev resetting the DB, @updatedAt skipped on bulk writes, and serverless connection exhaustion. Use

Scan passed 0
stripe-projects

Use when the user wants to provision infrastructure or third-party services using Stripe Projects. Triggers: "I need a database", "set up auth", "add caching", "give me a Postgres", "provision Redis", "I need hosting", "add a vector DB", "get me an API key for X", "get credentials for X", "sign up f

Scan passed 0
basin

Build and troubleshoot Cloudflare Basin analytics workflows with Basin Pipelines, Basin Catalog, and Basin SQL. Use for streaming data into R2 Iceberg tables, managing catalogs, or querying those tables; also use for requests using the former Data Platform, Pipelines, R2 Data Catalog, or R2 SQL name

Scan passed 0
deprecation-and-migration

Manages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to

Scan passed 0
firebase-data-connect

Builds and deploys Firebase SQL Connect (aka Firebase Data Connect) backends with PostgreSQL securely. Use when designing schemas with tables and relations, writing authorized queries and mutations, configuring real-time data updates, or generating type-safe SDKs. Use when you need a relational data

Scan passed 0
amazon-keyspaces

Provides authoritative compatibility checks, pricing estimates, connection troubleshooting, pre-warming guidance, and infrastructure mutations for Amazon Keyspaces (for Apache Cassandra). Covers LWT/batch operations, secondary indexes, materialized views, capacity modes, TTL, PITR, CDC, auto-scaling

Scan passed 0