Can it help with both PostgreSQL and MySQL?

Yes, while the syntax examples focus on PostgreSQL, the core optimization concepts like indexing, execution plans, and pagination are applicable to most relational databases including MySQL and SQL Server.

Will it help me design a new database schema?

Yes, it includes best practices for data types, indexing strategies, and table partitioning to ensure your database schema is optimized for scalability from the start.

Does this skill address the N+1 query problem?

Absolutely. It includes specific patterns for identifying N+1 anti-patterns and replacing them with efficient batch queries or JOIN-based data retrieval.

How does this skill help with slow SQL queries?

It provides systematic patterns for analyzing execution plans (EXPLAIN), identifying missing indexes, and refactoring inefficient JOINs or subqueries to reduce execution time.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: HermeticOrmus

byHermeticOrmus

Database Management

Optimizes SQL queries and database performance through systematic indexing strategies, execution plan analysis, and query restructuring.

About

This skill empowers developers to transform sluggish database operations into high-performance queries by applying industry-standard optimization techniques. It provides expert guidance on interpreting EXPLAIN plans, implementing advanced indexing strategies (like covering and partial indexes), and resolving common performance bottlenecks such as N+1 queries and inefficient pagination. Whether you are designing a scalable schema from scratch or debugging a production performance issue, this skill offers the implementation patterns and best practices needed to minimize latency and reduce database server load.

Key Features

Advanced Indexing Strategies including GIN, GiST, and BRIN
Query Refactoring for Batch Operations and Materialized Views
N+1 Query Detection and Resolution Patterns
High-Performance Cursor-based Pagination Implementation
0 GitHub stars
Execution Plan (EXPLAIN) Analysis and Interpretation

Use Cases

Designing performant schemas for large-scale data sets
Refactoring application logic to eliminate redundant database calls
Debugging slow-running production database queries

About

Key Features

Advanced Indexing Strategies including GIN, GiST, and BRIN
Query Refactoring for Batch Operations and Materialized Views
N+1 Query Detection and Resolution Patterns
High-Performance Cursor-based Pagination Implementation
0 GitHub stars
Execution Plan (EXPLAIN) Analysis and Interpretation

Use Cases

Designing performant schemas for large-scale data sets
Refactoring application logic to eliminate redundant database calls
Debugging slow-running production database queries