---
name: java-jdbc-performance
description: JDBC performance playbook: connection pool sanity, batching, fetch size, statement tuning, and measurement. Produces measurable changes + benchmark guidance.
license: CC-BY-4.0
compatibility:
  jdk: "17+"
  drivers: ["PostgreSQL JDBC", "MySQL Connector/J", "SQL Server JDBC", "Oracle JDBC"]
  pools: ["HikariCP"]
metadata:
  owner: "backend-platform"
  version: "1.0"
  tags: ["java", "jdbc", "performance", "hikaricp", "batching", "fetch-size", "pooling"]
---

# java-jdbc-performance

## Intent

Improve database interaction performance by reducing:

- network round-trips,
- per-statement overhead,
- connection churn,
- inefficient result fetching,
while ensuring changes are measurable and regression-safe.

## When to use

- Slow queries at the application layer (even when DB looks okay)
- High DB CPU due to too many small queries
- Thread pool saturation caused by DB waits
- Connection pool exhaustion or timeouts
- Large result sets causing memory spikes or long tail latency

## Scope

### In scope

- Connection pool configuration sanity (HikariCP)
- PreparedStatement usage, batching, fetch size
- Transaction scope and autocommit implications
- Read streaming patterns for large result sets
- Basic measurement and before/after benchmarking guidance

### Out of scope

- Schema/index/query-plan design (belongs to DB optimization + migrations skill)
- ORM-level fetch shaping (see java-jpa-transaction-patterns)

## Step 0 — Baseline measurement (no tuning without data)

Collect:

- p50/p95/p99 latency per endpoint
- DB time vs CPU time (if you have tracing/metrics)
- Connection pool metrics: active, idle, pending threads
- Query counts and average statement duration
- Error counters: timeouts, transient failures, deadlocks

Minimal reproducible benchmark:

- A single representative operation loop (N requests) with stable input data
- Warm-up phase + measurement phase
- Record:
  - throughput (ops/s)
  - latency distribution
  - CPU usage
  - GC metrics

## Step 1 — Connection pool sanity (HikariCP baseline)

Goals:

- avoid too-small pools causing queueing,
- avoid too-large pools causing DB overload,
- ensure lifecycle settings prevent stale connections.

Baseline rules:

- maxPoolSize should align with DB capacity and app concurrency (not “CPU cores * 2” blindly)
- set maxLifetime < DB idle timeout to avoid broken connections
- prefer explicit connectionTimeout to fail fast under saturation
- keep idleTimeout reasonable to avoid churn
- use leakDetectionThreshold only for debugging (it adds overhead)

## Step 2 — Reduce per-query overhead

### Use PreparedStatement for repeated queries

- ensures server-side plan caching (varies by DB)
- avoids SQL string concatenation and injection risks

### Avoid SELECT *

- fetch only needed columns
- reduces IO and object mapping overhead

### Use server-side pagination for lists

- LIMIT/OFFSET (or keyset pagination for deep paging)
- never pull massive lists into memory

## Step 3 — Batching writes (massive throughput gains)

Use batching for:

- insert/update of many rows
- bulk status updates
- event/outbox inserts

Guidelines:

- choose batch size (e.g., 100–1000) based on row width and DB
- wrap batch in explicit transaction (autocommit off)
- use addBatch/executeBatch
- handle partial failures and retry carefully (idempotency keys where needed)

Example skeleton (JDBC):

- conn.setAutoCommit(false)
- PreparedStatement ps = conn.prepareStatement(...)
- loop:
    ps.addBatch()
    if (i % batchSize == 0) ps.executeBatch()
- ps.executeBatch()
- conn.commit()

## Step 4 — Fetch size and streaming reads

Large result sets:

- default behavior often fetches too much into memory
- configure fetch size and use forward-only ResultSet if supported

Guidelines:

- setFetchSize(N) on Statement/PreparedStatement
- for streaming:
  - use forward-only, read-only ResultSet
  - keep transaction open only as needed
  - avoid mapping entire result set into a list; process row-by-row

Important:

- some drivers require autocommit=false for cursor-based fetching
- verify driver-specific behavior before assuming streaming

## Step 5 — Transaction and autocommit semantics

- Autocommit=true means each statement is its own transaction
  - adds commit overhead
  - may defeat cursor streaming in some drivers
- For batch writes and streaming reads:
  - set autocommit=false
  - commit explicitly

## Step 6 — Network and timeouts

- set query timeout where appropriate
- ensure socket/read timeouts are configured at driver or datasource level
- treat timeouts as signals: don’t just increase them; reduce load and improve queries

## Verification and safety checks

- Validate correctness:
  - row counts
  - idempotency for retries
- Validate resource usage:
  - memory remains stable in streaming operations
  - connection pool does not get pinned by long transactions
- Validate observability:
  - metrics show reduced query counts or reduced DB time

## Output artifacts

- A short “Performance Change Log”:
  - baseline metrics
  - changes applied (pool/batch/fetch)
  - after metrics
- Code diffs: datasource config + JDBC call sites
- A benchmark snippet or reproducible load script

## Definition of Done (DoD)

- [ ] Baseline and after numbers recorded (latency + throughput + pool metrics)
- [ ] Connection pool settings reviewed against DB timeouts
- [ ] Batching enabled where appropriate and tested
- [ ] Fetch size/streaming approach validated with driver behavior
- [ ] Regression tests added for correctness (especially around batching)
- [ ] Rollback path defined (config toggles / feature flags)

## Guardrails (What NOT to do)

- Do not increase pool size blindly; you can DDoS your own DB
- Do not batch with autocommit=true and expect real gains
- Do not stream huge results while also doing slow per-row processing in the same transaction
- Do not “optimize” without recording baseline and after metrics

## Cursor usage (recommended)

Attach:

- DataSource config (Hikari)
- JDBC call sites (DAO/repository)
- Production symptoms: pool metrics, timeouts, p95/p99 graphs
Prompt snippet:
“Use java-jdbc-performance. Propose measurable improvements (pool + batching + fetch size). Provide a micro-benchmark plan and regression tests.”