Data Warehouse vs Operational Database

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.

-- 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

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.

-- 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

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.

-- 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