Transactional Systems and OLTP

Ledgerline has 12,000 units of SKU 842. A release drives 2,200 reservation attempts per second. The business rule is short:

available = committed stock - active reservations - completed sales
available >= 0

The first design stores available in one row and decrements it transactionally. It is easy to explain and impossible to oversell if the database serializes the updates correctly. It is also a single contested interval. The release does not ask whether rows, documents, or key-value records are fashionable. It asks:

Which facts must be observed and changed together to preserve the invariant, and how much contention, logging, failure coupling, and recovery work does that boundary create?

That is the controlling question for online transaction processing. Shape transactions around invariants and contention, not merely logical entities. The fastest transaction is often the one that coordinates the least data.

Draw the invariant before the schema

A transaction is a promise that a group of observations and effects will be interpreted together at a declared isolation and durability boundary. Begin with the decision, not the tables.

For each operation, record:

• the invariant it may change;
• authoritative facts read and written;
• keys or ranges that can conflict;
• whether conflicts commute, wait, abort, or retry;
• the acknowledgment and ambiguity boundary;
• required read visibility after acknowledgment; and
• the compensation or reconciliation path when the operation spans systems.

Ledgerline’s purchase flow contains several different invariants:

Putting the entire “order entity” in one distributed transaction can add owners that do not participate in the invariant. Splitting available, reservations, and sales merely because they are separate tables can weaken the invariant. The boundary follows the rule that must remain true.

A contention graph makes the load-bearing facts visible. Nodes are keys, ranges, indexes, sequences, predicates, and external authorities. An edge means two operations may require incompatible access or validation over the same logical fact. Weight edges with arrival rate, hold time, skew, and retry probability. A graph with one high-degree SKU node predicts a hot authority even when storage and CPU averages look healthy.

Choose a state and access shape together

Rows, documents, key-value records, and relations are not mutually exclusive database religions. They expose different access and update boundaries.

Row-oriented relational state is strong when operations need selective predicates, joins, constraints, multiple access paths, and transactional changes across a modest set of facts. Its optimizer and indexes are part of the runtime contract.

Document state can align one aggregate’s reads and writes, reducing joins and network calls. It becomes awkward when high-churn subfields rewrite large documents, independent facts need different contention boundaries, or cross-document invariants dominate.

Key-value state makes identity and routing explicit and can give predictable point access. Secondary queries, range scans, uniqueness, and cross-key invariants then need deliberate indexes, directories, transactions, or derived views.

Relational decomposition with derived read models preserves normalized authority while serving endpoint-shaped projections. It trades synchronous query work for propagation, rebuild, reconciliation, and version semantics.

Evaluate shape by operation:

The same logical order may therefore have a normalized transactional authority, a customer-history index, and a search projection. Chapter 38’s rule still applies: derived shapes do not inherit write authority.

Indexes buy reads with write and recovery work

An index is an ordered or otherwise searchable copy maintained with authoritative changes. Select it from query predicates, ordering, projection, cardinality, and update pattern.

A composite index’s column order should serve the actual equality, range, and order requirements. A partial index can exclude irrelevant states when its predicate is stable and query-visible. A covering index includes enough output columns to avoid a base-record visit when the engine and visibility state permit it. Covering is not free: extra leaf width reduces fan-out, consumes cache and storage, increases write amplification, and lengthens build and restore work.

For each proposed index, record:

queries helped and required latency population
predicate, order, projection, and expected selectivity
reads avoided and bytes/pages touched
insert/update/delete maintenance demand
changed-field frequency and hot-page risk
storage, cache, compaction/vacuum, backup, and restore cost
build, validation, rollback, and removal plan

The counterexample to “indexes make reads faster” is a write-heavy table whose added indexes dominate log volume and cache churn. An unused index can still tax every relevant write. Conversely, deleting an apparently redundant index can break one rare but critical close-of-day query. Use workload evidence and a rollback window.

A plan is a hypothesis with estimates

The query optimizer chooses physical work from a logical query, available access paths, statistics, cost parameters, and current engine rules. Its plan estimates are not observations.

Ledgerline deploys a filter on a correlated pair of columns. The planner estimates 120 outer rows and one inner match per row, making a nested loop appear cheap. The observed execution returns 38,400 outer rows:

Nested Loop
  estimated outer rows:       120
  actual outer rows:       38,400
  estimated inner rows/outer:   1
  actual inner executions: 38,400

cardinality error = 38,400 / 120 = 320x

The loop algorithm is not inherently wrong. It is wrong for this observed cardinality, inner cost, cache state, and concurrency. A hash or merge strategy might reduce repeated inner work, but could require memory, sorting, or spill. A better composite index or query shape may remove the join. More representative or extended statistics may repair the estimate. The correct response begins with estimate-versus-actual rows, loops, time, buffers/I/O, memory/spill, and parameter values—not with forcing a favorite join.

Plan instability appears when a small change in statistics, parameters, data distribution, memory, index availability, engine version, or prepared-statement behavior crosses a cost boundary. Track normalized query identity, plan identity, estimate error, execution distribution, resource demand, and deployment/statistics timeline. Canary important query classes with production-shaped parameters and cold/warm state.

Do not average away plan populations. One query fingerprint can serve tiny tenants and a whale tenant, recent and historical ranges, or common and rare values. Parameter-sensitive evidence may justify separate query shapes, admission classes, or routing.

Conflict control is an application-visible behavior

Locking, multiversion concurrency control (MVCC), optimistic validation, and serial execution choose where readers wait, writers wait, versions accumulate, or transactions abort. None eliminates coordination.

Locks make incompatible access wait or fail. Their behavior depends on lock granularity, acquisition order, duration, deadlock detection, and the predicate/range semantics needed for the invariant. Keep transactions short in wall time and avoid user/network pauses while holding locks.

MVCC lets readers observe a snapshot while writers create new versions. It reduces some read/write interference but still requires write conflict handling, version cleanup, visibility metadata, and protection for predicates or serializable invariants. “Readers do not block writers” is not “the system is lock-free.”

Optimistic validation performs work and checks at commit whether observations remain valid. It is attractive when conflicts are rare and work is cheap. Under a hot key, repeated aborts multiply reads, CPU, log work, and latency.

Serialized ownership routes operations for a fact through one ordered executor or partition owner. It makes order explicit and can avoid distributed conflict, but the owner is a queue and availability boundary.

The application must classify conflict outcomes. A serialization failure may be safely retryable if the transaction has no untracked external effects, uses the same logical-operation identity, and retains enough deadline and retry budget. A uniqueness violation may be a terminal business outcome. A deadlock victim can be retried, but recurring deadlocks indicate acquisition-order or boundary defects.

Observe conflict rate by operation/key class, lock/validation wait, abort reason, attempts per logical operation, transaction age, oldest snapshot, version cleanup pressure, and post-abort side effects. An average transaction duration can conceal one long administrative transaction holding back cleanup or index validation.

Redesign the hot inventory counter without overselling

Ledgerline’s single-row critical interval is modeled at 0.65 ms. Its optimistic upper bound is:

serialized capacity = 1,000 ms/s / 0.65 ms/attempt
                    ≈ 1,538 attempts/s

offered attempts = 2,200/s
modeled utilization = 2,200 / 1,538 ≈ 1.43

At these constant assumptions, the queue is unstable before retries. Adding replicas does not increase one write authority’s serialized capacity. Blindly sharding the counter can oversell because each shard may believe it owns the full stock.

Ledgerline considers three designs.

One authoritative counter

Keep one row/key owner, enforce available >= 0, bound admission near 1,538/s below a safe operating limit, and queue/reject excess attempts. This is the simplest correct design. Use it when hot events can be shaped, the business accepts bounded rejection/wait, or contention is rare.

Reservation ledger plus derived available count

Append uniquely identified reservation decisions under one SKU authority and derive the displayed count. This improves audit/reconciliation and can batch some materialization, but it does not remove the invariant’s serialization if every decision still needs the same remaining-stock check.

Bounded escrow owners

The global authority allocates exactly 12,000 reservation tokens across 12 fenced cell owners, 1,000 each. A cell may reserve only from its local token balance. The invariant becomes:

sum(tokens allocated to owners) <= committed stock
tokens issued by owner <= its fenced allocation

Local reservations no longer contend on the global counter while tokens remain. Token transfer is an authoritative transaction: decrement the donor allocation and advance versions/epochs before the receiver can issue. A stale owner cannot mint or reuse transferred tokens. Returns and expiry restore tokens exactly once by reservation identity.

Escrow changes failure behavior. One cell can exhaust its tokens while another has spare capacity. Transfers add latency and an availability dependency. A lost or partitioned owner strands its allocation until recovery or a proven reclaim protocol establishes that old authority is fenced. Product routing may steer demand toward owners with tokens, subject to geography and fairness.

Reject escrow when the invariant cannot be represented as conserved units, when units have interdependencies, when allocation churn dominates, or when fencing/reconciliation cannot prove that two owners never spend the same unit. It preserves the invariant by narrowing authority, not by relaxing correctness.

The deterministic fixture at examples/performance-engineering-system-design-handbook/part-05/transactional-oltp/ verifies the capacity and allocation arithmetic.

The commit path defines “success”

A transactional write crosses several boundaries:

client admission
-> connection/session checkout
-> begin and snapshot/serialization context
-> reads and lock/validation acquisition
-> invariant check
-> record/index changes
-> log record creation
-> required log flush/replication acknowledgment
-> commit visibility
-> response delivery

Write-ahead logging records changes before the corresponding data pages need to reach durable storage. Group commit can amortize a flush across several transactions, trading a small batching delay for fewer flush operations. The acknowledgment contract must name what is durable: local log, remote receipt, remote durable log, remote apply, or some other boundary. A successful network write to the database process is not necessarily a durable business commit.

Response loss after commit creates ambiguity. The client needs a stable operation identity and a lookup/replay path. The server must return the original terminal outcome for an identical replay and reject a conflicting payload under the same key. Retention must cover realistic retry and reconciliation windows.

Commit latency evidence separates pool wait, transaction work, conflict wait/attempts, log insertion, group-commit wait, flush, replication, visibility, and response. Storage latency alone cannot explain a synchronous replica wait; transaction time alone can hide pool starvation.

Recovery is part of the commit promise. Log retention, checkpoints, page state, transaction outcome, replica positions, and recovery algorithms determine whether acknowledged transactions reappear correctly after crash. A faster normal commit that makes restart or data loss unacceptable is not a performance improvement.

Pools are admission controllers with session state

A database connection pool bounds concurrent sessions, memory, transactions, and server work. Size it from measured transaction residence, database capacity, operation classes, and headroom—not application thread count.

At steady state, concurrency ≈ throughput × mean residence time is a check. Residence includes server execution, lock waits, log flush, network, and time the application holds the connection between statements. The most effective pool optimization is often shortening the checkout interval or transaction, not adding connections.

Separate high-value short transactions from administrative scans and backfills. One FIFO pool can let long work occupy every connection. Admission should consider operation, tenant, expected service demand, current transaction age, and remaining deadline.

Session state complicates reuse. Transaction isolation, temporary objects, prepared statements, role, locale, time zone, search path, and application variables can leak or invalidate assumptions across borrowers. Define reset behavior and test it. Transaction-pooling proxies may not preserve features that rely on session identity; treat compatibility as an implementation claim.

When the pool saturates, reject or queue within a bounded age before checkout. Holding thousands of application tasks while waiting does not create database capacity. Expose waiters, oldest wait, checked-out duration, transaction state, and owner operation.

Replica reads need a visibility contract

Read replicas can add read capacity, locality, and recovery options. They also introduce apply delay, stale visibility, conflict with long reads, and failure/reconnect behavior.

After a successful write, Ledgerline chooses per operation:

• route the immediate read to authority;
• carry a commit/log position and wait on a replica up to a sub-deadline;
• read the replica only if its applied position satisfies the required version;
• return the committed representation directly; or
• accept bounded stale state for a noncritical view and label its as-of point.

“Read after write” needs a subject and scope. The same session may require its own order immediately, while another user’s catalog view can lag. Monotonic reads may matter even without latest-state reads; routing from a newer replica to an older one can make data disappear.

Measure commit-to-visible age and source/apply positions, not just byte lag. A small byte gap can contain an expensive transaction. A replica can be caught up in transport but blocked in apply. Failover changes timeline, authority, pool connections, prepared state, caches, and client retry behavior; validate the entire transition.

Partition around local invariants

Partitioning improves capacity when most transactions and invariants remain within one partition. Customer ID may localize order history; SKU-location may localize inventory. A transaction that touches five partitions pays five ownership, logging, failure, and recovery boundaries even if the API call is singular.

Distributed transactions are justified when one invariant truly spans owners and the business requires atomic outcome across them. They add coordinator/participant state, prepare/commit phases, timeouts, in-doubt recovery, log retention, and operator procedures. Do not avoid them by silently weakening a required invariant, and do not add them merely to preserve a convenient object model.

Alternatives include:

• redesigning ownership so the invariant is partition-local;
• reserving bounded rights such as escrow tokens;
• using a durable workflow with explicit intermediate states and compensation;
• accepting an asynchronous derived view; or
• serializing the decision through one authority while effects follow independently.

Each changes availability, latency, user-visible states, and recovery. “Use a saga” is not a semantic answer until compensation and irreversible effects are defined.

Global sequences and counters deserve the same scrutiny as hot inventory. If only uniqueness is required, partitioned ranges or composite identities may avoid total order. If strict order has business meaning, pay for one ordering boundary and state its throughput/failure limit.

Online change is foreground competition

Schema migrations, backfills, and index builds consume CPU, I/O, cache, log bandwidth, locks, connections, replica apply, backup space, and operator attention. Treat them as a workload class with admission and abort—not as deployment metadata.

Use expand/migrate/contract when application and schema versions overlap:

1. add backward-compatible representation;
2. deploy writers/readers that tolerate both forms;
3. backfill under stable identity, checkpoint, and foreground guards;
4. validate counts, constraints, samples, and semantic equivalence;
5. switch reads or enforce the new constraint through a canary;
6. observe a rollback window; and
7. remove the old form only when no supported reader, replay, or restore path needs it.

Dual writes can diverge under partial failure. Prefer one authoritative mutation plus a recoverable derivation where possible. If dual write is required, define ordering, idempotency, partial outcome, repair, and which copy controls reads during mismatch.

Plan the index as two lower bounds

Ledgerline wants a new index on a 1.2 TiB order table. Production measurements allow the build 54 MiB/s without violating foreground write p99. The chosen engine’s concurrent build procedure requires two table scans plus transaction waits and validation. The modeled scan lower bound is:

1.2 TiB = 1.2 × 1,048,576 MiB = 1,258,291.2 MiB

one scan = 1,258,291.2 MiB / 54 MiB/s
         = 23,301.689 s
         ≈ 6.47 h

two scan lower bound ≈ 12.95 h

This is neither completion time nor a generic claim about all databases. It omits transaction waits, random access, cache effects, log generation, retries, validation, throttling changes, and failure.

Measured maintenance cost for the candidate index is 0.22 CPU-ms per relevant write. At 18,000 writes/s:

18,000 writes/s × 0.22 CPU-ms/write = 3,960 CPU-ms/s

That continuing cost is nearly four fully busy CPU cores before other resource effects. The rollout record must justify it with the query benefit.

Ledgerline gates the change:

• baseline foreground/query/replica/backup evidence;
• throttle with write-p99, queue, log, and replica-lag guards;
• build one scoped target;
• detect and remove an invalid failed artifact;
• validate structure and semantic query results;
• canary the plan with representative parameters;
• adopt only after benefit and steady write cost are measured; and
• retain a reversible query/index choice through the observation window.

Long-lived transactions can delay phases or cleanup. A concurrent method may avoid blocking normal writes yet perform more total work. “Online” means a different interference contract, not zero interference.

Recovery performance closes the design

A backup is an input. Recovery is the operation that restores an authoritative state, replays required changes, proves invariants, reconnects dependents, and returns service within an objective.

Ledgerline’s authority is 5 TiB. A clean transfer path measured at a modeled 450 MiB/s gives:

5 TiB = 5 × 1,048,576 MiB = 5,242,880 MiB
transfer lower bound = 5,242,880 / 450
                     = 11,650.844 s
                     ≈ 3.24 h

Restore time must add provisioning, backup discovery, key access, transfer variance, decompression, data-file construction, log retrieval and replay, index work, catalog/permission restoration, integrity validation, replica rebuild, cache/projection convergence, routing, and canary traffic. If the recovery objective is two hours, this backup/throughput combination is infeasible before replay begins.

Test from the same access controls and failure assumptions used in disaster recovery. A backup encrypted with an unavailable key or stored behind the failed identity plane does not satisfy the plan. Measure recovery point, recovery time distribution, maximum replay position, acknowledged-operation presence, invariant checks, and application query behavior.

Recovery load can overwhelm a fragile primary or network. Stage restore drills, cap rebuild demand, and decide whether indexes are restored or rebuilt. A minimal data restore that requires a day of index construction before the critical workload can run has a different service recovery objective than its byte-copy time suggests.

OLTP design review worksheet

Workload:
  operations, rates, mix, skew/hot keys, payloads, growth, geographies

Invariants and authority:
  rule, facts, mutation owner, transaction boundary, acknowledgment,
  ambiguous outcome, read-after-write requirement

Contention graph:
  keys/ranges/indexes/sequences, conflicting operations, arrival,
  hold time, wait/abort/retry, hot-node evidence

State and access paths:
  row/document/key/value/relation, queries, predicates, ordering,
  projections and version/freshness

Indexes and plans:
  query benefit, estimate/actual, loops, buffers/I/O, spill,
  maintenance demand, build and rollback

Concurrency:
  locking/MVCC/validation/owner, isolation, deadlock/conflict,
  attempt budget and external effects

Commit and durability:
  log boundary, group commit, replication acknowledgment,
  visibility, operation replay, crash recovery

Pools and admission:
  connection/session state, operation classes, queue-age limit,
  transaction-age and control-plane reserve

Distribution:
  partition key, local versus cross-partition invariants,
  coordinator/in-doubt recovery or workflow semantics

Change:
  expand/migrate/contract, backfill/index demand, checkpoints,
  validation, canary, abort, rollback window

Recovery:
  backup boundary, keys, restore/replay lower bound, invariant proof,
  dependent-state convergence and drill evidence

Economics and ownership:
  steady read/write cost, storage/log/backup growth, on-call owner,
  capacity reserve and accepted failure modes

Reject a review that lacks an invariant graph, treats plan estimates as observed work, sizes pools without database capacity, adds an index without steady write cost, or states a recovery objective without a timed restore path.

What evidence changes the design

The decisive telemetry is operation- and plan-specific:

• transaction rate, latency, outcome, and attempts by operation;
• lock/validation wait and conflict graph by key class;
• pool wait and checked-out/transaction age;
• rows estimated versus observed, loops, buffers, I/O, memory, and spill;
• index hit/use and maintenance demand;
• log generation, group-commit batch, flush, and replication/apply time;
• source/replica position and commit-to-visible age;
• backfill/index progress plus foreground harm;
• checkpoint/restart and restore phase timing; and
• invariant/reconciliation failures after recovery.

Trace exemplars link a slow logical operation to its attempts, pool wait, transaction spans, locks, queries and plan IDs, commit phases, and response ambiguity. Database-wide CPU can be healthy while one lock edge or pool class is saturated. A high cache-hit ratio can coexist with plan regression on a critical miss path.

Use experiments that preserve correctness: replay representative parameters in a safe environment; canary plan/index changes; inject conflict and failover; kill a process after durable commit but before response; restore from backup; and compare acknowledged operation identities with recovered state.

Do not use this archetype when

• Operations are independent append-only events whose governing concerns are partition order, backlog, checkpoint, and replay rather than synchronous invariants.
• The main workload is large analytical scanning where throughput, partition pruning, spill, and scheduling dominate short transactions.
• State can be rebuilt from an authority and serves bounded-stale reads; a projection or cache archetype may be the proper center.
• A long-running business process includes human or external steps that cannot hold a transaction; use a durable workflow with explicit intermediate and compensation semantics.
• The business rule is actually commutative or decomposable, and a narrow key/escrow authority can avoid a general cross-record transaction.

Conversely, do not avoid this archetype merely because a managed product or key-value interface hides transaction machinery. If the business requires an invariant across concurrent changes, some authority must serialize, validate, reserve, or reconcile it.

Applied review and red-team work

Field design — SKU 842. Reproduce the 1,538 attempts/s hot-row bound and 1.43 modeled utilization. Compare admission around one authority, a reservation ledger, and 12 escrow owners. Specify token transfer, stale-owner fencing, expiry, reclaim, skew, and the evidence that no unit is issued twice.

Field migration — online index. Begin with 1.2 TiB, 54 MiB/s, two scans, 18,000 writes/s, and 0.22 CPU-ms/write. Build a schedule with foreground p99, log, replica, disk, long-transaction, validation, and invalid-index gates. State how the application rolls back if the new plan regresses only for large tenants.

Principal recovery drill. The 5 TiB restore path is capped at 450 MiB/s and the stated recovery objective is four hours. Add 70 minutes of log replay, 40 minutes of validation, and a failed identity dependency. Decide whether the objective is feasible, which work can overlap safely, and what architectural change—not optimism—would reduce the bound.

Diagnostic packet — plan or contention? A write endpoint’s p99 triples. CPU is 46%, pool wait p99 is 4 ms, conflict retries rise from 0.3% to 18%, one plan’s actual/estimated rows rise to 320x, and log flush is unchanged. Rank causal paths, identify whether the plan affects the write transaction, and request the next joined evidence rather than choosing from dashboard coincidence.

Durable rules for transactional systems

1. Define invariants and conflicting operations before choosing records, products, or isolation labels.
2. Make transaction boundaries as narrow as correctness permits and no narrower.
3. Treat rows, documents, keys, relations, and projections as access/ownership choices with different costs.
4. Price every index in write, cache, log, build, backup, and restore work.
5. Compare plan estimates with observed rows, loops, I/O, memory, and parameters.
6. Locking, MVCC, validation, and serialized ownership move conflict; none abolishes it.
7. Bound conflict retries by logical operation, deadline, side effects, and overload state.
8. A commit response must name the durability and visibility boundary and support ambiguous-outcome replay.
9. Size pools as admission to database capacity; control session state and transaction age.
10. Route replica reads by required version or declared staleness, not hope.
11. Partition where invariants stay local; pay explicitly for cross-owner atomicity or workflow states.
12. Run backfills and index builds as admitted production workloads with abort and rollback.
13. Prove recovery time by restoring, replaying, validating, and serving—not by counting backup files.

A bounded caller path and a bounded durable-invariant path now meet at one narrower interface: key-based access and caching, where predictable lookup is powerful only if hot keys, eviction, consistency, and authority remain explicit.

Evidence and transfer limits

• PostgreSQL 18’s current MVCC and concurrency-control documentation provides a concrete implementation of snapshots, isolation, and locking. It supports PostgreSQL-specific examples; engines differ in version storage, predicate protection, cleanup, and conflict behavior.
• PostgreSQL 18’s EXPLAIN documentation distinguishes planner estimates from execution observations and shows plan-node evidence. Ledgerline’s 320x example is deterministic teaching data, not a PostgreSQL benchmark.
• PostgreSQL 18’s CREATE INDEX documentation describes the extra scans, transaction waits, invalid-index failure state, and foreground interference of its concurrent procedure. Do not generalize those phases to another engine or infer the chapter’s 54 MiB/s cap from the documentation.
• PostgreSQL 18’s WAL configuration and standby documentation distinguish several local/remote flush and apply boundaries. These are useful acknowledgment examples, not a universal durability taxonomy.
• Mohan et al.’s ARIES paper establishes a seminal write-ahead-log recovery design with fine-grained locking and partial rollback. Modern engines can use different recovery architectures; verify the active engine.
• The deterministic fixture at examples/performance-engineering-system-design-handbook/part-05/transactional-oltp/ verifies the hot-row, escrow, plan, index, write-cost, and restore arithmetic. It assumes constant rates and omits distribution tails, random I/O, cache state, waits, log replay, checkpoints, validation, throttling changes, and failures. Replace it with production-shaped plans, contention traces, staged migrations, and timed restore drills.