How does this help with slow pagination on large tables?

It teaches cursor-based pagination patterns that avoid the performance degradation associated with large OFFSET values on big datasets.

Which database systems are supported by these patterns?

The patterns are broadly applicable to most relational databases, with specific syntax examples provided for PostgreSQL and MySQL.

Does it provide guidance on indexing beyond simple B-Trees?

Absolutely. It covers advanced index types including GIN for JSONB and search, partial indexes for filtered datasets, and covering indexes to minimize table lookups.

Will this help me understand EXPLAIN ANALYZE output?

Yes, it includes a breakdown of key metrics like Sequential Scans vs. Index Scans, join methods, and cost estimates to help you pinpoint bottlenecks.

Can this skill help resolve N+1 query issues?

Yes, it provides specific SQL and application-level patterns to replace N+1 query loops with efficient JOINs or batch loading strategies.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: GaitanS

byGaitanS

0•

Database Management

Optimizes database performance through advanced indexing strategies, execution plan analysis, and efficient query refactoring patterns.

SQL Optimization Patterns is a specialized skill designed to help developers transform slow, resource-heavy database operations into high-performance queries. It provides comprehensive guidance on interpreting execution plans (EXPLAIN), implementing sophisticated indexing strategies like partial and covering indexes, and refactoring common performance bottlenecks such as N+1 queries and heavy OFFSET pagination. By following these patterns, developers can significantly reduce database load, lower infrastructure costs, and ensure application scalability across various SQL environments including PostgreSQL and MySQL.

Key Features

01Advanced Indexing (Composite, Partial, GIN, and Covering indexes)

02High-Performance Pagination (Cursor-based and keyset techniques)

030 GitHub stars

04Query Execution Plan Analysis (EXPLAIN/EXPLAIN ANALYZE)

05Query Refactoring (Eliminating N+1 queries and correlated subqueries)

06Advanced Scaling Strategies (Materialized views and table partitioning)

Use Cases

01Designing high-performance database schemas for large-scale applications

02Reducing cloud database costs by optimizing query efficiency and resource usage

03Troubleshooting and fixing slow-running production database queries

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add gaitans/ai-mancare sql-optimization-patterns

For use in Claude.ai and ChatGPT

Download Skill