High-Performance Java Data Systems

High-Performance Java Data Systems

This note brings together database access-layer and indexing work derived from real enterprise systems, Java Persistence engineering notes, and current principles for data-intensive software. The goal is not to provide a list of knobs. The goal is to understand which layer becomes the limit under load, why it becomes the limit, and what every optimization costs in return.

The method does not change:

measure
find the bottleneck
change one variable
measure again under the same load

An optimization cannot be described only as "faster." An index improves reads while increasing write cost. Batching improves throughput while potentially increasing per-item latency. Caching reduces latency while adding consistency work. Replication improves availability or read capacity while creating staleness and failover semantics.

This August 2026 revision keeps the original framework and fills the gaps around Spring Boot and JVM observability, load testing, thread pools, HikariCP, reactive execution, Caffeine and Redis, HTTP/2 and HTTP/3, serialization, PostgreSQL operations, Kafka producer/consumer behavior, capacity planning, and GraalVM. Version-sensitive statements were rechecked against primary product documentation.

The Zen principle here is an engineering rule:

not more tuning
less work

not more concurrency
measured limits

not more abstraction
visible cost

If a change does not reduce work, make queues visible, or make a limit more deterministic, it may only be adding complexity.

1. Performance model

Latency and throughput

Latency is the time required to complete one request. Throughput is the amount of completed work per unit time. A system can achieve higher throughput while making an individual request slower.

Little's Law describes average concurrency in a stable system:

L = λ · W

L : work present in the system
λ : arrival rate
W : average time in the system

The same relation applies to HTTP requests, connection pools, queues, workers, and database sessions.

Example:

1000 requests/s
DB connection lease time per request = 5 ms

L = 1000 × 0.005 = 5

The theoretical average is five concurrent database connections, with additional headroom for variance. A thousand concurrent HTTP requests therefore do not imply a thousand database connections.

Saturation and queues

A simple single-server queue model illustrates the nonlinear effect of utilization:

R = S / (1 - ρ)

R : response time including waiting
S : service time
ρ : utilization

As a resource approaches saturation, latency does not rise linearly. Moving from 50% to 95% utilization is not a small increase; queue behavior changes qualitatively.

Production capacity is selected below the cliff, with failure and burst headroom. A resource operated at its saturation point has no reserve for traffic spikes, GC, plan changes, storage stalls, or slow downstream calls.

Amdahl and coordination cost

Amdahl's Law limits total speedup by the serial fraction:

S(N) = 1 / ((1-p) + p/N)

Distributed systems add coordination. The Universal Scalability Law models serialization and coherence/coordination cost:

C(N) = N / (1 + α(N-1) + βN(N-1))

α : serialization
β : coordination / coherence

When β is not zero, adding nodes can eventually reduce total throughput. Adding application instances that all contend on the same database rows, latches, or pages may only add competing clients.

Tail latency

Average latency hides bad queue behavior. P95, P99, and P99.9 are more representative of production tails.

When one request fans out to several dependencies, one slow dependency can dominate the entire request. As fan-out grows, tail events are observed more frequently by users.

Percentiles cannot be averaged. Per-instance P99 values must not be averaged to obtain a fleet P99. Histograms or raw distributions must be merged and the percentile recalculated from the combined distribution.

Coordinated omission

If a load generator waits for one response before sending the next request, the generator itself slows down when the system slows down. Requests that would have queued in real traffic are never sent, and P99 looks better than reality.

Where the workload represents an external arrival rate, the test should preserve an open-loop arrival process and measure latency from the scheduled arrival time.

2. Reliability, scalability, and maintainability

Performance is not the only quality attribute of a data-intensive system. Three questions must be answered together:

• Does the service continue when a component fails?
• Does behavior remain predictable as traffic and data grow?
• Can the system be understood and changed safely?

A component failure is not necessarily a service failure. If a disk fails and replication masks it within the SLO, the component failed but the service did not.

Scalability does not mean merely "more machines can be added." The load dimension must be defined, the resource whose cost grows with that dimension must be identified, and the architecture must show how that cost is distributed.

Maintainability is not the opposite of performance. An opaque trick that no one can safely change six months later is not a sustainable optimization.

3. See the complete data-access path

A request does not reach a database in one step:

HTTP request
-> application thread
-> transaction boundary
-> connection pool
-> JDBC driver
-> network
-> SQL parse / plan
-> locks and MVCC
-> buffer cache
-> index / table / storage
-> result set
-> ORM mapping
-> serialization
-> network response

Every arrow is a separate cost boundary. A single repository call can represent dozens of SQL statements, thousands of rows, many network round trips, or long lock waits.

Optimization starts by selecting the right layer. If SQL execution takes 2 ms but connection acquisition takes 150 ms, query tuning is aimed at the wrong layer. If the query takes 400 ms, adding threads simply runs the expensive query more concurrently.

4. Measurement discipline

Four observation layers

Observe the same event at four layers:

application       request, transaction, query count
JVM               CPU, allocation, GC, thread waiting
database          plan, rows, blocks, locks, redo/WAL
operating system  CPU, memory, disk, network

One layer alone rarely establishes causality.

Application metrics

Request rate, errors, and latency distributions describe service health. Utilization, saturation, and errors describe resource health.

High-cardinality labels destroy metric systems. User IDs, request IDs, raw URLs, SQL text, and free-form error strings should not become metric labels.

At minimum, a connection pool should expose:

active connections
idle connections
pending requests
acquisition time
timeouts

JFR and profiling

Java Flight Recorder provides a low-overhead timeline of CPU, allocation, GC, locking, parking, I/O, and virtual-thread events. A continuously rotating recording is often more valuable than a profiler attached only after the incident.

A CPU profile shows code that is executing. If a service is slow while CPU is low, inspect wall-clock time: locks, network, pools, parking, and other waits appear there.

Inspect allocation before increasing heap size. GC tuning without identifying unnecessary allocation usually hides the cost rather than removing it.

SQL observation

Three independent views are required:

• the SQL and parameters actually sent by the application,
• ORM query/entity/flush counts,
• the database's real execution plan and actual row counts.

show_sql can help during development but does not explain duration, batching, bind behavior, or the total number of calls. Query-count assertions can catch N+1 during tests instead of in production.

Separate work from elapsed time

Elapsed time changes with cache warmth, concurrent load, and storage state. Blocks read, rows returned, bytes transferred, and round trips are more stable descriptions of work.

Every optimization should answer:

what did we do less of?

If there is no answer, the gain is often temporary or accidental.

Micrometer, histograms, and cardinality

A Spring Boot timer is not sufficient by itself. Micrometer represents counters, gauges, timers, and distributions in a common model. For backends such as Prometheus, aggregable latency is represented through histogram buckets.

Client-side P95 or P99 values calculated per meter ID cannot be aggregated correctly across instances or tag sets. Histogram buckets can be summed across compatible dimensions and the fleet percentile can then be estimated from the aggregate distribution.

Histograms also have a cost. Every bucket and every tag combination creates time series. SLO boundaries and expected ranges should be bounded to the workload rather than enabled indiscriminately.

RED and USE complement each other:

RED: rate, errors, duration
USE: utilization, saturation, errors

If P99 rises while CPU remains low, inspect pool, disk, network, and lock saturation. If CPU rises while request rate is constant, inspect allocation, serialization, plan changes, and newly hot code paths.

async-profiler and flame graphs

JFR provides JVM-wide context and event chronology; async-profiler is strong for low-overhead CPU, allocation, wall-clock, and lock sampling. They are complementary.

A CPU flame graph shows execution. A wall-clock profile includes waiting. An allocation flame graph answers which call paths create objects before GC becomes the visible symptom.

Flame-graph width represents sample or time share; the visually highest frame is not automatically the problem. Find the wide base, follow the stack, and profile again after the change. Differential profiles help explain where the win actually came from.

Continuous recording and native memory

Production-only incidents are easier to diagnose when telemetry existed before the failure. JFR can be run continuously with a bounded recording and dumped when an event occurs.

Not every diagnostic tool belongs in always-on mode. Native Memory Tracking is disabled by default and has measurable overhead. It tracks HotSpot/JVM-native categories but not all third-party native allocations. If RSS grows while heap does not, distinguish:

heap
metaspace
thread stacks
direct buffers
JVM native structures
JNI / third-party native memory

5. Connection pools

Connections are expensive

Creating a new database connection can involve TCP establishment, authentication, database-session creation, and memory allocation. A pool amortizes that cost over the application lifetime.

A pool does not make a database connection itself faster. It reuses expensive sessions and, more importantly, bounds how much concurrent work may enter the database.

Bigger is not necessarily better

Database parallelism is bounded by CPU, storage, locks, and shared internal structures. If the pool exceeds that useful parallelism, the queue moves from the application into the database.

A small bounded pool creates visible waiting and controlled timeouts. An oversized pool can create more simultaneous SQL, more lock and I/O contention, and worse tail latency.

Sizing

Core-count formulas are starting hypotheses, not laws. A better starting point is measured connection lease time combined with Little's Law.

Load-test several pool sizes and find the region that sustains the target throughput with the lowest stable P99. The answer is usually a safe interval, not a magical integer.

Timeouts and lifetime

Connection acquisition must be bounded. Infinite waiting only hides overload inside request queues.

Maximum connection lifetime can be set below database or infrastructure time limits so the application retires connections before the network does. Keepalive should be used only where the infrastructure requires it and should remain below maximum lifetime.

Prefer JDBC driver validation such as Connection.isValid() when supported instead of issuing a heavy validation query. Leak detection is a diagnostic for unexpectedly long leases, not a permanent performance optimization.

Acquire late, release early

Do not hold a database connection across file I/O, long computation, or remote service calls unless the consistency model truly requires it.

bad:
  begin transaction
  read DB
  call remote service
  compute
  write DB
  commit

prefer:
  read required data in a short transaction
  compute/call outside the transaction
  write in a short transaction

If atomicity cannot span those steps, the solution is usually a different process design rather than a longer connection lease.

Multiple application instances

Total sessions grow approximately as:

application instances × pool size per instance

Ten instances with pools of twenty can create two hundred database sessions. Capacity must be calculated at the database level, not one service instance at a time.

A transaction pooler can reduce server sessions but loses or virtualizes session state. Temporary tables, session variables, session locks, and prepared-statement semantics must be reviewed.

Read HikariCP settings by purpose

Important settings are not interchangeable:

maximumPoolSize    upper bound on DB concurrency
connectionTimeout  waiting budget when saturated
maxLifetime        connection retirement age
keepaliveTime      liveness interval for idle connections
validationTimeout  validation budget

maximumPoolSize is not derived from application thread count. HikariCP's own sizing guidance emphasizes that fewer connections often outperform more connections once the database is saturated.

When no connection is available, getConnection() should fail within a bounded budget. Hiding pool saturation behind more retries or threads increases pressure.

Pool behavior is best diagnosed by reading pending requests, acquisition P99, and database service time together. If acquisition time rises while DB service time remains stable, the pool/application queue is the limiting boundary. If DB service time rises as well, widening the pool often worsens contention.

6. Virtual threads and the real concurrency limit

Virtual threads make thread-per-request code cheaper during blocking I/O because a blocked virtual thread can release its carrier. They do not accelerate CPU-bound work.

Therefore:

10,000 virtual threads
10 DB connections

still yield roughly ten concurrent database operations. Removing the platform-thread bottleneck often exposes the connection pool as the next limit.

The response is not to expand the pool automatically. Bound arrival rate, shorten transactions, measure acquisition time, and keep database concurrency inside the safe region.

Spring Boot 4 and version boundaries

As of August 2026, Spring Boot 4 does not enable virtual threads by default; spring.threads.virtual.enabled=true enables them. With virtual threads enabled, many traditional executor-pool sizing properties no longer have the same effect because scheduling is handled by the JVM-wide carrier scheduler.

Virtual threads are daemon threads. Applications whose lifetime depends only on scheduled tasks may need to consider Spring Boot's keep-alive behavior explicitly.

JDK 21 finalized virtual threads. JDK 24 delivered JEP 491, allowing virtual threads blocked in many synchronized constructs to release their carrier threads, removing a major historical source of pinning. This does not justify assuming that pinning or carrier starvation can never occur; native calls and library-specific behavior must still be verified with JFR.

@Async, schedulers, and uncontrolled fan-out

@Async is not capacity. It is another execution queue. Schedulers do not create downstream capacity either. Virtual threads can make fan-out cheap enough to overwhelm the next resource faster.

The effective limit is the smallest safe downstream boundary:

10,000 cheap virtual threads
-> 10 DB connections
-> 4 downstream-service permits
-> 1 hot lock

Structured Concurrency

Structured Concurrency treats related subtasks as one lifecycle for cancellation, failure propagation, and observability. As of JDK 26 it remains a preview API. Code that adopts it must acknowledge preview-version compatibility instead of treating the API as a stable cross-release contract.

WebFlux solves a different problem

WebFlux is useful when the full path is non-blocking and Reactive Streams backpressure, streaming, or high fan-out are real requirements. Spring MVC with virtual threads keeps a blocking programming model while reducing the cost of waiting.

Wrapping blocking JDBC/JPA in a reactive chain does not make the database non-blocking. R2DBC is a separate driver and transaction model. Choose from the blocking behavior of the whole path, not from framework fashion.

7. Transaction boundaries

The physical meaning of ACID

Atomicity is implemented with undo/rollback mechanisms, durability with redo/WAL, and isolation with locking or MVCC. Consistency results from constraints plus correct application logic.

A commit is not simply the end of a Java method. If durability is required, the relevant log records must reach durable storage. Group commit amortizes the fsync cost across multiple transactions and can materially increase throughput.

MVCC

MVCC lets readers observe a consistent older version so readers and writers interfere less. The cost is version retention and cleanup.

Long transactions can extend not only lock lifetime but also the lifetime of old versions. A "read-only transaction cannot hurt" rule is therefore false in general.

Isolation anomalies

Important anomalies include dirty read, dirty write, non-repeatable read, phantom read, read skew, lost update, and write skew.

Snapshot isolation resolves many read anomalies but does not inherently prevent write skew. Two transactions can update different rows based on the same cross-row invariant and violate the invariant together.

Serializable execution guarantees an outcome equivalent to some serial order. The implementation may pay with lock waiting or by aborting/retrying conflicts.

Isolation names are not guarantees

READ COMMITTED, REPEATABLE READ, and SERIALIZABLE differ in detail across database products. Portable code should test the business invariant on the target database rather than trusting the annotation name alone.

Keep transactions short

Do not include user interaction, large file transfers, long computation, or avoidable remote calls inside a database transaction.

Short transactions release connections sooner, reduce lock lifetime, reduce MVCC pressure, shrink optimistic-conflict windows, and reduce the amount of work repeated after failure.

8. Spring transaction management

@Transactional is proxy/interceptor behavior, not syntax magic. A direct self-invocation that bypasses the proxy may not create the intended transaction boundary.

Default rollback behavior depends on exception type. If the business rule requires rollback for a particular failure class, encode the policy explicitly rather than relying on accidental defaults.

REQUIRES_NEW can require a second physical connection. If outer transactions occupy every pool connection while inner transactions wait for new ones, the application can deadlock itself at the pool.

readOnly=true is not a cryptographic write barrier. It is a hint that may affect flush and tracking behavior and can also be used as a routing signal. Its actual effect is provider- and version-dependent.

Read-replica routing must also handle replica lag and read-your-writes semantics.

9. Concurrency control

Optimistic locking

A version column is added to the update predicate:

UPDATE account
SET balance = ?, version = 4
WHERE id = ? AND version = 3;

Zero affected rows mean the state changed. There is no lock wait, so optimistic locking is cheap when conflicts are rare.

It protects the versioned entity, not every cross-row business invariant. Aggregate versioning, unique constraints, serializable transactions, or explicit locks may still be required.

Pessimistic locking

A pessimistic lock creates a queue on the resource. Under very high contention with short critical sections it can be cheaper than a retry storm.

Rules:

acquire late
keep work short
lock resources in a consistent order
bound waiting
never call remote services while holding the lock

Deadlocks

A deadlock is an expected failure mode of a locking system, not evidence that the database is broken. The database detects the cycle and aborts a victim.

The application should classify the transient error, retry the whole transaction rather than the last statement, cap attempts, and use exponential backoff with jitter.

Idempotent retry

Running the same business command twice must not create two external effects. Payment, messages, files, or remote calls should not be repeated blindly with a database transaction retry.

An idempotency key maps the same business command to the first recorded result so a duplicate attempt observes the existing outcome.

10. Transactional outbox and external effects

A database transaction and a message broker or HTTP endpoint normally cannot be made atomic by one local transaction.

Transactional outbox:

same DB transaction:
  write business state
  write outbox record

separate process:
  read outbox
  publish message
  mark delivery state

Delivery can be at least once, so consumers must be idempotent. Claims of "exactly once" become meaningful only when the exact boundary and the identities stored across all external effects are specified.

11. Persistence context

The persistence context represents one database identity as one managed Java object, accumulates state changes, and performs dirty checking.

Convenience has a cost:

managed entities ↑
snapshot memory ↑
flush scanning ↑
GC pressure ↑

Do not let a long batch job grow the persistence context without bound.

Flush and clear

for (int i = 0; i < items.size(); i++) {
    entityManager.persist(items.get(i));

    if ((i + 1) % batchSize == 0) {
        entityManager.flush();
        entityManager.clear();
    }
}

flush() synchronizes pending SQL, clear() detaches managed entities, and commit makes the transaction durable. They are different operations.

Read-only paths

For read-only output, DTO or scalar projection is often cheaper than loading managed entities. If entities are required, read-only transaction/provider hints can reduce unnecessary snapshot and flush work.

persist versus merge

Use persist for a new entity. merge copies detached state into a managed instance and can trigger hidden reads. In bulk write paths, detached-entity workflows therefore need careful measurement.

Explicit DTO boundaries also reduce accidental lazy loading across layers.

Stateless paths

For very large write streams where first-level caching, cascades, and dirty checking are not required, a stateless Hibernate session or direct JDBC can be more appropriate. Less magic means more explicit responsibility.

12. Open Session in View

With Open Session in View, the web layer can continue reaching into the persistence context. That convenience can hide transaction boundaries, issue lazy SQL during JSON serialization, conceal N+1 from service tests, and keep database resources alive longer than expected.

Prefer:

service transaction
-> fetch exactly what is needed
-> build DTO
-> close transaction
-> web layer never touches DB state

Setting open-in-view=false does not create lazy-loading bugs; it reveals data access that was previously hidden.

13. Identifier strategies

What a primary key is for

A primary key is not merely a lookup accelerator. It defines row identity and underpins ORM identity maps, association resolution, and update semantics.

If a separate business uniqueness rule exists, enforce it with a unique constraint. An application-side "check then insert" can race under concurrency.

IDENTITY

With IDENTITY, the identifier is generally known only after the insert. This makes it harder for Hibernate to delay and group inserts, so it is often a poor fit for heavy batched writes.

SEQUENCE

A sequence can provide the identifier before the insert. Pooled and pooled-lo optimizers reduce one-database-call-per-ID overhead.

Larger sequence caches can create gaps. A technical primary key is an identity mechanism, not an accounting sequence. Gapless business numbering is a separate requirement.

UUID

UUID-like identifiers can be generated independently across nodes. Fully random identifiers can reduce B-tree insertion locality; time-ordered UUID variants can improve locality.

Think in terms of:

single DB authority          sequence
distributed independent IDs UUID-like
business meaningful key     natural key + separate technical PK

14. Mapping cost

Types

Java and database types must represent the same semantics. Mixing timezone-aware and timezone-free temporal types creates correctness problems and can also introduce conversion cost or prevent effective index use.

Storing enums by ordinal makes existing data dependent on source-code declaration order. Stable string/code values are safer.

Large fields

Do not move LOB, JSON, or large text through every list endpoint. Hot queries should select only required columns, and large content can use a separate access path.

A LAZY declaration on a large field is not evidence that the provider actually avoids fetching it. Verify generated SQL and bytecode-enhancement requirements.

Inheritance

ORM inheritance transfers object-model convenience into query cost. Single-table inheritance generally minimizes joins but creates sparse columns. Joined inheritance normalizes the model but polymorphic reads can require several joins. Table-per-class can turn polymorphic reads into unions.

Use inheritance in the persistent model only when its domain benefit exceeds the query and schema cost.

15. Associations and N+1

N+1 is not "one query is slow." It is a query-count problem:

1 query for parents
+ N queries for children

A low-latency database can still perform poorly when hundreds of round trips are introduced.

EAGER is not a universal fix. It can eagerly load data that a request did not need and can still produce many SQL statements depending on the mapping and query.

Options include DTO projections, explicit join fetches, entity graphs, batch fetching, and subselect-style fetching. The correct choice depends on result cardinality and whether the association is required for that use case.

Join fetch and Cartesian multiplication

Fetching several to-many collections in one SQL can multiply rows. Ten parents with ten children in two collections can produce hundreds or thousands of physical rows even if the logical object graph is small.

The database, network, and ORM must process every physical row. One large query is therefore not automatically cheaper than a small bounded number of queries.

Pagination with collection fetch

Collection join fetch and pagination are a dangerous combination. Some ORM paths may fetch a much larger result and apply root-entity limits in memory.

A safer design is often:

query page of root IDs
-> fetch required graph for those IDs

For read-only screens, DTO projection can avoid the entity graph entirely.

16. Result sets and transferred data

Project only required columns

SELECT * is not neutral. It increases database block-to-row work, network bytes, JDBC decoding, Java allocation, and serialization.

DTO projections make the read contract explicit and can avoid persistence-context tracking.

Fetch size

JDBC fetch size controls how rows are transferred from the driver/database, not how many rows the query logically returns. It can reduce round trips on large result sets, but behavior is driver- and database-specific.

For example, Oracle's historical small default row-prefetch behavior can make an explicit fetch size important for medium/large result sets, while other drivers may already stream or buffer differently. Measure on the exact driver and database version.

Streaming

Streaming avoids materializing a large result all at once, but it also keeps database resources open while the stream is consumed. Slow downstream processing can therefore hold a connection for a long time.

For long exports, bounded pages or database-native bulk/export mechanisms can be safer than a single hour-long transaction-backed stream.

17. Pagination

Offset pagination

ORDER BY created_at, id
OFFSET 100000
LIMIT 50

Large offsets can force the database to identify and discard many rows before returning the page. Cost grows with page depth.

Keyset pagination

WHERE (created_at, id) > (?, ?)
ORDER BY created_at, id
LIMIT 50

Keyset pagination continues from the last key rather than counting from the beginning. It is efficient for scrolling APIs, feeds, and batch traversal, although arbitrary direct jumps to page 5000 become less natural.

The ordering must be unique. A timestamp alone can produce duplicates or gaps when several rows have the same value; append a unique key.

COUNT cost

A full COUNT(*) is not required on every page. If the client needs only "is there a next page?", fetch one extra row or use slice semantics.

Compute an exact total only when it is a business requirement, not because a framework page abstraction happens to expose it.

18. Batch writes

Batching primarily wins by reducing network and protocol round trips, not by making a single insert cheaper to execute.

10,000 inserts
one by one   -> ~10,000 sends
batch of 100 -> ~100 groups

The exact packet and execution behavior depends on the driver and database protocol.

As batch size grows, round trips decrease but client/server buffers grow, rollback scope grows, and per-record waiting time can increase. Values such as 50 or 100 are experiment starting points, not constants.

Hibernate batching

Statements with the same SQL shape need to occur close together so batches can fill. Ordering inserts or updates can help.

Identifier strategy matters. IDENTITY can prevent effective insert batching because the generated key is needed after each insert. Sequences with pooled allocation work better for batch-heavy paths.

Bulk DML

One set-based statement can be dramatically cheaper than loading thousands of entities and mutating them one at a time:

UPDATE job
SET state = 'EXPIRED'
WHERE state = 'OPEN'
  AND expires_at < ?;

Bulk DML bypasses persistence-context state. Clear or refresh affected managed entities afterward to avoid stale in-memory state.

19. Set-based SQL versus row-by-row processing

Relational databases are designed for set operations. A row-by-row loop in application code or stored procedural code increases round trips, context switches, and lock duration when one set-based SQL statement could express the same transformation.

The problem is not "using a cursor" as a concept; database engines already use cursor-like execution internally. The problem is converting a set problem into a row algorithm without need.

Prefer:

single set-based statement
-> bulk/batch
-> controlled row processing only when unavoidable

On shared clusters such as RAC, unnecessary row-by-row access can also increase cross-instance block coordination.

20. SQL preparation and plan caching

A SQL statement passes through:

parse
-> semantic resolution
-> plan selection
-> execution
-> result

For short OLTP statements, hard-parse cost can be comparable to execution cost.

Bind parameters

WHERE user_id = ?

allows the SQL shape to be reused and is also a primary defense against SQL injection.

Concatenating values into the SQL text produces more distinct statements, more parsing, fragmented plan caches, and a security risk.

Data skew

The same plan is not optimal for every bind value. One value may return one row while another returns half the table. Do not conclude that "binds are bad" before inspecting statistics, actual rows, and the target database's bind-sensitive/adaptive planning behavior.

IN lists

Variable-length IN lists can create many SQL shapes and reduce plan reuse. ORM parameter padding can reduce shape variety in some workloads but should not be enabled as a universal performance switch.

Transaction poolers and prepared statements

With PgBouncer or another server-session pooler, an application connection and a PostgreSQL server session are no longer the same thing.

• session pooling releases the server connection after the client disconnects,
• transaction pooling releases it after each transaction,
• statement pooling releases it after each statement and therefore cannot support multi-statement transactions.

Modern PgBouncer can track protocol-level named prepared statements in transaction/statement modes when configured with max_prepared_statements. That does not make all session semantics portable. Session SET state, advisory locks, temporary-table behavior, and SQL PREPARE require explicit review.

The Zen rule is simple: if the application does not need session state, do not create session state.

21. Execution plans

A plan is the tree of operations the database selected. The first question is not "did it use an index?" but:

estimated rows
versus
actual rows

If the optimizer expects 100 rows and receives 10 million, join algorithms, memory sizing, and access paths can all be wrong.

Access paths

A sequential/full scan can be correct for a small table or a low-selectivity predicate.

An index scan can be excellent when few rows qualify, but if many rows qualify the resulting random table lookups can be more expensive than a scan.

An index-only path can avoid table lookups when all required data and visibility conditions permit it.

Join algorithms

Nested loop is strong when the outer input is small and the inner side has an efficient lookup.

Hash join is strong for larger equality joins but can spill when the hash state exceeds memory.

Merge join is efficient for already ordered inputs but may pay an explicit sort cost otherwise.

Read block/page access, spills, loop counts, and row flow, not just total time.

EXPLAIN versus real execution

EXPLAIN describes the optimizer's estimate. EXPLAIN ANALYZE actually executes the statement and reports observed rows/timing. This distinction is critical for DML with side effects.

PostgreSQL BUFFERS and equivalent vendor-specific execution statistics help answer:

what did the optimizer expect?
what actually happened?
how many blocks/pages moved?
did an operator spill?
how many times did the node execute?

Plan cost units are not milliseconds and should not be compared across database products as if they were wall-clock measurements.

22. Indexing

An index is not merely a performance add-on; it is a physical expression of an access pattern. Primary and unique constraints are first about correctness.

Primary and unique constraints

A primary key defines row identity. A unique constraint atomically enforces a business key even under concurrent requests.

SELECT whether row exists
then INSERT

is race-prone unless the database also enforces uniqueness.

B-tree

B-tree is the general-purpose default for equality, range, ordering, and prefix access. The theoretical search depth is logarithmic, but real performance depends on fan-out, cache residency, row lookup cost, and selectivity.

Composite indexes

For an index (a, b, c), left-prefix behavior matters. A common starting heuristic is:

equality columns
-> range column
-> ordering / covering columns

but the real plan and workload decide.

Covering indexes

If all required columns are in the index, the database may avoid returning to the table. Reads decrease, but the index becomes larger and writes become more expensive.

Partial/filter indexes

Indexing only a hot subset such as WHERE active = true can make the access structure much smaller. Syntax and capability are database-specific.

Expression indexes

A predicate such as LOWER(code) = ? may require an expression/function-based index matching the expression.

Foreign keys

Foreign-key columns are common join paths and are relevant to parent update/delete behavior. Missing indexes can create broad scans and lock effects depending on the database.

Index cost

Every index must be maintained on insert, delete, and updates to indexed columns. It adds redo/WAL, storage, buffer-cache use, and maintenance work.

Therefore both "no indexes" and "index every column" are wrong. Measure unused and overlapping indexes.

Full scans are not automatically wrong

For low-selectivity predicates, the cost of reading the index and then repeatedly locating table rows can exceed a sequential scan. Ask which path performs the least work, not why an index was ignored.

Hash, GIN, and BRIN

B-tree is not the only useful index family.

A hash index is specialized for equality and does not provide range ordering. It is not automatically superior to B-tree for routine equality lookups.

GIN behaves as an inverted index and is useful when one row contains many searchable keys, such as PostgreSQL arrays, full-text terms, and supported jsonb operators. The read benefit comes with heavier write/maintenance cost.

BRIN stores summaries over physical block ranges rather than one entry per row. It can be extremely small and effective for very large tables whose physical order correlates strongly with time or increasing identifiers. Poor correlation reduces its pruning value.

Choose an index family from the operators and access pattern, not from the column type alone.

PostgreSQL memory and maintenance

shared_buffers, work_mem, and effective_cache_size represent different concepts. PostgreSQL documentation's 25% shared_buffers figure is a starting point for a dedicated server, not a universal formula.

work_mem can be consumed by several sort/hash operators in one query, by many concurrent sessions, and by parallel workers. Total memory can therefore be many times the configured value.

Autovacuum is not merely deletion cleanup. It recovers dead tuples, maintains planner statistics and visibility information, and can directly affect the ability to use index-only scans. Disabling it to improve a short benchmark can damage steady-state performance.

pg_stat_statements is useful for ranking SQL families by aggregate system cost rather than staring only at the single slowest query.

Read replicas can be selected using an application signal such as a read-only transaction, but lag and read-your-writes semantics must be solved explicitly.

23. SSDs, storage, and write amplification

The main cost of an unindexed query is unnecessary reads, CPU, buffer pressure, and concurrent-resource consumption. SSD endurance is more directly driven by bytes written and write amplification.

write amplification = physical bytes written / logical application bytes written

B-tree writes can involve WAL plus data/index page writes. LSM writes may traverse WAL, memtable flush, and compaction. Both can turn one logical change into several physical writes.

Random writes can create more internal flash-management work than sequential patterns. Short benchmarks can overstate LSM write throughput if compaction has not reached steady state. Run long enough to include maintenance work.

24. B-tree and LSM

A B-tree updates pages in place and provides predictable point/range lookup through a bounded tree depth.

An LSM design typically follows:

WAL
-> memtable
-> immutable SSTables
-> compaction

It converts much of the write path into sequential work. Reads may consult several SSTables; Bloom filters help avoid unnecessary reads for absent keys.

General tendency:

B-tree  lower point/range read latency
LSM     higher sustained write throughput

The actual result depends on dataset size, overwrite rate, key distribution, compaction policy, cache, and storage.

Compaction is not free background work. If incoming writes exceed compaction capacity, a healthy engine must eventually apply backpressure.

25. OLTP and OLAP

Operational systems create and update current state. Analytical systems scan and aggregate large datasets.

OLTP                         OLAP
--------------------------   ---------------------------
short transactions           long scans / aggregation
few rows                     many rows
frequent writes              mostly reads
row-oriented                 column-oriented
indexed point access         scans, compression, vectorization
low latency                  high aggregate throughput

The same physical system can support both, but if analytical work damages transaction latency the workloads should be separated.

Row and column storage

Row storage keeps fields of one record close and is efficient for transactional record access. An analytical query reading three columns out of a hundred wastes bandwidth if it must read all hundred.

Column storage reads only selected columns, compresses similar values effectively, and can use SIMD/vectorized execution across large blocks.

Materialized views

A repeated expensive query can be precomputed. This moves work from read time to refresh/write time.

A materialized view is derived state, not the system of record. It is safer when it can be rebuilt from authoritative data.

Move analytics away from OLTP

Prefer:

OLTP
-> CDC / ETL
-> analytical system

rather than repeatedly scanning the transaction database. Columnar systems such as ClickHouse are not generic drop-in replacements for Oracle or PostgreSQL OLTP; they solve a different workload.

26. System of record and derived data

The system of record holds the authoritative copy of a fact. A cache, search index, materialized view, warehouse, or machine-learning model is derived state.

authoritative state
-> transformation
-> derived view

Failure handling becomes simpler when derived data can be reconstructed from the source.

This distinction also clarifies caching: losing a non-authoritative cache is a performance event, not data loss.

27. Replication

Why replicate

Replication can serve three different goals:

• fault tolerance,
• read capacity,
• geographic proximity.

One mechanism does not satisfy all three at the same consistency and latency cost.

Physical and logical replication

Physical replication transfers low-level WAL/storage changes. It is close to the storage engine and efficient, but can impose stronger version/engine coupling.

Logical replication transfers row-level changes and is better suited for CDC or heterogeneous consumers.

A missing stable key can make logical updates/deletes more expensive because more old-row state may be required to identify what changed.

Synchronous and asynchronous replication

Synchronous replication adds durability but also latency and failure dependency to the commit path. Asynchronous replication keeps the write path lighter but can lose not-yet-replicated commits if the leader is lost.

The choice follows RPO, RTO, and latency requirements.

Replica lag

Moving reads to replicas reduces primary load but can return stale data. The classic failure appears as:

write
immediately read

and the user cannot see their own write.

Solutions include reading from primary for a bounded period after a write, tracking a commit/log position and waiting until the replica catches up, or keeping strongly consistent endpoints on primary.

Replica lag must be observable as a real metric; "eventual" is not useful if the delay cannot be measured.

28. Change Data Capture

CDC reads changes from the database log instead of polling tables repeatedly.

transaction database
-> change log
-> CDC
-> queue / search / analytics / cache

It separates OLTP from reporting pressure, enables near-real-time derived views, and reduces coupling between downstream systems and source queries.

Oracle GoldenGate and Debezium are examples with different support, licensing, topology, and operational models. The selection is not simply "which is faster."

CDC does not automatically provide exactly-once external side effects. Log positions, transaction identity, and consumer idempotency remain part of the design.

29. Partitioning

Partitioning divides one logical table into physical pieces. Time-oriented data can be divided as:

2026-07
2026-08
2026-09

Partition pruning reduces scanned data, and dropping an old partition can be far cheaper than deleting billions of rows because it avoids row-by-row undo/redo, locking, and index maintenance.

If the partition key is absent from the predicate, the engine may need to inspect many or all partitions and the benefit disappears.

Local and global indexes

A local index follows partition boundaries and simplifies partition maintenance. A global index can serve cross-partition queries but makes partition maintenance more coupled and expensive.

There is no universal "always local" or "always global" rule. Query paths and lifecycle operations decide.

30. Sharding

Partitioning can occur inside one database. Sharding distributes data across multiple database nodes.

Range and hash distribution

Range sharding preserves range scans but an increasing key can create one hot shard.

Hash distribution balances keys more evenly but can turn range queries into scatter/gather operations.

A composite concept such as:

partition key + sort key

can preserve efficient ranges within each partition.

Hot keys

Consistent hashing can distribute keys evenly without distributing requests evenly. One popular customer, account, or record can saturate its shard.

Measure access skew, not only key-count skew. Hot keys can require sub-sharding, caching, write combining, or a different data model.

Secondary indexes

A local secondary index makes writes local but can require searching every shard. A global secondary index narrows reads but introduces cross-shard write/coordination cost.

The trade-off remains:

cheap write -> expensive read
expensive write -> cheaper read

Distributed transactions

Once one business operation writes multiple shards, single-node transaction assumptions end. Options include distributed transactions, sagas, or redesigning the partition key so related state stays together.

The cheapest distributed transaction is the transaction that never had to become distributed.

31. Data model and access path

Normalization keeps one authoritative representation of a fact and simplifies write consistency. Denormalization can reduce joins but introduces synchronization responsibility for duplicated values.

A useful default is:

normalize authoritative state
derive specialized read views when justified

If a denormalized value changes, the update mechanism must be explicit: same transaction, CDC, event processing, or deterministic recomputation.

"We denormalized for performance" is not enough. State which read became cheaper, by how much, and how consistency is maintained.

32. Event sourcing and CQRS

In event sourcing, state transitions are first recorded as immutable events and read models are derived from them:

command
-> validation
-> event
-> materialized views

Benefits can include an audit trail, explicit causality, rebuildable read models, and the ability to create new projections from historical events.

Costs include event ordering, schema evolution, deterministic replay, suppression of repeated external effects, deletion/privacy requirements, and greater operational complexity.

CQRS and event sourcing are not defaults for ordinary CRUD. Use them when auditability, replay, and substantially different read models are actual requirements.

Replay must be deterministic. If an old event depends on today's exchange rate, current time, or a mutable remote response, replay can generate a different result unless the required historical input is stored or reproducibly queryable.

33. Schema evolution

Old and new application versions can coexist during deployment. Schema change is therefore a transition period, not a single deployment instant.

A safe expand-contract sequence is:

1. add the new structure
2. keep old code working
3. let new code read both forms
4. backfill if required
5. move all readers/writers
6. remove the old structure last

The same rule applies to API and message schemas: new readers should tolerate old data and old readers should tolerate new data where possible.

Binary schema formats can help with forward/backward compatibility through field IDs and defaults, but semantic compatibility matters too. Changing a field from meters to centimeters breaks meaning even if the wire type remains an integer.

34. Caching

Fix the source path first

A cache is not a substitute for a broken query. If a missing index is hidden behind a cache, the same failure returns on cache miss, restart, or mass expiry.

Cache layers

Common layers include:

• persistence context: transaction-local identity map,
• ORM second-level cache: entities across sessions,
• application cache: computed business results,
• remote shared cache: data across service instances,
• HTTP/CDN cache: response data closer to clients.

Each layer has a different invalidation boundary.

ORM second-level cache

It is valuable for frequently read, rarely modified reference data. On write-heavy transaction tables, invalidation and coordination can outweigh the read gain.

Native SQL, triggers, or another application can mutate the same table without the ORM cache automatically understanding the change.

Query cache

Many ORM query caches store entity identifiers rather than complete business responses. They must be evaluated together with the entity cache.

A high hit ratio is not meaningful if every write invalidates broad query-cache regions or if cache hits still trigger many entity loads.

Stampede

If a popular key expires simultaneously for hundreds of callers, they can all hit the source.

Techniques include single-flight/coalescing, TTL jitter, stale-while-revalidate, refresh-ahead, and source-aware rate limiting.

Negative caching

A costly "not found" can be cached briefly, but a long negative TTL can hide newly created data. Choose TTL from business semantics.

Caffeine: admission and refresh

Local-cache sizing is not always an entry count. If values have very different memory or load costs, use a weight model.

Caffeine's Window TinyLFU policy combines recency and frequency, helping protect the cache against scan-like patterns that can pollute plain LRU.

expireAfterWrite and refreshAfterWrite are different. Expiry can force a caller to wait for a new load. Refresh can asynchronously obtain a new value while serving the old value until the refresh completes. This can protect P99, but the acceptable staleness window is a business rule.

Measure more than hit ratio:

hit rate
miss rate
miss load cost
load failures
load P99
evictions / weight
cache size

A 99% hit rate is poor if the 1% misses can saturate the database.

Redis: pipelining is not atomicity

Redis pipelining reduces round-trip and socket overhead by sending several commands without waiting for each response. A giant pipeline can accumulate a large reply queue on the server, so bounded batches are safer.

Pipelining is a network optimization. MULTI/EXEC is transaction execution semantics. Do not use transactions merely to reduce RTT when atomicity is not required.

A two-level design:

L1 Caffeine
-> L2 Redis
-> DB

can reduce latency but creates a three-level invalidation problem. If L1 lives too long, Redis freshness becomes irrelevant. Use explicit versioning, invalidation events, or bounded TTLs.

35. Message queues

A queue does not remove load; it redistributes load over time.

Producer batching trades protocol overhead against waiting time:

larger batch -> higher throughput, potentially more latency
smaller batch -> lower wait, more protocol overhead

Partition count increases possible consumer parallelism, but ordering is normally scoped to a partition.

Consumer lag is not merely a broker metric. After an outage, processing a huge backlog at maximum speed can overload the database far beyond normal traffic.

Recovery should use gradual consumption, rate limits, and downstream-saturation-aware backpressure.

At-least-once processing requires an idempotent consumer.

Kafka producer path

Kafka producers batch records destined for the same partition. batch.size bounds the batch, while linger.ms controls how long a partially filled batch may wait.

Kafka 4.0 changed the default linger.ms from 0 to 5 ms, which illustrates a useful principle: a small controlled delay can produce fuller batches and improve both throughput and effective end-to-end latency under some workloads. The default is still not an SLO; measure it.

Compression trades network and broker I/O for producer/broker/consumer CPU.

acks=all and idempotent production strengthen durability and suppress duplicates caused by producer retries. Producer idempotence does not create exactly-once external side effects in a database or remote service.

Partition count is also an ordering boundary. If events for one aggregate must remain ordered, the partition key must preserve that invariant. More partitions also increase metadata, file, rebalance, and operational cost.

Kafka consumer path

The important question is not simply thread count; it is whether one poll's work completes within the consumer group's liveness/rebalance budget.

max.poll.records, fetch sizing, and max.poll.interval.ms interact. Fetching more records can improve transfer efficiency but can also make processing exceed the poll interval and trigger rebalances.

Offset commit must correspond to the completion point. Committing before work completes risks loss. Completing work and crashing before the commit creates replay. That is why idempotent consumers are the foundation of at-least-once processing.

Observe:

lag records
lag time
consume rate
produce rate
rebalances
processing P99
downstream saturation

Replay storms after outages should be limited by the safe recovery capacity of the database or downstream service, not by the consumer's theoretical maximum speed.

36. Backpressure and overload

A healthy system does not hide its capacity limit.

Prefer:

bounded queue
-> timeout
-> rate limit
-> load shedding

over an unbounded queue.

An unbounded queue converts overload into "accepted now, processed minutes later," eventually causing memory growth and timeout cascades.

Timeout budgets

If the top-level request has a 500 ms budget, giving three downstream calls 500 ms each is not a budget.

Partition the deadline across connection acquisition, queueing, query execution, and downstream work.

Cancellation must also release work. If the client timed out but the database query continues for minutes, the system did not shed load.

Retry storms

Layered retries multiply traffic:

1 user request
× 3 gateway attempts
× 3 service attempts
× 3 DB attempts
= 27 attempts

Own retries in one deliberate layer where possible, retry only classified transient failures, keep a total deadline, cap attempts, and use jitter.

37. Access design in Oracle RAC

RAC provides access to the same database from multiple instances. It does not remove coordination cost for shared data blocks.

Patterns that magnify cluster cost include:

• heavy writes to the same hot row,
• broad low-selectivity scans,
• unnecessary index maintenance,
• long transactions,
• row-by-row operations,
• hot blocks constantly transferred across instances.

The useful statement is not "RAC is slow." RAC makes coordination costs visible at scale that may be hidden on one node.

Adding RAC nodes does not remove a serialization point in the data model. If every transaction still waits on one hot key or lock, the work remains serialized.

38. Separate OLTP from analytical load

Protect the primary transaction database for short, selective, transactional, correctness-sensitive work.

Reports, historical scans, model training, time-series aggregation, and large exports can move to replicas, warehouses, columnar OLAP engines, or file/lakehouse systems.

CDC makes this separation more natural than repeatedly polling WHERE modified_at > ?, which can be fragile around indexes, clock/timestamp semantics, and deletes.

39. Search and vector indexes

B-tree is not the answer to every search problem.

Full-text search uses inverted indexes. Geographic access uses multidimensional structures. Similarity search uses vector indexes.

Approximate nearest-neighbor structures such as HNSW and IVF trade:

latency
memory
build cost
recall
update cost

A vector index does not replace a transaction primary key or relational constraint. Operational metadata can remain relational while semantic search is maintained as a derived vector view.

Keeping search as derived state makes it possible to rebuild the index from authoritative data after corruption or model changes.

40. JVM memory and garbage collection

Allocation first

High allocation means:

more frequent GC
more memory bandwidth
more cache pressure

Common hot-path sources are large DTO graphs, unnecessary intermediate collections, string construction, JSON serialization, boxing, and materializing entire result sets.

Increasing heap size does not remove allocation; it only postpones collection.

G1, ZGC, and Parallel GC

Collector selection is a trade-off among:

throughput
latency
footprint

G1 is a balanced general-purpose collector. ZGC is a strong candidate when pause budgets are tight. Parallel GC remains useful when aggregate throughput matters more than pause time.

Do not choose an "absolute fastest GC" without the exact JDK version, heap, allocation rate, live-set size, and workload.

G1 details

G1 divides the heap into regions. Large objects can become humongous allocations with special placement and reclamation behavior. If hot paths create objects near or above the humongous threshold, first identify the allocation source rather than blindly changing region size.

A pause target is not a hard guarantee. The collector attempts to meet it within the available heap and allocation pressure. A heap with no headroom limits GC flexibility; an unnecessarily large heap increases RSS and cache footprint.

Current ZGC behavior

Generational ZGC became the default ZGC mode in JDK 23, and current JDKs no longer require the historical separate ZGenerational selection. Old tuning notes that compare non-generational and generational ZGC should be treated as version-specific history.

ZGC does most expensive work concurrently to minimize pauses, which requires CPU and memory headroom. Low pauses are not free.

Off-heap and native memory

Process memory is more than Java heap:

heap
+ metaspace
+ thread stacks
+ direct buffers
+ code cache
+ GC structures
+ native libraries

Assigning the entire container limit to -Xmx creates OOM-kill risk.

Ask:

how large is the live set?
what is the allocation rate?
what headroom does GC require?
what is the native peak?
how close is RSS to the cgroup limit?

JIT, tiered compilation, and deoptimization

A long-running HotSpot process does not execute code in one fixed form. Interpretation and tiered compilation collect runtime profiles; hot methods receive progressively optimized code and inlining depends on observed call patterns.

A short benchmark is therefore not equivalent to a warmed production service. Class loading, compilation queues, caches, and branch/type profiles may still be changing.

When JIT assumptions become invalid, code can deoptimize and be recompiled. A latency spike is not automatically GC.

Use compilation/inlining logs for targeted investigations; JFR usually provides a more coherent production timeline.

CDS and AppCDS

Class Data Sharing reuses preprocessed class metadata from an archive to reduce startup work and some memory duplication. AppCDS extends the approach to application classes.

CDS is primarily a startup/footprint optimization. It does not automatically accelerate a request hot path.

Direct buffers, metaspace, and classloader leaks

If RSS rises while heap remains stable, inspect direct buffers, native libraries, thread stacks, and class metadata. A classloader that remains reachable can keep classes alive and grow metaspace even when heap-object counts look normal.

Native Memory Tracking separates HotSpot native categories but does not track every third-party native allocation. Combine NMT with operating-system memory maps or native profiling when required.

GraalVM Native Image

Native Image performs ahead-of-time compilation under a closed-world reachability model. Dynamic reflection, resources, JNI, and serialization may require reachability metadata.

Its major advantages can be millisecond-class startup and lower memory footprint. Steady-state throughput is not guaranteed to exceed a warmed JIT service.

Profile-Guided Optimization can instrument a native executable, collect a representative profile, and rebuild with that profile. The profile must represent production behavior; PGO can optimize the wrong workload just as effectively as any other profiler-guided technique.

Native Image is especially attractive for startup-sensitive, short-lived, scale-to-zero, or footprint-constrained processes. Long-lived high-throughput services should compare it against warmed HotSpot under the same traffic and resource budget.

41. Serialization and network

Fetching little data from the database and then constructing a huge JSON graph is not an optimization.

The response path includes:

DB bytes
-> Java objects
-> serialized bytes
-> network
-> client parsing

Discarding fields at the serializer is later and more expensive than never selecting them from the database.

HTTP connection reuse and multiplexing

Keep-alive amortizes TCP/TLS setup. HTTP/2 multiplexes several streams over one connection and compresses repeated headers with HPACK. This reduces connection churn but does not remove TCP-level loss behavior shared by streams on that connection.

HTTP/3 carries HTTP semantics over QUIC and changes transport behavior, especially for independent streams and lossy/high-latency networks. It does not fix a slow database, serializer, or application lock.

Compression

Compression is a trade-off among payload size, CPU, client support, bandwidth/RTT, and caching.

Large text responses can benefit from GZIP or Brotli. Tiny payloads can cost more CPU and framing than they save. Already compressed media such as JPEG, MP4, and ZIP should normally not be recompressed.

Jackson and the hot path

The Spring Boot 4 era overlaps with the Jackson 3 transition. Jackson 3 moved most Maven coordinates and Java packages from com.fasterxml.jackson to tools.jackson and changed API/default behavior.

Benchmark conclusions from older Jackson 2 Afterburner/Blackbird configurations should therefore be revalidated on the exact Jackson and JDK versions rather than treated as timeless tuning advice.

First shrink the object graph:

fewer fields
fewer nested objects
fewer String conversions
fewer temporary collections

Then evaluate serializer choices.

Binary formats such as Protobuf, Avro, or MessagePack can reduce size or CPU in some paths, but they introduce schema, compatibility, tooling, and client costs. If JSON already meets the SLO, changing the wire format is not automatically performance engineering.

42. Schema ownership and ORM

An ORM cannot behave correctly without understanding schema semantics. Primary keys, unique constraints, foreign keys, indexes, and column types are part of the application access model, not only DBA concerns.

Conversely, a database team cannot design the right physical structures without knowing application predicates, cardinality, traffic, and SLOs.

A healthy boundary is:

application team:
  access pattern, business invariant, volume, SLO

database team:
  physical plan, indexes, statistics, storage, maintenance

shared:
  measurement and change outcome

Automatic live-schema mutation by the application is risky in controlled production environments. Schema change should be reviewable, versioned, observable, and have a rollback/forward-recovery plan.

43. Performance testing

Test data must approximate production volume, skew, and relationship density. One million uniform records do not model real selectivity or hot-key distributions.

Use separate scenarios for:

• cold start,
• warmed caches,
• steady load,
• ramps,
• spikes,
• long soak,
• failure and recovery,
• replica lag,
• backlog replay.

A performance test should produce pass/fail criteria rather than only graphs:

P99 < target
error rate < target
DB acquisition < target
query count <= limit
heap stable
backlog not growing

After the load ends, verify that resources recover. A queue that never drains or a heap that keeps growing is not stable behavior.

Open and closed workload models

The load-tool brand matters less than the arrival model.

In a closed model, a virtual user waits for a response before issuing the next request. When the system slows, the generator also slows, hiding some of the queue pressure.

In an open model, arrivals are independent of service time:

target: 1000 requests/s
service slows
arrival remains 1000 requests/s

This better models an external traffic source and reduces coordinated-omission error.

k6 arrival-rate executors directly support open arrival models. Gatling can express constant and ramping arrival rates as well. If two tools produce materially different results for the same intended workload, validate the benchmark definition before tuning the application.

Traffic shapes

A single ten-minute constant-rate test is insufficient:

ramp      locate the capacity curve
steady    measure stable behavior
spike     expose queue/load-shedding limits
soak      expose leaks, compaction, GC, maintenance
recovery  expose backlog and reconnection behavior

Think time and pacing must represent the intended user/system behavior. If every virtual user repeatedly hits the same cache key, the test may be measuring cache locality rather than the service.

CI thresholds and regression analysis

Performance tests can be CI acceptance gates, but thresholds must exceed environmental noise. Pin hardware profile, kernel/runtime versions, dataset, and generator topology where practical. Use multiple runs when the expected difference is small.

Take JFR or flame graphs before and after a change. If P99 improves but allocation, block reads, CPU, and network work do not move, investigate test variance or a shifted bottleneck.

44. Capacity planning

Start from service-level requirements:

P99 target
error-rate target
peak requests/s
data growth
failure scenario

Then measure low-load service time, the saturation curve, and a safe operating point below the cliff.

A first instance-count estimate is:

peak load / safe capacity per instance

then add n-1, n-2, or broader failure headroom as required.

CPU alone is not a universal autoscaling signal. In I/O-heavy services, queue length, connection waiting, P99, and consumer backlog can show saturation earlier.

USE, RED, and deriving instance count from the bottleneck

Read RED at service level and USE for each critical resource. If CPU is 40% but the connection pool is saturated, a CPU-based capacity model is wrong.

Safe instance capacity is not the first point where errors appear. It is the point where the SLO begins to degrade with sufficient headroom for variance.

Burst and failure budgets

n-1 can represent more than one server. A database node, cache shard, availability domain, or network path can fail and redistribute work.

If failover leaves every surviving component at 100% utilization, the system is only moving from one failure into the next saturation cliff.

Cost per request

Cost is not only a cloud invoice. In private or isolated infrastructure it still means CPU-seconds, DB-seconds, IOPS, bytes transferred, GPU time, and operational capacity.

cost per work item = total resources consumed / completed work

If P99 improves while CPU per request doubles, record that trade-off.

Autoscaling must also include reaction time. If an instance takes 90 seconds to become ready, autoscaling cannot solve a five-second spike; headroom and load shedding must absorb it.

45. Optimization priority

The single slowest query is not always the highest-value target.

Example:

A: once/day × 5 s        = 5 s/day
B: 100/s × 20 ms         = 172,800 s/day of DB service time

A small improvement to B can create much more total capacity.

Prioritize by:

total resource consumption
× user impact
× change risk

46. Common mistakes

Copying tuning values

A maximumPoolSize=50 value from another system is not evidence. Database CPU, SQL service time, storage, network, and application-instance count may all differ.

Caching everything

A system that collapses after cache restart has hidden a capacity debt. Measure miss cost, not only hit rate.

Indexing everything

Unused indexes still consume write work, redo/WAL, storage, and cache.

Making everything an entity

Read-only lists and reports rarely need entity lifecycle. DTO/scalar projection is often clearer and cheaper.

Keeping transactions too broad

"One service method equals one transaction" can bind connection and lock lifetime to unrelated business work. The transaction should be the smallest boundary that protects the invariant.

Treating connections as capacity

More database sessions do not create more database CPU, IOPS, or lock parallelism.

Treating thread count as throughput

Waiting threads do not complete work. Virtual threads reduce the cost of waiting; they do not create downstream capacity.

Treating full scans as errors

The optimizer can intentionally choose a scan for low-selectivity predicates. Evaluate work, not ideology.

Treating native SQL as automatically faster

Native SQL can bypass ORM overhead, but it cannot rescue a bad plan, oversized result set, or excessive round trips. Bypass abstractions only for a measured reason.

Treating observability as free

High-cardinality metrics, verbose SQL logging, detailed NMT, and heavy tracing can become their own bottleneck. Measure diagnostic overhead and use high-cost tools deliberately.

47. Application decision sequence

If a read is slow:

1. is this data actually required?
2. are unnecessary rows returned?
3. are unnecessary columns returned?
4. are there too many queries?
5. are there too many round trips?
6. is there an appropriate index?
7. are cardinality estimates correct?
8. is the plan appropriate?
9. are locks, replica lag, or I/O dominating?
10. only then consider caching

If a write is slow:

1. is the transaction too long?
2. how many round trips occur?
3. is batching really active?
4. does the ID strategy break batching?
5. are there unnecessary indexes?
6. can row-by-row become set-based?
7. is there lock contention?
8. is WAL/redo/fsync the limit?
9. is storage write amplification the limit?
10. should this workload be separated?

If the system collapses under load:

1. where is the queue growing?
2. what is the arrival rate?
3. what is the service rate?
4. are timeouts effective?
5. are retries multiplying load?
6. is the pool waiting?
7. is the database saturated?
8. is there a cache stampede?
9. is backlog replay crushing the DB?
10. is load shedding available?

48. General conceptual framework

This entire note reduces to a small set of rules.

The bottleneck moves. When thread cost falls, the pool becomes visible. When SQL improves, serialization or network may become visible. A new bottleneck is often evidence of progress.

Every optimization has a cost. Indexes tax writes. Caches tax consistency. Batches tax latency. Replication taxes freshness or commit latency. Partitioning taxes operations. Denormalization taxes synchronization.

Abstraction does not remove physical work. JPA can hide SQL, but the database still executes plans against pages, locks, and logs.

Separate authoritative from derived state. Caches, search indexes, materialized views, and analytical copies are safer when they can be rebuilt.

Predictability is often worth more than peak speed. A stable P99 of 40 ms can be operationally better than a 10 ms average with regular two-second cliffs.

Keep the queue where it can be seen. A bounded application queue is easier to control than hidden connection, lock, or database work queues.

The fastest query is the query that never runs. Do not fetch unnecessary data, relationships, counts, or recompute results without need.

Correctness comes before performance. Removing unique constraints, isolation, identity, or idempotency to gain speed is not an optimization.

Tuning without measurement is a hypothesis. No profile, plan, or load test means no verified performance claim.

49. Fundamental distinctions

• Latency != throughput.
• Average != P99.
• Average of percentiles != combined percentile.
• Utilization != remaining capacity.
• CPU utilization != saturation.
• Thread count != real parallelism.
• Virtual threads != unlimited DB concurrency.
• Connection-pool size != database capacity.
• Connection wait != SQL execution time.
• flush != commit.
• flush != clear.
• Persistence context != second-level cache.
• readOnly != an absolute write-security boundary.
• IDENTITY != SEQUENCE.
• Primary key != merely a performance index.
• Unique constraint != application-side exists check.
• N+1 != one slow query.
• EAGER != an N+1 solution.
• JOIN FETCH != pagination.
• DTO != entity.
• Fetch size != total result size.
• Offset pagination != keyset pagination.
• COUNT(*) != a mandatory part of every page.
• Calling a batch API != proof of network batching.
• Row-by-row != set-based processing.
• Bind parameter != SQL text concatenation.
• Plan cache != result cache.
• Estimated rows != actual rows.
• Full scan != automatic error.
• Index scan != automatically faster.
• Index != free read acceleration.
• B-tree != the only index family.
• Partitioning != indexing.
• Partitioning != sharding.
• Replica != always-current copy.
• Asynchronous replication != zero data loss after commit.
• CDC != exactly-once external side effects.
• Cache != system of record.
• Materialized view != ordinary virtual view.
• OLTP != OLAP.
• Row storage != column storage.
• B-tree != LSM.
• Read amplification != write amplification.
• MVCC != serializability.
• Snapshot isolation != protection from write skew.
• Optimistic lock != complete business-invariant consistency.
• Deadlock != permanent failure.
• Retry != a solution for every error.
• Idempotency != an assumption that duplicates never happen.
• Event sourcing != the default for CRUD.
• CQRS != merely splitting read and write service classes.
• Schema change != one deployment instant.
• Native SQL != automatically fastest.
• Native image != a faster database.
• Larger heap != fewer memory problems.
• More servers != linear scaling.
• More metrics != better observability.
• Higher concurrency != higher throughput.
• Faster != better unless the cost is stated.

Zen summary

The note is long; the method is short:

measure
-> find the most expensive work
-> remove unnecessary work
-> bound concurrency
-> verify under the same load
-> record the trade-off

The first optimization is often subtraction:

• not running a query is better than making it faster,
• not fetching a row is better than serializing it faster,
• not retrying uselessly is better than creating more threads,
• keeping one transaction local is better than optimizing a distributed transaction,
• a small visible queue is better than a large hidden queue,
• stable P99 is better than an impressive average.

Zen here means removing unnecessary work, hidden state, and unverified tuning—not removing correctness, observability, or reliability.

References

• Köker, Muhammet Ali. Kurumsal Veri Tabanı İndeksleme Gereksinimi. T.C. İçişleri Bakanlığı Arge Notları, 12.12.2025.
• Köker, Muhammet Ali. Kurumsal Veri Tabanı Erişim Katmanı Optimizasyonu. T.C. İçişleri Bakanlığı Arge Notları, 22.12.2025.
• İnan, Umur. Spring Boot 4 Performance. CineTrack-based work on measurement, JVM, Spring, data, Kafka, capacity, and GraalVM performance.
• Kleppmann, Martin; Riccomini, Chris. Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. 2nd ed. O'Reilly Media, 2026.
• Mihalcea, Vlad. High-Performance Java Persistence. Leanpub version, June 9, 2020. https://vladmihalcea.com/books/high-performance-java-persistence/
• Red Hat / Hibernate. Hibernate ORM User Guide. https://hibernate.org/orm/documentation/
• Eclipse Foundation. Jakarta Persistence Specification. https://jakarta.ee/specifications/persistence/
• Spring. Spring Boot Reference Documentation. https://docs.spring.io/spring-boot/reference/
• Spring. Spring Framework Reference — Data Access and Transaction Management. https://docs.spring.io/spring-framework/reference/data-access.html
• Spring. Spring Boot 4 Common Application Properties. https://docs.spring.io/spring-boot/4.0/appendix/application-properties/index.html
• Oracle. Oracle Database SQL Tuning Guide. https://docs.oracle.com/en/database/oracle/oracle-database/
• Oracle. Oracle Database Concepts. https://docs.oracle.com/en/database/oracle/oracle-database/
• Oracle. Java SE 25 Garbage Collection Tuning Guide. https://docs.oracle.com/en/java/javase/25/gctuning/
• Oracle. Java SE 25 Troubleshooting Guide. https://docs.oracle.com/en/java/javase/25/troubleshoot/
• PostgreSQL Global Development Group. PostgreSQL 18 Documentation. https://www.postgresql.org/docs/18/
• PgBouncer. Configuration. https://www.pgbouncer.org/config
• HikariCP. About Pool Sizing. https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
• OpenJDK. JEP 444: Virtual Threads. https://openjdk.org/jeps/444
• OpenJDK. JEP 491: Synchronize Virtual Threads without Pinning. https://openjdk.org/jeps/491
• OpenJDK. JEP 474: ZGC: Generational Mode by Default. https://openjdk.org/jeps/474
• OpenJDK. JEP 525: Structured Concurrency (Sixth Preview). https://openjdk.org/jeps/525
• Micrometer. Histograms and Percentiles. https://docs.micrometer.io/micrometer/reference/concepts/histogram-quantiles.html
• async-profiler. async-profiler. https://github.com/async-profiler/async-profiler
• Grafana Labs. k6: Open and Closed Models. https://grafana.com/docs/k6/latest/using-k6/scenarios/concepts/open-vs-closed/
• Caffeine. Design, Efficiency and Refresh. https://github.com/ben-manes/caffeine/wiki
• Redis. Pipelining. https://redis.io/docs/latest/develop/using-commands/pipelining/
• Apache Software Foundation. Kafka Documentation. https://kafka.apache.org/documentation/
• GraalVM. Native Image Reference Manual. https://www.graalvm.org/latest/reference-manual/native-image/
• FasterXML. Jackson 3 Migration and Release Documentation. https://github.com/FasterXML/jackson
• IETF. RFC 9113: HTTP/2. https://www.rfc-editor.org/rfc/rfc9113
• IETF. RFC 9114: HTTP/3. https://www.rfc-editor.org/rfc/rfc9114
• Berenson, Hal; Bernstein, Philip; Gray, Jim; Melton, Jim; O'Neil, Elizabeth; O'Neil, Patrick. “A Critique of ANSI SQL Isolation Levels.” SIGMOD Record, 24(2), 1995.
• Gunther, Neil J. Guerrilla Capacity Planning. Springer, 2007.
• Gregg, Brendan. Systems Performance: Enterprise and the Cloud. 2nd ed. Addison-Wesley, 2020.
• Goetz, Brian et al. Java Concurrency in Practice. Addison-Wesley, 2006.