Database Performance & Administration

Recommends, orders, and prunes indexes for a specific query or table - composite column order, selectivity rules, partial and covering indexes, duplicate/unused cleanup, and write-amplification 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 to diagnose an unknown slow query from scratch - start with sql-query-optimizer; do NOT use when the query shape itself is the problem (function-wrapped predicates, leading wildcards, correlated subqueries) - use query-rewriter instead; do NOT use when the table is too large and needs partitioning or a time-series strategy - use partition-planner instead.

Rewrites a structurally inefficient SQL query into a faster equivalent that returns 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.

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.

Diagnoses connection pool exhaustion from captured evidence and sets correct pool sizes and timeouts across app pools and PgBouncer. Use when someone says "we keep hitting database connection limit errors under load", "my API times out waiting for a connection from the pool", "the database shows hundreds of idle connections", or sees "too many clients", checkout/acquire timeouts, "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.

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.

Diagnoses read-replica lag and the stale-read bugs it causes, then applies read-after-write consistency strategies to fix them. Use when someone says "users see stale data right after saving", "my read replica is lagging behind the primary", "data written a second ago is missing from the next request", when replica lag monitoring alerts or grows after a backfill, or when deciding which reads must hit the primary versus a replica.