OLTP vs OLAP: why is your dashboard slowing production?

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

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

1. The app writes orders to OLTP, all day.
2. The pipeline (ELT) copies history to the warehouse.
3. The warehouse lets analysts aggregate at will.
4. 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".