What is OLTP and OLAP - Revision history

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T09:59:41Z

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 -->
 {| class="wikitable"
-|+
+! Workload !! Typical operations !! Important properties
 |-
-|Typical SQL Queries
+| OLTP || create an order, update inventory, look up one account, enforce unique/foreign keys || short latency, concurrency, atomicity, durability, selective indexes
 |-
-|Best Storage Format
+| OLAP || revenue by month/region, cohort analysis, wide joins, windows, top-k reports || batch scans, compression, parallelism, grouping, ordering, memory bandwidth
 |-
-|Database Engines
+| Hybrid/HTAP || ingest events while dashboards aggregate the latest state || consistent visibility between writes and scans, predictable maintenance, no stale export pipeline
 |}
-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]].

Carli: /* See also */

2024-11-20T18:27:55Z

Carli: Created page with "There are basically two types database workloads leading to different database designs: {| class="wikitable" |+ ! !OLTP !OLAP |- |abbr. |Online Transaction Processing |Online ''Analytical'' Processing |- |Example applications |ERP, Ticket System |BI suite, Statistical programs |- |Typical SQL Queries |large amounts of small UPDATE, INSERT |few SELECT with big computation demand |- |Best Storage Format |Row Store |Columnar store |- |Database Engines |MySQL, Postgres |Mone..."

2024-05-17T07:14:18Z

Created page with "There are basically two types database workloads leading to different database designs: {| class="wikitable" |+ ! !OLTP !OLAP |- |abbr. |Online Transaction Processing |Online ''Analytical'' Processing |- |Example applications |ERP, Ticket System |BI suite, Statistical programs |- |Typical SQL Queries |large amounts of small UPDATE, INSERT |few SELECT with big computation demand |- |Best Storage Format |Row Store |Columnar store |- |Database Engines |MySQL, Postgres |Mone..."

New page

There are basically two types database workloads leading to different database designs:
{| class="wikitable"
|+
!
!OLTP
!OLAP
|-
|abbr.
|Online Transaction Processing
|Online ''Analytical'' Processing
|-
|Example applications
|ERP, Ticket System
|BI suite, Statistical programs
|-
|Typical SQL Queries
|large amounts of small UPDATE, INSERT
|few SELECT with big computation demand
|-
|Best Storage Format
|Row Store
|Columnar store
|-
|Database Engines
|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.

Of course, queries on the database have to consider both: delta and main storage.

== See also ==
[[Shards, RecordIDs, Main Storage, Delta Storage]]

← Older revision		Revision as of 20:27, 20 November 2024
Line 53:		Line 53:

	== See also ==		== See also ==
	[[Shards, RecordIDs, Main Storage, Delta Storage]]
			* [[Shards, RecordIDs, Main Storage, Delta Storage]]