OLTP vs OLAP vs HTAP
Every database answers two very different kinds of question, and most production pain comes from asking one kind of database the other kind of question.
The first kind is "what is the state of this one thing right now, and let me change it": load order #8812, decrement the stock of SKU 4471, insert a payment, mark a ticket as sold. These touch a handful of rows, must finish in milliseconds, and arrive thousands of times per second from many users at once. This is OLTP (Online Transaction Processing).
The second kind is "what happened across everything": revenue per region per week for the last two years, the 95th percentile checkout latency by app version, which marketing channel produced customers who are still active after six months. These touch millions or billions of rows, read only a few columns of each, and arrive a few times per minute from analysts and dashboards. This is OLAP (Online Analytical Processing).
The two workloads are so different that, since the 1990s, the industry has built different engines for each, with different on-disk layouts, different execution models and different operational assumptions. A third category, HTAP (Hybrid Transactional/Analytical Processing), tries to serve both from one system. This page explains why the split exists, what each side optimises for down to the byte layout, how data moves between them, and when HTAP is (and is not) the answer.
Where this page sits
This page is the map. For the B-tree and LSM machinery under OLTP engines, see Storage Engines. For the inside of a column store, see ClickHouse Internals. For designing the transactional side well, see the companion page OLTP Database Design. For modelling the analytical side, see Dimensional Modeling.
Two Workloads, Side by Side
Start with what the queries actually look like. A typical OLTP request in an e-commerce app:
-- Load one order with its lines for the order-details page
SELECT o.id, o.status, o.total_cents, o.created_at,
l.sku, l.quantity, l.unit_price_cents
FROM orders o
JOIN order_lines l ON l.order_id = o.id
WHERE o.id = 88123456
AND o.customer_id = 4410021; -- authorisation check rides along
-- Then, on checkout, a short write transaction
BEGIN;
UPDATE inventory SET on_hand = on_hand - 2
WHERE sku = 'TSHIRT-BLK-M' AND on_hand >= 2;
INSERT INTO orders (customer_id, status, total_cents) VALUES (4410021, 'placed', 3998)
RETURNING id;
COMMIT;
A typical OLAP request over the same business:
-- Weekly revenue by region and channel for the last 104 weeks
SELECT date_trunc('week', o.created_at) AS week,
c.region,
o.channel,
SUM(o.total_cents) / 100.0 AS revenue,
COUNT(DISTINCT o.customer_id) AS buyers
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '104 weeks'
AND o.status IN ('placed', 'shipped', 'delivered')
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;
The first query touches perhaps 6 rows through a primary-key index. The second touches every order placed in two years, possibly 400 million rows, but reads only five of the thirty columns in orders. Everything else in this page follows from that difference.
The Big Comparison Table
| Dimension | OLTP | OLAP |
|---|---|---|
| Typical question | "Get/change this entity" | "Aggregate across many entities" |
| Rows touched per query | 1 – 1,000 | 10⁶ – 10¹¹ |
| Columns touched per query | Most or all of a row | A few of many (5 of 30, 10 of 300) |
| Access path | Index seek on key | Full or range scan, sometimes skipping blocks |
| Read/write mix | Mixed; often 70–95% reads, constant small writes | ~99% reads; writes are bulk loads or append streams |
| Write shape | Single-row INSERT/UPDATE/DELETE, random keys | Batch append (thousands to millions of rows per insert) |
| Updates in place | Constant | Rare; often modelled as new versions or rewrites |
| Latency target | p99 1–50 ms | 100 ms – minutes, depending on tool |
| Concurrency | 1,000s – 100,000s of short queries/sec | 1 – 100s of concurrent heavy queries (Druid/Pinot: 1,000s of light ones) |
| Data volume (hot) | GB – low TB | TB – PB |
| History | Current state, recent history | Years of history, append-mostly |
| Schema style | Normalised (3NF), many narrow tables | Denormalised: star/snowflake schemas, wide fact tables |
| Consistency need | Strict: ACID, isolation, constraints | Relaxed: minutes-stale is usually fine, idempotent loads |
| Users | End users via applications | Analysts, BI dashboards, ML pipelines, finance |
| Bottleneck | Lock contention, random I/O, WAL fsync, connections | Disk/network bandwidth, CPU per row, memory for joins/aggregates |
| Storage layout | Row-oriented pages (8–16 KB) | Column-oriented chunks (MBs), heavily compressed |
| Indexes | Many B-tree indexes, secondary indexes everywhere | Few or none; sort keys, zone maps, min/max, bloom filters |
| Execution model | Tuple-at-a-time (Volcano iterator) | Vectorized batches or compiled pipelines, parallel/MPP |
| Scaling | Vertical, read replicas, then sharding | Horizontal MPP, separated storage and compute |
| Cost model | Pay for provisioned instance and IOPS | Pay per TB stored plus per TB scanned or per compute-second |
| Examples | PostgreSQL, MySQL/InnoDB, Oracle, SQL Server, Aurora, CockroachDB | ClickHouse, Snowflake, BigQuery, Redshift, Druid, Pinot, DuckDB |
Read the table as two internally consistent bundles of choices. You cannot take "row storage" from the left and "scan 400 million rows in a second" from the right; the layout determines the speed.
graph LR
subgraph OLTP["OLTP: many tiny queries"]
U1["App user"] --> Q1["SELECT ... WHERE id = ?"]
U2["App user"] --> Q2["UPDATE ... WHERE id = ?"]
U3["App user"] --> Q3["INSERT one row"]
Q1 --> IDX["B-tree index seek<br/>3-4 page reads"]
Q2 --> IDX
Q3 --> IDX
end
subgraph OLAP["OLAP: few huge queries"]
A1["Analyst / BI"] --> S1["SELECT agg(...) GROUP BY ..."]
S1 --> SCAN["Scan 3 of 30 columns<br/>over 400M rows"]
end
Workload Characteristics in Numbers
Rough, order-of-magnitude figures for a mid-sized product (say, 5 million monthly active users):
| Metric | OLTP primary (PostgreSQL) | Warehouse (Snowflake/BigQuery/ClickHouse) |
|---|---|---|
| Queries per second | 5,000 – 30,000 | 0.1 – 50 |
| Median query latency | 0.3 – 3 ms | 0.5 – 20 s |
| Bytes read per query | 8 – 200 KB | 100 MB – 1 TB |
| Rows written per second | 500 – 5,000, one at a time | 0 most of the time, then 10⁶ in a batch |
| Working set that must be in RAM | Hot indexes + hot rows (10–200 GB) | Nothing has to be; streaming scans |
| Total data | 200 GB – 5 TB | 20 TB – 2 PB (includes years of history and events) |
The product of "queries per second" and "bytes per query" is surprisingly similar on both sides: both are bandwidth-bound systems. OLTP spends its bandwidth on many small random reads; OLAP spends it on a few enormous sequential ones. The storage layout decides which of these is cheap.
Row-Oriented vs Column-Oriented Storage
The Same Table, Two Layouts
Take a tiny orders table:
| id | customer_id | status | total_cents | created_at |
|---|---|---|---|---|
| 1 | 501 | shipped | 2599 | 2026-11-01 09:14 |
| 2 | 502 | placed | 1200 | 2026-11-01 09:15 |
| 3 | 501 | shipped | 4999 | 2026-11-01 09:20 |
| 4 | 503 | cancelled | 899 | 2026-11-01 09:22 |
A row store writes each row's fields next to each other, then the next row. A column store writes all values of one column next to each other, then the next column.
Byte-level view of a PostgreSQL-style heap page (simplified; real tuples also carry alignment padding):
ROW STORE: one 8 KB heap page
offset 0 ┌──────────────────────────────────────────────────────────────┐
│ PageHeader (24 B): LSN, checksum, flags, pd_lower, pd_upper │
24 │ ItemId[1] ItemId[2] ItemId[3] ItemId[4] (4 B each) │
40 │ ........ free space ........ │
│ │
7900 │ Tuple4: [hdr 23B: xmin xmax ctid infomask][4][503] │
│ ["cancelled"][899][2026-11-01 09:22] │
7950 │ Tuple3: [hdr 23B][3][501]["shipped"][4999][2026-11-01 09:20] │
8000 │ Tuple2: [hdr 23B][2][502]["placed"][1200][2026-11-01 09:15] │
8100 │ Tuple1: [hdr 23B][1][501]["shipped"][2599][2026-11-01 09:14] │
8192 └──────────────────────────────────────────────────────────────┘
Each tuple ~ 23 B header + ~40 B data. ~120 rows per page.
Reading total_cents for every row = reading EVERY page.
COLUMN STORE: one file (or chunk) per column, values contiguous
id.bin [1][2][3][4]... int64, delta-encoded -> ~1 B/row
customer_id.bin [501][502][501][503]... int32, dictionary/bit-packed
status.bin dict{0:placed,1:shipped,2:cancelled} + codes [1][0][1][2]... 2 bits/row
total_cents.bin [2599][1200][4999][899]... int32, frame-of-reference + LZ4
created_at.bin [t0][t0+60][t0+360][t0+480]... delta-of-delta -> ~1-2 B/row
+ per-chunk metadata: min/max per column, row count, null count
Reading total_cents for every row = reading ONE small file.
graph TB
subgraph RS["Row store: rows packed into pages"]
P1["Page 1<br/>row1: id,cust,status,total,ts<br/>row2: id,cust,status,total,ts<br/>..."]
P2["Page 2<br/>row121: id,cust,status,total,ts<br/>..."]
end
subgraph CS["Column store: one stream per column"]
C1["id: 1,2,3,4,..."]
C2["customer_id: 501,502,501,503,..."]
C3["status: shipped,placed,shipped,cancelled,..."]
C4["total_cents: 2599,1200,4999,899,..."]
C5["created_at: t0,t0+60,t0+360,..."]
end
Q["SUM(total_cents)"] -.->|"must read every page"| RS
Q -.->|"reads only this stream"| C4
Why Row Stores Win for OLTP
A row store puts everything about one entity in one place. That is exactly what OLTP wants.
- One page read fetches the whole row.
SELECT * FROM orders WHERE id = 88123456is a B-tree descent (3–4 pages, the top levels almost always cached) plus one heap page. In a column store, the same query must touch one chunk per column: 30 columns means 30 separate reads and 30 decompressions to reassemble one row. - Single-row writes are cheap. An
INSERTappends one tuple to one page and writes one WAL record. AnUPDATEin PostgreSQL writes a new tuple version; in InnoDB it modifies the row in its clustered-index leaf and writes undo. In a column store an insert touches every column file, and compressed, sorted columns are expensive to modify in place, which is why column stores buffer writes and merge them in the background. - Fine-grained locking and MVCC are natural. A row has an identity (a
ctidin PostgreSQL, a primary key in InnoDB) with a header that carries transaction visibility (xmin/xmax). Locking one row, or detecting a conflicting update to one row, is local. See MVCC. - Secondary indexes point at rows. A B-tree on
customer_idmaps to row locations; following it costs one more page per match. That makes "orders for customer 501" fast without scanning.
Why Column Stores Win for OLAP
Four mechanisms compound. Each alone gives a factor of a few; together they give two to three orders of magnitude.
1. I/O: Read Only the Columns You Need
If orders has 30 columns averaging 7 bytes and the query uses 3 of them, a row store reads 100% of the table and throws away 90% of the bytes after reading them. A column store reads 10%. Reading less is the single largest win, and it is purely a layout effect.
Even when the whole table fits in RAM this matters: memory bandwidth (roughly 20–50 GB/s per socket) and CPU cache are also finite, and dragging 27 unused columns through the cache evicts the data you do need.
2. Compression: Similar Values Next to Each Other
A column holds values of one type and usually one distribution. That makes it compress far better than a row, which interleaves integers, strings and timestamps.
| Encoding | Works on | Example | Typical ratio |
|---|---|---|---|
| Dictionary | Low-cardinality strings | status: 5 distinct values → 3-bit codes | 10–50× |
| Run-length (RLE) | Sorted or repetitive columns | country sorted: ('IN', 2,100,000) | 100×+ when sorted |
| Delta / delta-of-delta | Monotonic ints, timestamps | created_at: store differences of a few seconds | 4–8× |
| Frame-of-reference + bit-packing | Ints in a narrow range | quantity 1–20 → 5 bits each | 6× |
| General-purpose (LZ4, ZSTD) | Applied on top of the above | Page-level compression | 2–4× extra |
Real-world numbers: event and log tables in ClickHouse commonly compress 5–15× overall, with sorted low-cardinality columns hitting 100× or more; Parquet files are typically 3–10× smaller than the equivalent CSV. A row-store table with page compression (InnoDB ROW_FORMAT=COMPRESSED, SQL Server page compression) usually gets 1.5–3×.
Compression is not just storage savings. If the bottleneck is I/O, a column compressed 8× is effectively read 8× faster, and decompression with LZ4 runs at multiple GB/s per core, faster than the disk delivers.
The sort order matters enormously. A column store chooses a sort key per table (ClickHouse ORDER BY, Redshift SORTKEY, Snowflake clustering keys). Sorting by (tenant_id, event_date) makes both columns collapse into long runs, and makes every chunk's min/max range narrow, which enables the next trick.
3. Data Skipping: Zone Maps Instead of Indexes
Column stores rarely use B-tree secondary indexes. Instead they keep tiny per-chunk metadata: the minimum and maximum of each column in each block of rows (ClickHouse calls a block a granule, 8,192 rows by default; Snowflake calls the unit a micro-partition, 50–500 MB uncompressed; Redshift blocks are 1 MB per column; Parquet has row groups and pages with statistics).
A filter WHERE created_at >= '2026-10-01' checks the metadata first and skips every block whose max is earlier. On a table sorted by time, a one-month filter over two years of data skips ~96% of blocks before reading a byte of data. These zone maps (also called min/max indexes or sparse indexes) cost kilobytes per billion rows, where a B-tree on the same column would cost tens of gigabytes and slow every insert.
4. Vectorized Execution
A classic OLTP engine executes queries with the Volcano (iterator) model: each operator calls next() on its child to get one row, processes it, and returns one row. For a 6-row lookup that overhead is irrelevant. For 400 million rows, the per-row cost of virtual function calls, branch mispredictions and interpretation of expression trees dominates: PostgreSQL typically aggregates on the order of 10–50 million rows per second per core for a simple SUM.
Column engines process vectors of values instead: a batch of 1,024–65,536 values from one column at a time (DuckDB uses 2,048-value vectors; ClickHouse blocks are up to 65,536 rows). The inner loop is a tight loop over a contiguous array:
// What "SUM(total_cents) WHERE status = 'shipped'" becomes, conceptually
for (int i = 0; i < n; i++) {
sum += (status_code[i] == SHIPPED) ? total_cents[i] : 0; // branch-free
}
That loop has no function calls, predictable memory access the hardware prefetcher loves, and maps to SIMD instructions (AVX2 processes 8 int32 values per instruction, AVX-512 processes 16). Vectorized engines routinely aggregate 500 million to over a billion values per second per core on simple expressions. Some systems (Hyper, Umbra, Spark's whole-stage codegen) get similar results by compiling the query to machine code instead; the goal is the same: remove per-row overhead.
5. Late Materialization
A row store must "materialize" (construct) every row in full as soon as it reads it. A column store can delay building rows until it knows which ones survive the filters.
For SELECT customer_id, total_cents FROM orders WHERE status = 'cancelled' AND created_at >= '2026-10-01':
- Scan
created_at(cheap, compressed, partly skipped by zone maps). Produce a selection vector: bitmap of matching positions. - Scan
statusonly at those positions; AND the bitmaps. Now perhaps 0.4% of positions survive. - Only then fetch
customer_idandtotal_centsat the surviving positions.
Columns that only appear in the projection are read for 0.4% of rows instead of 100%. ClickHouse calls a version of this PREWHERE, applied automatically to the most selective filter columns.
graph LR
A["created_at column<br/>skip blocks by min/max"] --> B["selection vector<br/>12% of rows"]
B --> C["status column<br/>read only selected positions"]
C --> D["selection vector<br/>0.4% of rows"]
D --> E["fetch customer_id, total_cents<br/>for 0.4% only"]
E --> F["build output rows"]
Where Each Layout Hurts
| Operation | Row store | Column store |
|---|---|---|
| Point lookup by key, all columns | 1 index descent + 1 page | 1 read per column, decompress whole blocks |
| Single-row insert | 1 page + 1 WAL record | Touches every column; usually buffered into a new small "part" |
| Update one field of one row | Cheap (new version or in-place) | Expensive: rewrite a part, or mark deleted + reinsert |
| Delete one row | Cheap | Usually a delete bitmap or async mutation |
| Scan 3 of 30 columns over 10⁹ rows | Reads 100% of bytes, tuple-at-a-time | Reads ~5% of bytes after compression, vectorized |
COUNT(*) with time filter | Index-only scan if indexed, otherwise full scan | Metadata and zone maps; often near-instant |
| High concurrency (10k qps) | Designed for it | Most are not (except Druid/Pinot-style systems) |
Column stores hate small writes
Inserting one row at a time into ClickHouse creates one tiny part per insert and quickly hits "Too many parts". Snowflake and BigQuery charge and perform best with micro-batches. The column-store way is to buffer (Kafka, async inserts, staging files) and load thousands to millions of rows at once. See ClickHouse Internals.
Hybrid Layouts You Will Meet
The line is not perfectly sharp:
- PAX (Partition Attributes Across): inside each page, store values column by column. Parquet and ORC row groups are PAX-like at a larger scale: a file contains row groups (64–128 MB typical), and inside each row group each column is a contiguous chunk. This keeps a row's columns in the same file while preserving columnar scans.
- Column-family stores (Cassandra, HBase) are not column stores in this sense. They group columns into families but store each row's family together, keyed by row. They are write-optimised LSM row stores. See Cassandra Internals.
- Columnstore indexes inside row databases (SQL Server, Oracle In-Memory, AlloyDB) keep a second, columnar copy of selected data. These are the basis of most HTAP designs below.
The Same Query on Both: Benchmark Intuition
Numbers make the difference concrete. Consider:
orders: 500 million rows, 30 columns, ~200 bytes per row in PostgreSQL including the 23-byte tuple header and padding. Heap size about 100 GB.- Query: monthly revenue for the last 12 months, filtered to non-cancelled orders.
SELECT date_trunc('month', created_at) AS month, SUM(total_cents)
FROM orders
WHERE created_at >= '2025-11-01' AND status <> 'cancelled'
GROUP BY 1 ORDER BY 1;
Assume the last 12 months are 40% of the table (200M rows), and an NVMe disk sustaining ~2 GB/s.
PostgreSQL, row store, no special index. Sequential scan of 100 GB. Even with a B-tree on created_at, the planner will usually prefer a sequential scan when 40% of the table qualifies (random heap fetches would be worse). Disk time is around 50 s cold; if the table is fully cached, the CPU still has to walk 500 million tuples, checking visibility for each. With 8 parallel workers you might see 20–60 seconds. If the table were range-partitioned by month, partition pruning would cut the scan to the 12 relevant partitions (~40 GB): perhaps 8–25 seconds.
Column store (ClickHouse, sorted by created_at). The query needs three columns:
| Column | Raw size for 200M rows | Compressed (typical) |
|---|---|---|
created_at (DateTime, 4 B) | 800 MB | ~40 MB (sorted, delta + ZSTD) |
status (LowCardinality) | ~200 MB as codes | ~10 MB |
total_cents (Int32) | 800 MB | ~300 MB |
| Total | 1.8 GB | ~350 MB |
Zone maps skip the 60% of the table outside the date range, so only 350 MB is read, at 2 GB/s about 0.2 s, and often it is already in the page cache. The vectorized aggregation over 200M values on 16 cores runs well under a second. Expected result: 0.2–1 second.
| Row store (PG, 8 workers) | Row store, partitioned | Column store | |
|---|---|---|---|
| Bytes read | ~100 GB | ~40 GB | ~0.35 GB |
| Rows processed by CPU | 500M | 200M | 200M (in vectors) |
| Wall time (rough) | 20–60 s | 8–25 s | 0.2–1 s |
| Ratio | 1× | ~2.5× | ~50–200× |
Now flip it: fetch order #88123456 with all 30 columns.
| Row store | Column store | |
|---|---|---|
| Work | B-tree descent (cached) + 1 heap page | Primary-key range lookup, then 30 columns × 1 granule each, decompress 8,192 rows per granule |
| Wall time | 0.05–0.5 ms | 2–20 ms |
| At 10,000 qps | Comfortable on one node | Likely to saturate the cluster |
These are intuitions, not a benchmark
The ratios above are what you should expect to see in the order of magnitude; real results depend on hardware, data distribution, caching and tuning. Published benchmarks such as ClickBench (single-table analytics over ~100M web-analytics rows) consistently show column engines one to three orders of magnitude ahead of row stores on aggregates, and TPC-C style benchmarks show the reverse for transactions. Always benchmark your own queries before migrating.
OLTP Engines
The major transactional databases share a family resemblance: row-oriented pages, B-tree indexes, a write-ahead log, MVCC or locking for isolation, and a cost-based optimizer tuned for index access.
| Engine | Storage model | Concurrency | Strengths | Watch out for |
|---|---|---|---|---|
| PostgreSQL | Heap tables (8 KB pages) + separate B-tree indexes pointing at ctid | MVCC with tuple versions in the heap; old versions cleaned by VACUUM | Rich types (JSONB, arrays, ranges), extensions (PostGIS, pgvector, Citus, TimescaleDB), partial and expression indexes, strong standards compliance | Bloat and VACUUM tuning, process-per-connection (pool it), write amplification from updating indexed columns |
| MySQL / InnoDB | Clustered index: table is the primary-key B+tree (16 KB pages); secondary indexes store the PK | MVCC with undo logs; default isolation REPEATABLE READ with gap/next-key locks | Very fast PK lookups, mature replication, huge operational ecosystem, Vitess for sharding | Random PKs fragment the clustered index; long transactions grow undo history; gap locks surprise people |
| Oracle | Heap segments, 8 KB blocks by default; index-organized tables optional | MVCC via undo segments ("read consistency"); writers never block readers | RAC clustering, partitioning, mature tooling, In-Memory column store option | Licensing cost, complexity, "snapshot too old" when undo is undersized |
| SQL Server | Clustered index (B+tree) or heap, 8 KB pages | Locking by default; optional RCSI / snapshot isolation using tempdb version store | Excellent tooling, columnstore indexes for HTAP-ish use, temporal tables built in | Lock escalation, tempdb pressure under RCSI, licensing |
| Cloud variants (Aurora, AlloyDB, Neon, Azure SQL Hyperscale) | Same engines over a distributed log-structured storage layer | Same as parent engine | Fast replicas sharing storage, storage auto-grows, fast failover | Still a single writer; I/O billed differently |
| Distributed SQL (CockroachDB, YugabyteDB, TiDB, Spanner) | LSM key-value per range + Raft replication | MVCC with hybrid/atomic clocks | Horizontal writes, multi-region | Higher per-query latency, cross-range transactions cost more. See NewSQL |
What they optimise for:
- Latency of a single small transaction, dominated by WAL fsync (0.05–2 ms on SSD) and lock waits.
- Throughput under concurrency, via row-level locking, MVCC and group commit.
- Correctness: constraints, foreign keys, serializable isolation when you need it. See Isolation Levels.
What they are bad at is exactly the column store's strength: reading a few columns from a huge number of rows. They can do it, and for a few million rows a well-indexed PostgreSQL is a perfectly good analytics engine. At hundreds of millions of rows with ad hoc queries, it becomes slow and, worse, it hurts the transactional workload sharing the machine.
OLAP Engines
Analytical engines differ more from each other than OLTP engines do. The main axes are: where the data lives (local disks vs object storage), who runs it (you vs the vendor), and what concurrency and freshness they target.
| Engine | Architecture | Storage | Sweet spot | Weak spot |
|---|---|---|---|---|
| ClickHouse | Shared-nothing MPP (or shared storage in ClickHouse Cloud); MergeTree parts | Local columnar parts, sorted by ORDER BY key, sparse primary index | Real-time event/log analytics, sub-second dashboards on billions of rows, self-hosting | Joins on large right tables, frequent updates/deletes, single-row inserts |
| Snowflake | Separated storage and compute; virtual warehouses (compute clusters) scale independently | Micro-partitions (50–500 MB uncompressed) on object storage with rich metadata | Enterprise warehouse, many teams, elastic compute, data sharing, semi-structured VARIANT | Per-second compute billing adds up; seconds-level, not milliseconds-level, latency |
| BigQuery | Serverless; Dremel execution over Colossus storage; capacity measured in "slots" | Capacitor columnar format; partitioning and clustering | Zero-ops, huge ad hoc scans, pay-per-query or reserved slots | Unbounded scans cost money (billed per byte scanned on-demand); not for high-qps serving |
| Amazon Redshift | MPP: leader node + compute nodes; RA3 nodes use managed storage on S3 | 1 MB columnar blocks with zone maps; sort keys and distribution keys | AWS-centric warehouses, predictable workloads | Tuning dist/sort keys, concurrency scaling costs, vacuum/sort maintenance on older setups |
| Apache Druid / Apache Pinot | Real-time ingestion from Kafka + historical segments; scatter-gather brokers | Segments with dictionary encoding, bitmap and inverted indexes, pre-aggregation (rollup) | User-facing analytics: thousands of qps, p99 under 100 ms, fresh within seconds | Limited joins, schema/rollup decisions are rigid, operationally heavy |
| DuckDB | In-process (like SQLite for analytics), vectorized, single node | Own columnar file format; reads Parquet/CSV/Iceberg directly | Local analysis, notebooks, ETL steps, embedded analytics, querying Parquet on S3 | Single machine; one writer process; not a shared server |
| Spark SQL / Trino / Databricks SQL | Distributed query engines over a lake | Parquet/ORC in Iceberg/Delta/Hudi tables on object storage | Lakehouse queries, huge batch transforms | Startup latency, tuning; see Query Engines |
For a detailed comparison of the real-time trio, see ClickHouse vs Druid vs Pinot. For time-series specific engines (TimescaleDB, InfluxDB, VictoriaMetrics), which apply column-store ideas to metrics, see Time-Series Databases and Time-Series Storage Engines.
MPP and the Separation of Storage and Compute
Most analytical engines are MPP (massively parallel processing): a query is split into fragments that run on many nodes over different slices of data, and partial results (partial sums, partial hash tables) are merged.
graph TB
Client["BI tool / SQL client"] --> Coord["Coordinator<br/>parse, plan, split"]
Coord --> W1["Worker 1<br/>scan slice 1<br/>partial aggregate"]
Coord --> W2["Worker 2<br/>scan slice 2<br/>partial aggregate"]
Coord --> W3["Worker 3<br/>scan slice 3<br/>partial aggregate"]
W1 --> Merge["Merge partial results<br/>final GROUP BY, ORDER BY"]
W2 --> Merge
W3 --> Merge
Merge --> Client
W1 -.-> S3[("Object storage<br/>columnar files")]
W2 -.-> S3
W3 -.-> S3
The second big idea is separating storage from compute. Snowflake, BigQuery, Redshift RA3, Databricks and ClickHouse Cloud keep the columnar files in object storage (S3, GCS, Azure Blob) and spin up compute only to query them. Consequences:
- You can run a 64-node cluster for 3 minutes for the month-end report, then pay nothing.
- Different teams get isolated compute over the same data (no noisy neighbours).
- The first read of cold data is slower (object storage latency is tens of milliseconds), so engines cache aggressively on local SSD.
OLTP systems cannot easily do this, because every transaction needs low-latency, durable, consistent writes; cloud OLTP engines like Aurora and AlloyDB separate storage only at the log/page level, keeping a single writer.
Warehouse vs Lake vs Lakehouse
Analytical data lives in one of three broad architectures. They share column-oriented storage but differ in who owns the format and the transactions.
| Data warehouse | Data lake | Lakehouse | |
|---|---|---|---|
| What it is | A database built for analytics: you load data, it owns storage | Files in object storage (Parquet, JSON, CSV, images), schema-on-read | Lake files + an open table format that adds transactions and metadata |
| Examples | Snowflake, BigQuery, Redshift, ClickHouse, Teradata | S3/GCS/ADLS + Hive metastore, raw zones | Apache Iceberg, Delta Lake, Apache Hudi on object storage, queried by Spark, Trino, Snowflake, DuckDB |
| Schema | Schema-on-write, enforced | Schema-on-read, often messy | Schema enforced and evolvable by the table format |
| Transactions | Full ACID within the warehouse | None; partial writes are visible | ACID commits via snapshot metadata (atomic swap of manifest pointer) |
| Performance | Best out of the box, managed indexes/clustering | Depends entirely on file layout | Close to warehouse with good compaction and clustering |
| Openness | Proprietary storage (mostly) | Fully open | Open format, many engines over the same data |
| Good for | BI, finance, governed metrics | Raw data, ML training data, cheap retention | Both, with one copy of data |
A typical modern layout is medallion: raw (bronze) → cleaned (silver) → business-level aggregates (gold), with gold tables often served from a warehouse or real-time engine. Details in Medallion Architecture and Table Formats.
Within the warehouse, data is modelled differently from OLTP. The transactional schema is normalised to make writes correct; the analytical schema is a star schema of fact tables (one row per event: an order line, a page view) surrounded by dimension tables (customer, product, date), deliberately denormalised to make reads simple and fast. See Dimensional Modeling, Slowly Changing Dimensions and Normalization vs Denormalization.
Moving Data from OLTP to OLAP
If OLTP and OLAP live in different systems, data has to flow from one to the other. That pipeline defines how fresh your analytics are, how much load you put on the primary, and how many ways things can break.
graph LR
subgraph Sources["OLTP sources"]
PG[("PostgreSQL")]
MY[("MySQL")]
end
PG -->|"nightly dump / query"| Batch["Batch ETL/ELT<br/>Airflow, dbt"]
PG -->|"WAL logical decoding"| Deb["Debezium CDC"]
MY -->|"binlog"| Deb
Deb --> K["Kafka topics<br/>one per table"]
K --> Sink["Sink connector /<br/>stream processor"]
Batch --> WH[("Warehouse /<br/>lakehouse")]
Sink --> WH
Sink --> RT[("Real-time OLAP<br/>ClickHouse, Pinot")]
WH --> BI["BI dashboards,<br/>ML features"]
RT --> APP["User-facing<br/>analytics"]
ETL vs ELT
ETL (Extract, Transform, Load) transforms data in a separate system (Informatica, Spark jobs, custom scripts) before loading it. ELT (Extract, Load, Transform) loads raw data into the warehouse first and transforms it there with SQL, typically with dbt. ELT won in the cloud era because warehouse compute became elastic and SQL transforms are easier to version, test and review. See ETL vs ELT and Incremental Loads.
The naive extraction approach is a query against the primary:
-- "Incremental" extract by updated_at watermark, run every 15 minutes
SELECT * FROM orders
WHERE updated_at > :last_watermark
ORDER BY updated_at;
It works, but has three classic problems:
- Missed rows: a transaction that started before the watermark but committed after the extract read can have an
updated_atolder than the new watermark. Use an overlap window and idempotent upserts downstream. - Missed deletes: a deleted row has no
updated_atto find. You need soft deletes or a full comparison. - Load on the primary: big extracts compete with production traffic. Run them on a replica.
Change Data Capture
CDC reads the database's own replication log instead of querying tables: PostgreSQL's WAL via logical decoding (pgoutput plugin and a replication slot), MySQL's binlog in ROW format, SQL Server's CDC tables, Oracle's redo logs (LogMiner). Every committed insert, update and delete becomes an event, in commit order, with before and after images.
Debezium is the standard open-source CDC platform: Kafka Connect source connectors for PostgreSQL, MySQL, SQL Server, Oracle, MongoDB and others, emitting one Kafka topic per table.
-- PostgreSQL side of a Debezium setup
ALTER SYSTEM SET wal_level = 'logical'; -- requires restart
CREATE PUBLICATION dbz_pub FOR TABLE orders, order_lines, customers;
-- Debezium creates the replication slot itself (slot.name in connector config)
SELECT slot_name, active, pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
A change event (simplified):
{
"before": { "id": 88123456, "status": "placed", "total_cents": 3998 },
"after": { "id": 88123456, "status": "shipped", "total_cents": 3998 },
"source": { "connector": "postgresql", "lsn": 81723984512, "txId": 9921331, "table": "orders" },
"op": "u",
"ts_ms": 1794134400123
}
CDC advantages over polling: captures deletes, captures every intermediate state, near-zero query load on the primary, seconds of latency. Costs: a replication slot that retains WAL until the consumer confirms, so a stalled Debezium can fill the primary's disk; schema changes must be handled downstream; initial snapshots of large tables need care. Patterns, pitfalls and the outbox variant are covered in CDC Patterns and Transactional Outbox.
The replication slot that ate the disk
An inactive logical replication slot pins WAL forever. Monitor retained_wal from the query above and alert well before the disk fills. Since PostgreSQL 13 you can cap it with max_slot_wal_keep_size, which invalidates the slot instead of taking down the primary. Pick the failure you prefer: a broken pipeline you re-snapshot, or a production outage.
Freshness Tiers
| Approach | Typical freshness | Primary load | Complexity |
|---|---|---|---|
| Nightly full dump | 24 h | High, once a day | Low |
| Incremental batch by watermark | 15 min – 1 h | Medium | Medium (deletes, late commits) |
| CDC → Kafka → warehouse micro-batches | 1 – 10 min | Very low | Medium–high |
| CDC → Kafka → real-time OLAP (ClickHouse/Pinot) | 1 – 30 s | Very low | High |
| HTAP (same system) | Sub-second | Shared hardware or replica | Depends on product |
Most organisations need the first or third tier for most data, and the fourth for a handful of user-facing metrics. The urge to make everything real-time is one of the more expensive mistakes in data engineering.
HTAP: Serving Both from One System
The pipeline above has costs: two systems to run, a pipeline that breaks, minutes of staleness, and duplicate copies of data. HTAP systems try to remove the pipeline by maintaining both a row-oriented and a column-oriented representation inside one database, keeping them consistent, and routing each query to the right one.
The core problem HTAP must solve is the one this page started with: the two layouts are optimal for opposite operations. Every HTAP design is some answer to "how do I keep a column copy fresh without paying column-store write costs on every OLTP write, and without analytical queries starving transactional ones?"
graph TB
App["Application<br/>OLTP traffic"] --> Router["SQL layer / optimizer<br/>chooses engine per query or per table scan"]
BI["Analytics<br/>dashboards"] --> Router
Router --> Row[("Row store<br/>B-tree / LSM<br/>source of truth")]
Router --> Col[("Column store<br/>replica or in-memory copy")]
Row -->|"log replication or<br/>delta merge"| Col
TiDB with TiFlash
TiDB is a distributed SQL database (MySQL-compatible) whose row storage, TiKV, is an LSM-based key-value store replicated with Raft in regions of ~96 MB. TiFlash is a columnar storage engine that joins each Raft group as a learner: it receives the replicated log asynchronously, does not vote, and therefore cannot slow down OLTP commits.
-- Add 2 columnar replicas of a table
ALTER TABLE orders SET TIFLASH REPLICA 2;
-- Check replication progress
SELECT table_name, replica_count, available, progress
FROM information_schema.tiflash_replica;
-- The optimizer picks TiKV or TiFlash per table scan; you can force it
SELECT /*+ READ_FROM_STORAGE(TIFLASH[o]) */ o.region, SUM(o.total_cents)
FROM orders o GROUP BY o.region;
Freshness: a TiFlash read first asks the Raft leader for its current commit index and waits until the learner has applied up to that point (a learner read), then reads the snapshot at the query's timestamp. So analytical reads are strongly consistent with OLTP writes, typically adding milliseconds of wait. Isolation: TiFlash runs on separate nodes, so heavy scans do not compete for TiKV's CPU and disk. TiFlash also supports an MPP mode for distributed joins and aggregations.
Trade-offs: you pay for and operate a second set of storage nodes; a distributed SQL database has higher single-transaction latency than a single-node PostgreSQL; MySQL compatibility has gaps.
SingleStore
SingleStore (formerly MemSQL) is a distributed SQL database whose default table type, "universal storage", is a columnstore that supports OLTP: newly written rows land in an in-memory rowstore segment that is periodically flushed into compressed column segments, and the column segments carry hash indexes and seekable encodings so point lookups do not require full-segment decompression. It also offers pure in-memory rowstore tables for the hottest transactional data.
Trade-offs: a strong fit for "real-time analytics on fresh data with moderate transactions" workloads; a less natural fit than PostgreSQL for complex, highly concurrent OLTP with many secondary indexes and heavy update contention. It is a commercial product with a licence cost.
AlloyDB Columnar Engine
AlloyDB for PostgreSQL (Google Cloud) keeps standard PostgreSQL row storage and adds a columnar engine: an in-memory columnar representation of selected columns or tables. It can be populated automatically, based on observed query patterns, or manually:
-- AlloyDB: add a table (or specific columns) to the columnar engine
SELECT google_columnar_engine_add('orders');
-- The planner uses Columnar Scan nodes when beneficial; check with EXPLAIN
EXPLAIN SELECT region, SUM(total_cents) FROM orders GROUP BY region;
Changes to the row store are tracked and the columnar data is refreshed, with recent changes read from the row store so results stay consistent. Google cites up to 100× speed-ups for analytical queries in favourable cases. Because it is in memory on the same instance (or on read pools), it competes with the buffer cache for RAM, and heavy analytics on the primary still compete for CPU; most teams point analytics at a read pool instance with the columnar engine enabled.
Oracle In-Memory and SQL Server Columnstore
The incumbents had HTAP features before the term was fashionable:
- Oracle Database In-Memory: a "dual-format" architecture. Tables stay in row format on disk and in the buffer cache; selected tables are also held in an in-memory column store (IM column store), kept transactionally consistent by tracking changed rows. The optimizer chooses per query.
- SQL Server columnstore indexes: since SQL Server 2016, you can add an updatable nonclustered columnstore index to a normal rowstore table. New rows go to a delta rowgroup (a B-tree), and a background tuple mover compresses closed rowgroups (up to ~1 million rows each) into columnar segments. Microsoft calls this "real-time operational analytics".
-- SQL Server: columnar copy of selected columns for analytics on the OLTP table
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_orders
ON dbo.orders (created_at, status, region, total_cents)
WHERE status <> 'draft'; -- filtered columnstore keeps hot, churning rows out
MySQL HeatWave
MySQL HeatWave (Oracle Cloud, also available on AWS and Azure) attaches an in-memory, columnar, massively parallel query accelerator to a MySQL database. You load tables into the HeatWave cluster; InnoDB changes propagate to it automatically, and the optimizer offloads qualifying queries transparently.
ALTER TABLE orders SECONDARY_ENGINE = RAPID;
ALTER TABLE orders SECONDARY_LOAD;
-- Queries now offload when the cost model says so; EXPLAIN shows "Using secondary engine RAPID"
Trade-offs: excellent for MySQL shops that want analytics without a pipeline; you are tied to a specific managed service, and the HeatWave cluster's memory determines how much data you can accelerate.
PostgreSQL Columnar Extensions
PostgreSQL's extensibility has produced several ways to get columnar storage or vectorized execution without leaving Postgres:
| Option | What it does | Notes |
|---|---|---|
| Citus columnar | Table access method USING columnar: stripes of compressed column chunks with min/max skipping | Append-oriented; historically no UPDATE/DELETE on columnar tables; great for cold partitions |
| Hydra columnar | Fork of Citus columnar with vectorized execution and update/delete support | Positioned as "Postgres for analytics" |
| TimescaleDB compression / Hypercore | Converts older chunks of hypertables to a columnar, compressed form | Ideal for time-series: recent chunks in row form, old ones columnar |
| pg_duckdb, pg_mooncake, ParadeDB pg_analytics | Embed DuckDB's vectorized engine to query Parquet/Iceberg/Delta or columnar tables from Postgres | Newer; fast-moving; check maturity before betting on it |
A pragmatic pattern combines PostgreSQL partitioning with a columnar access method: recent partitions stay as heap tables (fast OLTP updates), and partitions older than N days are converted to columnar.
-- Citus columnar: older months as compressed columnar partitions
CREATE EXTENSION IF NOT EXISTS citus_columnar;
CREATE TABLE events (
tenant_id bigint NOT NULL,
event_type text NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_11 PARTITION OF events
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01'); -- heap, hot
CREATE TABLE events_2026_09 PARTITION OF events
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01') USING columnar; -- cold, compressed
How HTAP Systems Keep Both, Compared
| System | Column copy lives | Freshness | Isolation of analytics from OLTP | Main cost |
|---|---|---|---|---|
| TiDB + TiFlash | Separate nodes, Raft learner | Strongly consistent (learner read) | Strong: separate hardware | Second storage tier, distributed-SQL latency |
| SingleStore | Same tables (rowstore segment + column segments) | Immediate | Shared nodes; resource pools | Licence, OLTP features narrower than Postgres |
| AlloyDB columnar engine | In memory, same instance or read pool | Consistent (deltas from row store) | Good if on read pool | RAM, Google Cloud lock-in |
| Oracle In-Memory | In memory, same instance | Transactionally consistent | Shared instance | Licence option cost, RAM |
| SQL Server NCCI | On disk, same database | Immediate (delta rowgroups) | Shared instance | Write overhead, index maintenance |
| MySQL HeatWave | Separate in-memory cluster | Near-real-time propagation | Strong: separate nodes | Managed-service lock-in, memory size |
| Postgres columnar extensions | Same database, different partitions/tables | Immediate (but often append-only) | Shared instance | Limited update support, extension maturity |
HTAP Trade-offs, Honestly
HTAP is real and useful, but it does not abolish the laws of physics described earlier:
- Resources are still finite. If the column copy lives on the same instance as the row store, a 40-second analytical query still consumes CPU, memory bandwidth and buffer cache that OLTP needs. The good HTAP designs (TiFlash, HeatWave) put the column copy on separate hardware, which is architecturally a replica with a smarter router.
- It solves freshness, not modelling. Analysts usually want star schemas, history (SCD type 2), cleaned and conformed data from many sources, and metrics definitions. An HTAP database holds only the operational schema of one application. You still need a warehouse for cross-system analytics.
- Write amplification. Every write updates the row store and, eventually, the column representation. Systems hide this with delta stores and background merges, but the work happens.
- Lock-in. Most HTAP options are tied to a vendor or a specific cloud.
HTAP shines for operational analytics: dashboards and decisions inside the product that need fresh data from the same application (fraud scoring on the last hour of transactions, a seller dashboard showing today's sales, inventory analytics during a flash sale). For company-wide BI, a warehouse or lakehouse fed by CDC remains the norm.
Decision Guide
graph TD
Start["What does the workload need?"] --> Q1{"Mostly single-entity<br/>reads/writes, ms latency,<br/>ACID?"}
Q1 -->|"Yes"| OLTP["OLTP database<br/>PostgreSQL / MySQL"]
Q1 -->|"No, aggregates over<br/>many rows"| Q2{"Data size?"}
Q2 -->|"Under ~50M rows,<br/>few analysts"| PGAN["Postgres read replica<br/>+ indexes, matviews,<br/>maybe columnar partitions"]
Q2 -->|"100M+ rows"| Q3{"Who queries it?"}
Q3 -->|"Internal BI,<br/>many sources"| WH["Warehouse / lakehouse<br/>Snowflake, BigQuery, Redshift,<br/>Iceberg + Trino"]
Q3 -->|"End users,<br/>high qps, sub-second"| RT["Real-time OLAP<br/>ClickHouse, Pinot, Druid"]
Q3 -->|"Single analyst,<br/>files on disk/S3"| DUCK["DuckDB"]
OLTP --> Q4{"Need fresh analytics<br/>on the same app data?"}
Q4 -->|"Seconds-fresh, inside product"| HTAP["HTAP option or<br/>CDC to real-time OLAP"]
Q4 -->|"Minutes-hours ok"| CDC["CDC or batch ELT<br/>to warehouse"]
Rules of thumb:
| Situation | Choose |
|---|---|
| New product, < 10M rows, one team | PostgreSQL for everything; analytics on a read replica |
| Reports slowing down the primary | Move them to a replica first; then to a warehouse via CDC when the replica struggles |
| Event/log data, billions of rows, real-time dashboards | ClickHouse (or Pinot/Druid for very high qps user-facing) |
| Many data sources, analysts, governed metrics | Cloud warehouse or lakehouse with dbt |
| Data science on Parquet files | DuckDB locally, Spark/Trino at scale |
| Need seconds-fresh analytics on transactional data, MySQL shop | HeatWave or TiDB/TiFlash |
| PostgreSQL shop, operational dashboards | Read replica + partitioning + columnar extension or AlloyDB columnar engine; CDC to ClickHouse if that is not enough |
For the broader database choice, see Database Selection Guide.
Common Mistakes
1. Running Analytics on the Primary
The most common and most damaging mistake. A single GROUP BY over a large table on the OLTP primary:
- Reads gigabytes through the buffer cache, evicting the hot pages the application depends on. After the report finishes, OLTP queries that were hitting cache start hitting disk; p99 latency jumps for minutes.
- Holds an MVCC snapshot for its whole duration. In PostgreSQL, a 2-hour query prevents VACUUM from removing any dead tuple newer than its snapshot across the entire database, so hot tables bloat. In MySQL, long transactions grow the undo history list and slow every read.
- Consumes CPU cores and connections, especially with parallel query.
War Story (illustrative)
A marketplace ran its "seller performance" report directly on the primary every Monday at 09:00. The query joined 14 months of orders with order lines and took about 25 minutes. For years it was harmless. Then traffic grew, the working set no longer fit beside the report's scan in the buffer cache, and every Monday morning the checkout p99 went from 40 ms to over 2 s for half an hour. Autovacuum also could not clean the inventory table during the report, and it bloated to four times its size over a few weeks. The fix took a day: point the report at a replica with hot_standby_feedback = off and a generous max_standby_streaming_delay, then later move it to ClickHouse fed by Debezium, where it ran in 3 seconds.
Remedies in order of effort: read replica (cheap, but replicas have their own conflicts with long queries, see Replication); summary tables maintained incrementally; columnar extension on cold partitions; a proper OLAP system via CDC.
2. Using an OLAP Database for Transactions
The reverse mistake, usually made by teams that fell in love with their analytics engine:
- Storing user balances or inventory in ClickHouse and "updating" them with mutations (
ALTER TABLE ... UPDATE) that rewrite whole parts asynchronously. There is no row-level locking, no multi-statement transaction in the OLTP sense, no foreign keys. Two concurrent decrements can both succeed against stale data. - Using BigQuery or Snowflake as the backend for an API that serves per-user pages. Each request costs seconds of latency and real money; concurrency limits throttle you at traffic peaks.
- Single-row inserts from the application into a column store, producing millions of tiny parts or files and terrible performance.
The correct pattern: OLTP database is the source of truth for anything that must be correct and current; OLAP is a derived, read-optimised copy.
3. Assuming "Columnar" Means "Faster for Everything"
Teams migrate a mixed workload to a column store and discover that point lookups got 20× slower and updates became a background job. Column stores are faster for scans of few columns across many rows. Measure your actual queries.
4. Treating the Warehouse as a Dumping Ground
Replicating OLTP tables one-to-one into a warehouse and having every analyst write their own joins over normalised tables gives you slow queries and inconsistent metrics. Model the analytical side: facts, dimensions, conformed definitions. See Dimensional Modeling.
5. Ignoring Sort Keys and Partitioning in the OLAP Store
A column store with a bad sort key is a slow column store. A ClickHouse table ordered by a random UUID, or a BigQuery table without partitioning, scans everything for every query. Pick the sort key from the most common filters (usually tenant, then time) and partition by time for retention.
6. Over-Investing in Real-Time
A streaming CDC pipeline with exactly-once semantics into a real-time engine costs several engineers' time to build and operate. If the dashboard is viewed once a day, a nightly ELT job is the right answer.
7. Forgetting That CDC Couples Schemas
Once Debezium streams the orders table, renaming a column in the application becomes a breaking change for downstream consumers. Treat CDC topics as an API, or publish explicit domain events via the outbox pattern instead of raw table changes. See Schema Evolution.
Summary
- OLTP and OLAP differ in rows-per-query by six or more orders of magnitude, and every design difference follows from that.
- Row stores keep a row together: cheap point reads, cheap single-row writes, natural row locking. Column stores keep a column together: read only needed columns, compress 5–15× or more, skip blocks with zone maps, execute in vectors, materialize late.
- On a 500M-row aggregate the column store is typically 50–200× faster; on a single-row lookup the row store is typically 10–100× faster.
- Data reaches the analytical side through batch ELT or CDC (Debezium reading WAL/binlog). Watch replication slots.
- HTAP keeps a row store and a column copy in one system. It is excellent for fresh operational analytics and does not replace a modelled warehouse.
- Never run heavy analytics on the OLTP primary; never use an analytics engine as your transactional source of truth.
Interview Questions
1. Why is a column store faster than a row store for SELECT SUM(amount) FROM orders WHERE created_at > X?
Four compounding reasons. It reads only the amount and created_at columns instead of every byte of every row (often 10–20× less I/O). Those columns compress very well because similar values are adjacent (delta-encoded timestamps, bit-packed integers), so even fewer bytes are read. Per-block min/max metadata lets it skip blocks outside the date range without reading them. And it executes the filter and sum over arrays of thousands of values at a time with SIMD instead of one tuple per function call. Together that is commonly 50–200× faster.
2. Why is the same column store slower for SELECT * FROM orders WHERE id = 42?
The row's 30 fields are spread across 30 column files. The engine must locate and decompress a block (thousands of rows) in each column to extract one value from each, then stitch them together. A row store does one index descent and reads one page that contains the whole row.
3. What is late materialization?
Delaying the construction of output rows until filters have been applied. The engine evaluates predicates column by column, producing a selection vector of surviving positions, and only reads the projected columns at those positions. Columns used only in the SELECT list are fetched for the small fraction of rows that pass, not for all rows.
4. Explain vectorized execution versus the Volcano model.
Volcano: each operator returns one row per next() call, so per-row overhead (virtual calls, interpretation, branches) is paid billions of times. Vectorized: operators exchange batches of 1,000–65,000 values of one column; inner loops are tight, cache-friendly, branch-free and SIMD-friendly, amortising the overhead over the batch. Query compilation (Hyper, Spark codegen) is the alternative approach to the same overhead problem.
5. Your product's reporting queries are slowing down checkout. What do you do, in order?
Confirm with pg_stat_statements and pg_stat_activity that the reports are the cause (buffer cache eviction, CPU, long snapshots blocking vacuum). Move reports to a read replica immediately. Add summary tables or materialized views for the most common reports. If data volume keeps growing, set up CDC (Debezium) into a warehouse or ClickHouse and move reporting there. Set statement_timeout for reporting roles on the primary as a guard.
6. What is CDC, and why is it preferable to polling with updated_at?
Change data capture reads the database's replication log (WAL in PostgreSQL via logical decoding, binlog in MySQL) and emits every committed change as an event in commit order. Unlike updated_at polling it captures deletes, does not miss rows from transactions that commit late, captures every intermediate state, and puts almost no query load on the primary. The main operational risk is a stalled consumer causing the replication slot to retain WAL until the disk fills.
7. What is HTAP, and how does TiDB implement it? Hybrid transactional/analytical processing: one system serving both workloads with fresh data. TiDB stores rows in TiKV (LSM key-value, Raft replicated) and adds TiFlash, a columnar engine that joins each Raft group as a non-voting learner. Writes commit through TiKV without waiting for TiFlash. An analytical read on TiFlash waits until the learner has applied the leader's commit index, so it sees a consistent snapshot. TiFlash runs on separate nodes, isolating analytical load.
8. When would you not use HTAP? When analytics need data from many systems (a warehouse is unavoidable anyway), when you need dimensional models and history rather than the operational schema, when the team cannot accept vendor lock-in, or when minutes-stale data is fine and a CDC pipeline to an existing warehouse already exists.
9. Data warehouse vs data lake vs lakehouse in one sentence each. A warehouse is a database that owns its storage and is built for SQL analytics with enforced schemas. A lake is files in object storage with schema-on-read and no transactions. A lakehouse adds an open table format (Iceberg, Delta, Hudi) on top of lake files, giving ACID commits, schema evolution and time travel, so many engines can share one copy of the data with warehouse-like guarantees.
10. Why do column stores avoid B-tree secondary indexes? B-trees speed up selective lookups but cost space and write amplification on every insert. Analytical queries are rarely selective enough to benefit, and column stores get most of the benefit from sort keys plus tiny zone maps (min/max per block), which cost kilobytes and let the engine skip most blocks for range filters on sorted columns. Some add bloom filters or bitmap indexes for specific columns.
11. How do column stores handle updates and deletes? Usually not in place. Common techniques: write new data into a small row-oriented delta store and merge it into compressed columnar segments in the background (SQL Server delta rowgroups, SingleStore, Vertica WOS/ROS); mark deleted rows in a delete bitmap and filter them at read time; or rewrite the affected parts asynchronously (ClickHouse mutations, lakehouse copy-on-write). Engines like ClickHouse also model updates as new versions resolved at merge time (ReplacingMergeTree).
12. Give a back-of-envelope estimate: 1 billion rows, 40 columns of ~8 bytes, query uses 3 columns, 2 GB/s disk. Row store reads ~320 GB: about 160 s of I/O alone. Column store reads 3 × 8 GB = 24 GB raw, roughly 3–5 GB after compression: 1.5–2.5 s, less if zone maps skip blocks or data is cached. Roughly a 60–100× difference before counting CPU efficiency.