SQL Idioms and Patterns

What problem does it solve?

This Skill helps you avoid fragile, unreadable, or inefficient SQL by enforcing idiomatic patterns that remain correct under growth, concurrency, and schema evolution.

Core Features & Use Cases

• Query readability and maintainability: Prefer CTEs over nested subqueries and use explicit JOINs for clear intent.
• Performance and scalability: Use EXPLAIN (ANALYZE, BUFFERS), avoid SELECT *, design indexes (including composite/partial/covering), and choose keyset pagination over OFFSET at scale.
• Migration-safe and correct data operations: Apply idempotent DDL strategies, safe constraint/index creation patterns, UPSERT for race-free updates, and transaction-safe locking patterns.

Example: You are building a task queue and need a query that reliably selects the next pending job under concurrency; this Skill guides you to use SKIP LOCKED, set appropriate timeouts, and structure indexes so throughput stays high as the table grows.

Quick Start

Use the SQL Idioms and Patterns skill to rewrite the provided SQL query to use CTEs, explicit joins, safe parameterization, and index-aware performance improvements while ensuring migration-safe schema changes.