sql-expert

SQL & Database Expert

Think in sets, not loops. The database is smarter than your application loop. When something is slow, read the plan (EXPLAIN ANALYZE) before guessing — the answer is almost always a missing index or a bad join.

When to Use

• Writing, debugging, or optimizing SQL.
• Schema design: tables, relationships, constraints, types, migrations.
• Slow queries, missing/unused indexes, N+1, lock contention.
• Transactions, isolation levels, and concurrency correctness.

When NOT to Use

• ORM/app wiring → nodejs-backend-expert / language skill.
• Vector/semantic retrieval → rag-expert.
• Data-platform / warehouse-scale architecture → software-architect.

Core Principles

1. Schema design

• Normalize to 3NF by default; denormalize only for a measured read win, and then keep it consistent (triggers/jobs).
• Right types: timestamptz (not naive timestamps), numeric/decimal for money (never float), uuid/bigint keys, native enum/boolean. Enforce integrity at the DB: NOT NULL, UNIQUE, FOREIGN KEY, CHECK — the app is not the only writer.

2. Query like a pro

• Select only needed columns (no SELECT * in app queries — it breaks covering indexes and over-fetches).
• Know your JOINs and beware fan-out: a one-to-many join multiplies rows and inflates SUM/COUNT. Pre-aggregate in a subquery/CTE.
• Kill N+1 patterns: one query with a JOIN or WHERE id IN (...) instead of a query per row.
• EXISTS over IN (subquery) for correlated existence checks; window functions for running totals/ranking instead of self-joins.

3. Indexing

• Index columns in WHERE, JOIN, and ORDER BY. Composite index order: equality columns first, then the range/sort column. The index serves a left-to-right prefix.
• A function/expression on the column (WHERE lower(email)=…, WHERE created_at::date=…) defeats a plain index — index the expression or rewrite as a sargable range.
• Indexes speed reads, slow writes, and use space — add deliberately. Drop unused ones. Use partial/covering indexes for hot queries.