OLAP vs OLTP Workloads

By Michael Samuel Naeem · August 14, 2025 · 5 min read

The checkout API p99 jumped from 80ms to 2.4 seconds after BI connected Metabase directly to production Postgres and ran SELECT date_trunc('day', created_at), sum(total) FROM orders GROUP BY 1 during Black Friday. Same database, same tables — completely different workload shapes. OLTP optimizes for short, indexed reads and writes; OLAP optimizes for scanning millions of rows and aggregating. Treating them as interchangeable is how transactional systems die quietly under dashboard load.

Defining the two workload types

OLTP answers: "What's order #847291's status right now?" OLAP answers: "What was revenue by region and product category for the last 36 months?"

OLTP design characteristics

Normalized schema reduces update anomalies:

-- OLTP: orders + order_items (3NF)
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  status      TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id    BIGINT REFERENCES orders(id),
  product_id  BIGINT REFERENCES products(id),
  quantity    INT NOT NULL,
  unit_price  NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

Properties:

• Narrow indexes for lookup paths
• Row-level locking — many concurrent writers
• Foreign keys enforce integrity at write time
• Connection pooling — thousands of short transactions

Typical engines: PostgreSQL, MySQL, CockroachDB, DynamoDB (key-value OLTP patterns).

OLAP design characteristics

Star schema denormalizes for scan speed:

-- OLAP: fact_sales + dimensions
CREATE TABLE fact_sales (
  sale_date_key    INT,      -- FK to dim_date
  product_key      INT,      -- FK to dim_product
  store_key        INT,      -- FK to dim_store
  customer_key     INT,
  quantity         INT,
  revenue          NUMERIC(14,2),
  cost             NUMERIC(14,2)
) ENGINE = columnar;  -- BigQuery, Snowflake, ClickHouse, etc.

CREATE TABLE dim_product (
  product_key      INT PRIMARY KEY,
  sku              TEXT,
  category         TEXT,
  brand            TEXT
);

Analytical query:

SELECT d.year, p.category, SUM(f.revenue)
FROM fact_sales f
JOIN dim_date d ON f.sale_date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.year BETWEEN 2022 AND 2025
GROUP BY 1, 2;

Columnar storage reads only revenue, product_key, sale_date_key — not full rows. Aggregations vectorize across billions of rows.

Typical engines: Snowflake, BigQuery, Redshift, ClickHouse, DuckDB, Apache Druid.

Why mixing workloads fails

Running OLAP on OLTP causes:

1. Lock contention — long SELECTs hold snapshots or block vacuum (Postgres MVCC bloat)
2. Buffer pool pollution — analytical scans evict hot OLTP pages from cache
3. Unpredictable p99 — checkout latency spikes when analyst runs wide join
4. Index mismatch — OLTP indexes don't help aggregations; analytical indexes hurt write speed

Metabase-on-prod is the classic failure mode. Fix: read replica for BI (still imperfect at scale) or proper pipeline to warehouse.

The modern split architecture

┌─────────────┐    CDC/ETL     ┌──────────────┐    SQL    ┌────────────┐
│  OLTP       │ ─────────────► │  Staging /   │ ────────► │  OLAP      │
│  Postgres   │   Debezium,    │  dbt models  │           │  Snowflake │
│             │   Fivetran     │              │           │            │
└─────────────┘                └──────────────┘           └────────────┘
      ▲                                                          │
      │ short queries                                            ▼
   Application                                              Dashboards

Change Data Capture (CDC) streams inserts/updates/deletes to warehouse without batch windows. Debezium + Kafka → Snowpipe pattern gives near-real-time OLAP without touching OLTP query paths.

dbt transforms raw CDC tables into star schema marts — fact_orders, dim_customers — versioned in Git.

HTAP: when one system tries both.

Hybrid Transaction/Analytical Processing (TiDB, SingleStore, Apache Pinot with upserts) blurs the line. Useful when:

• Team lacks pipeline ops capacity
• Data volume fits single cluster (< few TB active)
• Analytics need sub-minute freshness on narrow queries

Trade-offs: jack of both trades, expert in neither; licensing cost; harder tuning than pure-play split.

We use Postgres OLTP + ClickHouse OLAP via PeerDB sync — HTAP products weren't worth the lock-in premium at our scale.

Choosing storage.

Operational differences.

Reconcile OLTP and OLAP counts nightly — SUM(orders.total) in warehouse vs OLTP snapshot catches pipeline bugs before executives see wrong numbers.

Define SLAs separately: OLTP p99 latency SLOs should exclude analytical query paths entirely. When BI teams request "just one more direct query," redirect to the warehouse or a dedicated read replica with statement_timeout enforced (SET statement_timeout = '30s'). Reconciliation jobs comparing row counts and sum totals between OLTP snapshots and OLAP marts catch pipeline bugs before executives see them in board decks. For early-stage teams, Postgres plus nightly pg_dump to DuckDB or BigQuery may suffice — but set a growth trigger (e.g., analytical queries exceed 5% of CPU for three days) to fund proper CDC. FinOps should tag warehouse compute by team; unconstrained OLAP spend often exceeds OLTP RDS cost once analysts adopt self-serve SQL.

Never run SELECT * analytics against the production OLTP primary without statement_timeout — one analyst's cartesian join becomes everyone's checkout outage.

Resources

• Ralph Kimball — The Data Warehouse Toolkit
• Debezium change data capture
• dbt documentation — staging and marts
• ClickHouse vs OLTP databases
• AWS — OLTP vs OLAP overview