database-optimizer

Name: database-optimizer
Author: Jeffallan

4.2kinstalls971forks

How to install database-optimizer?

jeffallan/claude-skills/database-optimizer

$npx -y skills add Jeffallan/claude-skills --skill database-optimizer

Installs into the current project.

Summary

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, loc

Files · 1

View on GitHub

SKILL.md

1	# Database Optimizer
2
3	Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.
4
5	## When to Use This Skill
6
7	- Analyzing slow queries and execution plans
8	- Designing optimal index strategies
9	- Tuning database configuration parameters
10	- Optimizing schema design and partitioning
11	- Reducing lock contention and deadlocks
12	- Improving cache hit rates and memory usage
13
14	## Core Workflow
15
16	1. Analyze Performance — Capture baseline metrics and run `EXPLAIN ANALYZE` before any changes
17	2. Identify Bottlenecks — Find inefficient queries, missing indexes, config issues
18	3. Design Solutions — Create index strategies, query rewrites, schema improvements
19	4. Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next
20	5. Validate Results — Re-run `EXPLAIN ANALYZE`, compare costs, measure wall-clock improvement, document changes
21
22	> ⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.
23
24	## Reference Guide
25
26	Load detailed guidance based on context:
27
28	\| Topic \| Reference \| Load When \|
29	\|-------\|-----------\|-----------\|
30	\| Query Optimization \| `references/query-optimization.md` \| Analyzing slow queries, execution plans \|
31	\| Index Strategies \| `references/index-strategies.md` \| Designing indexes, covering indexes \|
32	\| PostgreSQL Tuning \| `references/postgresql-tuning.md` \| PostgreSQL-specific optimizations \|
33	\| MySQL Tuning \| `references/mysql-tuning.md` \| MySQL-specific optimizations \|
34	\| Monitoring & Analysis \| `references/monitoring-analysis.md` \| Performance metrics, diagnostics \|
35
36	## Common Operations & Examples
37
38	### Identify Top Slow Queries (PostgreSQL)
39	```sql
40	-- Requires pg_stat_statements extension
41	SELECT query,
42	calls,
43	round(total_exec_time::numeric, 2) AS total_ms,
44	round(mean_exec_time::numeric, 2) AS mean_ms,
45	round(stddev_exec_time::numeric, 2) AS stddev_ms,
46	rows
47	FROM pg_stat_statements
48	ORDER BY mean_exec_time DESC
49	LIMIT 20;
50	```
51
52	### Capture an Execution Plan
53	```sql
54	-- Use BUFFERS to expose cache hit vs. disk read ratio
55	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
56	SELECT o.id, c.name
57	FROM orders o
58	JOIN customers c ON c.id = o.customer_id
59	WHERE o.status = 'pending'
60	AND o.created_at > now() - interval '7 days';
61	```
62
63	### Reading EXPLAIN Output — Key Patterns to Find
64
65	\| Pattern \| Symptom \| Typical Remedy \|
66	\|---------\|---------\|----------------\|
67	\| `Seq Scan` on large table \| High row estimate, no filter selectivity \| Add B-tree index on filter column \|
68	\| `Nested Loop` with large outer set \| Exponential row growth in inner loop \| Consider Hash Join; index inner join key \|
69	\| `cost=... rows=1` but actual rows=50000 \| Stale statistics \| Run `ANALYZE <table>;` \|
70	\| `Buffers: hit=10 read=90000` \| Low buffer cache hit rate \| Increase `shared_buffers`; add covering index \|
71	\| `Sort Method: external merge` \| Sort spilling to disk \| Increase `work_mem` for the session \|
72
73	### Create a Covering Index
74	```sql
75	-- Covers the filter AND the projected columns, eliminating a heap fetch
76	CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
77	ON orders (status, created_at)
78	INCLUDE (customer_id, total_amount);
79	```
80
81	### Validate Improvement
82	```sql
83	-- Before optimization: save plan & timing
84	EXPLAIN (ANALYZE, BUFFERS) <query>; -- note "Execution Time: X ms"
85
86	-- After optimization: compare
87	EXPLAIN (ANALYZE, BUFFERS) <query>; -- target meaningful reduction in cost & time
88
89	-- Confirm index is actually used
90	SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
91	FROM pg_stat_user_indexes
92	WHERE relname = 'orders';
93	```
94
95	### MySQL: Find Slow Queries
96	```sql
97	-- Inspect slow query log candidates
98	SELECT * FROM performance_schema.events_statements_summary_by_digest
99	ORDER BY SUM_TIMER_WAIT DESC
100	LIMIT 20;
101
102	-- Execution plan
103	EXPLAIN FORMAT=JSON
104	SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;
105	```
106
107	## Constraints
108
109	### MUST DO
110	- Capture `EXPLAIN (ANALYZE, BUFFERS)` output before optimizing — this is the baseline
111	- Measure performance before and after every ch

People who liked this also viewed

6 picks

Type

database-optimizer

4.2kinstalls971forks

View on GitHub

How to install database-optimizer?

jeffallan/claude-skills/database-optimizer

$npx -y skills add Jeffallan/claude-skills --skill database-optimizer

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Database Optimizer
2
3	Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.
4
5	## When to Use This Skill
6
7	- Analyzing slow queries and execution plans
8	- Designing optimal index strategies
9	- Tuning database configuration parameters
10	- Optimizing schema design and partitioning
11	- Reducing lock contention and deadlocks
12	- Improving cache hit rates and memory usage
13
14	## Core Workflow
15
16	1. Analyze Performance — Capture baseline metrics and run `EXPLAIN ANALYZE` before any changes
17	2. Identify Bottlenecks — Find inefficient queries, missing indexes, config issues
18	3. Design Solutions — Create index strategies, query rewrites, schema improvements
19	4. Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next
20	5. Validate Results — Re-run `EXPLAIN ANALYZE`, compare costs, measure wall-clock improvement, document changes
21
22	> ⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.
23
24	## Reference Guide
25
26	Load detailed guidance based on context:
27
28	\| Topic \| Reference \| Load When \|
29	\|-------\|-----------\|-----------\|
30	\| Query Optimization \| `references/query-optimization.md` \| Analyzing slow queries, execution plans \|
31	\| Index Strategies \| `references/index-strategies.md` \| Designing indexes, covering indexes \|
32	\| PostgreSQL Tuning \| `references/postgresql-tuning.md` \| PostgreSQL-specific optimizations \|
33	\| MySQL Tuning \| `references/mysql-tuning.md` \| MySQL-specific optimizations \|
34	\| Monitoring & Analysis \| `references/monitoring-analysis.md` \| Performance metrics, diagnostics \|
35
36	## Common Operations & Examples
37
38	### Identify Top Slow Queries (PostgreSQL)
39	```sql
40	-- Requires pg_stat_statements extension
41	SELECT query,
42	calls,
43	round(total_exec_time::numeric, 2) AS total_ms,
44	round(mean_exec_time::numeric, 2) AS mean_ms,
45	round(stddev_exec_time::numeric, 2) AS stddev_ms,
46	rows
47	FROM pg_stat_statements
48	ORDER BY mean_exec_time DESC
49	LIMIT 20;
50	```
51
52	### Capture an Execution Plan
53	```sql
54	-- Use BUFFERS to expose cache hit vs. disk read ratio
55	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
56	SELECT o.id, c.name
57	FROM orders o
58	JOIN customers c ON c.id = o.customer_id
59	WHERE o.status = 'pending'
60	AND o.created_at > now() - interval '7 days';
61	```
62
63	### Reading EXPLAIN Output — Key Patterns to Find
64
65	\| Pattern \| Symptom \| Typical Remedy \|
66	\|---------\|---------\|----------------\|
67	\| `Seq Scan` on large table \| High row estimate, no filter selectivity \| Add B-tree index on filter column \|
68	\| `Nested Loop` with large outer set \| Exponential row growth in inner loop \| Consider Hash Join; index inner join key \|
69	\| `cost=... rows=1` but actual rows=50000 \| Stale statistics \| Run `ANALYZE <table>;` \|
70	\| `Buffers: hit=10 read=90000` \| Low buffer cache hit rate \| Increase `shared_buffers`; add covering index \|
71	\| `Sort Method: external merge` \| Sort spilling to disk \| Increase `work_mem` for the session \|
72
73	### Create a Covering Index
74	```sql
75	-- Covers the filter AND the projected columns, eliminating a heap fetch
76	CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
77	ON orders (status, created_at)
78	INCLUDE (customer_id, total_amount);
79	```
80
81	### Validate Improvement
82	```sql
83	-- Before optimization: save plan & timing
84	EXPLAIN (ANALYZE, BUFFERS) <query>; -- note "Execution Time: X ms"
85
86	-- After optimization: compare
87	EXPLAIN (ANALYZE, BUFFERS) <query>; -- target meaningful reduction in cost & time
88
89	-- Confirm index is actually used
90	SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
91	FROM pg_stat_user_indexes
92	WHERE relname = 'orders';
93	```
94
95	### MySQL: Find Slow Queries
96	```sql
97	-- Inspect slow query log candidates
98	SELECT * FROM performance_schema.events_statements_summary_by_digest
99	ORDER BY SUM_TIMER_WAIT DESC
100	LIMIT 20;
101
102	-- Execution plan
103	EXPLAIN FORMAT=JSON
104	SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;
105	```
106
107	## Constraints
108
109	### MUST DO
110	- Capture `EXPLAIN (ANALYZE, BUFFERS)` output before optimizing — this is the baseline
111	- Measure performance before and after every ch

People who liked this also viewed

6 picks

Type