Module P-22·34 min read

Row vs columnar storage, warehouse vs lake vs lakehouse, why one analyst's GROUP BY takes production down, and why a read replica is the wrong fix.

JJS

Written by Jatin Jain Saraf · Senior Software Engineer

Module P-22 — OLTP vs OLAP vs HTAP

Q: After an analytical query took production down, a team moved all analytics to a dedicated read replica. Six months later: analysts still wait several minutes per query, the application's replica reads have become slow, replica lag alarms fire during business hours, and after someone enabled `hot_standby_feedback` the primary began showing table bloat. What does the module say went wrong?

A replica addresses read *volume*, while this is a read *shape* problem — the row-oriented scan costs the same wherever it runs, it evicts the replica's buffer cache and so degrades the application traffic the replica existed for, and a hot standby is not isolated from the primary: long queries conflict with WAL replay, so you choose between cancelled queries, climbing lag, or `hot_standby_feedback` letting the analyst's snapshot retain dead tuples on the primary. — The module walks exactly this six-month sequence and names the volume-versus-shape distinction as the reason the replica route defers the decision rather than solving it, including the Module P-5 mechanism that makes the "isolation" partly illusory. Option B is the near-miss that sounds most like standard tuning, and the module answers it directly: you cannot index your way out of aggregating 40 million rows — an index scan over that fraction of a table is slower than a sequential scan, which is why the planner chose the scan. Also, a physical streaming replica cannot carry indexes the primary lacks; it is byte-identical by construction. Option C is genuinely good practice that the module recommends as a guardrail, but a timeout limits the damage rather than delivering the analytics, and killing the analysts' queries is not a working analytics platform. Option A treats a layout mismatch as a capacity shortfall; a larger replica reads the same 20 GB and evicts the same cache.

Q: A warehouse table `fact_orders` is partitioned by `order_date` and clustered by `region`. An engineer builds an in-product dashboard where each customer's page load runs `SELECT * FROM fact_orders WHERE customer_id = $1 AND order_date > current_date - 30`. It works in testing. In production the dashboard is slow, and the monthly warehouse bill rises sharply. What are the two defects?

`SELECT *` reads every column instead of the few needed — the dominant cost in a columnar store — and, more fundamentally, a warehouse is the wrong engine for customer-facing analytics: thousands of page loads needing sub-200 ms against an engine with seconds of latency and low concurrency. Because the query set is fixed, the answer is pre-aggregated rollups or a real-time OLAP store, with the result cached. — Both defects are the module's: `SELECT *` is the single most expensive habit in a columnar store because it converts a three-column read into a sixty-column read, and the internal-BI versus customer-facing table makes the architectural point — a fixed query set at high concurrency and a 200 ms budget is a pre-aggregation problem, not a warehouse query, and pointing customer page loads at a warehouse is how a large bill appears. Option C is the strongest near-miss and is real advice: clustering on `customer_id` would tighten zone maps considerably, and byte limits are a guardrail the module recommends. But it optimises a query that should not be running per page load at all, and it leaves the concurrency ceiling and the latency budget unaddressed. Option B gets partitioning backwards — date is the column people filter on and the one that enables pruning — though its nightly materialised view is half of the right answer. Option D misstates pruning, which works at partition granularity and correctly skips all partitions outside the window.

Q: A warehouse's `dim_customer` table is updated in place by the nightly load: when a customer's region changes, the existing row's `region` is overwritten. Finance reports that a revenue-by-region report for last year now returns different numbers than the version they filed at the time, and nobody can explain the difference. What is the defect and the fix?

This is a Type 1 (overwrite) dimension, which rewrites history retroactively. Attributes whose history matters need Type 2 versioning — a new row per version with `valid_from`/`valid_to` and a surrogate key that facts reference — so an order stays attached to the region that was current when it happened. Type 1 is correct only for genuine error corrections. — The module uses precisely this example — a customer moving cities silently changing last year's regional revenue — to explain why warehouses use surrogate keys and Type 2 dimensions, and it also states the cost that makes this a judgement call rather than a blanket rule: only model as Type 2 the attributes whose history someone will actually ask about, since each one adds ETL work and forces every query to choose between historical and current attribution. Option B is the interesting near-miss: a temporal join on the natural key is a real modelling technique and would produce correct historical attribution — but only if the dimension retained its history, which a Type 1 overwrite has already destroyed, so there is nothing for the date predicate to select. Option D is a reasonable governance practice for filed reports and does not fix the underlying model; the warehouse would still be unable to answer any historical regional question correctly. Option A describes a genuine load defect from Module P-16, but double-applied rows would inflate figures rather than reattribute them, and it would not correlate with customers changing region.

What this module covers: Why one analyst's GROUP BY can take production down, told as a physical-layout story rather than a query-tuning one. Row-oriented versus columnar storage, and why columnar wins on analytics by a factor of ten to thirty through I/O elimination and compression rather than cleverness. The two workload profiles set side by side, so the mismatch is legible. Why the read replica everyone reaches for is the wrong fix — it addresses read volume and the problem is read shape, and it drags Module P-5's recovery conflicts along with it. Warehouses, lakes and lakehouses, and what table formats added to files to make the middle option viable. Dimensional modelling, where denormalisation is correct and slowly-changing dimensions are how you avoid rewriting history. HTAP's real options and honest costs. The cost model inversion, where a SELECT * is a line item. And the distinction that decides your architecture: internal BI and customer-facing analytics are different systems with different latency budgets.

One `GROUP BY` Takes Production Down, and It Is Not the Analyst's Fault

The incident is always the same. An analyst, given read access to the production database because that is where the data is, runs something reasonable:

sql

SELECT date_trunc('month', o.created_at) AS month, c.region, count(*), sum(o.total_cents)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '3 years'
GROUP BY 1, 2 ORDER BY 1;

Nine minutes later checkout latency is at four seconds, the connection pool is exhausted (Module P-4), and the pager fires. Nobody wrote a bad query — that SQL is fine, and against the right engine it returns in under a second. The mismatch is physical, and it has three separate mechanisms.

It reads the whole table, columns included that it does not want. orders has 60 columns; the query needs three. A row store reads 8 KB pages containing entire rows, so fetching created_at, customer_id and total_cents for 40 million rows means reading all 60 columns of all 40 million rows — perhaps 20 GB of I/O to answer a question about 600 MB of data.

It evicts the working set from the buffer cache. This is the part that hurts other people. Your OLTP workload runs fast because the hot 8 GB of index and data lives in memory (Module F-2's hierarchy, Module F-12's working set). Scanning 20 GB through a 16 GB buffer cache flushes it. When the scan ends, every subsequent point lookup is a disk read until the cache re-warms — so the damage outlasts the query, and latency stays bad after the "cause" has gone. Teams frequently misdiagnose this as a second, unrelated incident.

It holds one snapshot for nine minutes. An MVCC snapshot open for nine minutes prevents vacuum from reclaiming any row version created after it started (Module F-13), so the write-heavy tables bloat during exactly the window when the database is already struggling.

The correct conclusion is not "train the analysts" or "add an index." No index helps a query that aggregates 40 million rows — you cannot index your way out of reading everything, and an index scan over that fraction of a table is slower than a sequential scan, which is why the planner chose the scan. The conclusion is that you are running an analytical query on an engine built for a different physical access pattern.

Analogy: a warehouse organised for order picking, with each customer's order boxed together on a shelf, addressed by order number. Perfect for "fetch order 4417." Now someone asks how many blue items you shipped last year: a picker must open every box in the building, look at every item, and put it all back. The shelving that made single-order retrieval instant is precisely what makes the inventory question a full building sweep. A stocktaking warehouse is laid out differently — all the blue items together, counted in bulk. Neither layout is wrong; they answer different questions, and no amount of hiring more pickers converts one into the other.

Columnar Storage Wins by Reading Less and Compressing Better

Same logical table, two physical layouts:

text

ROW-ORIENTED (heap pages)              COLUMN-ORIENTED
┌──────────────────────────────┐       ┌──────────────────────────────┐
│ 1│2026-01-03│ 88│ 4200│ …57 more     │ id:      1, 2, 3, 4, …        │
│ 2│2026-01-03│ 91│ 1150│ …            │ created: 2026-01-03, 2026-01… │
│ 3│2026-01-04│ 88│ 9900│ …            │ cust:    88, 91, 88, 12, …    │
└──────────────────────────────┘       │ total:   4200, 1150, 9900, …  │
Read one row: one page.                └──────────────────────────────┘
Read one column: every page.           Read one column: only its blocks.
                                       Read one row: touch every column file.

Two effects compound, and both come from Module F-2 rather than from any algorithmic trick.

I/O elimination. Reading 3 of 60 columns reads roughly 3/60 of the bytes. That alone is a 20× reduction before anything else happens.

Compression, because adjacent values share a type and often a value. A column of created_at timestamps in insert order is nearly sorted, so delta encoding stores tiny differences. A region column with eight distinct values becomes a dictionary plus 3-bit codes. A status column that is 94% 'completed' run-length encodes to almost nothing. Typical columnar compression is 5–20× on real data; the same data in a row store, where a page interleaves timestamps, integers, UUIDs and free text, compresses at perhaps 2–3× because there is no local similarity to exploit.

Multiply them and a scan reads 1–3% of the bytes the row store would. That is where the "ten to thirty times faster" comes from — not clever algorithms, just far fewer bytes crossing the bus.

Three further mechanisms ride on the layout:

Vectorised execution. Values of one type, contiguous in memory, are processed in batches of thousands with SIMD instructions, instead of a function call per row. Module F-2's cache-line argument at its most favourable.
Late materialisation. Filter using only the columns in the WHERE clause; assemble full rows only for the survivors. A query filtering to 0.1% of rows never touches the other columns for the 99.9%.
Zone maps and pruning. Each block stores min/max per column, so a block whose maximum created_at predates your range is skipped without being read. Combined with partitioning, a three-year query against a ten-year table reads three years of blocks.

And the flip side, which is why nobody runs OLTP on columnar storage: a single-row read touches every column's storage, and a single-row update is expensive — you cannot update a run-length-encoded, dictionary-compressed block in place, so columnar engines write immutable files and merge them in the background. That is the Module P-2 LSM shape yet again, and it is why columnar systems are append-oriented and generally do not offer row-level locking, per-row constraints, or high-concurrency point writes.

	OLTP (row)	OLAP (columnar)
Access pattern	few rows by key, all columns	all rows, few columns
Concurrency	thousands of sessions	tens of queries
Latency target	1–10 ms	1 s – 10 min
Write pattern	many small `INSERT`/`UPDATE`	bulk append, rarely updated
Schema	normalised, constrained	denormalised star schema
Indexes	many B-trees	few or none; zone maps and partitions
Wins by	index seeks	not reading the data
Optimised for	correctness under concurrency	bytes scanned per second

The Read Replica Is the Wrong Fix, and It Is Everyone's First Fix

"Point the analysts at a read replica" is the reflex, it is cheap, and it is wrong in a way worth being precise about — because it does relieve the symptom just enough to look like a solution for a few months.

A read replica solves read volume: more of the same shape of query than one machine can serve. This problem is read shape: a query whose physical access pattern the engine cannot serve efficiently anywhere. Moving it changes who suffers, not what it costs.

Five specific consequences:

The scan costs the same. The replica is byte-identical to the primary, row-oriented, with the same layout. A nine-minute query is a nine-minute query. You have bought isolation, not performance, and analysts still wait nine minutes.
It destroys the replica's other job. Most replicas exist to serve application read traffic. Analytical scans evict the replica's buffer cache exactly as they would the primary's, so your application's replica reads get slow — you have moved the incident to a different endpoint.
The replica is not isolated from the primary. This is the one that surprises people, and Module P-5 covered the mechanism: a long-running query on a hot standby conflicts with WAL replay. Either replay stalls and replica lag climbs (breaking read-your-own-writes for the application, Module P-11), or the query is cancelled with ERROR: canceling statement due to conflict with recovery. Enable hot_standby_feedback to stop the cancellations and the primary retains dead tuples on the analyst's behalf — so a nine-minute analytical query now bloats the primary. The replica gave you less isolation than the architecture diagram suggests.
It does not answer the actual questions. Analysts want to join orders against marketing spend, support tickets, and the billing system's data. None of that is in your OLTP database, and adding it there is how a transactional schema becomes a swamp.
The economics are wrong. You are paying for a full OLTP-shaped replica — provisioned IOPS, memory sized for a working set, high availability — to run a workload that wants cheap bulk sequential throughput (Module O-8).

Read replicas are the right tool for read volume, geographic read latency, and isolating a bounded reporting query that runs occasionally. They are the wrong tool for an analytics practice. The tell that you have crossed the line is when someone asks for a second replica "just for analytics" — at that point you are building an analytics platform out of the wrong primitive.

Why this matters in production: the replica route is attractive because it defers the decision at almost no cost, and it fails gradually. Queries get slower as data grows, someone raises statement_timeout for the analytics role, then lag alarms start firing, then hot_standby_feedback gets turned on and the primary starts bloating, and the connection between that bloat and the analytics workload is genuinely hard to see from the primary's metrics. Six months of small accommodations, and the eventual migration happens under pressure.

Warehouse, Lake, Lakehouse: What Each One Actually Is

A data warehouse is a columnar MPP database: Snowflake, BigQuery, Redshift, Databricks SQL. Data is loaded in a defined schema (schema-on-write), queried with SQL, and query work is distributed across nodes. The modern architectural feature is separation of storage and compute — data lives in object storage, compute clusters are spun up per workload and scale independently, so two teams' queries do not contend, and you pay for compute only while it runs. That property is what made warehouses operationally boring compared to the Hadoop era.

A data lake is files in object storage — Parquet or ORC in S3/GCS — with no database in front of them (schema-on-read). It is cheap, holds anything including data you have not decided what to do with, and any engine can read it. What it lacks is what a database gives you: no transactions, so a reader can see a half-written batch; no schema enforcement, so a producer changing a field type breaks consumers silently; no efficient row-level update or delete, which matters enormously the first time someone exercises a right to erasure (Module O-9); and no way to list a table's files cheaply once there are a million of them.

A lakehouse is a lake plus a table format — Apache Iceberg, Delta Lake, or Hudi — which is essentially a transaction log and metadata layer over the same Parquet files. That layer adds:

ACID commits, via atomic metadata pointer swaps, so readers never see partial writes.
Snapshot isolation and time travel — query the table as of yesterday, which is a genuinely useful debugging and audit primitive.
Schema evolution that is a metadata operation rather than a rewrite.
Row-level DELETE and UPDATE, implemented as delete files merged at read time and compacted later. (The same immutable-plus-compaction pattern, one more time.)
Efficient metadata, so pruning does not require listing objects.

	Warehouse	Lake	Lakehouse
Storage cost	higher	lowest	lowest
Schema	on write, enforced	on read, unenforced	on write, evolvable
Transactions	yes	no	yes
Engine choice	one vendor's	any	any that supports the format
Row-level delete	yes	painful	yes
Governance	mature	build it yourself	improving
Best for	BI and SQL analytics	raw landing zone, ML feature data	both, at the cost of more moving parts

The pragmatic reading: most companies want a warehouse for SQL analytics and object storage as a landing zone, and reach for a table format when the volume, the multi-engine requirement, or the deletion requirement makes it worth the operational surface. "Lakehouse" as a starting architecture for a team of four is usually premature.

And the option that gets skipped: your data is probably small

Before any of the above, run the numbers. A great many companies with "big data" problems have 50–500 GB of analytical data, which is one machine. DuckDB querying Parquet files on object storage handles that range on a laptop or a single instance, in-process, with no cluster, no vendor, and no per-query bill — and it is columnar and vectorised, so it gets the full 10–30× benefit described above. The honest sequence for a mid-sized product is: nightly export to Parquet, DuckDB for analysis, and move to a warehouse when concurrency, governance, or volume genuinely demands it. Choosing Snowflake for 200 GB is not wrong, but it should be a deliberate purchase of managed convenience rather than an assumption that the data is large.

Dimensional Modelling: Denormalise, and Do Not Rewrite History

The transactional schema you built in Module F-14 is wrong for analytics, and the reason is not aesthetic. Normalisation exists to make writes cheap and consistent by storing each fact once; analytics does almost no writes and pays for every join across a wide scan. So the modelling instinct inverts.

A star schema has a central fact table — one row per business event, mostly numeric measures and foreign keys — surrounded by dimension tables that are wide, denormalised and descriptive.

text

                 dim_date            dim_customer
                     \                  /
   dim_product ── fact_order_items ── dim_store
                       │
              (order_id, date_key, customer_key, product_key,
               quantity, unit_price_cents, discount_cents, …)

Two decisions carry most of the weight.

Grain: what does one row mean? "One row per order line item" is a grain. Get it wrong — mix orders and line items in one table — and every aggregate double-counts, silently and in a way that survives review because the numbers look plausible. State the grain in the table's comment; it is the single most useful piece of documentation in a warehouse.

Slowly changing dimensions: history must not be rewritten. A customer moves from Leeds to Berlin. If you UPDATE dim_customer.region, every historical report changes retroactively: last year's revenue-by-region no longer matches the report you filed last year, and no one can tell you why. That is a Type 1 dimension (overwrite), and it is right only for corrections of genuine errors.

Type 2 keeps history by adding a row per version:

sql

CREATE TABLE dim_customer (
  customer_key   bigserial PRIMARY KEY,   -- surrogate key; the fact table references THIS
  customer_id    bigint NOT NULL,         -- the natural key from the OLTP system
  region         text   NOT NULL,
  valid_from     timestamptz NOT NULL,
  valid_to       timestamptz,             -- NULL = current
  is_current     boolean NOT NULL
);

Facts reference the surrogate key that was current when the event happened, so a 2025 order stays attached to Leeds forever while today's orders attach to Berlin. This is why warehouses use surrogate keys rather than the OLTP primary key — and it is the same "an event records what was true then" instinct that Module P-24's ledger design depends on.

The cost of Type 2 is real: dimensions grow, the ETL must detect changes and close rows, and every query must decide whether it wants historical or current attribution. Model as Type 2 only the attributes whose history someone will actually ask about — usually a handful — and Type 1 the rest.

HTAP: One System for Both, and What It Costs

The appeal is obvious: no pipeline, no lag, no second copy, query fresh transactional data analytically. The approaches differ enough that "HTAP" alone tells you little.

Approach	Examples	How	Cost
Columnar index inside the OLTP engine	SQL Server columnstore, Oracle In-Memory	a second, columnar copy of chosen columns, maintained transactionally	memory and write amplification; same machine, so contention remains
Columnar replica in the same cluster	TiDB TiFlash, SingleStore, MySQL HeatWave	rows replicated to a columnar node inside the product	operational surface; a distributed system either way
Columnar extension to Postgres	Citus columnar, `pg_duckdb`, `pg_analytics`	columnar tables or an embedded vectorised engine alongside heap tables	maturity varies sharply; feature gaps in what SQL is supported
Embedded engine over exported files	DuckDB over Parquet	not HTAP at all, but often what people actually need	freshness is whatever your export cadence is

What HTAP genuinely buys is the elimination of the pipeline — which is not a small thing, because the pipeline (Module P-23) is where lag, schema drift, and most of the operational cost live. What it costs, and what vendors underplay:

Resource contention does not disappear by being scheduled. Analytical queries want to consume all available I/O and CPU. Sharing a machine with your transactional workload means you now depend on resource governance — workload groups, per-query memory limits, priorities — being configured correctly and holding under load. Module P-8's noisy-neighbour problem, with the neighbour inside the house.
One blast radius. An analytical query that OOMs the node takes checkout down with it. The separation you gave up was also a safety property.
Storage and compute stay coupled. A warehouse lets you keep ten years of history in cheap object storage and spin up compute for one query. An HTAP database keeps analytical data on the same expensive, replicated, high-IOPS storage as your transactional data (Module O-8).
It does not integrate the other systems. Your marketing, support and billing data still is not there, which was reason four the replica failed.

The reasonable position: HTAP is attractive when analytics are operational — dashboards over live state, real-time fraud rules, in-product metrics needing seconds of freshness — and when the analytical volume is bounded. It is not a substitute for a warehouse when the job is company-wide BI across many sources with ten years of history.

The Cost Model Inverts, and So Does the Optimisation

In OLTP you optimise for latency per query, and the lever is indexes. In a warehouse you optimise for bytes scanned or compute-seconds consumed, because that is literally the billing unit — BigQuery charges per byte scanned, Snowflake per second of warehouse uptime. This changes what a "bad query" is.

sql

-- $40. Scans every column of every row, three years back.
SELECT * FROM fact_orders WHERE created_at > '2023-01-01';

-- $0.30. Two columns, partition-pruned to one month, aggregated server-side.
SELECT region, sum(total_cents) FROM fact_orders
WHERE order_date >= '2026-07-01' AND order_date < '2026-08-01'
GROUP BY region;

The three levers, in order of impact:

Partition on the column people filter on, almost always date. It is what allows pruning entire days without reading them, and a table partitioned on the wrong column prunes nothing.
Cluster/sort within partitions on the next most common filter, so zone maps are tight. A randomly-ordered column has min/max spanning everything in every block, and prunes nothing.
Never SELECT *. In a row store it is mild sloppiness; in a columnar store it is the difference between reading 3 columns and 60, and it is the single most expensive habit in a warehouse.

Then the operational guardrails, which belong in place before the first surprise invoice: per-query byte or cost limits, per-team compute quotas, auto-suspend on idle warehouses (an idle cluster nobody turned off is the classic cloud-analytics bill), materialised or pre-aggregated tables for dashboards that thousands of page loads hit, and a query-cost dashboard so the feedback reaches the person writing the SQL. Module O-8 develops the discipline; this is the workload where it bites first and hardest.

Internal BI and In-Product Analytics Are Different Systems

This distinction decides architecture and is routinely missed, because both are called "analytics."

	Internal BI	Customer-facing analytics
Users	tens of analysts	every customer, in the product
Concurrency	tens of queries	thousands per second
Latency budget	seconds to minutes	under 200 ms
Queries	arbitrary, exploratory	a fixed, known set
Freshness	hourly or daily	minutes, sometimes seconds
Right tool	warehouse	pre-aggregation, or a real-time OLAP store

A warehouse is the wrong engine for the second column: a per-query latency of two seconds and a concurrency ceiling of a few dozen makes an in-product dashboard both slow and expensive, and pointing customer page loads at Snowflake is how a five-figure monthly bill appears. Because the query set is fixed, you have a much better option — pre-compute it:

Materialised or rollup tables, refreshed incrementally, queried by primary key. Often this is all you need, and it can live in Postgres.
A real-time OLAP store — ClickHouse, Druid, Pinot — which is columnar but built for high-concurrency, low-latency aggregation over recent data, typically fed from Kafka (Module P-15).
Cache the result (Module F-12); a dashboard tile that is 60 seconds stale is almost always acceptable, and it converts thousands of aggregations into one.

The related question to ask whenever someone requests a "real-time dashboard" is what decision changes based on it. The honest answer is usually that a five-minute or hourly refresh is fine, and the cost difference between hourly batch and streaming freshness is large enough that the question is worth asking before the pipeline is built rather than after.

Where This Shows Up in Your Stack

PostgreSQL: an excellent OLTP engine and a poor warehouse, but it stretches further than people think — native range partitioning gives you real pruning, BRIN indexes are cheap zone maps for naturally-ordered columns, and materialised views cover fixed rollups. Guardrails matter more than tuning: give analytics its own role with a low statement_timeout and work_mem, its own connection pool so it cannot exhaust the application's (Module P-4), and never hot_standby_feedback on a replica serving arbitrary analytical queries unless you accept primary-side bloat (Module P-5). When someone asks for a second replica "for analytics," treat it as the signal that you have outgrown this.
Node.js: never proxy a warehouse query synchronously from a request handler — the latency is seconds and the concurrency ceiling is low, so it becomes a background job (Module P-19) with a concurrency limit (Module P-18) and a cached or materialised result the request actually reads. Serialising a million-row result set through the event loop is its own outage; aggregate in the database and return tens of rows.
Redis: the natural cache for dashboard tiles and rollups, with an extremely favourable hit ratio because every customer viewing the same dashboard requests the same aggregate (Module F-12). A 60-second TTL on a tile is usually the cheapest performance work available in this whole module.
Kafka: the transport between the two worlds, and the substrate for real-time OLAP stores that ingest continuously. It is also what makes the freshness spectrum a dial rather than a rebuild (Module P-15).
Object storage: Parquet on S3/GCS is the interchange format of analytics — columnar, compressed, readable by every engine — and it is where the cheap end of the spectrum lives. Combined with DuckDB it is a complete analytics stack for a surprising number of companies.
Warehouses: partition on the filter column, cluster within partitions, set per-query cost limits and auto-suspend on day one, and put spend on a dashboard someone reads (Module O-8).

Summary

The GROUP BY that takes production down does so through physical layout, not bad SQL. A row store reads all 60 columns to answer a question about 3, evicts your hot working set from the buffer cache so latency stays bad after the query ends, and holds an MVCC snapshot that blocks vacuum for its whole duration. No index fixes a query that must read everything.
Columnar storage wins by reading 1–3% of the bytes: I/O elimination from touching only the needed columns, plus 5–20× compression because adjacent values share a type and often a value. Vectorised execution, late materialisation and zone-map pruning ride on top. The same properties make it unsuitable for OLTP — a single-row update cannot be applied in place, so columnar engines append immutable files and compact in the background.
A read replica solves read volume; this is a read shape problem. The scan costs the same, it evicts the replica's cache and so degrades the application traffic the replica existed for, and it is not isolated from the primary — Module P-5's recovery conflict means you choose between cancelled analytical queries, climbing replica lag, or hot_standby_feedback letting a nine-minute query bloat the primary. It also cannot answer questions that need data from other systems, and it pays OLTP prices for a bulk-throughput workload.
Warehouse, lake and lakehouse differ in what enforces the schema and whether there are transactions. The modern warehouse's real feature is separation of storage and compute, so workloads do not contend and idle costs nothing. A lake is cheap and unmanaged: no transactions, no schema enforcement, and painful row-level deletion — which Module O-9's erasure requirements make a first-order problem. A table format (Iceberg, Delta, Hudi) adds ACID commits, time travel, schema evolution and row-level deletes over the same Parquet files.
Check whether your data is actually big. At 50–500 GB, DuckDB over Parquet in object storage delivers the full columnar benefit on one machine with no cluster and no per-query bill. Buying a warehouse at that size should be a deliberate purchase of managed convenience, not an assumption.
Dimensional modelling inverts the OLTP instinct: denormalise. Declare the fact table's grain explicitly, because a mixed grain double-counts silently and plausibly. Use Type 2 dimensions with surrogate keys for attributes whose history matters, so a customer moving cities does not retroactively rewrite last year's regional revenue — and use Type 1 elsewhere, because Type 2 costs ETL complexity and query-time attribution decisions.
HTAP eliminates the pipeline and keeps the contention. Analytical queries want all the I/O and CPU, so you depend on resource governance holding under load; one blast radius means an analytical OOM takes checkout with it; storage and compute stay coupled on expensive replicated disks; and the other source systems still are not there. It fits operational analytics with bounded volume, not company-wide BI over ten years of history.
The cost model inverts from latency-per-query to bytes-scanned, so SELECT * goes from sloppy to expensive, partitioning on the filtered column is what enables pruning, and clustering within partitions is what makes zone maps tight. Set per-query cost limits, per-team quotas and auto-suspend before the first surprise invoice, not after.
Internal BI and customer-facing analytics are different systems. Tens of analysts tolerating seconds is a warehouse; thousands of customers needing sub-200 ms is pre-aggregation, a real-time OLAP store fed from Kafka, or a cached rollup — because the query set is fixed and can therefore be computed in advance. And when someone asks for "real-time," ask which decision changes: the answer is usually hourly.

The next module, Change Data Capture, is how data actually crosses the boundary this module just drew. Polling for updated_at misses deletes and races with transactions; reading the write-ahead log instead gives you every change in commit order — which turns out to be the same mechanism that keeps a search index in sync (Module P-20), feeds a real-time OLAP store, and, when someone exercises a right to erasure, is what makes the deletion reach every copy you have made.

Knowledge Check

After an analytical query took production down, a team moved all analytics to a dedicated read replica. Six months later: analysts still wait several minutes per query, the application's replica reads have become slow, replica lag alarms fire during business hours, and after someone enabled hot_standby_feedback the primary began showing table bloat. What does the module say went wrong?

A warehouse table fact_orders is partitioned by order_date and clustered by region. An engineer builds an in-product dashboard where each customer's page load runs SELECT * FROM fact_orders WHERE customer_id = $1 AND order_date > current_date - 30. It works in testing. In production the dashboard is slow, and the monthly warehouse bill rises sharply. What are the two defects?

A warehouse's dim_customer table is updated in place by the nightly load: when a customer's region changes, the existing row's region is overwritten. Finance reports that a revenue-by-region report for last year now returns different numbers than the version they filed at the time, and nobody can explain the difference. What is the defect and the fix?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule P-21: Vector Search and Embeddings Next Module P-23: Change Data Capture

Discussion

Join the discussion

Loading comments...