sapui5tutors SAPUI5 • Fiori • SAP BTP Step-by-step tutorials Real project examples Interview Q&A
Practical SAPUI5 • Fiori • SAP BTP tutorials and interview prep

SAP HANA Architecture: Column Store, Dictionary Compression and Delta Merge Explained

Ask any SAP developer what makes HANA fast and you'll hear "in-memory" within seconds. That's true, but it's only half the story. Plenty of databases can hold data in RAM. What sets HANA apart is how it organizes that data — and the column store is the heart of it.

This post walks through the three ideas that do most of the heavy lifting in HANA's architecture: the column store, dictionary compression, and the delta merge. Understand these and a lot of HANA behavior — good and bad — starts making sense.


Why column store wins for analytics

Traditional databases store tables row by row. A sales order row — order number, customer, date, ten line items, amounts, statuses — sits together in one contiguous block. That's perfect when your workload reads or writes whole rows, like posting a document or displaying a single order.

Analytical workloads don't work like that. A typical report asks: "give me total revenue by region for the last quarter." It needs one or two columns out of fifty, across millions of rows. In a row store, the engine drags every full row off disk (or memory) just to read two fields. Most of the data it touches gets thrown away.

A column store flips the layout: each column is stored contiguously. Reading "revenue" and "region" means scanning exactly those two columns — nothing else. For wide tables with selective queries, that's an enormous win. It's the single biggest reason HANA chews through aggregations that would choke a row-based system.

There's a second, subtler advantage. Values inside one column look alike. A "country" column contains a few dozen distinct values repeated millions of times; a "status" column maybe five. Similar values compress far better than the mixed bag inside a row. Which brings us to...


Dictionary compression: small IDs instead of long strings

Here's the trick. Instead of storing the string "Germany" a million times, HANA builds a dictionary: a sorted list of every distinct value in the column. "Germany" becomes value ID 7, stored as a tiny integer. The column itself is just a dense array of small integers.

Two things fall out of this. First, memory usage collapses — a column of repeated strings shrinks to a fraction of its original size. Second, and less obvious, many operations get faster. Comparing integers is cheaper than comparing strings, so filters, joins, and group-bys on compressed columns run on the small IDs and only translate back to real values at the very end.

The dictionary is sorted, which enables another optimization: range queries and binary search on the dictionary itself. And because the IDs are assigned in sort order, min/max aggregations can sometimes be answered from the dictionary alone without touching the column data at all.

Key takeaway: dictionary compression isn't just about saving memory. Storing small integer value IDs instead of raw values makes scans, filters, and aggregations genuinely faster.

The write problem — and the delta store

Column stores have one famous weakness: writes. Appending a single row means touching every column's data structure — fifty separate writes for one logical insert. Do that for every incoming sales order and performance falls off a cliff.

HANA's answer is the delta store: a small, row-oriented staging area sitting in front of each column table. All inserts, updates, and deletes land in the delta first, where row-wise writes are cheap. Reads transparently combine both stores, so queries always see fresh data.

Think of it like a desk in-tray. New paperwork piles up in the tray (fast to drop in, messy to search), and periodically someone files it all into the cabinet (the columnar main store) in one organized batch. That batch filing is the delta merge.


Delta merge: filing the in-tray

During a delta merge, HANA takes everything accumulated in the delta store, sorts and compresses it, and folds it into the main column store — rebuilding dictionaries as needed. It's a heavier operation, so HANA doesn't do it on every write. Instead it triggers merges based on heuristics: delta size, time since last merge, memory pressure.

This is worth knowing because a bloated delta store is one of the classic HANA performance culprits. If writes vastly outpace merges, queries slow down — they're scanning an ever-larger uncompressed row store alongside the main store. Symptoms: gradually degrading read performance on a table with heavy insert activity.

You can check delta sizes in the HANA cockpit and, if needed, trigger a manual merge:

-- Force a delta merge on a specific table
MERGE DELTA OF "MYSCHEMA"."SALES_ORDERS";

In practice, HANA's automatic merge usually keeps up. Manual merges are for the exceptions — bulk loads, data migrations, or troubleshooting a slow table.


Putting it together

The full picture is elegant: writes land in the row-based delta (fast inserts), reads hit the compressed columnar main store (fast analytics), and the delta merge continuously converts one into the other. Dictionary compression keeps the main store small and scans quick.

Next time someone says "HANA is fast because it's in-memory," you'll know better. Memory helps. But the column store, the dictionaries, and the delta merge are doing the real work.

Key takeaway: HANA's speed comes from architecture, not just RAM — columnar layout for selective scans, dictionary compression for size and speed, and a delta store plus merge to keep writes cheap without sacrificing read performance.