Database Optimizer

What it does

When your agent uses it

• Investigating slow queries and reading execution plans
• Designing indexes or rewriting queries for better performance
• Tuning PostgreSQL or MySQL configuration parameters
• Reducing lock contention and deadlocks

Example prompts

• “Find the ten slowest queries in our Postgres database and explain their plans.”
• “This report query does a sequential scan on orders. Propose an index and verify the gain.”
• “Our MySQL replica lags after peak writes. Check for lock contention.”

Requirements

• Access to a PostgreSQL or MySQL database
• The pg_stat_statements extension for the slow-query example

Workflow steps

5 steps, taken from the first numbered list in SKILL.md.

1. 1Analyze Performance — Capture baseline metrics and run EXPLAIN ANALYZE before any changes
2. 2Identify Bottlenecks — Find inefficient queries, missing indexes, config issues
3. 3Design Solutions — Create index strategies, query rewrites, schema improvements
4. 4Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next
5. 5Validate Results — Re-run EXPLAIN ANALYZE, compare costs, measure wall-clock improvement, document changes

What it can do on your machine

Read from SKILL.md and the folder at commit 1be15d8. It shows what the files ask for, not the result of running them.

• Tool permissions

  Pre-approves nothing: there is no allowed-tools line, so your agent's usual permission prompts apply.

  From allowed-tools in the SKILL.md frontmatter.

• Runs code

  No scripts in the folder and no shell commands in SKILL.md (its code samples are sql).

  From the folder's file list and the shell code blocks in SKILL.md.

• Network

  Links to these hosts (documentation or services it may open):

  • github.com
  • synergetic.solutions
  • jeffallan.github.io

  From URLs in SKILL.md, links to its own repository left out.

• Credentials

  Names no API keys, tokens, secrets or passwords.

  From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

Context cost

Database Optimizer loads about 1.6k tokens when it runs, and up to ~14k if it reads all its reference files. Until then it costs about 81 tokens; SKILL.md has 430 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it

~81

When it runs · the whole SKILL.md, loaded when a task matches

~1.6k

With references · SKILL.md plus every file in references/, read only if the agent opens them

~14k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); files beside SKILL.md are not scanned.

SKILL.md

The full file from Jeffallan/claude-skills at commit 1be15d8, republished under its MIT licence (© Jeffallan). 430 words, ~1,554 tokens.

name

database-optimizer

description

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.

license

MIT

metadata.author

https://github.com/Jeffallan

metadata.company

https://synergetic.solutions

metadata.version

1.1.1

metadata.domain

infrastructure

metadata.triggers

database optimization, slow query, query performance, database tuning, index optimization, execution plan, EXPLAIN ANALYZE, database performance, PostgreSQL…

metadata.role

specialist

metadata.scope

optimization

metadata.output-format

analysis-and-code

metadata.related-skills

devops-engineer, postgres-pro, graphql-architect

-- 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;

-- 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';

-- 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);

-- 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';

-- 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;

© Jeffallan, MIT. Rendered from Markdown: HTML in the file is shown as text, images as links, and headings moved down two levels. Raw file

Files

SKILL.md and 5 other files (references) in skills/database-optimizer of Jeffallan/claude-skills.

• SKILL.md
• references/index-strategies.md
• references/monitoring-analysis.md
• references/mysql-tuning.md
• references/postgresql-tuning.md
• references/query-optimization.md

Open the folder on GitHubat commit 1be15d8