How does this help with N+1 query problems?

It provides specific code patterns and SQL structures to convert repetitive application-level loops into efficient batch queries or relational JOINs.

Does this skill support specific database engines?

The core optimization patterns are applicable to most SQL databases, with specific implementation examples provided for PostgreSQL and MySQL.

Can it help with very large datasets?

Yes, it includes advanced architectural techniques like table partitioning and materialized views specifically designed to maintain performance at scale.

Will this skill help me understand EXPLAIN outputs?

Absolutely. It breaks down key metrics like Sequential Scans vs. Index Scans and different Join methods to help you understand exactly how the database executes your query.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: HermeticOrmus

byHermeticOrmus

0•

Database Management

Optimizes SQL queries and database schemas using advanced indexing, execution plan analysis, and performance patterns.

This skill empowers developers to transform sluggish database operations into high-performance queries by applying systematic optimization techniques. It provides expert guidance on interpreting EXPLAIN plans, implementing strategic indexing (B-Tree, GIN, Composite), and resolving common performance bottlenecks like N+1 queries or inefficient pagination. Whether you are designing a new schema or debugging a production database, this skill offers the patterns and best practices needed to reduce latency, lower costs, and improve application scalability across various SQL environments.

Key Features

01Deep analysis and interpretation of SQL EXPLAIN execution plans to identify bottlenecks

02Guidance on advanced techniques like materialized views and table partitioning for large datasets

030 GitHub stars

04Performance-driven pagination using cursor-based methods to replace slow OFFSET operations

05Elimination of N+1 query anti-patterns through optimized JOINs and batch loading

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

Use Cases

01Designing performant and scalable database schemas for high-traffic applications

02Debugging and fixing slow-running production database queries to improve user experience

03Reducing cloud database compute costs by optimizing query efficiency and resource usage

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add hermeticormus/floreserlife sql-optimization-patterns

For use in Claude.ai and ChatGPT

Download Skill