What is the best way to handle large dataset pagination?

The skill recommends cursor-based pagination using keyset values rather than the OFFSET clause, which becomes increasingly slow as the result set grows.

Does it support specific database engines?

While many patterns are universal, the skill includes specific optimizations, syntax, and execution plan metrics tailored for PostgreSQL and MySQL.

Can this skill help with initial database schema design?

Yes, it offers guidance on choosing appropriate data types, implementing functional indexes, and setting up partitioning schemes to ensure long-term scalability.

How do I analyze specific query performance bottlenecks?

It teaches you how to interpret EXPLAIN ANALYZE output to identify Seq Scans, high-cost joins, and discrepancies between estimated and actual row counts.

How does this skill help with slow SQL queries?

It provides systematic patterns for analyzing EXPLAIN plans, identifying missing indexes, and rewriting inefficient queries like N+1 loops or high-offset pagination.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: Activer007

byActiver007

Database Management

Optimizes SQL queries and database performance through indexing strategies, EXPLAIN plan analysis, and efficient query patterns.

About

This skill provides a comprehensive toolkit for transforming slow database operations into high-performance queries. It guides developers through systematic optimization techniques including PostgreSQL and MySQL EXPLAIN analysis, advanced indexing strategies (B-Tree, GIN, Partial, and Covering), and the resolution of common bottlenecks like the N+1 query pattern. Whether you are designing a new schema or debugging performance issues in production, this skill offers practical patterns for cursor-based pagination, efficient aggregation, batching operations, and advanced features like table partitioning and materialized views to ensure scalability and reduce database load.

Key Features

Deep EXPLAIN and query execution plan analysis
Database scaling through partitioning and materialized views
Proven patterns for eliminating N+1 query bottlenecks
0 GitHub stars
Advanced indexing strategies including GIN, Partial, and Covering indexes
High-performance pagination and aggregation techniques

Use Cases

Designing performant schemas for high-traffic applications
Reducing database infrastructure costs through query efficiency
Debugging and resolving slow-running production database queries

About

Key Features

Deep EXPLAIN and query execution plan analysis
Database scaling through partitioning and materialized views
Proven patterns for eliminating N+1 query bottlenecks
0 GitHub stars
Advanced indexing strategies including GIN, Partial, and Covering indexes
High-performance pagination and aggregation techniques

Use Cases

Designing performant schemas for high-traffic applications
Reducing database infrastructure costs through query efficiency
Debugging and resolving slow-running production database queries