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

数据库管理

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.

主要功能

Advanced Indexing (Composite, Partial, GIN, and Covering indexes)
High-Performance Pagination (Cursor-based and keyset techniques)
0 GitHub stars
Query Execution Plan Analysis (EXPLAIN/EXPLAIN ANALYZE)
Query Refactoring (Eliminating N+1 queries and correlated subqueries)
Advanced Scaling Strategies (Materialized views and table partitioning)

使用场景

Designing high-performance database schemas for large-scale applications
Reducing cloud database costs by optimizing query efficiency and resource usage
Troubleshooting and fixing slow-running production database queries

关于

主要功能

Advanced Indexing (Composite, Partial, GIN, and Covering indexes)
High-Performance Pagination (Cursor-based and keyset techniques)
0 GitHub stars
Query Execution Plan Analysis (EXPLAIN/EXPLAIN ANALYZE)
Query Refactoring (Eliminating N+1 queries and correlated subqueries)
Advanced Scaling Strategies (Materialized views and table partitioning)

使用场景

Designing high-performance database schemas for large-scale applications
Reducing cloud database costs by optimizing query efficiency and resource usage
Troubleshooting and fixing slow-running production database queries