What common anti-patterns does this skill address?

It specifically helps eliminate N+1 query problems, inefficient OFFSET-based pagination, and performance issues caused by function-wrapped WHERE clauses.

How does this skill help with slow SQL queries?

It provides structured patterns to analyze EXPLAIN plans, identify bottlenecks like sequential scans, and suggests specific indexing or query refactoring strategies to improve speed.

Does it support databases other than PostgreSQL?

While it features PostgreSQL-specific examples, the core concepts like B-Tree indexes, JOIN optimization, and batching apply to MySQL and most relational databases.

Can this help with database schema design?

Yes, it includes best practices for data types, normalization vs. denormalization, and partitioning strategies for large datasets to ensure long-term scalability.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: 3commas-io

by3commas-io

Database Management

Optimizes database performance by identifying and fixing slow SQL queries through systematic indexing, query plan analysis, and efficient execution patterns.

About

This skill empowers developers to transform sluggish database operations into high-performance queries using industry-standard optimization techniques. It provides immediate guidance on interpreting EXPLAIN plans, selecting the correct index types (B-Tree, GIN, GiST), and eliminating common performance killers like N+1 queries and inefficient pagination. Whether you're designing a new schema or troubleshooting a production bottleneck, this skill offers practical patterns for PostgreSQL and general SQL environments to ensure your application scales effectively.

Key Features

Advanced indexing strategies including composite, partial, and covering indexes
Deep analysis of EXPLAIN and EXPLAIN ANALYZE query execution plans
0 GitHub stars
Elimination of N+1 query anti-patterns using JOINs and batch loading
Optimization patterns for batch operations, materialized views, and table partitioning
High-performance pagination techniques using cursor-based methods

Use Cases

Designing scalable database schemas for high-traffic applications
Optimizing large-scale data migrations and batch processing tasks
Troubleshooting and resolving slow-running production database queries

About

Key Features

Advanced indexing strategies including composite, partial, and covering indexes
Deep analysis of EXPLAIN and EXPLAIN ANALYZE query execution plans
0 GitHub stars
Elimination of N+1 query anti-patterns using JOINs and batch loading
Optimization patterns for batch operations, materialized views, and table partitioning
High-performance pagination techniques using cursor-based methods

Use Cases

Designing scalable database schemas for high-traffic applications
Optimizing large-scale data migrations and batch processing tasks
Troubleshooting and resolving slow-running production database queries