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.