Can this skill help with different database engines?

Yes, while many examples focus on PostgreSQL, the core concepts of indexing, query plans, and optimization patterns are applicable across most major SQL databases like MySQL, MariaDB, and SQL Server.

How does this skill help with N+1 query problems?

It provides specific patterns for converting repetitive queries into efficient JOINs or batch loading operations to minimize database round trips and improve application response times.

When should I use cursor-based pagination over OFFSET?

This skill recommends cursor-based pagination for large datasets where OFFSET becomes prohibitively slow because the database must scan and discard all preceding rows before reaching the requested page.

What is the benefit of using EXPLAIN ANALYZE?

EXPLAIN ANALYZE executes the query and provides the actual execution time and row counts, allowing for precise identification of bottlenecks that theoretical cost estimates might miss.

How do I know which index type to use?

The skill guides you through choosing between B-Tree for standard ranges, GIN for full-text or JSONB, and BRIN for very large tables, ensuring the index matches your specific query patterns.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: Microck

byMicrock

•

データベース管理

Optimizes database performance through advanced indexing strategies, query plan analysis, and efficient SQL pattern implementation.

This skill empowers developers to transform slow database queries into high-performance operations by providing expert guidance on EXPLAIN plan analysis, sophisticated indexing techniques, and the elimination of common anti-patterns like N+1 queries. It serves as a comprehensive toolkit for debugging performance bottlenecks, designing scalable schemas, and implementing batch operations to reduce database load and application latency, making it essential for any data-intensive application development.

主な機能

01Deep analysis and interpretation of EXPLAIN and EXPLAIN ANALYZE query plans

022 GitHub stars

03Guidance on database partitioning and materialized views for large-scale datasets

04Best practices for batch operations and efficient aggregation techniques

05Automated patterns for eliminating N+1 query problems and optimizing pagination

06Implementation of advanced indexing strategies including composite, partial, and covering indexes

ユースケース

01Designing high-performance database schemas for new features or microservices

02Refactoring legacy ORM-generated queries to improve scalability and reduce infrastructure costs

03Debugging and fixing slow-running production queries that cause application timeouts

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add microck/opendots-microck sql-optimization-patterns

For use in Claude.ai and ChatGPT

Download Skill