p50 800 ms for a screen that shows 100 orders. The service has 100 orders per customer and 200,000 orders in total; orders.customer_id has no index (a classic - the foreign key was declared but the index was never created), and the entity mapping has a lazy items collection that the serialiser touches.

Three independent suspects, each testable on its own: (a) the number of statements per request (N+1), (b) the cost of each statement (missing index, bad plan), (c) waiting for a connection (pool or long transactions). We test one at a time so each gain is attributable.

In order of cheapness: count statements per request (Hibernate statistics or a datasource proxy), then ask the database what it spends time on (pg_stat_statements, Performance Insights / Database Insights), then read the plan of the single worst statement (EXPLAIN (ANALYZE, BUFFERS)), then check the pool (hikaricp.connections.pending).

Chapters 3 to 7 each find one cause, with real numbers. The order matters: the cheapest fix with the largest payoff first (usually: eliminate repeated work, then add the missing index, then size the pool).

One change per chapter: batch fetch, index, keyset pagination, batch writes, pool size.

Each chapter ends with a before/after table from the same harness. A final "Try it yourself" chapter contains the complete code.

One page view of an order history issues 101 statements (1 for the orders, 100 for the items of each). Log line from Hibernate statistics (spring.jpa.properties.hibernate.generate_statistics=true) prints at the end of each session something like "... nanoseconds spent executing 101 JDBC statements" - the number to watch is the statement count, not the time.

Cost = statements × (round trip + parse + execute). With a 0.5 ms same-AZ round trip, 101 statements cost at least 50 ms of pure network before any database work, so the two-statement version should be about 25x faster on the network share alone.

DbLab.java (chapter 10) seeds H2 with 2,000 customers, 200,000 orders and 600,000 order items, then runs the same request three ways via plain JDBC exactly as an ORM would issue the SQL. It adds a configurable busy-wait per statement to simulate network round trip (H2 is in-process so its real RTT is ~0):

Mechanism: the number of round trips is the lever.

With the 0.5 ms simulated round trip and the index in place: N+1 takes 51.8 ms; batching takes 1.8 ms (28x faster); a single join takes 0.84 ms (62x). Look at the in-process row: N+1 is only 2.6 ms there because the network is absent - that is exactly why this bug passes code review and local tests and then fails in AWS. The statement count is the stable diagnostic; wall-clock hides it.

Pick the fix by the access pattern. Each is one variable:

Measured on the in-process H2 harness; shape (statements per request) transfers, absolute values do not.

Note the first block of the output (no index): even the single join takes 12.25 ms there because the scan of orders dominates - the next chapter. The two problems compound, which is why you fix them one at a time and re-measure.

After fixing N+1, the first statement of the request (select ... from orders where customer_id = ?) is the slowest by far. Nobody created an index on the foreign-key column: PostgreSQL does not create indexes for foreign keys automatically (unlike primary keys / unique constraints), and neither does H2.

The statement does a sequential/table scan of the whole table. At 500,000 rows (the numbers grow linearly with table size) it costs tens of milliseconds per call, and CPU on the database instance rises with traffic.

Ask the database for its plan and its row-examination counts, then time the query. H2 prints the access path with EXPLAIN ANALYZE:

(In PostgreSQL the equivalent is EXPLAIN (ANALYZE, BUFFERS) in psql; with Spring you can find the statement from pg_stat_statements.)

tableScan and scanCount: 500001: the engine visited every row to return 250. The ratio of rows examined to rows returned (500,001 : 250 = 2,000 : 1) is the number to hate.

CONCURRENTLY (PostgreSQL) avoids blocking writes during the build at the cost of a slower build and a possible invalid index if it fails - check pg_index.indisvalid. With Flyway/Liquibase, run it outside the migration transaction.

H2 2.2.224 in-process, 500,000 rows, customer 777 has 250 rows.

Mechanism diagram; page layouts vary by engine.

Honest surprise from our run: we also created a three-column index (customer_id, created, id) for ORDER BY created DESC LIMIT 10. H2's planner kept using the single-column index, and the timing barely moved (0.494 ms vs 0.444 ms) because each customer has only 250 rows to sort. Composite indexes pay off when the sorted slice is large; never assume - read the plan.

After adding the index, one report query ("orders of customers in a region") is still slow, and it is not obvious why: the index exists.

The planner chose a sequential scan or a bad join order because its row estimates are wrong.

ANALYZE actually executes the statement (wrap writes in a transaction you roll back), so never run it on a destructive statement without care. Plain EXPLAIN shows only the estimate.

Illustrative diagram of the output above.

For this plan: create (created_at) (or a partial/composite index matching the filter) and refresh statistics. One change, re-run EXPLAIN (ANALYZE, BUFFERS), confirm the node became an Index Scan/Bitmap Heap Scan and Rows Removed by Filter fell to zero.

Compare Execution Time and Buffers before and after, on the same data and a warmed cache (run twice; the first run pays cold-cache reads). Record both in your pull request. We do not quote a speed-up here because no PostgreSQL ran in this lab.

"Page 5,000" of the order list takes seconds while page 1 is instant, and the nightly import of 480,000 orders runs for an hour.

OFFSET n forces the database to produce and discard the first n rows, so cost grows with depth. Row-by-row inserts pay per-statement and per-commit overhead 480,000 times.

DbLab2.java (chapter 10) times ORDER BY id LIMIT 20 OFFSET n against WHERE id > :last ORDER BY id LIMIT 20 at several depths, and inserts rows with and without batching/transactions.

OFFSET cost grows linearly with depth (0.17 ms at 1,000, 45 ms at 400,000: about 3,000x worse than the keyset query at the same position). Keyset stays flat because the primary-key index jumps straight to id > last.

Hibernate cannot batch inserts for entities whose id is GenerationType.IDENTITY (it needs the generated key after each insert); use a sequence with allocationSize > 1 for write-heavy tables. For very large imports use PostgreSQL COPY or a bulk loader instead of ORM entities.

H2 2.2.224, 500,000 rows, page size 20; averages over 20-2,000 repetitions.

In-process H2; real servers add round trips and WAL flushes per commit.

Under load, OrderService starts failing with a wall of exceptions and latency jumps from milliseconds to 30 seconds. The database CPU graph is only 40 %.

Two competing theories: the pool is too small (requests wait for connections), or it is too large and the real limit is the database. They need opposite fixes, so we measure with a model of a database that has a hard capacity.

PoolLab.java runs HikariCP 5.1.0 over H2 with 200 closed-loop clients. Each "query" holds a connection for 20 ms, taking one of 8 permits (8 DB cores) for that time, so the database can do at most 8 / 0.020 = 400 queries/s however many connections are open. We vary maximumPoolSize only.

Measured; closed loop with 200 clients; the database is simulated as 8 permits x 20 ms.

Throughput scales with the pool until the database saturates: pool 4 gives 187 q/s (theory 200), pool 8 gives 387 (theory 400). Beyond that, pool 16, 50 and 200 give 396, 371 and 375 - no gain. The extra connections just queue inside the database:

Averages over completed requests (see the caveat below on starvation).

Size the pool from the database, not from the incoming traffic. The HikariCP maintainers' guidance (from their "About Pool Sizing" article, based on PostgreSQL experience) is a small starting point, then measure:

Caveat from the run: p50 was about 20 ms in every configuration while p99 was 4-5 seconds. With 200 closed-loop clients competing for few connections some callers repeatedly lose the race (Hikari does not guarantee FIFO hand-off), so averages over completed requests understate the pain of the starved ones. In production this shows up as a long tail, not a slow median - exactly why you watch p99 and hikaricp.connections.acquire, not averages.

In production the default timeout is 30 seconds, so every request thread waits half a minute for a connection and then fails with SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms (Hikari 5 appends the pool counters in parentheses, as shown). All Tomcat workers pile up waiting, and the service looks dead although CPU is low - the earlier chapters' queue, relocated. Metrics to alert on (Micrometer, with Boot's Hikari binding): hikaricp.connections.pending > 0 for seconds, hikaricp.connections.acquire p99, hikaricp.connections.timeout (count), hikaricp.connections.active = max.

Arithmetic illustration with example durations.

A @Transactional method that calls the PaymentGateway holds a connection for the whole HTTP call. Combined with open-in-view the connection is held through response rendering too. Diagnose in the database: select state, wait_event_type, now() - xact_start, query from pg_stat_activity where state = 'idle in transaction' (a session that opened a transaction and is doing nothing = application code waiting on something else). Guard with idle_in_transaction_session_timeout, keep remote calls outside transactions (outbox pattern, see Transactions), and lock in a consistent order to avoid deadlocks (ERROR: deadlock detected). For hot rows prefer optimistic locking (@Version) or SELECT ... FOR UPDATE SKIP LOCKED for work queues.

Report endpoints and list screens (95 % reads) share the writer with order placement. During business hours the writer's CPU is 85 %, and a report run makes checkout slow.

Read traffic can move to replicas, if the data can tolerate replication lag, leaving the writer for transactions.

Before redesigning, quantify the read share and the lag tolerance: statements by type in pg_stat_statements, CloudWatch CPUUtilization, DatabaseConnections, ReadIOPS/WriteIOPS, and for replicas ReplicaLag (RDS) / AuroraReplicaLag (Aurora).

Illustrative.

RDS Performance Insights plots DB load as average active sessions (AAS) against the instance's vCPU count and breaks it down by wait event, SQL statement, user and host. Performance Insights is being superseded by CloudWatch Database Insights (check the current AWS documentation for modes, dates and availability); the reading skill is the same: AAS above vCPU means sessions are waiting; the dominant colour tells you whether to look at SQL (CPU), storage/indexes (IO), or transactions (Lock).

Sort by total time, not mean: a 0.9 ms statement called a million times beats one 200 ms report. That one ordering is how you separate "N+1" (calls huge, mean tiny) from "missing index" (mean big) at a glance. The extension must be in shared_preload_libraries; on RDS/Aurora enable it in the parameter group.

Route read-only transactions to a replica. In Spring, the usual pattern is a routing DataSource keyed on the current transaction's read-only flag:

Two cautions: the flag is set when the transaction starts (so LazyConnectionDataSourceProxy or equivalent is required), and replica lag means "read your own write" flows must use the writer. Other levers: add a cache (page 09) before adding replicas - it is cheaper per request.

Check writer CPU / AAS drop and reader load rise by the expected share, AuroraReplicaLag stays within your tolerance during the peak hour, and no endpoint returns stale data it must not. No numbers are claimed here; run it against your cluster.