OLAP vs OLTP

Also Known As

TL;DR

Explanation

OLTP databases (MySQL, PostgreSQL, SQLite) are row-oriented: every column of a row is stored together. This is fast for inserting or updating a single row and looking up individual records by primary key. OLAP databases (ClickHouse, BigQuery, Redshift, DuckDB) are column-oriented: all values for one column are stored together. This makes scanning the 'revenue' column across 100 million rows extremely fast — only that column is read from disk. OLTP schemas are normalised (many small tables with foreign keys). OLAP schemas are denormalised (wide flat tables, star schema, snowflake schema) because JOINs across billions of rows are expensive. Most applications use OLTP for operations and periodically ETL data into an OLAP system for analytics.

Common Misconception

Why It Matters

Common Mistakes

• Running GROUP BY / COUNT DISTINCT queries on production OLTP databases — these require full table scans that compete with live traffic.
• Confusing a read replica with an OLAP database — a read replica has the same row-oriented structure; it reduces load on primary but does not make analytical queries faster.
• Building an analytics system on MySQL with heavy JOINs — MySQL is not optimised for analytical workloads; consider DuckDB (embeddable) or ClickHouse for serious analytics.
• Not accounting for ETL lag in reporting — data in your OLAP system may be hours old; make sure dashboards communicate data freshness to avoid trust issues.

Code Examples

-- ❌ Running analytical query on OLTP production database
-- This scans 50M rows on the same database handling live orders
SELECT
    region,
    DATE_TRUNC('month', created_at) AS month,
    SUM(total_amount) AS revenue,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE created_at >= NOW() - INTERVAL '12 months'
GROUP BY region, month
ORDER BY month, revenue DESC;
-- Blocks the orders table, causes timeouts for live users

-- ✅ Same query on ClickHouse (columnar) — 100x faster for aggregates
-- Data ETL'd from OLTP hourly or via CDC
SELECT
    region,
    toStartOfMonth(created_at) AS month,
    sum(total_amount)           AS revenue,
    uniq(user_id)               AS unique_customers
FROM orders_analytics  -- Denormalised, columnar store
WHERE created_at >= now() - INTERVAL 12 MONTH
GROUP BY region, month
ORDER BY month, revenue DESC;
-- Sub-second on 50M rows; zero impact on production OLTP

-- ✅ For PHP: use a read replica + materialised view for medium scale
-- CREATE MATERIALIZED VIEW monthly_revenue AS
-- SELECT ... GROUP BY ... -- Refresh nightly
-- REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Tags

Related Terms

References

• https://clickhouse.com/docs/en/intro
• https://duckdb.org/docs/