Database Performance

Database Performance

Purpose

Turn “the database is slow” into a bounded symptom, a short set of competing mechanisms, and a handoff to the skill that owns the decision. Database incidents cross layers: one endpoint can combine an ORM statement explosion, pool waiting, a stale plan, lock contention, and engine maintenance debt.

Leave this skill once the owner is known.

Triage contract

Start with the symptom, time window and known environment; collect only the signals needed to choose the next owner. The packet below is a menu, not a prerequisite to handoff. For greenfield selection, route from requirements and workload evidence without inventing existing engine metrics.

business symptom, SLO impact, and exact time window:
engine, exact version/edition/service tier, topology, and recent changes:
operation/query/job and representative parameters/data distribution:
offered/completed rate, concurrency, errors/timeouts, and transaction p50/p99:
application statement count, pool acquire/usage/pending, and connection count:
database CPU, I/O, waits/locks, active sessions, log/WAL/redo, and replica lag:
plan identity plus estimated/actual rows, loops, reads/buffers, spills, and cache state:
affected cohort and comparable healthy control:
evidence gaps, collection risk, rollback window, and success measure:

Do not infer an engine mechanism from an application symptom. Align clocks and workload before correlating layers. Identify metric boundaries: pool usage is connection checkout-to-return, not SQL execution, and cumulative engine counters need interval deltas with resets accounted for. When JVM instrumentation or configuration is involved, inspect compiler/runtime, resolved driver, pool and ORM versions and transaction ownership; this router imposes no Java baseline or upgrade.

Route by established question

Separating questions

• If pool acquire time is high, compare arrival rate, active/idle/pending connections, connection creation failures and hold-time distribution. Stable completed-borrow usage does not exclude long active borrows or leaks that have not returned. Route pool admission to connection-pool-sizing and follow evidence of long holds into the owning transaction/statement; do not enlarge the pool from acquire latency alone.
• If statement count scales with rows, attribute repeated calls to ORM loading, handwritten loops, retries or intended per-row work. Route confirmed ORM amplification to orm-fetch-and-batching-performance; route repeated ingestion to database-bulk-loading. Reduce avoidable call amplification before optimizing every plan, while keeping per-call cost as a possible coexisting problem.
• If one statement dominates, seek its observed plan and representative parameters/data before proposing an index. An estimated plan is not an executed plan. Prefer existing traces or plan history; EXPLAIN ANALYZE executes the statement and adds instrumentation overhead. Replays need bounded duration/load and assessed side effects; rollback does not remove incurred load or every possible external/nontransactional effect. If safe runtime evidence is unavailable, hand off the estimate with that limitation instead of forcing a production replay.
• If the plan is stable but elapsed time moves, compare locks, waits, I/O/cache state, log pressure, and replica topology in the same interval.
• If maintenance “succeeded,” verify its observable effect. VACUUM can remove nothing, an online DDL can wait on a metadata lock, and an index rebuild can appear to help only because it refreshed statistics.
• If the request is “which database is best,” route to requirements, vetoes, and workload proof; product ranking is not an engineering decision.

Evidence discipline

For any recommendation that changes production state, separate:

Evidence: command/metric/plan and its window/provenance
Observation: direct reading of that evidence
Inference: mechanism that best explains it, plus alternatives
Intervention: smallest reversible change and predicted signal
Validation: same workload/evidence, guardrails, and rollback trigger
Confidence: HIGH/MEDIUM/LOW with reason

When evidence is unavailable, state the gap and what would discriminate the hypotheses. Never turn a vendor default, a folklore threshold, or a lab result into a production prescription.

Definition of done

• Relevant symptom/workload, engine/version, cohort and time window are explicit or marked unknown/not applicable; missing fields do not block an otherwise clear handoff.
• Application, pool, statement, engine, and host signals are not mixed without aligned evidence.
• At least one plausible alternative survives until a discriminating signal is checked.
• The confirmed question is handed to one primary owner, with adjacent skills only when needed.
• The handoff includes the evidence, leading hypothesis, plausible alternative and next discriminating check; routing to an owner is not proof of the root cause.
• Any proposed intervention predicts a measurable effect and has guardrail and rollback criteria. A routing-only answer can stop at the owner and the evidence it needs.

Sources for collection boundaries

• PostgreSQL 18 EXPLAIN — ANALYZE executes the statement, adds overhead and excludes client network transfer costs.
• MySQL 8.4 EXPLAIN — distinguish estimates from actual execution analysis for the deployed version.
• HikariCP configuration — acquisition timeout, pool limits and connection lifecycle; verify the installed version.