OLTP and OLAP split
Run reports on a separate columnar warehouse, so large scans stop evicting the cache and slowing the database that serves users.
Keep analytics on a warehouse that stores columns, and keep the transactional database for the small reads and writes users wait for.
Use it when#
A report or an export makes the primary's CPU spike and checkouts slow down.
Analysts want to scan months of history, and the production database cannot hold it all cheaply.
Queries aggregate a few columns over millions of rows.
How it works#
A transactional database stores each row together, which suits fetching one order. A warehouse stores each column together, so summing amount over 100 million rows reads one column, not the whole table. Rows reach the warehouse through a pipeline, often change data capture, so the app never queries it for live traffic.
A big scan on the primary also pushes hot rows out of its buffer cache, so ordinary reads go to disk afterward. That second harm is why a single nightly report can slow the whole afternoon.
What it costs#
The warehouse lags the primary by the pipeline's delay, so reports show slightly old data. You pay per scan or per byte read, and a careless query can be expensive. You also run a pipeline and keep two schemas in step. A replica takes read load but not scan-heavy analytics, because it has the same row layout.
In Quorum#
Drag to pan. Hold Ctrl or Cmd and scroll to zoom.
| Where | Setting | Value | Why |
|---|---|---|---|
| App Server | Instance size | medium | Medium, so the App Server is not the limit. |
| Database | Instance size | medium | Medium. It serves users and no longer scans history. |
| Realtime Hub | Stack | node | An event loop holds each open connection for a few KB, where the default Stack holds a worker for each. |
| Realtime Hub | Clustering | On | Without it one box takes every connection. |
Research this Design needs: Data warehousing, Hosted models, Checkout, WebSockets, Object storage, WebSockets, Data warehousing.
The other Nodes in the picture only serve the other request kinds of Series B, such as checkouts and realtime. Report requests send scans. With only a Database, the scans run there. With a Data Warehouse linked, the scans go there instead, so the Database serves users alone. A scan on the warehouse is slow but has no capacity limit, and it is billed per scan. The game does not model how stale the warehouse copy is.
The warehouse unlocks with Data warehousing. The Report kills the database incident is what it prevents. The glossary defines OLTP and OLAP. Reports off the primary is the ladder rung.