SQL Idioms

SQL Idioms and Patterns

SQL rewards set-based thinking, explicit joins, and query plan awareness. Idiomatic SQL = readable, performant, migration-safe.

Scope: SQL coding idioms. For database design principles, load @.agents/rules/database-design-principles.md.

Query Patterns

1. CTEs over subqueries for readability:

   -- ✅ CTE — readable, debuggable
   WITH active_tasks AS (
       SELECT id, title, priority, user_id
       FROM tasks
       WHERE status = 'active'
   )
   SELECT u.name, COUNT(at.id) AS task_count
   FROM users u
   JOIN active_tasks at ON u.id = at.user_id
   GROUP BY u.name;
   
   -- ❌ Nested subquery — hard to read
   SELECT u.name, (SELECT COUNT(*) FROM tasks t WHERE t.user_id = u.id AND t.status = 'active')
   FROM users u;

2. Window functions for ranking, running totals:

   SELECT title, priority,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
   FROM tasks;

3. Explicit JOIN syntax — never implicit joins in WHERE.

4. Parameterized queries — never string concatenation. (See .agents/rules/security-mandate.md.)

Migration Safety

For migration strategy (additive-first, two-phase drops, reversibility), see @.agents/rules/database-design-principles.md § Migrations.

1. Index creation: CONCURRENTLY on PostgreSQL for zero-downtime.
2. Idempotent DDL — use IF NOT EXISTS for tables/indexes; DO $$ ... pg_constraint check ... $$ for constraints.

Index Strategy

1. Choose the right index type:

   • B-tree (default): =, <, >, BETWEEN, IN, IS NULL
   • GIN: arrays, JSONB (@>), full-text search (@@)
   • GiST: geometric data, range types, nearest-neighbor (KNN)
   • BRIN: large time-series tables (10-100x smaller than B-tree)
   • Hash: equality-only (marginally faster than B-tree for =)

2. Composite indexes — column order matters:

   -- Equality columns first, range columns last (leftmost prefix rule)
   CREATE INDEX idx ON orders (status, created_at);
   -- Works for: WHERE status = 'pending'
   -- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
   -- Does NOT work for: WHERE created_at > '2024-01-01' (alone)

3. Partial indexes for filtered queries:

   -- Index only active rows (5-20x smaller)
   CREATE INDEX idx_users_active_email ON users (email) WHERE deleted_at IS NULL;

4. Covering indexes to avoid heap fetches:

   -- INCLUDE non-searchable columns for index-only scans
   CREATE INDEX idx_orders_status ON orders (status) INCLUDE (customer_id, total);

5. Indexes on foreign keys — always. PostgreSQL does not auto-index FKs.

Performance

1. EXPLAIN (ANALYZE, BUFFERS) before optimizing — never guess.

   • Seq Scan on large table = missing index
   • Rows Removed by Filter = poor selectivity
   • read >> hit in Buffers = data not cached
   • Sort Method: external merge = work_mem too low

2. Avoid SELECT * — list specific columns.

3. Keyset pagination over OFFSET for large datasets:

   -- O(1) regardless of page depth
   SELECT * FROM products WHERE (created_at, id) > ($1, $2)
   ORDER BY created_at, id LIMIT 20;

   Use LIMIT/OFFSET only for small, bounded result sets.