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

0•

Database Management

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

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

01Deep EXPLAIN and query execution plan analysis

02Database scaling through partitioning and materialized views

03Proven patterns for eliminating N+1 query bottlenecks

040 GitHub stars

05Advanced indexing strategies including GIN, Partial, and Covering indexes

06High-performance pagination and aggregation techniques

Use Cases

01Designing performant schemas for high-traffic applications

02Reducing database infrastructure costs through query efficiency

03Debugging and resolving slow-running production database queries

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add activer007/ordinary-claude-skills sql-optimization-patterns

For use in Claude.ai and ChatGPT

Download Skill