R11 — Read and write performance: what to do when the database is the bottleneck

R10 chose the store, replicated it and split it across machines. This section is about the step before that and the steps around it: the payment-status API is slow, or the end-of-day file takes three hours to load, and the database is the reason. Most of the fixes are cheaper than sharding, and an interviewer is grading whether you reach for them in the right order.

The situation

The payment-history screen took 80 ms at launch. A year later it takes four seconds at month end, the ledger import that used to run in ten minutes now runs past the cutoff, and someone has proposed "moving to Cassandra".

Before anyone moves anything, you need to know two things: what the database is actually doing on each request, and which of its resources has run out. Without those, every fix is a guess, and the expensive guesses take quarters.

So start with how a database does its work. Every fix in this section is a way of making one of these steps do less.

What a database actually does

Think of the database as a library.

A read finds the right pages (through an index or by scanning), brings them into memory, filters, sorts and joins them, and sends rows over the network.

A write appends to the WAL and flushes it to disk (fsync) at commit, updates the table page, updates every index on the table, takes locks, and often ships the change to replicas.

That gives you the whole diagnosis in two sentences:

• Reads get slow when the database reads too much (scans, large results, bad joins) or reads too often (N+1 queries, nothing cached).
• Writes get slow when each write does too much (many indexes, an fsync per row), when writers wait for each other (locks, hot rows), or when one machine cannot keep up (volume).

Every technique below either makes the database touch fewer pages, or makes fewer requests reach it, or makes writers stop waiting for each other.

The mental model

The seesaw

Every lever moves reads, writes and cost at once. ++ is a large gain, + a gain, − a cost, 0 no direct effect.

Before reading further, play with the levers. Pull each one and watch which side of the seesaw drops — the pattern to notice is that nearly every read lever costs the write side something, and only batching, queues and sharding help writes without taxing reads.

The trap to name: adding an index, a cache and a read model to fix a slow screen, on a table whose real problem is write throughput. Each one made the import slower. The seesaw is the reason you ask "is this table read-heavy or write-heavy?" before you touch it.

The decision path

Measurement first, then classification by symptom, then the cheapest fix that addresses the cause, then measure again.

1. MEASURE   slow query log · EXPLAIN · p95/p99 latency · CPU / IO / lock waits / connections · replication lag
2. CLASSIFY  which symptom?
   ├─ one endpoint / query is slow            → query and index        (Read rungs 1–2)
   ├─ everything slow at peak, reads heavy    → cache, replicas, scale (Read rungs 4–6)
   ├─ writes slow / timeouts / deadlocks      → batching, fewer indexes, shorter tx, hot rows (Write rungs 1–3)
   ├─ data grew huge, slower month by month   → partition, archive, then shard (Read rung 7 / Write rungs 6–7)
   └─ write volume beyond one machine         → async queue, shard, LSM store (Write rungs 4, 7, 8)
3. FIX       apply the cheapest rung that addresses the cause
4. VERIFY    re-measure; climb the ladder only if the numbers say so

Try the triage tool with a few symptoms. Notice that "add a replica" and "shard" almost never appear first — the plan it gives you starts with EXPLAIN nearly every time.

The two ladders

The fixes arrange into two ladders, cheapest rung first. The names "Read rung" and "Write rung" are used through the rest of this section.

Making reads faster

Read rung 1 — Query hygiene

Most slow reads are a query asking for more than it needs, or asking too many times.

Ask for less. SELECT id, status, amount instead of SELECT *, and filter in SQL rather than in Java. Every column you do not need is bytes read, held in memory and sent over the network.

The N+1 problem. Load 100 payments, then run one query per payment for its debtor account: 101 queries for one screen. Each is fast; together they are a round-trip storm. Fix it with a join, JOIN FETCH, @EntityGraph or batch fetching.

public interface OrderRepository extends JpaRepository<Order, Long> {
    @EntityGraph(attributePaths = {"customer", "lines"})
    List<Order> findByStatus(Status status);
}

Do not wrap indexed columns in functions. WHERE DATE(created_at) = '2026-10-01' cannot use an index on created_at, because the index is sorted by created_at, not by DATE(created_at). Write a range instead: created_at >= '2026-10-01' AND created_at < '2026-10-02'.

Pagination is where this bites hardest. OFFSET 100000 LIMIT 20 does not skip 100,000 rows — it reads them, sorts them, and throws them away, and it does so again for every page. A customer scrolling deep into their transaction history makes each page slower than the last. Keyset (seek) pagination remembers where the last page ended and starts there: WHERE id > :lastSeenId ORDER BY id LIMIT 20. With an index on the sort key, page 5,000 costs the same as page 1.

@Query("select o from Order o where o.customerId = :cid and o.id < :lastId order by o.id desc")
List<Order> nextPage(@Param("cid") long cid, @Param("lastId") long lastId, Pageable limitOnly); // PageRequest.of(0, 20)

Drag the slider deeper into the result set and compare rows read. OFFSET grows linearly with the page number; keyset stays flat.

The price of keyset: no "jump to page 400". For statements and feeds that is fine — people scroll, they do not jump.

Read rung 2 — Indexes, the biggest win most of the time

An index is a sorted structure — usually a B-tree — that maps column values to row locations. Lookup cost grows like log(n): a million rows is roughly 3–4 page hops instead of reading everything.

The rules that decide whether your index is actually used:

Step through the walkthrough: a full scan, then a B-tree lookup, then a composite index used correctly and incorrectly, a covering index, and finally what the same indexes cost an insert. The last step is the one people forget.

Verify with EXPLAIN (or EXPLAIN ANALYZE). Look for full table scans (Seq Scan in Postgres, type: ALL in MySQL), the ratio of rows examined to rows returned, and filesorts or temporary tables. A query that examines 400,000 rows to return 20 has a missing or wrong index, however fast it looked in test.

Declare the index next to the entity, matching the query it serves:

@Entity
@Table(name = "orders", indexes = {
    @Index(name = "ix_orders_customer_created", columnList = "customer_id, created_at")
})
public class Order { @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "order_seq")
    @SequenceGenerator(name = "order_seq", sequenceName = "order_seq", allocationSize = 50) Long id;
    @Column(name = "customer_id") Long customerId; @Column(name = "created_at") Instant createdAt; /* ... */ }

The payments version. The idempotency key from R3 is enforced by a unique index on that column. That index is not a performance choice — it is the correctness guarantee that a retried request cannot create a second payment — but it is also the index every duplicate check reads, so it earns its write cost twice.

Read rung 3 — Connections and round trips

Opening a database connection is expensive, so you reuse them through a pool (HikariCP is Spring Boot's default). Bigger is not better: a pool a little larger than the database's CPU cores × 2 is a common starting point, and too many connections cause contention rather than throughput. The section below sizes it properly.

Each round trip also costs network latency — roughly 0.5–1 ms inside a data centre. Fetch related data in one query, use IN (...) lists, or batch-load. Twenty sequential queries at 1 ms each is 20 ms of pure waiting before the database has done any work.

Connection pooling, properly

Why pools exist. In Postgres each connection is a separate backend process with its own memory, and establishing one costs a TCP handshake plus authentication. Opening a connection per request would be slow and would exhaust the server's memory long before it exhausted its CPU. R1 explains what a connection is and what one costs.

Sizing, from Little's Law rather than superstition. More connections than the database can usefully run concurrently makes things slower, because they contend for the same cores and locks.

pool size ≈ throughput per instance × average query time
          = 200 req/s × 0.015 s  ≈  3   →  round up for burst, say 10

Ten is plausible; the fifty someone copy-pasted is not. R1 sizes every pool on the request path together — Tomcat threads, this pool and the HTTP client pools — and checks the fleet's total against the database. And the number that actually reaches the database is pool size × task count, which changes every time autoscaling fires — this is where pooling and autoscaling collide, and it is worth stating explicitly in an interview.

HikariCP settings that matter, with the reasoning:

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=10            # = max; avoids churn in steady state
spring.datasource.hikari.connection-timeout=2000    # fail fast; don't queue for 30s
spring.datasource.hikari.max-lifetime=1500000       # 25 min: shorter than any idle
                                                    # timeout in the DB, NLB or NAT gateway
spring.datasource.hikari.validation-timeout=1000
spring.datasource.hikari.leak-detection-threshold=20000

connection-timeout is the one people get wrong. A long timeout means a struggling database turns into a thread pool full of waiters and an unresponsive service; failing fast lets the circuit breaker (R14) do its job.

Watch hikaricp.connections.pending — Micrometer exposes it, and a non-zero value means requests are queuing for a connection, which is the earliest signal that you are at the wall. Also watch hikaricp.connections.acquire timings and hikaricp.connections.usage.

A proxy in front, once you scale out. RDS Proxy or PgBouncer multiplexes many client connections onto few database ones, and it also holds connections through an Aurora failover so your application reconnects faster. The caveat to know: transaction-level pooling breaks session state — session-scoped advisory locks (R8), SET statements, some prepared-statement patterns and WITH HOLD cursors no longer behave as you expect, because you are not guaranteed the same backend across statements. Use transaction pooling deliberately and keep anything session-scoped off it.

Making reads faster, continued: copies and precomputation

Rungs 1–3 make each query cheaper. Rungs 4–8 stop queries reaching the primary at all, by answering them from somewhere else.

Read rung 4 — Caching

Hot, read-mostly results go into memory (Caffeine) or Redis. It is the biggest read win on the ladder and costs you staleness. R12 covers when, what, how and how consistent; the payments short version is in the section after next.

Read rung 5 — Precompute: denormalise, summary tables, materialised views

If the same expensive answer is computed on every request, compute it once and store it.

• Denormalised column. Store order_count on the customer row instead of running COUNT(*) every time.
• Summary table or materialised view. The stored result of an expensive query, refreshed on a schedule — daily settled volume per merchant, for example.

The cost lands on the write side. Every write must keep the copy correct, which is more work per write and a chance of drift. In a payments system, a precomputed total is a derived number: the ledger entries are the truth, and the summary is something you reconcile against them (R6), never something you post from.

Read rung 6 — Read replicas

The primary takes writes and streams changes to replicas; reads go to the replicas. This scales read throughput, not write throughput — every write still lands on one primary. R10 covers how replication works and why replicas lag; the rules for using them are in the next section.

Read rung 7 — Partitioning and sharding

Partitioning splits a big table into pieces inside one database — one partition per month, say. A query with WHERE created_at in October reads only the October partition (partition pruning), and dropping a year of old data is dropping partitions, which is instant.

Sharding splits data across machines. It helps reads and writes, and makes cross-shard queries and transactions hard. R10 covers choosing a partitioning scheme and building a shard key.

Read rung 8 — Purpose-built read stores

When the shape of the question is wrong for the transactional database, give it its own store.

Read replicas, caching and CQRS, for juniors

Read replicas copy the primary asynchronously. They are the cheapest read capacity you will ever buy, with one catch that causes real bugs: replication lag. Write, then immediately read from a replica, and you may not see your own write. The rule is read your own writes from the primary, and send only tolerant traffic — reports, search, dashboards — to replicas.

Caching is the next cheapest win and the easiest to get wrong. Cache reference data that changes rarely: routing tables, BIN ranges, bank directories, FX rates with an explicit staleness bound. Never cache a balance you are about to act on. Know the three failure modes by name — stampede (a popular key expires and a thousand requests hit the database at once; fix with a lock or early refresh), penetration (requests for a key that does not exist bypass the cache every time; cache the negative result), and staleness (you served the old value; decide deliberately whether that is acceptable, per key).

CQRS sounds grand and means something simple: the shape that is good for writing is rarely the shape that is good for reading. Keep the write model normalised and correct, and build separate read models — a denormalised table, a search index — fed by the events from R4. The cost is that read models are eventually consistent, and the UI has to be honest about it. Taken one step further, the write side can store only the events themselves and derive its state by replaying them — event sourcing, covered in R1.5's architecture styles, with an animation of appends, conflicts, projections and replays.

R12 takes caching much further — strategies, invalidation races, TTLs and eviction.

Routing reads to a replica in Spring

The cleanest way to apply "tolerant reads to replicas" in Spring is to let the transaction declare it. Methods marked @Transactional(readOnly = true) go to the replica; everything else goes to the primary.

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "replica" : "primary";
    }
}
@Bean @Primary
DataSource dataSource(DataSource primary, DataSource replica) {
    RoutingDataSource r = new RoutingDataSource();
    r.setTargetDataSources(Map.of("primary", primary, "replica", replica));
    r.setDefaultTargetDataSource(primary);
    r.afterPropertiesSet();
    return new LazyConnectionDataSourceProxy(r);   // decide the target when the first statement runs, after readOnly is known
}
// @Transactional(readOnly = true) → replica; everything else → primary

The part people miss: LazyConnectionDataSourceProxy. Without it, Spring fetches the connection when the transaction begins — before the read-only flag is set — and every query goes to the primary. And readOnly = true is a routing decision, not a correctness one: the payment-status read that a customer makes straight after submitting must not be marked read-only, or it will meet replication lag.

Making writes faster

What one write really costs

Before choosing a fix, know what you are paying for on each commit:

1. Append to the WAL and fsync at commit — wait for the disk.
2. Update the table page and every index.
3. Take row locks, and wait if someone else holds them.
4. Possibly wait for replicas, if replication is synchronous.

Each write rung attacks one of these four costs.

Write rung 1 — Batch

One commit per row pays the fsync cost every time. One commit per 500 rows pays it once. For a bulk load this is routinely the difference between hours and minutes.

Run the batching demo: 1,000 inserts committed one at a time, then in batches of 50, then 500. Watch the number of fsyncs, not the number of rows — the rows are the same in all three runs.

spring:
  jpa:
    properties:
      hibernate:
        jdbc.batch_size: 500
        order_inserts: true
        order_updates: true
  datasource:
    url: jdbc:postgresql://db:5432/app?reWriteBatchedInserts=true
    hikari:
      maximum-pool-size: 20

jdbcTemplate.batchUpdate(
    "insert into ledger_entry (id, account_id, amount, created_at) values (?, ?, ?, ?)",
    entries, 500,
    (ps, e) -> { ps.setLong(1, e.id()); ps.setLong(2, e.accountId()); ps.setBigDecimal(3, e.amount()); ps.setTimestamp(4, Timestamp.from(e.createdAt())); });

Hibernate silently stops batching inserts when IDs use GenerationType.IDENTITY.

It has to insert each row to learn its ID, so batch_size is ignored and you are back to one statement per row. Use a sequence with a pooled allocationSize (as in the Order entity above), so Hibernate can hand out 50 IDs from memory and batch the inserts.

The payments version. A ledger is append-only: a posting is a set of new entry rows, never an update to an old one. That makes ledger writes the easiest kind to batch — inserts, no read-modify-write, no lock on a shared row. A settlement or end-of-day file is exactly the workload batching was built for. Batch size is a throughput choice; the transaction boundary is a correctness one. The debit and credit legs of one posting commit together or not at all, whatever the batch size.

Write rung 2 — Make each write cheaper

Drop unused indexes. Check index usage statistics; an index nobody reads is pure write cost on every insert.

Use sequential primary keys. Auto-increment, Snowflake-style IDs (R0), ULID or UUIDv7 all append at the right-hand end of the B-tree, so inserts touch the same few hot pages. Random UUIDv4 keys land all over the tree, causing page splits, cache misses and bigger indexes.

Keep rows narrow and tables quiet. Avoid heavy triggers and cascades on hot tables, and move large blobs — the raw ISO 20022 message, a PDF — out of the hot row into their own table or object storage.

Write rung 3 — Reduce waiting

Contention is the write problem that more hardware does not fix. Two writers wanting the same row run one after the other no matter how many cores you buy.

Keep transactions short. No remote calls and no user think-time inside a transaction. A transaction that calls a sanctions service while holding a row lock holds it for the length of that call. (R2 and R5 explain why the remote call should not be in the transaction anyway.)

Access rows in a consistent order to avoid deadlocks — always lock the lower account ID first, for example.

Optimistic locking holds no lock at all: read with a version, write with WHERE version = ?, and retry on conflict. Excellent when conflicts are rare.

@Entity class Product { @Id Long id; int stock; @Version long version; }
// concurrent update with a stale version → OptimisticLockException → retry

Job queues in a table use SELECT ... FOR UPDATE SKIP LOCKED, so many workers each grab different rows without blocking one another — the same mechanism an outbox relay (R4) uses to scale out.

@Query(value = "select * from payment_job where status = 'PENDING' order by id limit 50 for update skip locked", nativeQuery = true)
List<PaymentJob> claimBatch();

Hot rows

A hot row is one row that everybody updates — a global counter, a popular product's stock, a single account that a large share of payments credit. Every writer queues on its lock, so throughput is capped at one update per lock hold, whatever the hardware.

The general fixes:

-- 8 rows per counter instead of 1
UPDATE like_counter SET n = n + 1 WHERE post_id = ? AND shard = ?;   -- shard = random 0..7
SELECT SUM(n) FROM like_counter WHERE post_id = ?;

Fire 40 updates at one row, then at 8 sharded rows, and watch the queue of waiting writers. One row serialises everything; eight rows let eight writers proceed at once.

The payments version, and why it is different. Counter sharding and write-behind are fine for likes and page views. They are wrong for a balance you authorise against: a sharded balance cannot answer "is there enough?" without reading every shard under lock, and write-behind loses money on a crash. For a hot account, the defensible answers are to keep the balance derived from append-only ledger entries, and to serialise updates per account — partition the work by account so one consumer owns each account's sequence. R9 covers per-key ordering on Kafka partitions; R13 covers where an account's writes are homed.

Write rung 4 — Absorb spikes with async writes

Accept the request, put it on a queue (Kafka, SQS, RabbitMQ), reply "accepted", and let workers write at a steady pace in batches. This is load leveling: the database sees the average arrival rate, not the peak — a flash sale, an end-of-day file, a cutoff rush.

The costs are eventual consistency and retries. A queue delivers at least once, so every write behind it must be idempotent — a unique key or idempotency key that turns the second attempt into a no-op (R3). R9 covers queues and consumers.

Write rung 5 — Tune durability, carefully

Group commit lets the database flush many transactions' WAL records with one fsync. It is usually on by default and costs nothing in safety.

Settings like PostgreSQL synchronous_commit = off or MySQL innodb_flush_log_at_trx_commit = 2 go further: the database acknowledges a commit before it is on disk, trading a small crash-loss window for speed.

Never relax durability for money.

Use these knobs only for data you can afford to lose — metrics, logs, clickstream. A payment or a ledger entry that was acknowledged and then vanished in a crash is a payment your system told someone had happened. No throughput gain is worth that, and it is worth saying explicitly.

Write rung 6 — Partition and archive

Time-partition append-heavy tables — events, transactions, audit logs. New writes land in one small, hot partition with small indexes that fit in memory.

Archive or drop old partitions instead of running giant DELETEs, which take locks, bloat the WAL and leave dead rows behind. For payments, "drop" means after the retention period, and usually "move to cold storage" first.

Write rung 7 — Shard writes

When one primary cannot take the write volume, split data by a shard key — customer, tenant, region — so each machine takes a slice of writes. Pick a key that spreads load and keeps related data together. R10 covers this in depth.

Write rung 8 — Write-optimised storage

This is the most expensive rung: new technology, new operational skills, a different consistency model. Reach it with numbers that say rungs 1–7 are exhausted.

Symptom, cause, first fix

The table to have in your head when someone says "the database is slow".

What real systems did

The lesson across all five: nobody started at Write rung 8. Stack Overflow is the reminder that the cheap rungs carry most systems a very long way, and Instagram and Discord are what it looks like when the numbers finally said otherwise.

The one-page version

MEASURE   slow log · EXPLAIN · p95 · CPU / IO / locks / connections · lag
READS     query hygiene → index (composite, covering) → pool / round trips → cache
          → precompute → replicas → partition / shard → search / OLAP / CQRS
WRITES    batch → fewer indexes, sequential keys → short tx, no hot rows, optimistic locks
          → queue + workers → durability knobs (never for money) → partition / archive → shard → LSM
SEESAW    indexes, copies and denormalisation speed reads and slow writes
RULE      cheapest layer first; climb only when the numbers say so

What to say in the interview

"Before I change anything I measure — the slow query log, EXPLAIN, and which resource is saturated: CPU, I/O, locks or connections. Then I fix the cheapest layer that addresses the cause: the query and its index first, then caching and replicas for read load, then batching, shorter transactions and per-account serialisation for writes, and only then partitioning and sharding. I keep in mind that every read optimisation is a tax on writes, so I decide which side this table serves. For money specifically, the ledger stays append-only and batched, idempotency is a unique index, hot accounts are serialised rather than sharded, and I never relax durability to buy throughput."