beginnerintermediateadvanced

System Design · Section 96

Data Warehouse vs Operational Database

Level: intermediate
Read: 17 min
Concepts: 3

OLTP and OLAP are two workloads with opposite shapes, and almost every engineering difference between an operational database and a warehouse follows from that. OLTP is a high rate of small, short transactions, each touching a few rows and expected to complete in milliseconds, with integrity enforced by the database rather than assumed by the application — which is what justifies row-oriented storage, B-tree indexes, isolation control and write-ahead logging, all of them favouring per-operation precision and low latency. OLAP is a small number of long queries, each scanning very many rows and reading very few columns to produce an aggregate — which justifies columnar storage that reads only the referenced columns and compresses far better, bulk appends rather than single-row updates, aggressive parallelism because seconds of latency are acceptable, denormalised wide tables because a join across billions of rows costs more than the duplicated storage, and validation moved upstream into the pipeline because per-row constraint checking would cost more than it protects. The practical skill is naming which workload a given query belongs to, because that determines whether a slow report is a tuning problem or a wrong-system problem. The operational rule that follows is often stated as a prohibition and is really a requirement to estimate: a reporting query on a transactional primary competes for buffer cache, disk bandwidth, CPU and connections, and its most underestimated cost is cache eviction — the working set is displaced by cold historical pages, so ordinary queries stay slower after the report finishes, often for longer than the report ran. On a multi-version store a long read also holds a snapshot open and blocks cleanup of dead row versions. The resolution is a ladder taken in order — confirm the query is genuinely small, move it to a read replica, then move it to a warehouse — remembering that a replica removes contention and not scan cost, since it shares the primary's row-oriented layout. And wherever it runs, reporting gets its own read-only role, its own connection pool and a statement timeout.

System Design overview

System Design for Beginners CoursefreeCodeCamp.org

What is true here

OLTP: few rows with most of their columns, milliseconds, integrity enforced by the database. OLAP: many rows with few columns, minutes, aggregates.
Columnar storage is the single largest difference in scan cost, because it reads only the columns a query names.
A reporting query's worst cost is usually cache eviction, which makes ordinary queries slower after the report has finished.
A read replica removes contention with the primary but has the same layout, so it does not make a large scan cheaper.
Give reporting a read-only role, a separate connection pool and a statement timeout, wherever it ultimately runs.

What you will be able to do

Classify a query as OLTP or OLAP and say which engine choices its shape justifies
Explain why a read-only report can degrade a healthy primary, and why the degradation outlasts the query
Choose between primary, replica and warehouse for a specific report, and state what each choice does and does not fix
Set the guardrails that keep a runaway reporting workload from consuming the application's capacity

Two workloads

OLTP and OLAP, and the opposite sets of engine choices each shape justifies.

OLTP: frequent small transactions and strong integrity

standardintermediate

OLTP stands for online transaction processing, and it describes the workload your application generates: a high rate of small, short transactions, each touching a few rows, each expected to complete in milliseconds. A checkout, a login, a comment, a status change. Two things characterise it beyond size. First, the operations are transactional — several writes must succeed or fail together, and the database is expected to guarantee that even under concurrency and crashes. Second, integrity is enforced rather than assumed: foreign keys, unique constraints, check constraints and isolation levels exist so that invalid states cannot be stored, no matter which code path attempts it. Those guarantees are why an OLTP store is designed the way it is — row-oriented layout so a whole record is one read, B-tree indexes so a lookup by key is a seek rather than a scan, careful concurrency control so thousands of small transactions interleave safely, and durable write-ahead logging so a committed transaction survives a crash. Every one of those choices favours precision and latency over throughput on large scans, which is exactly the right trade for the workload and exactly the wrong one for analytics.

Think of it as

A bank counter. Each interaction is small, specific and must be exactly right: the money either moved or it did not, and there is no acceptable middle state. Speed matters, but correctness under concurrent activity matters more, and the whole design of the counter — the ledger, the double-signing, the receipt — exists to make a wrong outcome structurally difficult rather than merely unlikely.

sql

-- the shape of an OLTP unit of work
BEGIN;
  UPDATE accounts SET balance = balance - 5000
   WHERE id = $1 AND balance >= 5000;   -- 1 row
  UPDATE accounts SET balance = balance + 5000
   WHERE id = $2;                        -- 1 row
  INSERT INTO transfers (from_id, to_id, cents)
  VALUES ($1, $2, 5000);
COMMIT;
-- two rows changed, one row inserted, all or
-- nothing, in single-digit milliseconds

The properties an OLTP workload depends on

Small and fast

a few rows, milliseconds

Atomic

several writes, all or nothing

Constrained

invalid states cannot be stored

Isolated

safe under concurrency

Durable

a commit survives a crash

What an OLTP store is optimised for

What an OLTP store is optimised for
Design choice	Serves	Costs
Row-oriented storage	Reading a whole record in one go	Reads unused columns during a scan
B-tree indexes	Constant-ish lookup latency as data grows	Write amplification and index maintenance
Constraints and foreign keys	Invalid states become unstorable	A small per-write check
Isolation levels and row locks	Safe concurrent interleaving	Contention on hot rows
Write-ahead logging	Durability across crashes	A synchronous write per commit

Remember: OLTP is a high rate of small, short, transactional operations with integrity enforced by the database rather than by the application. Row storage, B-tree indexes, isolation control and write-ahead logging all exist to serve that: per-operation correctness and low latency, guaranteed under concurrency and crashes. The same choices make large column-selective scans expensive, which is the trade the workload is meant to make.

OLAP: scans, aggregations and large datasets

standardintermediate

OLAP stands for online analytical processing, and it describes a workload with the opposite shape to OLTP: a small number of long queries, each scanning a very large number of rows, reading a few columns from each, and producing an aggregate rather than a record. "Revenue by month for four years", "conversion rate by acquisition channel", "median session length by cohort". Individual rows are rarely interesting; the answer is a summary. That shape leads to different engineering everywhere. Storage is columnar, so a query reading two columns from a billion rows reads two columns' worth of bytes rather than every column of every row, and compresses well because values within a column are alike. Data is usually appended in bulk rather than updated in place, so the store is optimised for large sequential writes and rarely for single-row updates. Queries are expected to take seconds or minutes, so throughput matters far more than per-query latency, and work is parallelised aggressively across many machines. And integrity constraints are largely absent, because the data arrives from a pipeline that already validated it and enforcing constraints per row would cost more than it protects. Recognising which of the two workloads a query belongs to is the practical skill — it is what tells you whether a slow report is a tuning problem or a system-choice problem.

Think of it as

A census. Nobody asks the census for one person's record; they ask for totals, distributions and trends across everyone. It is compiled in bulk, read in bulk, and its whole value is in aggregate answers. Optimising a census to be excellent at looking up one household would make it worse at the thing it exists for.

sql

-- the shape of an OLAP query
SELECT channel,
       date_trunc('month', occurred_at) AS month,
       count(*)                        AS sessions,
       sum(revenue_cents) / 100.0      AS revenue
  FROM events
 WHERE occurred_at >= now() - interval '4 years'
 GROUP BY 1, 2
 ORDER BY 1, 2;
-- billions of rows scanned, four columns read,
-- a few hundred rows returned

What an OLAP engine optimises, in order

Columnar layout

read only the referenced columns; compress values that are alike

Bulk append writes

large sequential loads rather than single-row updates

Parallel execution

split the scan across many machines; throughput over latency

Denormalised wide tables

avoid joins across billions of rows; storage is cheaper than the join

OLTP and OLAP side by side

OLTP and OLAP side by side
Property	OLTP	OLAP
Query shape	Few rows, most columns	Many rows, few columns
Result	Records	Aggregates
Latency target	Milliseconds	Seconds to minutes
Write pattern	Small transactional updates	Bulk appends
Storage layout	Row-oriented	Columnar
Normalisation	Normalised, constrained	Denormalised, wide
Integrity enforcement	In the database	In the pipeline, upstream

Remember: OLAP is few queries, each scanning many rows and reading few columns to produce an aggregate. That shape justifies columnar storage, bulk appends, aggressive parallelism, denormalised wide tables and validation moved upstream into the pipeline rather than enforced per row. The practical skill is naming which workload a given query belongs to, because that determines whether a slow report is a tuning problem or a wrong-system problem.

Where a report is allowed to run

The cost a scan imposes on a primary, and the ladder from primary to replica to warehouse.

Keeping heavy reporting off the transactional database

coreintermediate

The rule is not "never run a report against the production database" — it is "never do it without knowing what it costs". A reporting query on an OLTP primary competes for the same finite resources every application query needs: buffer cache, disk bandwidth, CPU, connections. A large scan pulls pages into the cache and evicts the working set that keeps ordinary lookups fast, so the impact continues after the report finishes, as live queries re-read from disk what they used to find in memory. On a multi-version store a long-running read also holds a snapshot open, which prevents cleanup of old row versions and can make tables and indexes grow measurably during a long report. And a report that hits a connection limit takes capacity from the requests the business actually depends on. The resolution is a ladder, taken in order of cost. First, ask whether the query is genuinely heavy — a well-indexed report over yesterday's data may be trivial. Second, move it to a read replica, which removes competition with writes and with other readers on the primary but shares the same row-oriented layout and can lag. Third, move it to a warehouse fed by a pipeline, which is where genuinely analytical work belongs. Whatever you choose, the query needs a timeout and a resource limit, because the failure mode being prevented is one report degrading the entire product, and an unbounded query on a primary is exactly that risk left unmanaged.

Think of it as

Running a stocktake in the middle of a shop's busiest hour. It is not forbidden, and for a small shelf it costs nothing worth measuring. Counting the whole warehouse while customers queue is a different act with the same name, and the difference between the two is a number somebody should have estimated first. The mistake is never "someone ran a query" — it is that nobody asked how big it was.

sql

-- give reporting its own bounded budget
CREATE ROLE reporting LOGIN;
GRANT pg_read_all_data TO reporting;

ALTER ROLE reporting SET statement_timeout = '60s';
ALTER ROLE reporting SET idle_in_transaction_session_timeout = '30s';
-- plus its own connection pool, sized separately
-- from the application's, so a runaway report
-- exhausts only its own allocation

What we're doing: Watch a quarterly report degrade a healthy primary, and see the effect outlast the query.

report-on-primary.txttext

09:00  primary healthy. p99 read latency 4 ms.
       buffer cache holding the hot working set:
       recent orders, active sessions, catalogue.

09:12  an analyst runs the quarterly revenue
       report against the primary. It scans
       four years of orders.

09:12   .. the scan pulls cold historical pages
          into the cache, evicting the hot set
09:15   p99 read latency 47 ms. Nothing is
          failing; everything is slower.
09:18   the report also holds a read snapshot,
          so dead row versions from 6 minutes of
          writes cannot be cleaned up yet
09:23   report finishes.

09:23   p99 read latency is STILL 41 ms. The
        cache now holds four-year-old orders
        nobody will read again, and the hot set
        has to be faulted back in from disk.
09:38   p99 back to 6 ms. Recovery took longer
        than the query did.

Nothing broke. No alert fired on errors. The
product was measurably slower for 26 minutes
because of one read-only query.

9: Cache eviction is the mechanism people underestimate, because a read-only query feels harmless. What it consumes is not write capacity but the memory that keeps everything else fast.
13: The held snapshot is the second, quieter cost on a multi-version store: cleanup of old row versions is blocked while the long read is open, so tables and indexes grow during the report and stay larger afterwards.
18: This is the part that makes the impact worse than it looks. The query took eleven minutes; the degradation lasted twenty-six, because the cache had to be repopulated with the working set the report displaced.

Why this works: The incident produces no errors and no failed requests, so it is invisible to alerting built around error rates and is usually attributed to something else entirely. Measuring the cost before running — rows scanned, expected duration — is what turns this from a recurring mystery into a decision somebody made with a number in front of them.

Giving a reporting tool the application's database credentials

Wrong

text

# BI tool configured with the app's connection
# string and pool.
# A dashboard with auto-refresh opens 40
# concurrent scans; the application cannot get
# a connection and starts returning 500s.

Better

text

# A separate read-only role, its own pool sized
# independently, a statement timeout, and
# (ideally) a replica endpoint.
# A runaway dashboard exhausts its own budget
# and the application never notices.

What you see: The application fails with connection-pool exhaustion at the same times each day, correlating with a scheduled dashboard refresh rather than with user traffic — and the database itself shows plenty of spare CPU and memory.

Why: Connections are a fixed, shared allocation, so any client sharing the application's pool can starve it regardless of how healthy the database is. Separate credentials with their own pool make the reporting workload's limits its own, which is the same bulkhead reasoning applied to a database rather than to a service.

Where a reporting query should land

A reporting query

Estimate its cost

rows scanned, columns read, runtime

Primary

only if measurably small, timeout enforced

Read replica

no competition with writes; still a row-store scan; lags

Warehouse

columnar, isolated, fed by a pipeline

Product degradation

what happens when nobody estimated

The ladder, in increasing order of cost and capability

The ladder, in increasing order of cost and capability
Option	Removes	Still costs	Use when
Run it on the primary	Nothing	Cache, CPU, connections, snapshot age	The query is measurably small and time-bounded
Run it on a read replica	Competition with writes and other primary readers	Row-oriented scan cost; replica lag	Moderate reports that need near-current data
Run it in a warehouse	Competition entirely; scan cost drops with columnar storage	A pipeline to build and run; freshness set by the pipeline	Genuine analytical work over history

Guardrails that apply wherever the query runs

Guardrails that apply wherever the query runs
Guardrail	Prevents
Statement timeout	One query running for hours and holding resources
Separate credentials and connection pool	Reporting exhausting the application's connections
Read-only role	A reporting session writing to production data
Query cost estimate before scheduling	Discovering the cost during the incident
A stated freshness expectation	Silent disagreement between a report and the application

Remember: The rule is not "never report on production" but "never without estimating the cost". A large scan competes for cache, bandwidth, CPU and connections, evicts the working set so the slowdown outlasts the query, and on a multi-version store holds a snapshot that blocks cleanup. Take the ladder in order — confirm the query is small, then a replica, then a warehouse — remembering a replica fixes contention and not scan cost. And wherever it runs, give reporting its own read-only role, its own connection pool and a statement timeout.