OLTP vs OLAP
中文版：OLTP 与 OLAP 对比
The key difference between OLTP and OLAP is the way the data are physically stored on disk.
Definitions
- OLTP (Online Transaction Processing): Row-oriented storage pattern.
- OLAP (Online Analytical Processing): Column-oriented storage pattern.
Row-Oriented (OLTP)
- Row-oriented databases store the whole row in the same block, if possible.
- Best fit for transactional workloads with frequent updates.
Column-Oriented (OLAP)
- Columnar databases store columns in subsequent blocks.
- Best fit for analytical queries and fast bulk operations.
Performance Example
Consider a database storing a total of 100 GB of data, with 100 million rows and 100 columns (1 GB per column). Assume there are no indexes, partitioning, or any other optimization.
For the analytical query: What is the average age of males?
| Storage Type | Data Read |
|---|---|
| Row-wise DB | 100 GB (all data) |
| Columnar DB | 2 GB (only age and gender columns) |
This is the reason why column-store databases can perform fast bulk operations.
Related Concepts
- Relational Database — Typically row-oriented
- NoSQL Database — Includes column-store options