Sql Patterns

SQL — policy & pitfalls

Baseline SQL knowledge (CTE, window functions, joins, DML, Flyway/Liquibase syntax) is assumed. This skill encodes policy and the traps that keep appearing in review — dialect-specific, blocking-behavior-specific, and optimizer-specific.

Setup Check (run first)

Before writing non-trivial SQL:

1. Dialect — identify the target (Postgres, MySQL, MariaDB, SQLite, SQL Server). Optimizer behavior, locking rules, and index features differ. Never assume Postgres semantics on MySQL or vice versa. 2. Migration tool — check db/migration/ (Flyway) or db/changelog/ (Liquibase). Both are immutable: already-applied migrations must NEVER be edited. 3. EXPLAIN access — if the DBHub MCP is configured, use it to run EXPLAIN (ANALYZE, BUFFERS) (Postgres) or EXPLAIN FORMAT=JSON (MySQL) against a representative dataset. Advice without a real query plan is a guess. 4. Table sizes — query advice differs by orders of magnitude between 10K and 100M rows. Use DBHub (or pg_stat_user_tables / information_schema.tables) to check sizes when choosing pagination, indexing, and migration strategy.

DBHub MCP

When the DBHub MCP server is available (configured in mcp/.mcp.json), use it actively:

• Schema inspection — list tables, columns, types, and indexes before writing queries or migrations.
• `EXPLAIN ANALYZE` — run against real data to verify index usage and row estimates before declaring a query optimized.
• Row counts / size estimates — query pg_stat_user_tables (Postgres) or information_schema.tables (MySQL) to determine pagination and migration strategy.
• Validate migrations — check current schema state before generating DDL to avoid duplicate columns or conflicting constraints.

Do not suggest schema changes or index additions without first confirming the current schema via DBHub or the codebase.

MUST DO

• List columns explicitly — never SELECT * in application code (breaks on schema change, pulls unused columns).
• `NOT EXISTS` / `LEFT JOIN ... IS NULL` instead of NOT IN when the subquery can return NULL (NOT IN with a single NULL returns zero rows — silently).
• Keyset pagination (WHERE id > :last ORDER BY id LIMIT n) for large / user-driven lists. OFFSET degrades as it grows.
• Parameterize every query — prepared statements / bound parameters. Never string-concat user input even with escaping.
• Index what you filter and join on — WHERE, JOIN ON, ORDER BY columns. Composite index order matters: leftmost columns usable, tail columns only with leading predicates.
• Transaction-scope migrations where possible — Flyway wraps single migration in a transaction by default; Postgres supports DDL in transactions, MySQL does NOT (each DDL auto-commits, a failed migration leaves partial state).
• `EXPLAIN ANALYZE` before declaring a query "optimized" — optimizer choice depends on stats, table size, and dialect.

MUST NOT DO

• No `NOT IN (SELECT ... )` on nullable columns. WHERE x NOT IN (NULL, 1, 2) returns empty. Use NOT EXISTS or LEFT JOIN ... IS NULL.
• No functions on indexed columns in `WHERE`. WHERE YEAR(created_at) = 2024 ignores the index — use WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'. Or create a functional / expression index.
• No `OR` across different columns in `WHERE` when one side isn't indexed — split into UNION ALL (or create a combined index).
• No `ALTER TABLE ... ADD COLUMN NOT NULL` without default on a large table — locks the table, rewrites every row. Split into: add nullable column → backfill in batches → add NOT NULL constraint.
• No `CREATE INDEX` on a large write-active table without CONCURRENTLY (Postgres) / ALGORITHM=INPLACE LOCK=NONE (MySQL 8.0) / WITH (ONLINE = ON) (SQL Server) — blocks writes for the duration.
• No editing applied migrations. Flyway checksums them; Liquibase hashes them. Changing a released V3__add_email.sql breaks every environment. Add a new migration.
• *No `SELECT COUNT() on large tables** for pagination UIs — on Postgres it's expensive; index-only scan is possible only when the visibility map is fully up-to-date (Postgres 9.2+, write-heavy tables rarely qualify). Use approximate counts (pg_class.reltuples`) or a "load more" cursor instead.
• No `DISTINCT` to fix duplicate rows — it masks a broken JOIN. Fix the join.
• No implicit type casts in joins (JOIN x ON x.id = y.id_str) — disables indexes, silently wrong if types differ. Match types.
• No credentials, PII, or secrets in migration files — they go to version control forever.

Reference Guide

Output Format

When producing SQL:

1. Short plan (1–3 bullets) — what the query / migration does and which tables / indexes it touches. 2. The SQL, dialect-qualified when dialect-specific (-- Postgres only). 3. If modifying schema on a large table, describe the locking / backfill plan (not just the DDL). 4. For non-trivial queries — include the expected plan shape (index scan vs seq scan) and the index it relies on. If no such index exists, flag that as part of the change.

When reviewing SQL: call out MUST-DO / MUST-NOT violations, point out NULL / type / locking traps, and suggest the minimal fix. Prefer EXPLAIN-verified advice over cargo-cult rules.

Migrations — Flyway & Liquibase policy + pitfalls

Both tools enforce immutability: once a version is applied in any environment, editing it breaks every other environment. New changes → new migration. Everything below is the policy and the traps that bite in production.

Immutability rules (both tools)

• Never edit an applied migration. Flyway stores SHA256 in flyway_schema_history.checksum; Liquibase stores MD5 in DATABASECHANGELOG.MD5SUM. Editing fails validation on every subsequent deploy.
• Never delete an applied migration file — MissingMigration / CHECKSUM MISMATCH on startup.
• Repair only as a last resort. flyway repair / UPDATE DATABASECHANGELOG SET MD5SUM = ... are escape hatches for broken environments, not a workflow. Every use is a postmortem.
• Fix-forward. If V5 has a bug, write V6 that corrects it. Don't revert V5.

Flyway naming & layout

• V{version}__{description}.sql — versioned, runs once, ordered.
• R__{description}.sql — repeatable, runs whenever checksum changes (views, stored procedures, functions).
• U{version}__{description}.sql — undo (Flyway Teams only); not available in OSS.
• Versions: V1, V1_1, V1.1, V2024_01_15_1200 — all valid, but pick one style per project and stick with it. Timestamps scale better than integers in teams.

Liquibase specifics

• Changelogs are YAML/XML/JSON with changeSet id + author as the unique key. id uniqueness is per-file, not global — two files can both have id: 001.
• <rollback> / rollback: block is required for non-trivial changes if you want liquibase rollback to work — automatic rollback only exists for DDL Liquibase itself generates.
• runOnChange: true → re-runs on checksum change (like Flyway R__). Use for views / stored procs.
• contexts: prod,stage — conditional execution. Abuse leads to environment-specific drift.

Safe schema changes — the expand / contract pattern

Any change that a running application cannot tolerate must be split into phases. Applies to rolling deployments (most prod systems).

Rename a column

-- Phase 1 (expand) — both names exist
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
UPDATE users SET email_address = email WHERE email_address IS NULL;
-- Trigger to keep both in sync while old code still writes `email`
CREATE TRIGGER sync_email ...

Deploy app reading/writing email_address. Remove reads of email. Then:

-- Phase 2 (contract)
DROP TRIGGER sync_email;
ALTER TABLE users DROP COLUMN email;

Never rename in place with active traffic — the old app sees a missing column.

Add a NOT NULL column to a large table

-- ❌ locks the table, rewrites every row
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

Postgres 11+ made ADD COLUMN ... DEFAULT ... NOT NULL fast (no rewrite), but only for non-volatile defaults. For user-provided / computed defaults:

-- ✅ Phase 1: nullable
ALTER TABLE orders ADD COLUMN status VARCHAR(20);

-- ✅ Phase 2: backfill in batches (application-side, or chunked UPDATE in a separate migration)
UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
-- ... repeat

-- ✅ Phase 3: add a CHECK constraint NOT VALID (no table scan), then validate without a full lock
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;   -- acquires ShareUpdateExclusiveLock, not AccessExclusiveLock
-- After VALIDATE, SET NOT NULL is a metadata-only operation (Postgres 12+: planner knows no nulls exist)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;       -- cleanup; optional

Drop a column

Phase 1 (deploy): stop reading / writing the column from application code. Phase 2 (migration): ALTER TABLE ... DROP COLUMN .... If you drop first, old instances during rolling deploy will crash.

Add an index on a large table

-- Postgres
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);  -- non-blocking; cannot run in a transaction
-- If it fails, the index is left in INVALID state — DROP it and retry

-- MySQL 8.0 (default is ONLINE for most algorithms)
CREATE INDEX idx_orders_status ON orders(status) ALGORITHM=INPLACE LOCK=NONE;

-- SQL Server
CREATE INDEX idx_orders_status ON orders(status) WITH (ONLINE = ON);

Flyway: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Two options — (1) place it in a separate migration file and set flyway.executeInTransaction=false in flyway.conf or via a .sql.conf sidecar file (executeInTransaction=false); (2) use a V__*.sql file with only the CREATE INDEX CONCURRENTLY and mark it in the Flyway config. Note: there is no -- flyway.nonTransactional=true comment syntax.

Rename / drop a table

Same expand-contract — route via a view first, let all consumers migrate, then drop.

Baseline on an existing database

• Flyway: flyway baseline -baselineVersion=1 -baselineDescription="existing schema" creates a row in history and starts tracking from V2+.
• Liquibase: liquibase changelog-sync or generate-changelog to import existing schema.
• Never run unbaselined migrations against a populated DB — order will be wrong; objects already exist; errors cascade.

Transaction behavior by dialect

• Postgres: DDL is transactional. A failed migration rolls back cleanly (except CREATE INDEX CONCURRENTLY, VACUUM, ALTER TYPE ... ADD VALUE — these can't be in a transaction).
• MySQL / MariaDB: DDL auto-commits. A migration with 5 DDLs that fails on #4 leaves the DB in a half-migrated state. Split each DDL into its own migration OR wrap with idempotency (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS MySQL 8.0.29+).
• SQLite: DDL is transactional.
• SQL Server: DDL is transactional; some operations lock more than others.

Idempotency defensive patterns

• CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS — available on Postgres, MySQL, SQLite. ADD COLUMN IF NOT EXISTS — Postgres only (ALTER TABLE t ADD COLUMN IF NOT EXISTS); MySQL has no such syntax — use a conditional check via information_schema or just rely on Flyway's checksum tracking. Helpful for repairs and re-applies.
• For types/columns without IF NOT EXISTS, wrap in a DO $$ ... EXCEPTION ... END $$ (Postgres) or a conditional in information_schema.
• Don't over-idempotent — Flyway/Liquibase already track state. Use idempotency only where DDL isn't transactional (MySQL).

Data migrations (vs schema migrations)

• Put data backfills in separate migration files from DDL. Easier to retry, easier to batch.
• Chunk large UPDATEs (10K–100K rows per transaction) to avoid long locks and WAL blowup.
• For Postgres bulk updates, consider UPDATE ... WHERE ctid = ANY(...) with batched ctids or a LIMIT ... FOR UPDATE SKIP LOCKED loop. A single UPDATE table SET x = ... on 100M rows will block writes for minutes.
• Don't put data migrations that take hours inside the app boot path — run via job, not Flyway on startup.

Rollback realism

• Automatic rollback is rare outside Liquibase (which requires you to write <rollback>).
• Data loss is irreversible. DROP COLUMN rollback can restore the schema but not the data (unless you snapshot first).
• Prefer "add, don't remove" during the change; clean up in a later release once safe.

Migration review checklist

• [ ] Migration filename follows project convention (version / timestamp).
• [ ] Already-applied migrations are not edited.
• [ ] Locking impact stated for any DDL on a table > 100K rows.
• [ ] CONCURRENTLY / ONLINE used for index creation on write-active tables.
• [ ] NOT NULL / NOT VALID split for constraints on large tables.
• [ ] Expand-contract phases explicit for renames / drops with live traffic.
• [ ] No secrets, no PII in the file.
• [ ] Idempotent where dialect requires (MySQL).
• [ ] Backfill data migrations separate from DDL migrations.

Query performance — diagnosis, indexing, optimizer traps

Everything here assumes you can run EXPLAIN on the target DB with a representative dataset. Advice without a plan is a guess.

Read the plan, don't guess

• Postgres: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>. ANALYZE actually executes; guard with a transaction + rollback for destructive queries.
• MySQL: EXPLAIN FORMAT=JSON <query>, or EXPLAIN ANALYZE (MySQL 8.0.18+).
• SQL Server: SET STATISTICS IO ON; SET STATISTICS TIME ON; + "Include Actual Execution Plan".
• SQLite: EXPLAIN QUERY PLAN <query>.

Look for:

• Seq Scan / Table Scan on a large table filtered by an indexable predicate → missing or unusable index.
• Rows Removed by Filter high → index exists but isn't selective enough; composite index may help.
• Nested loop with large outer → hash join would be better; check stats (ANALYZE in Postgres, ANALYZE TABLE in MySQL).
• Sort spilling to disk (Sort Method: external merge) → increase work_mem temporarily, or add an index that matches the ORDER BY.
• Index Scan reading many rows → filtering happens after the index, consider covering / composite index.

When indexes don't help

• Function on column: WHERE LOWER(email) = ? → can't use idx(email). Fix: functional index CREATE INDEX ON users (LOWER(email)), or store lowercased column.
• Implicit type cast: WHERE id = '123' when id is INT (MySQL tolerates; Postgres warns). Disables the index. Match types.
• Leading wildcard LIKE: LIKE '%foo' → no B-tree usage. Use trigram / full-text index (Postgres: pg_trgm; MySQL: FULLTEXT).
• OR across columns: WHERE a = 1 OR b = 2 can't use indexes on both. Use UNION ALL of two equality lookups — but if a row can satisfy both predicates you'll get duplicates; either ensure predicates are mutually exclusive (e.g. add AND NOT (a = 1) to the second branch), or use UNION (which dedups but costs a sort/hash).
• Negation: WHERE status != 'done' → usually seq-scan unless status has few values and the DB uses a bitmap scan.
• Low selectivity: index on is_active when 95% of rows are active → planner ignores the index. Use a partial index keyed on the minority case: CREATE INDEX idx ON orders(created_at) WHERE status = 'PENDING'.
• Correlated subquery with `IN` vs `EXISTS`: usually the same on modern optimizers, but EXISTS is safer for nullable columns (see MUST NOT).

Composite index rules

• Leftmost prefix rule: idx(a, b, c) serves WHERE a = ?, WHERE a = ? AND b = ?, WHERE a = ? AND b = ? AND c = ?. NOT WHERE b = ?.
• Equality before range: index (status, created_at) for WHERE status = ? AND created_at > ?.
• Include ORDER BY columns at the tail to let the index satisfy ordering (Index Scan without a sort).
• Covering index (Postgres 11+ INCLUDE, MySQL secondary indexes with extra columns): CREATE INDEX idx ON orders(user_id) INCLUDE (status, total) — avoids heap lookup for the selected columns.
• Don't add an index "just in case". Every index slows writes and consumes buffer cache.

Pagination

• Keyset (cursor) pagination — O(log n), stays fast forever:

  SELECT id, name FROM products
  WHERE id > :last_id
  ORDER BY id
  LIMIT 20;

Requires a stable sort key. For composite sorts: WHERE (created_at, id) > (:last_ts, :last_id).

• OFFSET pagination — O(n + offset), degrades linearly. Fine for admin UI with small offsets; lethal for LIMIT 20 OFFSET 100000.
• *`COUNT()`** for total on huge tables: use estimates.
• Postgres: SELECT reltuples::bigint FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = ? AND n.nspname = 'public' (stats-based, stale by default; filter by schema to avoid ambiguity when the same table name exists in multiple schemas).
• MySQL: information_schema.TABLES.TABLE_ROWS (InnoDB estimate).

Joins

• Prefer explicit INNER JOIN / LEFT JOIN. Comma joins (FROM a, b WHERE ...) work but obscure intent and are error-prone.
• Driver / outer table should be the smaller one for nested loop joins. Hash joins don't care. Optimizer usually gets this right when stats are current.
• LEFT JOIN + WHERE right_table.col = ? accidentally turns it into INNER JOIN (because NULL can't equal). Put the predicate in the ON clause to preserve the outer join.
• Too many joins (5+) → consider materialized views, pre-aggregation, or denormalization.
• `DISTINCT` after a JOIN is almost always a bug — it means the join produced duplicates. Fix the join (aggregate, or join on a unique side).

N+1 and batching

• Application-level N+1 is the #1 performance killer. Signs: many identical queries differing only in parameter.
• Fix from the app side (prefetch / join fetch / data loader), not SQL — but SQL can help with WHERE id = ANY(:ids) / WHERE (a, b) IN ((..,..), (..,..)).
• Beware of chunking: WHERE id IN (1..100000) creates a 100K-element IN list, some DBs choke. Cap at a few thousand per batch.

Updates & deletes

• Large UPDATE / DELETE hold row locks and inflate WAL / redo log. Batch via LIMIT (MySQL) or ctid/RETURNING-loops (Postgres).
• UPDATE orders SET status = 'done' WHERE id IN (SELECT id FROM orders WHERE ... LIMIT 1000 FOR UPDATE SKIP LOCKED) — the canonical Postgres chunked update pattern.
• DELETE on a huge range → consider partitioning the table so old data drops via DROP TABLE partition in O(1).

Stats & autovacuum

• Optimizer uses stats; stale stats → bad plans. After bulk load: ANALYZE <table> (Postgres), ANALYZE TABLE <t> (MySQL).
• Postgres: autovacuum handles most cases; tune autovacuum_vacuum_scale_factor for large append-only tables (default 0.2 × table-size is too lazy).
• Bloat (dead tuples) is a Postgres concern — monitor with pgstattuple or pg_stat_user_tables. Heavy UPDATE/DELETE workloads need tighter autovacuum.

Useful Postgres diagnostics

• pg_stat_statements — query normalization + counts + total time. Enable it; it's the single most useful extension.
• pg_stat_activity — current queries; filter by state = 'active' AND now() - query_start > '1 min'::interval for long-runners.
• pg_locks + pg_stat_activity — find blockers.
• EXPLAIN (ANALYZE, BUFFERS) — BUFFERS shows cache hits; a plan with millions of buffer reads is I/O-bound.

Useful MySQL diagnostics

• performance_schema.events_statements_summary_by_digest — normalized query stats (sum_timer_wait, sum_rows_examined).
• SHOW ENGINE INNODB STATUS — deadlocks, current transactions, lock waits.
• SELECT * FROM sys.schema_unused_indexes — indexes eating writes for no reads.

Don't optimize prematurely

• Measure first. "This might be slow" is a hypothesis, not evidence.
• EXPLAIN and table sizes beat intuition. A seq scan on 500 rows is fine.
• Optimizations that don't survive review: "add hint", "force index", "change to subquery I think is faster". Benchmark before and after.

Review checklist

• [ ] Plan attached (at least the relevant lines of EXPLAIN).
• [ ] Index the query relies on exists AND is used in the plan.
• [ ] No SELECT * outside ad-hoc queries.
• [ ] No function on indexed column in predicates.
• [ ] OFFSET replaced by keyset for user-facing lists over ~10K rows.
• [ ] COUNT(*) replaced by approximation / cursor where feasible.
• [ ] Joins don't produce duplicates (no DISTINCT band-aid).
• [ ] Bulk UPDATE/DELETE batched.
• [ ] New index justified by a query plan, not "feels right".