database-expert_skill

Overview

This skill provides expert guidance on database performance optimization, schema design, query analysis, and connection management across PostgreSQL, MySQL, MongoDB, and SQLite with ORM integration. I help identify root causes, propose progressive fixes, and deliver actionable changes that reduce latency, improve throughput, and increase reliability. Use it to tune queries, design safe migrations, configure pools, and align ORM usage with database best practices.

How this skill works

I first assess whether a database-specific sub-expert is required (Postgres, MongoDB, Redis, or ORM specialists). I detect environment details from connection strings, config files, and dependencies, then categorize the problem into query performance, schema design, connections/transactions, indexing/storage, security, or monitoring. For each category I run targeted diagnostics, recommend minimal-to-complete fixes, and provide validation steps and safe implementation guidance.

When to use it

• Query plans show sequential scans or long execution times
• Migrations fail or cause performance regressions on large tables
• Connection pool exhaustion, too many connections, or deadlocks
• Frequent slow queries, high disk I/O, or inefficient index usage
• ORM-generated N+1 queries or misaligned loading strategies

Best practices

• Start with read-only diagnostics (EXPLAIN, pg_stat, performance_schema) before making changes
• Apply progressive fixes: minimal -> better -> complete based on risk and impact
• Use connection pooling (PgBouncer/ProxySQL) and size pools to memory constraints
• Prefer parameterized queries, least-privilege roles, and encrypted credentials
• Test migrations on production-sized data and verify backups before schema changes

Example use cases

• Optimize a slow JOIN-heavy endpoint by adding composite indexes and rewriting subqueries
• Design zero-downtime migration plan for adding NOT NULL columns on a large table
• Resolve connection exhaustion by configuring PgBouncer and adjusting pool sizes
• Refactor ORM usage to eliminate N+1 queries with eager loading or optimized joins
• Tune MongoDB aggregation pipelines and recommend sharding keys for scale