irahardianto/agy-perf-optimization-demo/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:

```sql -- ✅ 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; ```

1. Window functions for ranking, running totals:

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

1. Explicit `JOIN` syntax — never implicit joins in `WHERE`.

1. 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 =)

1. Composite indexes — column order matters:

``sql -- 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) ``

1. Partial indexes for filtered queries:

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

1. Covering indexes to avoid heap fetches:

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

1. 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

1. Avoid `SELECT *` — list specific columns.

1. Keyset pagination over OFFSET for large datasets:

``sql -- 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.

Concurrency & Locking

1. Prevent deadlocks — consistent lock ordering:

``sql -- Acquire locks in PK order before updating SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; ``

1. SKIP LOCKED for queue processing:

``sql -- Workers skip locked rows instead of blocking (10x throughput) UPDATE jobs SET status = 'processing' WHERE id = ( SELECT id FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING *; ``

1. Advisory locks for application-level coordination:

``sql SELECT pg_advisory_xact_lock(hashtext('daily_report')); -- Released on COMMIT ``

1. `statement_timeout` — always set per-session to prevent runaway queries.

Data Operations

1. UPSERT — atomic insert-or-update (no race conditions):

``sql INSERT INTO settings (user_id, key, value) VALUES ($1, $2, $3) ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value, updated_at = now(); ``

1. Bulk loading — use `COPY` over batch INSERTs for large imports.

1. Batch inserts — multiple rows per statement, not one INSERT per row.

Diagnostics

1. `pg_stat_statements` — enable to identify top resource-consuming queries by total time and call frequency.

1. VACUUM/ANALYZE — run ANALYZE after large data changes. Tune autovacuum_vacuum_scale_factor for high-churn tables.

Advanced PostgreSQL

1. Full-text search: use tsvector + GIN index, not LIKE '%term%'.
2. JSONB indexing: GIN (jsonb_path_ops for @> only — 2-3x smaller), expression indexes for key lookups.

Naming

Follow conventions in @.agents/rules/database-design-principles.md § Schema (Naming).

Anti-Patterns

• ❌ Missing indexes on foreign keys
• ❌ N+1 queries (use JOIN or batch)
• ❌ String concatenation in queries (SQL injection risk)
• ❌ Storing comma-separated values in a single column
• ❌ OFFSET pagination on large datasets (use keyset)
• ❌ timestamp without timezone (use timestamptz)
• ❌ varchar(n) without reason (use text)
• ❌ Random UUID v4 as primary key on large tables (index fragmentation)
• ❌ Check-then-insert pattern (race condition — use UPSERT)

Related

• Database Design Principles @.agents/rules/database-design-principles.md
• Security Principles .agents/rules/security-principles.md
• Performance Optimization Principles @.agents/rules/performance-optimization-principles.md