OLAP vs. OLTP
Two different types of database processing architectures.
Detailed Explanation
OLTP (Online Transactional Processing) is for day-to-day operations (e.g., 'Update user balance'). It handles many small, fast transactions. OLAP (Online Analytical Processing) is for complex data analysis (e.g., 'What was the average revenue per user in Q3?'). OLAP databases (like BigQuery or Snowflake) are optimized for reading massive amounts of data in columns rather than rows.
Quick Summary
OLTP handles the live, transactional workload of running a business; OLAP handles the analytical, historical workload of understanding it. They're optimized so differently that they almost always live in separate systems.
Key Takeaways
- OLTP: row-oriented, normalized, optimized for small reads/writes and strong consistency (PostgreSQL, MySQL, Oracle).
- OLAP: column-oriented, denormalized, optimized for scanning billions of rows over a few columns (BigQuery, Snowflake, ClickHouse, Redshift, DuckDB).
- Don't run heavy analytics on the OLTP primary, query patterns are incompatible and you'll degrade live traffic.
- Move data from OLTP to OLAP via ELT (Fivetran, Airbyte, change-data-capture) for analytics workloads.
- HTAP databases (TiDB, SingleStore, CockroachDB) try to bridge both, useful niche, not yet a default.
When to use it
- OLTP: ecommerce checkout, banking transactions, user account management, anything write-heavy.
- OLAP: BI dashboards, cohort analysis, revenue reporting, ML feature engineering.
- Choosing a warehouse: BigQuery / Snowflake / Databricks for serverless scale; ClickHouse / DuckDB when you control compute.
- Real-time analytics: ClickHouse, Pinot, Druid for sub-second analytical queries over fresh data.
Common Mistakes
- Running large analytical queries against the production OLTP database, slow for analytics, painful for users.
- Copying all data to the warehouse continuously when batch nightly would suffice, high cost, little marginal value.
- Treating the warehouse as a system of record; it's a derived store and should always be rebuildable from sources.
- Designing OLAP schemas like OLTP (heavily normalized), analytical queries crawl across many joins.
OLAP vs. OLTP, Frequently Asked
Can I just use Postgres for both?
Up to surprisingly large scale, yes. Postgres with read replicas handles many analytical workloads, and tools like Postgres-XL or Citus extend it. The threshold to move is real-time dashboards over hundreds of GB or queries scanning many millions of rows.
Where does data engineering fit in?
Between them. The data team owns the pipelines (ELT, dbt, orchestration) that move data from OLTP into OLAP, model it for analytics, and serve it back to BI and ML. Without that bridge, the warehouse is just a backup.