database-performance

Diagnoses connection pool exhaustion from captured evidence and sets correct pool sizes and timeouts across app pools and PgBouncer. Use when you see "too many clients"/connection-limit errors, pool checkout/acquire timeouts, idle-connection or "idle in transaction" pile-up, or serverless functions storming the database on a traffic spike. Do NOT use when the problem is an unreliable external service (third-party API, downstream microservice) needing retries or fallbacks — use circuit-breaker-builder instead.

Read a captured EXPLAIN ANALYZE plan, name the single bottleneck node, and prescribe a targeted fix for it. Use when a specific query is slow and you have (or can capture) its execution plan from Postgres or MySQL and need to know WHICH node is burning the time and why.

Recommends, orders, and prunes indexes for a specific query or table — composite column order, partial and covering indexes, duplicate/unused cleanup, and write-cost tradeoffs. Use when a query is slow and EXPLAIN shows a Seq Scan or a sort, before adding a CREATE INDEX, or when auditing a table's index set for bloat or duplicates. Do NOT use when the table is too large and needs partitioning or a time-series strategy — use partition-planner instead; do NOT use when the query shape itself is the problem (function-wrapped predicates, leading wildcards, correlated subqueries) — use query-rewriter instead.

Rewrite a schema migration into independently deployable, zero-downtime steps that never take a long-held blocking lock on a live table. Use when you are about to run an ALTER TABLE, CREATE INDEX, add a constraint, rename a column, or change a column type against a production database with live traffic. Do NOT use for greenfield table/column design with no rows yet — use database-schema instead; do NOT use for broad correctness/integrity review of a migration — use review-db instead.

Detect ORM loop-of-queries (N+1) patterns from query logs and eliminate them with batched eager loading, keeping the N+1s that are cheap. Use when a list endpoint is slow, a request issues many near-identical SELECTs differing only by an id, query logs show repeated SELECTs in a loop, or a tool like the bullet gem flags an N+1 in ActiveRecord, Prisma, SQLAlchemy, or Hibernate.

Produces a partitioning or sharding plan for an oversized table — range/list/hash strategy, partition-key choice, composite-key and pruning constraints, and a verdict on whether to partition at all. Use when one table has grown past tens of millions of rows or hundreds of GB, vacuum/autovacuum on it runs for hours, retention deletes are giant DELETEs, or you are deciding between range/list/hash partitioning or sharding a hot table. Do NOT use when the goal is choosing indexes for specific slow queries — use index-advisor instead.

Rewrites a structurally inefficient SQL query into a faster equivalent that returns byte-identical results — correlated subqueries to joins or LATERAL, accidental cross joins to explicit ON predicates, OR-across-columns to UNION, SELECT * to projected columns, and deep OFFSET pagination to keyset. Use when a query is slow because of its shape rather than its indexes — EXPLAIN shows a Cartesian blowup, a per-row subquery, or OFFSET discarding tens of thousands of rows — and the indexes are already in place. Do NOT use when the fix is adding or reordering an index — use index-advisor instead; do NOT use to diagnose an unknown slow query from its plan — use sql-query-optimizer instead.

Diagnoses read-replica lag and the stale-read bugs it causes, then applies read-after-write consistency strategies to fix them. Use when a read returns data older than a just-committed write ("the row I just saved disappeared"), when replica lag monitoring alerts or grows after a backfill, or when deciding which reads must hit the primary versus a replica.