# OLAP and OLTP

> Two kinds of database work: many small transactions, or a few huge analytical questions.

- Level: Advanced
- Topics: Data
- URL: https://tidbit.legios.com.ar/olap-and-oltp/
- In Spanish: [OLAP y OLTP](https://tidbit.legios.com.ar/es/olap-and-oltp/)

**OLTP** (online transaction processing) is the work behind an app while people use it: create this order, charge this card, update this address. Each operation touches a handful of rows, there are thousands of them per second, and every one must be correct and safe even if two happen at once. Databases like PostgreSQL, MySQL, Amazon RDS or DynamoDB are built for this, usually storing data row by row with indexes to find a single record quickly.

**OLAP** (online analytical processing) is the work of asking big questions about all that history: revenue by region and month for the last five years, or which products are bought together. A single query reads millions or billions of rows but only a few columns, and nobody minds waiting a few seconds. Data warehouses such as Snowflake, BigQuery or Amazon Redshift are built for this, storing data column by column (like Parquet files) so a query reads only what it needs and compresses well.

The two are kept apart on purpose. Running a heavy report on the production OLTP database can slow down checkout for real customers, and a columnar warehouse is clumsy at updating one row at a time. So data is copied from OLTP systems into an OLAP one through ETL or streaming. Some newer systems, called **HTAP**, try to do both, but the split remains the default design in most companies.

## An analogy

A supermarket checkout versus the head office. The checkout handles one customer at a time, fast and without mistakes; the office looks at a year of receipts to decide what to stock. You would not want the office doing its yearly analysis at the till.

## Keep in mind

- OLTP: many small reads and writes, row storage, correctness under load.
- OLAP: few large read queries over history, columnar storage, aggregations.
- Data flows from OLTP systems into an OLAP warehouse through ETL or streaming.
- Keeping them apart protects the live app from heavy reports.

## Go deeper

- [OLAP vs OLTP (AWS)](https://aws.amazon.com/compare/the-difference-between-olap-and-oltp/)
- [Online analytical processing on Wikipedia](https://en.wikipedia.org/wiki/Online_analytical_processing)
- [Online transaction processing on Wikipedia](https://en.wikipedia.org/wiki/Online_transaction_processing)

## Related terms

- [Database](https://tidbit.legios.com.ar/database/): An organized store of data that programs can search and update safely.
- [Amazon RDS](https://tidbit.legios.com.ar/amazon-rds/): A ready-made relational database that AWS keeps patched, backed up and running.
- [Amazon DynamoDB](https://tidbit.legios.com.ar/amazon-dynamodb/): A key-value database that answers in milliseconds no matter how big the table gets.
- [Database index](https://tidbit.legios.com.ar/database-index/): A sorted shortcut that lets a database find rows without reading the whole table.
- [Regions and Availability Zones](https://tidbit.legios.com.ar/regions-and-availability-zones/): Where AWS physically lives: cities worldwide, each split into separate data centers.
- [Data warehouse](https://tidbit.legios.com.ar/data-warehouse/): A database built for analysis: years of history, from many sources, in one place.
- [Amazon Redshift](https://tidbit.legios.com.ar/amazon-redshift/): AWS's data warehouse: SQL over huge tables, built for analysis rather than apps.
- [Parquet](https://tidbit.legios.com.ar/parquet/): A file format that stores tables by column, compressed, so analytics reads far less.