What Does This Skill Do?
/pg-perf is a multi-tool workflow that calls three individual MCP tools in sequence and aggregates their results into a complete performance assessment. Instead of calling three tools individually and manually correlating results, the skill delivers a structured report with an overall score from 0 to 100.
Workflow Phases
- Phase 1 — Slow Queries:
pg_slow_query_digestidentifies the slowest queries by total execution time - Phase 2 — Missing Indexes:
pg_index_advisoranalyzes sequential scans and suggests indexes - Phase 3 — Cache Efficiency:
pg_cache_hit_ratiochecks the buffer cache hit ratio - Phase 4 — Aggregated Report: Results are correlated and an overall score is computed
How Is It Called?
Connecting First
Every skill starts with a database connection. The enterprise platform supports multiple profiles — ideal for multi-cluster setups:
▶ Calling tools...
→ pg_connect { "profile": "production-db" }
{
"status": "connected",
"database": "mcp_test",
"version": "PostgreSQL 16.x",
"host": "db.example.com"
}
Claude CLI (Natural Language)
▶ Calling tools...
→ Phase 1/4: pg_slow_query_digest {}
3 slow queries found (Top: SELECT * FROM perf.transactions WHERE status = $1)
Slowest: 847 calls, avg 14.2ms, total: 12.0s
→ Phase 2/4: pg_index_advisor {}
2 missing indexes detected:
→ CREATE INDEX idx_transactions_status ON perf.transactions(status)
→ CREATE INDEX idx_orders_customer_id ON app.orders(customer_id)
→ Phase 3/4: pg_cache_hit_ratio {}
Cache Hit Ratio: 94.7% (Heap), 98.2% (Index)
→ Heap cache below 95% — consider increasing shared_buffers
→ Phase 4/4: Aggregated Report
╔══════════════════════════════════════╗
║ Performance Score: 72 / 100 ║
╠══════════════════════════════════════╣
║ Slow Queries : 3 found ║
║ Missing Indexes : 2 suggested ║
║ Cache Hit Ratio : 94.7% (Heap) ║
║ Recommendation : Indexes + RAM ║
╚══════════════════════════════════════╝
MCP Protocol (Skill Definition)
const result = await client.callTool("/pg-perf", {});
// Response:
{
"skill": "/pg-perf",
"tools": [
"pg_slow_query_digest",
"pg_index_advisor",
"pg_cache_hit_ratio"
],
"phases": 4,
"output": {
"score": 72,
"slow_queries": 3,
"missing_indexes": 2,
"cache_hit_ratio": { "heap": 94.7, "index": 98.2 },
"recommendations": [
"CREATE INDEX idx_transactions_status ON perf.transactions(status)",
"CREATE INDEX idx_orders_customer_id ON app.orders(customer_id)",
"Increase shared_buffers from 128MB to 256MB"
]
}
}
What Happens Under the Hood?
The skill orchestrates three tools in a fixed sequence. Each tool returns structured data that is merged in the aggregation phase:
Phase 1: Slow Query Digest
Analyzes pg_stat_statements and identifies queries with the highest frequency x duration product. The top-N queries feed into Phase 2.
Phase 2: Index Advisor
Checks pg_stat_user_tables for tables with high sequential scan ratios and generates CREATE INDEX suggestions. Correlates with slow queries from Phase 1.
Phase 3: Cache Hit Ratio
Computes the buffer cache hit ratio from pg_statio_user_tables. A value below 95% (heap) or 99% (index) indicates insufficient shared_buffers.
Phase 4: Aggregation
Calculates the overall score using a weighted formula:
- 40% — Slow Query Severity (count x average duration)
- 30% — Missing Index Impact (estimated performance gain)
- 30% — Cache Efficiency (deviation from 99% target)
Pro Tip: A score below 60 requires immediate action. Between 60 and 80, there are clear optimization opportunities. Above 80, the database is in good shape.
What Does the Test Suite Validate?
The test suite validates the complete workflow against a PostgreSQL instance with 100,000 transactions in perf.transactions and deliberately missing indexes:
✓ /pg-perf > pg_index_advisor detects missing indexes on perf.transactions
Test Files 1 passed (1)
Tests 2 passed (2)
Duration 1.8s
Test Data Scenario
The seed database contains:
- perf.transactions — 100,000 rows with amount, currency, status, timestamp (no index on
status) - app.orders — Orders without an index on
customer_id(triggers sequential scan) - app.users — 4 users with realistic query patterns for pg_stat_statements
- pg_stat_statements — Automatically populated by prior test runs
Summary
/pg-perf replaces the manual three-step process of slow query analysis, index checking, and cache inspection with a single command. The aggregated score makes performance trends comparable over time — ideal for weekly health checks or post-deployment validation.