SQL Optimization with Claude Code: Practical Workflow Guide

What SQL Optimization Really Means

SQL optimization means adjusting queries, indexes, and data access patterns so the same result is returned with fewer reads and less waiting time. It is not just making a query look clever. The practical work spans the application endpoint, ORM calls, execution plans, production-like data volume, regression tests, and rollout monitoring.

Claude Code is useful because it can inspect more than a single SQL string. Ask it to read the route handler, Prisma or ORM model, migrations, slow-query logs, and tests together. In real systems, the painful latency often comes from repeated small mistakes: SELECT * on list pages, N+1 loops, deep OFFSET pagination, mismatched composite indexes, and dashboard aggregates that run synchronously on every page view.

This guide focuses on PostgreSQL while noting MySQL where relevant. Keep the official docs open when reviewing plans: PostgreSQL Using EXPLAIN, PostgreSQL EXPLAIN, PostgreSQL Indexes, PostgreSQL Multicolumn Indexes, and MySQL 8.4 Using EXPLAIN.

For adjacent work, see database design, database migrations, Prisma ORM, and performance optimization.

Three Use Cases to Start From

Start with the screen or workflow. It makes Claude Code’s task precise and prevents random index suggestions.

Use case	Symptom	Main fix	Risk
Admin list pages	Search and page 2+ are slow	composite indexes, keyset pagination, fewer selected columns	too many optional filters can create index sprawl
SaaS or ecommerce user pages	list APIs slow down as traffic grows	indexes matching WHERE and ORDER BY, caching, async aggregates	dropping stock, tenant, or permission filters is dangerous
Dashboards	monthly or daily charts scan too much data	summary tables, materialized views, scheduled refresh	stale numbers if freshness requirements are unclear

Set a query budget before editing. For example: p95 under 300 ms, no more than five SQL statements per list request, and response JSON must remain compatible.

Prompt for Claude Code:
Optimize this list API for SQL performance.
Goal: p95 under 300 ms, max 5 SQL statements, unchanged response JSON.
Review: src/routes/admin/orders.ts, prisma/schema.prisma, migrations, slow-query.log.
Do: identify slow SQL, read EXPLAIN ANALYZE, propose indexes, remove N+1, add regression tests.
Constraint: keep the change scoped and production compatible.

Step 1: Capture Slow Queries

Do not design indexes from memory. Capture the slow SQL first. PostgreSQL users can start with pg_stat_statements or database logs. Application-level ORM logs are also useful because they connect the SQL to the endpoint that generated it.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  query,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  rows
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

In Prisma, you can log slow queries in development with real Prisma Client APIs:

import { PrismaClient } from "@prisma/client";

export const prisma = new PrismaClient({
  log: [
    { emit: "event", level: "query" },
    { emit: "stdout", level: "error" },
    { emit: "stdout", level: "warn" },
  ],
});

prisma.$on("query", (event) => {
  if (event.duration > 100) {
    console.log({
      durationMs: event.duration,
      query: event.query,
      params: event.params,
    });
  }
});

Give Claude Code a sanitized log and ask it to group similar SQL by screen, caller, mean time, call count, and suspected cause. Mask emails, tokens, and customer identifiers before pasting logs.

Step 2: Read EXPLAIN and EXPLAIN ANALYZE

After identifying a candidate query, run EXPLAIN to see the planned execution strategy. In a safe environment with representative data, use EXPLAIN (ANALYZE, BUFFERS). Remember that ANALYZE executes the statement, so do not run it casually on destructive UPDATE or DELETE statements.

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  o.id,
  o.created_at,
  o.status,
  c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= DATE '2026-01-01'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 50;

Do not treat every Seq Scan as a bug. A sequential scan can be correct for small tables or low-selectivity predicates. Look for the gap between estimated rows and actual rows, large sorts, excessive buffer reads, and surprising join order.

Prompt for Claude Code:
Review this EXPLAIN ANALYZE. Separate the answer into bottleneck, row-estimate mismatch, index candidates, and safe SQL rewrites. Do not change the result set.

Step 3: Design Composite Indexes

Indexes speed up reads but add write cost, storage, and migration risk. Design them from real queries: equality predicates first, then range predicates, then sort columns, then join needs.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_created_id
  ON orders (status, created_at DESC, id DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

In PostgreSQL, CREATE INDEX CONCURRENTLY reduces write blocking, but it cannot run inside a normal transaction block. Your migration tool may need a special option or a separate SQL migration. Always include the rollout note in the PR.

Before asking Claude Code for index suggestions, provide existing indexes:

SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'orders'
ORDER BY indexname;

Step 4: Remove N+1 Queries

N+1 means you fetch a list, then fetch related rows one item at a time. It adds database work and network waiting.

// Bad: one customer query per order
const orders = await prisma.order.findMany({
  take: 100,
  orderBy: { createdAt: "desc" },
});

const rows = [];
for (const order of orders) {
  const customer = await prisma.customer.findUnique({
    where: { id: order.customerId },
  });
  rows.push({ ...order, customerName: customer?.name ?? "" });
}

// Better: fetch only the columns the UI needs
const orders = await prisma.order.findMany({
  take: 100,
  orderBy: { createdAt: "desc" },
  select: {
    id: true,
    createdAt: true,
    status: true,
    totalAmount: true,
    customer: {
      select: {
        id: true,
        name: true,
      },
    },
  },
});

Ask Claude Code to preserve the response shape while reducing query count. That forces it to read the UI or serializer instead of blindly adding include: true everywhere.

Step 5: Replace Deep OFFSET with Keyset Pagination

OFFSET 10000 LIMIT 50 usually gets slower because the database still has to walk through skipped rows. For chronological feeds or infinite scroll, use keyset pagination with the last seen created_at and id.

SELECT id, created_at, status, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

SELECT id, created_at, status, total_amount
FROM orders
WHERE status = 'paid'
  AND (created_at, id) < (TIMESTAMP '2026-05-01 10:30:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 50;

The (status, created_at DESC, id DESC) index supports this access pattern. Include id as a tie-breaker so ordering stays stable when many rows share the same timestamp.

Step 6: Move Aggregation Out of Hot Requests

Dashboards often become slow because every request recomputes the same totals. If the business can accept a delay, move the work into a summary table or scheduled refresh.

CREATE TABLE IF NOT EXISTS daily_sales_summary (
  sales_date date PRIMARY KEY,
  order_count integer NOT NULL,
  revenue numeric(12, 2) NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO daily_sales_summary (sales_date, order_count, revenue)
SELECT
  created_at::date AS sales_date,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid'
  AND created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY created_at::date
ON CONFLICT (sales_date) DO UPDATE
SET
  order_count = EXCLUDED.order_count,
  revenue = EXCLUDED.revenue,
  updated_at = now();

Tell Claude Code the acceptable freshness: one minute, five minutes, daily, or strictly real time. Without that requirement, an optimization can quietly change the meaning of a dashboard.

Query Budget and Regression Tests

Performance needs a guardrail. Exact execution-plan snapshots are often brittle, so start with query count, response compatibility, and a small set of representative cases.

import { PrismaClient } from "@prisma/client";
import { expect, it } from "vitest";

it("keeps the order list within the query budget", async () => {
  const queries: string[] = [];
  const prisma = new PrismaClient({
    log: [{ emit: "event", level: "query" }],
  });

  prisma.$on("query", (event) => {
    queries.push(event.query);
  });

  await prisma.order.findMany({
    take: 50,
    orderBy: [{ createdAt: "desc" }, { id: "desc" }],
    select: {
      id: true,
      createdAt: true,
      status: true,
      customer: { select: { id: true, name: true } },
    },
  });

  await prisma.$disconnect();
  expect(queries.length).toBeLessThanOrEqual(2);
});

Concept Map

flowchart LR
  A["Slow query log"] --> B["EXPLAIN / EXPLAIN ANALYZE"]
  B --> C["Index and SQL changes"]
  C --> D["N+1, pagination, aggregation fixes"]
  D --> E["Query budget tests"]
  E --> F["Rollout and monitoring"]

Pitfalls to Watch

• Reading large columns with SELECT * on list pages.
• Adding an index on a low-cardinality column such as status alone and expecting miracles.
• Trying to speed up LIKE '%keyword%' with a normal B-tree index.
• Judging plans on tiny development data.
• Running index migrations during peak traffic without checking locks, replica lag, and disk.
• Using ORM include options that return entire relation graphs.

Rollout Checklist

1. Identify the endpoint and user-facing screen.
2. Capture the slow SQL from logs or pg_stat_statements.
3. Save before/after EXPLAIN output.
4. Check existing indexes to avoid duplicates.
5. Review N+1, OFFSET, selected columns, and synchronous aggregation.
6. Test response compatibility.
7. Add query count or latency regression checks.
8. Document lock risk, runtime, and rollback for index changes.
9. Decide which metrics to watch after release.

SQL optimization is a workflow, not a one-line trick. Claude Code can keep the workflow coherent across code, migrations, and tests, but the final decision should still be grounded in real data, execution plans, and product requirements.

ClaudeCodeLab offers training and consultation based on this kind of practical review: bring the slow endpoint, logs, and schema, and we can turn them into a repeatable SQL optimization checklist for your team.