What is OLTP and OLAP: Difference between revisions

From MemCP
Jump to navigation Jump to search
(Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference)
 
Line 1: Line 1:
There are basically two types database workloads leading to different database designs:
<!-- Copyright (C) 2026 Carl-Philip Haensch -->
<!-- SPDX-License-Identifier: GPL-3.0-or-later -->
= What is OLTP and OLAP? =
 
'''OLTP''' describes operational workloads with frequent inserts, updates, deletes, point lookups, constraints, and short transactions. '''OLAP''' describes analytical workloads that scan, group, join, and aggregate larger portions of a dataset. Real applications commonly mix both.
 
Row storage can favor whole-record access; column storage can reduce memory traffic when a query uses only a few attributes. Neither layout alone determines whether a system can provide transactions, durability, or good point lookup performance.
 
MemCP targets mixed workloads. Delta structures, indexes, constraints, and transaction machinery serve operational writes, while compressed columns, batch/parallel scans, and reusable computed relations serve analytical queries. Persistent cold columns may be evicted and reloaded, so workload behavior also depends on memory budget and storage backend.
 
== Typical query shapes ==
 
{| class="wikitable"
{| class="wikitable"
|+
! Workload !! Typical operations !! Important properties
!
!OLTP
!OLAP
|-
|abbr.
|Online Transaction Processing
|Online ''Analytical'' Processing
|-
|Example applications
|ERP, Ticket System
|BI suite, Statistical programs
|-
|-
|Typical SQL Queries
| OLTP || create an order, update inventory, look up one account, enforce unique/foreign keys || short latency, concurrency, atomicity, durability, selective indexes
|large amounts of small UPDATE, INSERT
|few SELECT with big computation demand
|-
|-
|Best Storage Format
| OLAP || revenue by month/region, cohort analysis, wide joins, windows, top-k reports || batch scans, compression, parallelism, grouping, ordering, memory bandwidth
|Row Store
|Columnar store
|-
|-
|Database Engines
| Hybrid/HTAP || ingest events while dashboards aggregate the latest state || consistent visibility between writes and scans, predictable maintenance, no stale export pipeline
|MySQL, Postgres
|MonetDB
|-
|Users
|Many Small Users
|One Data Operator
|}
|}
OLAP databases use ETL (Extract, Transform, Load) processes to load data from an OLTP database. This allows for the data to be transformed into a format suitable for data analysis and reporting. The ETL process enables data to be extracted from the source database, transformed into the format desired for the analysis, and then loaded into the OLAP database.
However, wouldn’t it be nice to have a DBMS that can handle both, OLAP and OLTP workloads well?
== Marrying both worlds ==
OLAP databases work best with columnar storage, which allows for large data sets to be stored and accessed quickly. This makes it ideal for data analysis and reporting. On the other hand, OLTP databases are optimized for record-wise data storage, which is better suited for read/write operations.
The ideal DBMS should be able to handle both OLAP and OLTP workloads well. It should be able to store and access data quickly and efficiently, while also being able to handle the read/write operations associated with OLTP workloads. It should also provide a robust set of features for data analysis and reporting.
So we have two kinds of data in the database:
* A large set of old records where we want to do analysis on (called '''main storage''') stored in the OLAP-optimized columnar storage
* A small set of recent changes that came from the OLTP workloads (called '''delta storage''') stored as a OLTP-optimized row storage
While the main storage can be compressed and optimized for read operations, the delta storage must be optimized for write operations.
So, the main storage is a column store while delta storage is a row store.


Whenever there is enough „old“ data in the delta storage, the main storage can be rebuilt from main storage + delta storage and delta storage can start empty again.
== Combining both in MemCP ==


Of course, queries on the database have to consider both: delta and main storage.
New writes land in delta state suited to change, while rebuilt main columns use an encoding chosen from their values. Scans combine both under transaction visibility. The planner can use selective indexes for point access and compressed batch pipelines for analytical work, and table ENGINE choice separates durability requirements from physical query layout.


== See also ==
That does not make every mixed workload free of trade-offs. High-contention writes, a hot set larger than RAM budget, long-running snapshots, frequent rebuilds, or analytical queries touching nearly every column can interfere with operational latency. Measure them concurrently, not as isolated best cases.


* [[Shards, RecordIDs, Main Storage, Delta Storage]]
Whether one MemCP instance is suitable depends on isolation, durability, SQL compatibility, query mix, hot-set size, recovery needs, and measured performance. See [[Transactions and Isolation]], [[Persistency and Performance Guarantees]], and [[Performance Measurement]].

Latest revision as of 11:59, 28 August 2026

What is OLTP and OLAP?

OLTP describes operational workloads with frequent inserts, updates, deletes, point lookups, constraints, and short transactions. OLAP describes analytical workloads that scan, group, join, and aggregate larger portions of a dataset. Real applications commonly mix both.

Row storage can favor whole-record access; column storage can reduce memory traffic when a query uses only a few attributes. Neither layout alone determines whether a system can provide transactions, durability, or good point lookup performance.

MemCP targets mixed workloads. Delta structures, indexes, constraints, and transaction machinery serve operational writes, while compressed columns, batch/parallel scans, and reusable computed relations serve analytical queries. Persistent cold columns may be evicted and reloaded, so workload behavior also depends on memory budget and storage backend.

Typical query shapes

Workload Typical operations Important properties
OLTP create an order, update inventory, look up one account, enforce unique/foreign keys short latency, concurrency, atomicity, durability, selective indexes
OLAP revenue by month/region, cohort analysis, wide joins, windows, top-k reports batch scans, compression, parallelism, grouping, ordering, memory bandwidth
Hybrid/HTAP ingest events while dashboards aggregate the latest state consistent visibility between writes and scans, predictable maintenance, no stale export pipeline

Combining both in MemCP

New writes land in delta state suited to change, while rebuilt main columns use an encoding chosen from their values. Scans combine both under transaction visibility. The planner can use selective indexes for point access and compressed batch pipelines for analytical work, and table ENGINE choice separates durability requirements from physical query layout.

That does not make every mixed workload free of trade-offs. High-contention writes, a hot set larger than RAM budget, long-running snapshots, frequent rebuilds, or analytical queries touching nearly every column can interfere with operational latency. Measure them concurrently, not as isolated best cases.

Whether one MemCP instance is suitable depends on isolation, durability, SQL compatibility, query mix, hot-set size, recovery needs, and measured performance. See Transactions and Isolation, Persistency and Performance Guarantees, and Performance Measurement.

Retrieved from "https://www.memcp.org/index.php?title=What_is_OLTP_and_OLAP&oldid=367"