OLTP/OLAPGENERALSOURCE-SPECIFICSCALE-SPECIFIC

OLTP Workloads

Many small transactions against current state: point lookups by key, a few rows written, low latency, high concurrency. The shape that explains every design choice an operational database makes.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

What does the work an operational database actually does look like, one statement at a time?

Who needs this

The request handler that has to answer in the time a human will wait — and, one hop later, every analytical consumer that reads this system's output. The application needs a correct answer about *now* for one entity. The data platform needs a faithful record of every change that entity went through, which is exactly the thing an operational schema is least willing to give it.

What one row is

One row of current state per entity — one order, one user, one payment — with the transaction as the unit of atomicity. A statement usually touches one row, or a handful reachable through an index. Nothing in this workload is naturally shaped like "all rows", and that absence is the whole lesson.

The obvious build

Treat the operational database as *the* database and send every question to it: the checkout write, the account page read, the nightly revenue report, the ad-hoc "how many users signed up in France last year". It is the one place that has all the data, it speaks SQL, and it has always answered. For a while it will keep answering.

Why it breaks

The revenue report scans two years of orders. Every page it touches is pulled through the buffer pool, evicting the pages the checkout path was hitting, and p99 latency on a completely unrelated endpoint doubles for the duration (The Buffer Pool).

How it breaks with real data

The revenue report scans two years of orders. Every page it touches is pulled through the buffer pool, evicting the pages the checkout path was hitting, and p99 latency on a completely unrelated endpoint doubles for the duration (The Buffer Pool).
That report opens a read transaction that stays open for eleven minutes. Under MVCC the database cannot reclaim row versions newer than its snapshot, so dead tuples accumulate and the table bloats — on a system where nobody wrote a single extra row (MVCC: Multi-Version Concurrency Control, UPDATE, DELETE and Dead Tuples).
Someone asks what a customer's pricing tier was when they placed an order in March. customers.tier holds one value, today's. The answer was overwritten in place and no version of that table still knows it (Slowly Changing Dimensions).
The analytical query holds a connection for its entire runtime. The pool is sized for requests measured in milliseconds; four such queries and the application starts failing to acquire connections while the database itself is barely busy (Connection Pools, Connection Pool Exhaustion).
An index is added to make the report faster. It helps a little, and now every insert and update on that table pays to maintain it forever, for one query that runs once a day (Should I Add an Index?).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

An OLTP statement is a navigation: find the row for this key, read or modify it, commit. The database is built to make that navigation short — a B-tree descent is a handful of page reads regardless of table size, which is why an operational database at ten million rows feels the same as at ten thousand (B+ Tree Internals: Pages, Splits, Merges, Why Is This Query Slow? Indexes).
Rows are stored contiguously. A whole record sits together inside a page, so reading one row is one page read and writing one row is one page write (Records on Disk, Pages: The Unit of Everything, Slotted Pages). The layout was chosen for the access pattern, not the other way round — Row vs Column Storage is the same decision seen from the analytical side.
Durability comes from the write-ahead log: the change is appended sequentially and flushed before the commit returns, and the data pages are updated later. Commit latency is therefore one sequential flush rather than a scatter of page writes (Write-Ahead Logging).
Concurrency comes from MVCC plus row-level locks: readers see a snapshot instead of blocking on writers, and writers conflict only where they touch the same row. That is what lets thousands of sessions share one table without serialising (MVCC: Multi-Version Concurrency Control, Isolation Levels, Concurrency Control: Schedules and Serializability).
The workload is measured in transactions per second at a latency percentile, not in bytes processed. The part of the data that matters is recent and hot, so it lives in memory; the disk is there for durability and for the long tail (Working Set: Why Performance Falls Off a Cliff, Percentiles: Which One, and How Many Users Is That?).
Normalisation follows from the same shape. Writes touch one place, so a fact stored once is a fact updated once. It is a write-side optimisation that happens to cost the read side its joins (Normalization: 1NF to BCNF).

One request, one transaction, a handful of rows

SIMPLIFIEDShown as explicit SQL with a FOR UPDATE; most applications reach this shape through an ORM and an optimistic-concurrency column instead. The row-count ratio and the single log flush are the same either way, which is the part that matters here.

An operational statement always knows which row it wants. It arrives with a key — an order id, a user id, a session token — and the database's entire job is to turn that key into a page address quickly and then get out of the way. The work is a descent, a read, sometimes a write, and a commit.

Look at the statements below and count two things: how many rows they touch, and how many rows the table holds. That ratio is the definition of this workload. It is what justifies keeping a B-tree on every access path, what makes row-contiguous storage obviously correct, and what makes a sequential flush of the log the dominant term in commit latency.

Notice also what the transaction does *not* leave behind. After it commits, orders holds status = 'paid'. It does not hold the fact that the status was pending five seconds ago, or who changed it, or when. That information existed inside the database for the duration of one log record and was then the log's problem, not the table's.

The whole of an OLTP workload, in four statements

1BEGIN;
2 
3SELECT id, status, total_cents
4FROM   orders
5WHERE  id = 918273                     -- one index descent, one row
6FOR UPDATE;
7 
8UPDATE orders
9SET    status  = 'paid',
10       paid_at = now()
11WHERE  id = 918273;                    -- one row rewritten, every index on
12                                       -- the touched columns maintained
13 
14INSERT INTO payments (order_id, provider_ref, amount_cents)
15VALUES (918273, 'pref_9f2c41', 4990);  -- one row appended
16 
17COMMIT;                                -- one sequential log flush

Rows touched: three. Rows in orders: possibly hundreds of millions. Every physical decision an operational database makes is a bet that this ratio stays tiny — and every analytical query is a statement that, for its purposes, it does not.

Why the same design makes analytics slow

The interesting property of an operational database is not that it is fast. It is that it is fast at one shape and structurally poor at another, and both facts come from the same decisions. Rows kept together means reading one row is cheap and reading one column of every row is impossible. Indexes on access paths means selective predicates fly and non-selective ones do not benefit. Current state only means the read path never has to skip history — and never has any.

So when the analytical query arrives, nothing is misconfigured. The planner correctly declines the index, correctly chooses a scan, and correctly reads every column of every page in range because that is what a page contains. The query is not fighting the database; it is asking the database to be a different one.

The cost of doing it anyway is the part that gets missed, because it does not appear in the report's own runtime. It appears somewhere else, on someone else's dashboard, as a latency change with no obvious cause.

Answering "revenue by country, last twelve months"

Aggregate on the operational primary

Run the `GROUP BY` against `orders` joined to `customers` on the primary. It works. It reads every page of `orders` in range through the buffer pool, evicting the working set the checkout path depends on, and it holds a snapshot open for its whole duration.

Aggregate on a copy built for scans

Land the same rows in a columnar analytical store and aggregate there. The scan reads the two or three columns the query names instead of every column of every row, no buffer pool serving user requests is disturbed, and the long-lived snapshot on the primary never opens.

The cost of the operational version is not its runtime — it is the eviction of a working set a latency-sensitive workload depends on, plus the snapshot retention that blocks version cleanup. Both are paid by requests with no relationship to the report, which is exactly why the problem is invisible in the report's own metrics and gets diagnosed as "the database got slow".

What the operational table has already forgotten

The first genuinely unfixable data problem in most companies is not a pipeline bug. It is a question asked in year two about a value that was overwritten in year one. No amount of downstream engineering recovers it, because the information never left the row.

Track the word "one" down the table below. At each stage it means something different, and only two of the stages preserve *what happened* rather than *what is*. If your platform's only source is the first row of this table, then history is something you started keeping on the day you noticed, and every question about the period before that has no answer.

What one row means, from the operational table outwards

Stage	One row is	Breaks if
`orders` row, now	One order in its current state — the latest value of every column.	You ask what it looked like in March. The old values were overwritten in place; the row cannot answer and neither can any query against it.
`orders` row in last night's backup	One order as it stood at the instant the backup ran.	You need a state between two backups. A restore gives you points on a line, never the path between them.
`order_events` append-only table	One state change of one order, with the time it happened and who caused it.	The application writes the event outside the transaction that changed the order, so after a crash the two disagree and nothing reconciles them.
Change record from the database log	One committed change to one order — insert, update or delete — usually carrying the whole row as it stood after the change.	You count records and call the result orders. Four updates to one order are four records and one order.
`fct_orders` row in the warehouse	One order at a declared grain, carrying the dimension values that were true at order time — if the model bothered to keep them.	The dimension was overwritten instead of versioned, so every historical order silently reports today's customer tier.

Only the third and fourth rows preserve what happened. The first two preserve what is. An operational schema is under no obligation to give you the former, most do not, and the gap is discovered by a question rather than by a monitor.

How to build it

Most important first.

Keep operational transactions short and narrow. The cost of a long transaction is paid by the whole system through snapshot retention and lock hold time, not by the session that ran it (Transactions and ACID).
Index for the access paths the application actually uses, and be honest that every index is a tax on every write to that table (Composite Indexes and the Leftmost-Prefix Rule, Should I Add an Index?).
If a question needs history, model history explicitly — an append-only event table, an order_status_history, a transactional outbox — instead of expecting the current-state row to remember (Event vs Snapshot Modeling, The Transactional Outbox).
Send analytical questions somewhere else, and decide *where* deliberately rather than by whoever happened to have a connection string (Workload Isolation).
Treat the operational schema as the application's private property. It will change for the application's reasons; anything downstream that depends on its exact shape is a coupling nobody agreed to (Data Contracts, Operational vs Analytical Models).
Expose the change stream on purpose — a log, an outbox, log-based capture — so downstream consumers stop needing to poll the tables and stop needing the schema to preserve history for them (Change Data Capture, CDC vs Polling).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

Inside one transaction: atomicity, the declared isolation level, and durability once commit returns. That is the strongest guarantee anywhere in the data journey, and it stops at the edge of this system (Transactions and ACID).
Ordering: commits on one primary are totally ordered by the log. Nothing promises that a downstream consumer *observes* them in that order unless the transport preserves it (CDC Ordering and Transaction Boundaries).
Completeness of state, not of history. The table is a complete picture of now. It is not a picture of what happened — an update that overwrites a value leaves no evidence the old value existed.
Nothing here promises a read replica is current. Replication is asynchronous by default and a replica can be arbitrarily behind (Replication and Read Scaling, Replication Lag: Reads That Are Correct and Stale).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The check that matters at this stage is constraint coverage: primary keys, foreign keys, NOT NULL, CHECK constraints and unique indexes are data tests that run on every write and cannot be skipped or forgotten (Database Constraints).
They miss everything semantic. A status column constrained to a known set happily accepts the wrong member of that set, and an amount_cents the application quietly started populating in a different currency satisfies every constraint the database can express (Semantic Changes).
They also say nothing about downstream. The source being internally consistent is no evidence at all that the copy of it in the warehouse is complete (Reconciliation).

Freshness

This is the freshest data that exists anywhere. A committed row is visible to the very next transaction, and every stage after this one can only be equal to or worse.
What a downstream consumer experiences is not that number — it is that number plus transport lag plus the schedule of whatever reads it. Quoting the source's freshness as the platform's is one of the most common ways teams mislead their own analysts (Freshness Monitoring).
Moving the analytical query here does not buy freshness worth having, because staleness is rarely the binding constraint. History and query shape are, and neither improves by getting closer to the source (OLTP vs OLAP).

When the schema or meaning changes

The operational schema changes whenever the application needs it to, which is often, and the change is reviewed by people who are not thinking about analytics (Schema Migrations from the Application Side).
Renames and type changes are the ones that hurt downstream. An expand-and-contract migration that is entirely safe for the application — add the new column, backfill, switch reads, drop the old one — silently breaks every extract that selected the old name (Expand and Contract Migrations, Breaking Schema Changes).
The most dangerous change touches no schema at all: status = 'complete' starting to mean something different. Nothing in the database records it and no type check can catch it (Semantic Changes, Data Contracts).

How to re-run this safely

Recovery here is the database's own. Crash recovery replays the write-ahead log up to the last durable commit; point-in-time recovery replays it to a chosen instant (Crash Recovery, Write-Ahead Logging).
What is *not* recoverable is the history the schema never kept. A restore gives you the state at backup time, not the sequence of states between backups, and no restore reconstructs a value that was overwritten twice (Keeping Raw History: The Recovery Position and the Liability).
From the platform's side, recovery from the source means re-extracting — which works only while the source still holds the rows in the form you need. A hard-deleted row is gone from the source, and your only copy is whatever you already landed (The Raw Landing Zone).

What can go wrong

Failure modes

Lock contention on a hot row — a counter, a sequence, an inventory level — serialising a workload designed to be concurrent (Low CPU, High Latency: Lock Contention).
Connection exhaustion, where the database has capacity and the pool does not (Connection Pool Exhaustion).
A long-running reader preventing version cleanup, so a table grows steadily while no new data is written (MVCC Internals: Version Chains and Snapshots).
An N+1 access pattern turning one logical read into hundreds of round trips, each individually fast enough to look innocent (The N+1 Query Problem).
The mitigation failing: a read replica added for isolation, then used by the application for latency-sensitive reads, so replica lag turns from an analytics inconvenience into an application correctness bug (Read Replicas From the Application).

Misreads

"OLTP means small data." It means small *per statement*. Operational tables reach enormous sizes; what stays small is the number of rows any one transaction touches.
"Adding an index will make analytics fine." An index pays when a predicate is selective. An aggregate over most of a table has no selective predicate, so the planner chooses a scan and is right to (An Index Scan Is Not Automatically Faster, Cost-Based Optimization).
"The database is slow" when the real finding is that one workload evicted another's working set. The number to look at is the hit ratio and the query that changed it, not the CPU graph (From Symptom to Root Cause).
"We can just add analytics columns to the operational schema." Every such column is maintained on the write path forever, by a team whose incentives point the other way (Operational vs Analytical Models).

Privacy, retention and access

The operational database holds personal data in its most identifiable form and under the strongest access controls in the company. Every copy taken out of it inherits the obligation and leaves the controls behind (PII in Pipelines, Database Privileges and Blast Radius).
A deletion request executed here removes the row from this system only. Whether it is honoured downstream is a pipeline design question that must be answered before the request arrives, not after (Deletion Requests).

Operating it

How you see it in production

Latency percentiles per statement class, never averages. An operational system is judged on its tail, and an average hides precisely the requests that are failing (Percentiles: Which One, and How Many Users Is That?, Tail Latency: Why p50 Being Fine Does Not Help).
Buffer cache hit ratio alongside the query that caused a drop. A sudden fall is almost always one large scan evicting the working set (Which Signal Actually Means "The Database Is Slow").
Longest-running transaction and oldest snapshot age — one number that predicts bloat, lock waits and replication problems before any of them pages anyone.
Replication lag on every replica, with the analytical consumers of that replica listed next to it, so the lag has an owner (Replication Lag: Reads That Are Correct and Stale).

What changes at 10x and 100x

At 10x transactions the shape does not change. What changes is contention on the hottest rows and the number of connections in flight; B-tree lookups stay logarithmic and the system feels identical until one specific hot spot does not (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
At 100x data volume, point lookups are still fast and everything unindexed is now impossible. Queries that quietly did a sequential scan at small scale become the incidents (Sequential Scan, Page by Page).
Scaling this workload is a sharding problem, and sharding makes the analytical problem worse rather than better: cross-shard aggregation is precisely the query type shards are worst at (Partitioning and Sharding).

What drives cost here

Cost is driven by write amplification — each row write also writes the log and updates every index on that table — and by the memory needed to keep the working set resident (Write, Read and Space Amplification).
An analytical query on this system costs far more than its own runtime, because it evicts cache the operational workload then has to re-read. The bill arrives as latency on endpoints that never touched the report's tables.
Storage grows with whatever history the schema decided to keep, which for a pure current-state model is close to nothing. That is cheap, and it is exactly why the history has to be kept somewhere else (Snapshot Tables).

What this approach costs

Everything that makes this workload fast — narrow rows kept together, indexes on access paths, current state only, short transactions — makes analytical queries slow. That is not a defect to be tuned away; it is one design decision seen from the other side.
Normalisation minimises write anomalies and maximises join count. The analytical side pays for that in every query, which is why analytical models are denormalised (Denormalization on Purpose, Star Schema).
Keeping only current state is cheap, simple and irreversible. The cheapest schema you can run is the one that will not be able to answer next year's question.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe shape — few rows per statement, current state, short transactions, latency-judged — holds for any transactional system regardless of engine. What varies is the machinery underneath it, not the workload it was built for.
SOURCE-SPECIFICHow change history is exposed differs sharply: PostgreSQL keeps old row versions in the heap and publishes a logical replication stream, MySQL keeps them in an undo log and publishes a binlog, MongoDB publishes an oplog. The same CDC question therefore has three different answers about ordering, DDL visibility and how long the log survives.
SCALE-SPECIFICBelow the point where analytical scans measurably disturb the request path, most of the advice here about isolation is premature. The advice about keeping history is not — that one is cheap now and impossible retroactively.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasetransactions-and-acid mvcc write-ahead-logging b-plus-tree-internals records-on-disk buffer-pool

Backendconnection-pools transactions-in-backends read-replicas transactional-outbox database-constraints

Securitydatabase-privileges

Observabilitypercentiles tail-latency database-performance-signals lock-contention

Computer Architectureworking-set

Domains that do not exist yet

— Distributed Systems owns what a commit means once there is more than one node — synchronous versus asynchronous replication, quorum acknowledgement, and what a failover does to changes that were acknowledged but not replicated. Those are the guarantees a CDC consumer inherits without being told.
— DevOps / Production Engineering owns how the schema migration that renames a column is reviewed, deployed and rolled back. The reason analytics breaks on migrations is usually that the delivery process has no downstream consumer in it.