Insights on Columnar Data Storage

[Trupti81]

Introduction:

Instead of using conventional disk drives, an in-memory database is a kind of database management system that stores data directly in main memory (RAM) [3]. This design allows for real-time data processing, analytics, and transactional performance that greatly outperforms traditional systems because memory access is significantly faster than disk access. The one of primary characteristics of an in-memory database is,

Columnar Data Storage

Unlike traditional systems, which store data row by row, SAPHANA’s in-memory database stores data column by column. For instance:

Figure 1: How aColumn Store Table is Stored Physically

For example, the Orders table in the image above has four columns. OrderID, Amount, Shipping_country, and CustomerID.All the data in OrderID is stored first. Then the CustomerIDdata isstored, and so on. The Dictionary and Vector concept is used by HANA to improve CPU and memory utilization, speed up data access, and compress data.Dictionaryis a lookup table that contains all of the distinct values for every column is called a dictionary. HANA generates a distinct Dictionary table for every table column rather than storing raw values repeatedly in memory. Each column’s raw values are saved by HANA in the corresponding Dictionary table. Figure 2 shows how the dictionary table appears

Figure 2: Dictionary Tablefor Figure 1

For example, the Orders table’s Amount column contains the following values: 100, 200, 300, 100, and 150. In contrast, there are only four rows in the Amount column of the dictionary table above, with values of 100, 200, 300, and 150. The reason for this is that 100 is repeated.A vector is a condensed representation of every table column. It saves the Valueid from the matching dictionary table for every column value rather than repeatedly storing the raw values in memory

Figure 2: Vector Table for Figure 1. 

There are no raw values maintained in the picture above. Rather, each value’s Valueid is saved. As a result, it will minimize the amount of RAM needed to store the table

Comparative Advantages of Columnar Processing in Performance

Benefits Of Columnar Storage

Summary:

In-memory databases like SAP HANA store data in RAM for faster processing. They use columnar storage with dictionary and vector compression to reduce memory usage. This improves speed, efficiency, and overall performance for analytics and transactions.