OLTP vs OLAP

Imagine a bookshop with one order table. A checkout looks up and changes one order; a sales report totals amounts across every order. Both queries can run on the same database. The useful question is which access pattern dominates, and whether a large scan starts delaying checkouts. The bookshop is illustrative, not a measured incident.

OLTP means online transaction processing: short operations must commit correctly and meet their latency target while other orders arrive. OLAP means online analytical processing: questions often combine many records and must return a useful answer within their own time and freshness limits. The names describe workloads. They do not prescribe a database product or guarantee that one system cannot serve both.

Why row storage suits one and column storage the other#

A row-oriented table keeps fields of an order together in a row. That helps when a checkout needs most of that order. It does not make a lookup exactly one page read: an index, several heap pages or an out-of-line value may be involved. A full table scan for amounts visits row pages that also contain other fields. A covering index can sometimes answer the same query without visiting those pages at all. PostgreSQL’s index-only scan rules give that counterexample.

Try the same report on two layouts

Illustrative table: 1,000 orders, each with five equally sized fields. The report visits all orders. How many fields does it need?

Fields used by the report Amount only (one field) Whole order (all five fields)

Row-layout scan

5,000 field values share the row pages visited

Column-layout scan

1,000 field values are in the needed column chunks

For amount only, the column layout can avoid the four unrelated fields. The row scan visits pages containing whole orders.

This is a layout model, not measured bytes, page reads or response time. It assumes a full scan, no covering index, equal field sizes and no compression or cache effects. A selective lookup or a useful index can change the choice. Parquet's column-chunk layout and PostgreSQL's row pages show the two arrangements.

A column-oriented file such as Parquet groups values by column within each row group. A report that needs only amounts can avoid unrelated column chunks. If it needs all five fields, that projection advantage disappears; a point lookup has different costs again. Parquet can also store optional per-page minimum and maximum values to skip pages when a filter permits it. These are conditional optimisations, not a promise to read exactly the requested bytes or to beat every indexed row store. See the Parquet layout and its optional page index.

What normalisation is for, and when it stops paying#

Normalisation reduces duplicated facts and the risk of inconsistent updates. If the shop changes a customer’s current address, storing it once avoids editing many order rows. It does not make inconsistent data impossible: application rules and transactions still matter. An order may also need its historical delivery address, which is a different fact from the customer’s current one.

A star schema puts measured events in a fact table and descriptive attributes in dimension tables. It can make a sales question easier to express and aggregate, but the warehouse still receives new data and changing dimensions need an update policy. Star schema and column layout are separate choices: a star schema can be stored in row-oriented tables, and column storage does not require a star schema. Microsoft’s dimensional modelling guide describes fact and dimension tables, data loading and slowly changing dimensions.

Picking one on purpose#

Suppose the shop’s month-end query scans a year of orders on the checkout database. First inspect its actual plan, filters and indexes; a missing useful index or stale statistics might be fixable. If the query genuinely needs most orders, a broad scan may be sensible. Then measure whether it competes with checkout for CPU, memory or I/O. A slow query alone does not prove that the database must be split.

In PostgreSQL, an ordinary MVCC read does not block a normal write through a conflicting row lock. The scan can still compete for shared resources; cache eviction is possible, not inevitable. A long-running open transaction can also delay reuse of old row versions by vacuum. Do not infer that every long statement causes table and index bloat. PostgreSQL’s MVCC introduction explains reader/writer concurrency, and its vacuum guide explains old versions and open transactions. The adjacent MVCC example lets you change the snapshot rule.

If plans and measurements show real interference, a read replica may isolate the primary from reporting load while keeping a similar row layout. A warehouse or column-oriented copy may also speed broad analytical scans, at the cost of data movement and freshness. Compare these options with the measured query and required freshness. The index-plan guide helps with the first check; B-tree vs LSM-tree covers a different storage-engine trade-off.

Questions people also ask

5 QUESTIONS

▸ Can one database do both?

Yes. Measure whether reporting interferes with transaction latency before splitting workloads. Hybrid transactional and analytical systems (HTAP) are another option, but their trade-offs depend on the engine and workload.

▸ What is a star schema for?

It puts measured events in a fact table and descriptive attributes in dimension tables. This can make reporting queries easier to express, but dimensions still change and the data-loading process must handle those updates.

▸ Why is column storage faster for aggregates?

When a query needs only a few fields across many rows, a column layout can skip unrelated column chunks. Compression and page skipping may help further. The result depends on the engine, filters, indexes and data, not the layout alone.

▸ Do I need a warehouse, or is a read replica enough?

A replica of a row-oriented database can keep reporting work off the primary, but it does not itself turn the data into a column store. Check replica lag, available indexes and actual query plans before deciding whether a separate analytical layout helps.

▸ What is HTAP, and does it work?

Hybrid transactional/analytical processing runs both workloads in one system. Some engines maintain different representations for them; that is an implementation choice, not the definition. Test the product's freshness, isolation and resource costs against your workload.