OLTP vs OLAP: the one-image difference
Why the database that runs your app is the wrong place for your dashboards. Rows vs columns, the flow, and the prod-DB trap.
Get the free PDF
One page, print-ready, free to share. No signup needed.
Same data, two different jobs. OLTP (online transaction processing) runs the business: it writes order #123 in two milliseconds. OLAP (online analytical processing) understands the business: it aggregates a hundred million orders by city. Confuse the two and your dashboard can take down your checkout.
Side by side
| OLTP | OLAP | |
|---|---|---|
| job | run the business | understand the business |
| query shape | fetch order #123 | aggregate 100M rows |
| writes | constant, small | batch loads |
| storage layout | rows | columns |
| schema | normalized, 3NF | star, denormalized |
| examples | Postgres, MySQL | BigQuery, Snowflake |
Two typical queries
-- OLTP: point lookup, 1 row, 2 ms
SELECT * FROM orders WHERE id = 123;
-- OLAP: full-table aggregation, 100M rows scanned
SELECT city, SUM(amount)
FROM orders
GROUP BY city;
Different animals. Row storage answers the first in a single read; columnar storage answers the second by scanning only two columns, tightly compressed.
The flow
- The app writes orders to OLTP, all day.
- The pipeline (ELT) copies history to the warehouse.
- The warehouse lets analysts aggregate at will.
- BI dashboards read the marts, never prod.
The trap: BI on the prod DB
Monday 9:00: the CEO dashboard scans 50 million rows on the production database. Monday 9:01: checkout queries queue up behind it. One heavy analytical query can lock up the system that makes the money.
The fix: replicate to a warehouse; BI never touches prod. If analysts have a read login on the production database, this trap is already armed.
Who is what
- Postgres, MySQL: OLTP. Your app writes here.
- BigQuery, Snowflake, Redshift: OLAP cloud warehouses.
- DuckDB: OLAP, embedded, brilliant on a laptop.
- HTAP: marketing for "both, kind of".
Frequently asked questions
- Is Postgres OLTP or OLAP?
- Postgres is an OLTP database: row storage, transactions, indexes, built to run applications. It can serve light analytics, but heavy aggregations belong in an OLAP engine like BigQuery, Snowflake or DuckDB.
- Why are columnar databases faster for analytics?
- An analytical query usually touches a few columns across millions of rows. Columnar storage reads only those columns and compresses them extremely well, so the engine scans a fraction of the bytes a row store would.
- Do I need a data warehouse if my app database is small?
- Not immediately. But the moment dashboards compete with checkout queries, or analysts need history that the app prunes, replicate to a warehouse. Even DuckDB over nightly exports beats BI on prod.
- What is HTAP?
- Hybrid transactional/analytical processing: systems claiming to serve both OLTP and OLAP workloads. Some are genuinely useful, but for most teams the boring pattern (app on OLTP, ELT to a warehouse, BI on OLAP) is simpler and cheaper.
Get the free PDF
One page, print-ready, free to share. No signup needed.