database-advanced-optimization

Slow query identification uses EXPLAIN ANALYZE to reveal execution plans and bottlenecks. This skill guides you through query plan analysis, rewriting inefficient SQL, and implementing optimal indexes—B-tree, GIN, or GiST types—to drastically reduce execution time and improve application performance.

Indexing strategies depend on query patterns and data distribution. This skill teaches you to design and implement index types—B-tree for range queries, GIN for full-text search, GiST for spatial data—across PostgreSQL, MongoDB, and Redis to accelerate reads while minimizing write overhead.

Connection pooling manages concurrent database connections efficiently using tools like PgBouncer. This skill provides configuration templates and automation scripts to pool connections across SQL and NoSQL stores, reducing connection overhead and enabling production-grade deployments at scale.

While core principles differ, this skill addresses optimization across PostgreSQL, MongoDB, and Redis with platform-specific techniques. It covers indexing strategies, query optimization, caching approaches, and sharding/replication planning tailored to each database type's architecture.

VACUUM reclaims storage and prevents transaction ID wraparound; autovacuum automates this process. This skill covers both manual VACUUM tuning and autovacuum parameter configuration to maintain PostgreSQL performance without manual intervention in production environments.

Database monitoring uses profiling scripts and dashboards to track query execution, connection counts, and resource usage. This skill provides monitoring and profiling tooling, configuration templates, and automation scripts to identify bottlenecks and optimize production-grade deployments continuously.