Is it useful for initial schema design?

Absolutely. It helps you select the right data types and indexing strategies during the design phase to prevent performance issues before they occur in production.

How does this skill help with slow SQL queries?

It provides systematic patterns for analyzing execution plans, suggesting the correct indexes, and refactoring query logic to reduce database load and latency.

Can it help with N+1 query problems?

Yes, it includes specific strategies for identifying N+1 loops and transforming them into efficient single-query JOINs or batch loading operations.

Does it cover advanced scaling techniques?

Yes, the skill provides guidance on advanced topics such as materialized views for expensive aggregations and table partitioning for very large datasets.

Does it support databases other than PostgreSQL?

While the examples are primarily focused on PostgreSQL, the core optimization concepts and strategies are applicable to MySQL, MariaDB, and other relational databases.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: HermeticOrmus

byHermeticOrmus

•

Database Management

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

About

This skill empowers developers to transform slow-running database operations into high-performance queries by applying systematic optimization techniques. It provides comprehensive guidance on interpreting execution plans, implementing specialized index types like GIN and BRIN, resolving N+1 query anti-patterns, and utilizing advanced features such as materialized views and partitioning. Whether you are debugging a production bottleneck or designing a scalable schema from scratch, this skill ensures your database layer remains responsive, cost-effective, and capable of handling growing datasets.

Key Features

1 GitHub stars
Expert analysis of EXPLAIN and EXPLAIN ANALYZE query plans to identify bottlenecks
Implementation of specialized indexing strategies including B-Tree, GIN, Partial, and Covering indexes
Resolution of N+1 query anti-patterns through efficient JOINs and batch loading
Optimization of pagination using high-performance cursor-based techniques
Advanced scaling patterns including Materialized Views and Table Partitioning

Use Cases

Designing performant database schemas for high-growth, data-intensive applications
Refactoring legacy ORM code to eliminate inefficient database access patterns
Debugging and fixing slow-running production queries causing application timeouts

About

Key Features

1 GitHub stars
Expert analysis of EXPLAIN and EXPLAIN ANALYZE query plans to identify bottlenecks
Implementation of specialized indexing strategies including B-Tree, GIN, Partial, and Covering indexes
Resolution of N+1 query anti-patterns through efficient JOINs and batch loading
Optimization of pagination using high-performance cursor-based techniques
Advanced scaling patterns including Materialized Views and Table Partitioning

Use Cases

Designing performant database schemas for high-growth, data-intensive applications
Refactoring legacy ORM code to eliminate inefficient database access patterns
Debugging and fixing slow-running production queries causing application timeouts