Evidence-based optimizations

By the end of this chapter you have worked through five optimizations the way they should always be worked: a measured original condition, a falsifiable hypothesis, one change, an expected metric movement, a re-test, and the trade-offs written down. None of the changes is presented as universally correct — each is a response to a specific signature, and the section headers say which.

One rule before the first change: horizontal scaling cannot fix a database-bound design. If the evidence points at PostgreSQL — a bad plan, lock contention, a query doing N+1 round trips — adding API replicas multiplies the load on the bottleneck, not the capacity. Each optimization below either removes work from the bottleneck or bounds it.

1. Add the index the query actually needs

Original condition. The SearchSimulation runs clean at 15 req/s … until the traffic mix starts asking for recent orders of hot customers (?customerId=…&sort=createdAt,desc at deep pages). EXPLAIN shows the planner using idx_orders_customer_status and then sorting thousands of matching rows: a Sort node over a large Bitmap Heap Scan.

Hypothesis. The index supports the filter but not the ordering; extending it to (customer_id, status, created_at DESC) lets the index return rows already in order, eliminating the sort and the heap re-read.

The change. db/init/03-indexes.sql (or a migration in a real project):

1
CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
2
    ON orders (customer_id, status, created_at DESC);

(CONCURRENTLY because on any live table a plain CREATE INDEX takes a write lock — under load it is itself an incident. It cannot run inside a transaction; run it standalone.)

Expected metric change. Search p99 drops; pg_stat_statements mean_exec_time for the search query drops; EXPLAIN shows Index Scan with no Sort node. Total write cost rises slightly — every insert updates one more index.

Re-test. Same SearchSimulation, same profile, template-reset database. Compare p99 at the same load level only.

Risks/trade-offs. Indexes are not free: write amplification, storage, and planner complexity all rise. Column order matters — customer_id first because equality leads; created_at last because it feeds the sort. An index built for a query that turns out rare is dead weight you pay for on every write.

2. Fix an N+1 you can watch appear

Original condition. Suppose findByIdWithItems had never been written — the service calls findById, maps order.getItems() inside a read-only transaction… or worse, with open-in-view: true, during serialisation. Either way the cost shape is the same: fetch order (1 query) then fetch items per order (N queries). On the search endpoint returning entities instead of summaries, a 20-row page issues 21 queries.

Hypothesis. Query count per request is the bottleneck multiplier — it multiplies HikariCP hold time, and the pool, not the database, saturates first.

The change. Two legitimate fixes, chosen per endpoint:

1
// fetch-by-id: entity graph — items in one join fetch
2
@EntityGraph(attributePaths = "items")
3
@Query("select o from Order o where o.id = :id")
4
Optional<Order> findByIdWithItems(@Param("id") Long id);
5

6
// search: projection — never load entities or items at all
7
@Query("""
8
    select new in.o612.eng.orders.web.OrderSummary(
9
        o.id, o.customerId, o.status, o.totalAmount, o.createdAt)
10
    from Order o
11
    where (:customerId is null or o.customerId = :customerId)
12
      and (:status is null or o.status = :status)
13
    """)
14
Page<OrderSummary> search(@Param("customerId") Long customerId,
15
                          @Param("status") OrderStatus status,
16
                          Pageable pageable);

(These are the lines chapter 04 already shipped — which is the point of this entry: the “fix” is recognizing why the baseline was written this way, and being able to prove what the broken version costs.)

Expected metric change. rate(spring_data_repository_invocations_seconds_count) falls to ~2× request rate (page + count query); hikaricp_connections_acquire p99 collapses; per-request latency drops at high concurrency even though each individual query was already fast.

Re-test. The same workload at the level where hikaricp_connections_pending first climbed above zero.

Risks/trade-offs. @EntityGraph on collections can produce a cartesian product with multiple List associations (then Set or @BatchSize is the answer). Projections skip the persistence context’s dirty checking — correct for reads, wrong for update flows.

3. Bound the result set and return a projection

Original condition. The straw-man version of search — findAll() or an unpaged List<Order> — returns whatever the filters match. At seed scale that is tens of thousands of entities per request: heap churn in Hibernate’s persistence context, megabyte JSON bodies, and latency that grows with the dataset rather than the page.

Hypothesis. Bounding rows per request and returning scalars removes both the entity-instantiation cost and the unbounded serialisation.

The change — already in the baseline, shown here as the delta you would apply: Pageable + max-page-size: 100 (chapter 04) + the OrderSummary constructor expression above.

Expected metric change. jvm_gc_memory_allocated_bytes_total rate per request falls noticeably — the persistence context stops materialising thousands of Order objects. Response size falls; p99 stabilises with respect to dataset growth.

Risks/trade-offs. Page costs a COUNT(*) query per page — usually fine, but on very large filtered sets the count itself is the cost; the alternative is keyset (seek) pagination, which drops OFFSET and the count at the price of losing “jump to page N” — a UX constraint, not just a code change. This is a needs validation choice: measure count(*) time on your data before reaching for keyset.

4. Size HikariCP from measurement, not defaults

Original condition. maximum-pool-size: 10 — the default, written down in chapter 03 as a placeholder. Under MixedWorkloadSimulation at some rate, hikaricp_connections_pending leaves zero.

Hypothesis. The pool is undersized for this workload’s concurrency and hold time — or it is fine, and the pending threads are a symptom of queries being too slow (in which case resizing is wrong). Distinguish first: if acquire_seconds p99 is high but DB-side query time is low, the pool is the queue. If queries are slow, the pool is just where the queue happens to be visible.

The sizing method — this is the deliverable, not a number:

1
required connections ≈ arrival_rate × mean_connection_hold_time

Measure hold time via hikaricp_connections_usage_seconds mean. At 100 req/s and 15 ms mean hold, you need ~1.5 connections of average capacity — but you size for the tail and for contention, so a pool of 5–10 is plausible. At 30 ms hold and 200 req/s, you need ~6 average, and 10 is marginal. The other bound is PostgreSQL: max_connections (default 100) divided across replicas sets the ceiling — 4 replicas × 10 = 40 connections, leaving headroom for migrations and admin sessions.

1
spring:
2
  datasource:
3
    hikari:
4
      maximum-pool-size: 10          # placeholder — set from measured demand
5
      connection-timeout: 30000      # default; lower only if you want faster failure
6
      max-lifetime: 1800000          # just under typical LB/DB idle kill

Expected metric change. hikaricp_connections_pending back to ~0 at the target rate, with connections_active having headroom — not pegged at max. If pending stays non-zero after the resize, the hypothesis was wrong: the database is the queue.

Risks/trade-offs. Bigger is not safer: each connection costs memory on both sides, and an oversized pool hides query problems until max_connections on the server becomes the incident. connection-timeout below ~1 s turns queueing into user-visible errors fast — deliberate backpressure, useful in overload (chapter 11), wrong as a latency fix.

5. Touch thread pools only when threads are the evidence

Original condition. Tomcat busy threads pinned at 200, CPU and DB calm, thread dump shows workers parked on a long call — the blocking-call signature from chapter 09.

The honest options, in order:

1. Remove the block — the right fix almost every time. Timeouts, async boundaries, reactive clients for outbound calls.
2. Virtual threads (Java 21): spring.threads.virtual.enabled=true replaces platform workers with virtual threads, making thread-per-request cheap again when threads spend their time waiting. Needs validation: it removes the thread-count ceiling, not the downstream one — HikariCP still has 10 connections, and a blocking call still blocks its caller; it changes the pool arithmetic, not the physics.
3. Raise server.tomcat.threads.max: legitimate only when threads are the proven queue AND the downstream can absorb more concurrency. Each platform thread carries ~1 MB of stack; the number is bounded by memory and by what the connection pool behind it can serve — a 400-thread pool in front of 10 DB connections is 390 threads waiting.

Expected metric change. tomcat_threads_busy stops pinning; accept-queue delay disappears from the client-server gap. If it doesn’t, threads weren’t the queue.

6. Cache a read-heavy endpoint — with the correctness bill itemised

Original condition. GET /api/orders/{id} serves at 70% of read traffic, and pg_stat_statements shows the same point-lookup with calls proportional to request rate. The data changes only on status transitions — reads dominate writes roughly 10:1.

Hypothesis. A local cache absorbs repeat fetches of hot orders, trading memory for eliminated queries.

The change:

1
package in.o612.eng.orders.order;
2

3
import org.springframework.cache.annotation.EnableCaching;
4
import org.springframework.context.annotation.Configuration;
5

6
@Configuration
7
@EnableCaching
8
public class CacheConfig { }

1
// OrderService — reads cached, writes evict
2
@Cacheable(cacheNames = "orders", key = "#id")
3
@Transactional(readOnly = true)
4
public OrderResponse getById(long id) { ... }
5

6
@Caching(evict = @CacheEvict(cacheNames = "orders", key = "#id"))
7
@Transactional
8
public OrderResponse updateStatus(long id, OrderStatus target) { ... }

(Dependencies: spring-boot-starter-cache plus an implementation — Caffeine or the JDK’s ConcurrentMap default for a lab.)

Expected metric change. Repository invocation rate for the point-lookup decouples from request rate — hits stop reaching the DB; getById p99 drops toward in-memory time; DB CPU falls.

Risks/trade-offs — the part that decides whether caching is right, not whether it is fast:

• Correctness window. The @CacheEvict above keeps this service consistent. The moment a second replica or another writer exists, a local cache serves stale data until eviction propagates — that’s when Caffeine stops being enough and Redis (or TTL-bound staleness you can defend) enters.
• Invalidation coverage. Every write path must evict. One missed path — a bulk SQL update, a flyway migration, a support query — leaks stale reads silently.
• What you give up. p99 improves; worst-case correctness degrades. For money-adjacent data, that trade needs a staleness bound you can state in numbers, not vibes.

The re-run ledger

Every change above ends the same way: run sheet updated, identical simulation re-run, before/after percentiles recorded, saturation signature checked. If the expected metric didn’t move, the hypothesis was wrong — revert, and record that too. A falsified hypothesis documented is worth more than an unmeasured improvement.

Milestone check: for each of the five changes you can state the signature that justifies it and the metric that would falsify it. Chapter 11 uses the same discipline at the edges — stress, spike, and soak — where the goal is finding the boundary rather than confirming health inside it.