Compacting Transactional Data in Hybrid OLTP & OLAP Databases

Florian Funke, Alfons Kemper, Thomas Neumann

Abstract

Growing main memory sizes have facilitated database management systems that keep the entire database in main memory. The drastic performance improvements that came along with these in-memory systems have made it possible to reunite the two areas of online transaction processing (OLTP) and online analytical processing (OLAP): An emerging class of hybrid OLTP and OLAP database systems allows to process analytical queries directly on the transactional data. By offering arbitrarily current snapshots of the transactional data for OLAP, these systems enable real-time business intelligence. Despite memory sizes of several Terabytes in a single commodity server, RAM is still a precious resource: Since free memory can be used for intermediate results in query processing, the amount of memory determines query performance to a large extent. Consequently, we propose the compaction of memory-resident databases. Compaction consists of two tasks: First, separating the mutable working set from the immutable "frozen" data. Second, compressing the immutable data and optimizing it for efficient, memory-consumption-friendly snapshotting. Our approach reorganizes and compresses transactional data online and yet hardly affects the mission-critical OLTP throughput. This is achieved by unburdening the OLTP threads from all additional processing and performing these tasks asynchronously.

Introduction

Modern hybrid transactional/analytical processing (HTAP) systems, such as HyPer, aim to bridge the gap between high-speed transactions and complex real-time analytics. However, a fundamental tension exists: OLTP requires raw, mutable data for performance, while OLAP benefits from compact, immutable, and compressed formats. In-memory databases face the additional constraint that RAM is a finite and expensive resource.

This paper introduces "compaction"—a technique to identify "cold" data using hardware-assisted monitoring and compress it asynchronously. By transitioning stable data into compressed formats on huge memory pages, the system achieves significant storage savings and faster analytics without compromising the lock-free performance required for modern transactions.

The Hot/Cold Data Lifecycle

The core of the proposed approach is a data lifecycle that categorizes data into four distinct states based on access frequency and stability.

The lifecycle of data from Hot to Frozen states.
  1. Hot: This state contains the active working set—data that is frequently accessed or modified. It is kept uncompressed on standard 4KB pages to ensure low-latency writes.
  2. Cooling: Data that has not been recently touched enters the cooling phase. If a transaction accesses this data, it is immediately promoted back to the "Hot" state.
  3. Cold: Once data is confirmed as stable and no longer part of the active working set, it is moved to the "Cold" state.
  4. Frozen: This is the final, immutable state. Data is compressed using dictionary encoding and Run-Length Encoding (RLE), then migrated to 2MB Huge Pages.

Hardware-Assisted Access Monitoring

Identifying "cold" data typically requires software counters, which introduce significant overhead to every transaction. To avoid this, the authors utilize a Linux kernel module that reads "dirty" and "young" bits directly from the CPU’s Memory Management Unit (MMU). This hardware-assisted approach allows the system to monitor data access patterns with virtually zero overhead on transactional threads, preserving high throughput.

Position-based Run-Length Encoding

While standard RLE is efficient for sequential scans, it is poorly suited for point lookups because finding a specific row requires scanning from the beginning of the compressed block. To address this, the authors implement Position-based RLE.

Comparison of standard RLE and position-based RLE for efficient lookups.

Instead of storing a value and a count, Position-based RLE stores the starting position of each run. This structure enables the use of binary search to locate specific rows within compressed data, making it feasible for the OLTP engine to perform efficient point lookups even on "Frozen" data.

Snapshotting and Update Handling

HyPer leverages the OS fork() system call to create consistent snapshots for OLAP queries. A major bottleneck in fork() is the time taken to copy the page table. By using 2MB huge pages for frozen data instead of standard 4KB pages, the page table size is reduced by a factor of 500. This significantly accelerates snapshot creation and minimizes transaction latency "jitter."

If a record in the "Frozen" area must be updated, the system avoids the expensive operation of decompressing the chunk. Instead, it employs an out-of-place update strategy: the old record is marked as "invalid" in a bitmask, and the updated version is inserted into the "Hot" area.

Experimental Evaluation

The approach was evaluated using the CH-benCHmark, which combines TPC-C (transactional) and TPC-H (analytical) workloads. Experiments on a server with 64GB of RAM and dual quad-core CPUs demonstrated:

  • Minimal OLTP Impact: The Access Observer had no measurable impact on throughput. Compression caused only a minor 12.9% dip due to memory bus contention.
  • Significant Memory Savings: RLE achieved up to 3.3x compression for certain attributes.
  • Improved Analytics: Huge pages reduced snapshot delays from approximately 600ms to 400ms.
  • Lookup Efficiency: Position-based RLE proved to be orders of magnitude faster than standard RLE for point lookups.

Conclusion

Compaction provides a robust mechanism for managing memory in HTAP databases by exploiting the "working set" nature of transactional data. By offloading monitoring to hardware and performing compression asynchronously, the system maintains transactional performance while optimizing for analytical efficiency. Future work includes exploring "small materialized aggregates" to further accelerate analytical scans on frozen data chunks.

Summary updated

Abstract

Growing main memory sizes have facilitated database management systems that keep the entire database in main memory. The drastic performance improvements that came along with these in-memory systems have made it possible to reunite the two areas of online transaction processing (OLTP) and online analytical processing (OLAP): An emerging class of hybrid OLTP and OLAP database systems allows to process analytical queries directly on the transactional data. By offering arbitrarily current snapshots of the transactional data for OLAP, these systems enable real-time business intelligence. Despite memory sizes of several Terabytes in a single commodity server, RAM is still a precious resource: Since free memory can be used for intermediate results in query processing, the amount of memory determines query performance to a large extent. Consequently, we propose the compaction of memory-resident databases. Compaction consists of two tasks: First, separating the mutable working set from the immutable "frozen" data. Second, compressing the immutable data and optimizing it for efficient, memory-consumption-friendly snapshotting. Our approach reorganizes and compresses transactional data online and yet hardly affects the mission-critical OLTP throughput. This is achieved by unburdening the OLTP threads from all additional processing and performing these tasks asynchronously.
BibTeX
@article{funke2012compacting,
  author = {Florian Funke and Alfons Kemper and Thomas Neumann},
  title = {{Compacting Transactional Data in Hybrid OLTP \& OLAP Databases}},
  journal = {Proc. VLDB Endow.},
  year = {2012},
  url = {https://openreview.net/forum?id=vqhaMrdqM9},
}
Figure