Paimon LSM Storage Model: How Tiered Compaction Powers Lakehouse Read/Write

Why LSM Trees for Lakehouse?

Traditional databases like MySQL use B+ trees, which excel in read-heavy workloads with stable index levels and fast point lookups. However, in big data lakehouse scenarios, B+ trees face two fatal issues: random write bottlenecks (each update locates and modifies a specific page, making disk random I/O the throughput ceiling) and small file explosion (frequent small-batch writes in distributed settings generate massive small files). The article analogizes B+ trees to a paper book where changing a single character requires tearing out and replacing the entire page.

LSM (Log-Structured Merge-Tree) converts random writes into sequential writes: writes go to new files instead of modifying existing ones; background compaction merges small files into larger ones; reads merge multiple data versions. The analogy: LSM is like writing a diary — each day on a new page, monthly compiled into a bound volume. Finding a record requires flipping pages, but writing is extremely fast.

Paimon's LSM Innovations

Paimon does not simply copy RocksDB's LSM implementation; it optimizes for lakehouse scenarios:

Bucket-level LSM isolation : Each bucket is an independent LSM tree, enabling horizontal scaling.

Compute-storage separation : Compaction tasks can be scheduled independently without consuming read/write resources.

Changelog production : LSM merging automatically produces complete change logs.

Multi-engine compatibility : The same LSM structure supports Flink, Spark, StarRocks, and other compute engines.

Four-Layer LSM Structure

Paimon's LSM tree comprises four logical layers:

1. MemTable (In-Memory Active Table)

All writes first enter the in-memory MemTable, a mutable ordered data structure:

Implemented via Skip List, supporting O(logN) insertion and lookup.

Data sorted by primary key, preparing for disk flush.

Flush threshold controlled by write-buffer-size, default 64MB.

Analogy: MemTable is like the library's front-desk temporary shelf — new books placed here, readers can access directly, but capacity is limited.

2. Immutable MemTable

When MemTable fills, it is marked Immutable (no new writes accepted) while a new MemTable is created for incoming writes. A background thread flushes the immutable table to disk. This design ensures writes are not blocked by flush operations. Analogy: The front shelf is full; the librarian moves it aside for sorting and brings out a new empty shelf.

3. Level 0 (L0 Layer)

Flushed Immutable MemTables become Sorted Runs on disk:

L0 files may have overlapping primary key ranges.

Each file is internally strictly sorted by primary key.

Too many L0 files severely degrade read performance.

Analogy: L0 is like a temporary reading area — each shelf is ordered, but shelves may contain duplicate books; readers must check multiple shelves.

4. Level 1+ (L1/L2/Ln Layers)

After compaction, files are promoted to higher levels:

Files within the same level have non-overlapping primary key ranges.

Higher levels have larger, fewer files.

Paimon defaults to a maximum of 3 levels (L0/L1/L2).

Analogy: L1/L2 are the library's formal shelves — books strictly categorized, no duplicates, high lookup efficiency.

Levels and SortedRun Abstractions

The Levels class is the core manager of the LSM tiered structure in Paimon. It decouples tier state from the filesystem, managing it via in-memory metadata, greatly improving compaction decision efficiency. SortedRun represents an ordered file collection, the basic unit of LSM tiers. In L1+, a level typically corresponds to one SortedRun with non-overlapping file key ranges, supporting efficient range and point queries. Key difference: L0 has multiple SortedRuns (files may overlap), while L1+ usually has one SortedRun (no overlap). Excessive L0 files significantly reduce query performance because too many ordered segments must be merged.

Write Pipeline

Data flows from a Flink job into Paimon's LSM tree through a carefully designed pipeline ensuring efficiency, atomicity, and durability.

Key Write Mechanisms

Mechanism 1: Write Buffer and Spill

Two-level buffering controls write rhythm:

-- Write buffer configuration example
ALTER TABLE orders SET (
  'write-buffer-size' = '128MB',      -- MemTable size
  'write-buffer-spill-size' = '256MB'   -- Spill threshold
);

When memory usage reaches write-buffer-spill-size, Paimon triggers spill, flushing part of the data to disk early to avoid OOM risk.

Mechanism 2: Two-Level Sorting

Paimon performs two sorts in the write path:

In-memory sort : MemTable internally sorts by primary key, guaranteeing flushed files are ordered.

Merge sort : During compaction, multi-way merge sort ensures higher-level files remain ordered.

Analogy: The librarian first sorts each batch of new books by title, then merges them with existing shelf books, keeping the entire shelf ordered.

Mechanism 3: Atomic Commit

Paimon uses snapshots for atomicity:

Data files are written first but not immediately visible.

After all files are written, a new Manifest file is generated.

Finally, a Snapshot file is created; only then does data become visible to queries.

If the process fails mid-way, the old Snapshot remains valid.

Read Path: Merge-On-Read

If the write path is LSM's strength, the read path is its cost — to gain high-performance writes, extra merge cost is paid at read time.

Merge-On-Read Core Logic

Paimon primary key tables use Merge-On-Read (MOR):

Writes do not perform full merges; only new Sorted Runs are written.

Reads must scan files across multiple levels simultaneously.

In-memory K-way merge combines records with the same primary key.

Analogy: MOR is like finding a book by checking the temporary shelf, reading area, and formal shelves simultaneously, then consolidating the information — more work for the reader, but the librarian avoids frequent shelf reorganization, so new books appear quickly.

K-Way Merge with Loser Tree

Reading requires merging data from multiple Sorted Runs — the K-way merge problem. Paimon uses the Loser Tree algorithm for efficient K-way merge. Compared to a traditional min-heap, the loser tree significantly reduces comparisons when K is large:

Each adjustment requires only O(logK) comparisons.

Records with the same primary key are sent to the MergeFunction for merging.

Four Merge Engines (MergeFunction)

When K-way merge encounters records with the same primary key, the merge strategy depends on the table's merge-engine configuration. Paimon provides four strategies:

1. Deduplicate (Default)

'merge-engine' = 'deduplicate'

Compares sequence number or timestamp field.

Retains only the latest record.

Suitable for CDC sync and other full-update scenarios.

Analogy: The library receives a new edition of the same book and replaces the old one; the shelf always holds the latest version.

2. Partial-Update

'merge-engine' = 'partial-update'

Merges non-null fields from multiple records.

Null columns in new data do not overwrite existing values.

Suitable for multi-stream wide-table scenarios.

According to the Alibaba Cloud documentation "Paimon Primary Key Table - Data Lake Construction" (2026), partial update also supports specifying merge order per column via sequence groups, useful in multi-source wide-table scenarios:

CREATE TABLE user_profile (
  user_id INT,
  basic_info STRING,
  behavior_info STRING,
  g_1 INT,  -- basic_info timestamp
  g_2 INT,  -- behavior_info timestamp
  PRIMARY KEY (user_id) NOT ENFORCED
) WITH (
  'merge-engine' = 'partial-update',
  'fields.g_1.sequence-group' = 'basic_info',
  'fields.g_2.sequence-group' = 'behavior_info'
);

3. Aggregation

'merge-engine' = 'aggregation'

Aggregates records with the same primary key.

Supports SUM, MAX, MIN, LAST_NON_NULL_VALUE, etc.

Suitable for pre-aggregated metrics scenarios.

CREATE TABLE product_sales (
  product_id INT,
  sales_amount DOUBLE,
  order_count BIGINT,
  PRIMARY KEY (product_id) NOT ENFORCED
) WITH (
  'merge-engine' = 'aggregation',
  'fields.sales_amount.aggregate-function' = 'sum',
  'fields.order_count.aggregate-function' = 'sum'
);

4. First-Row

'merge-engine' = 'first-row'

Retains only the first encountered record.

Ignores subsequent updates and deletes.

Higher changelog production efficiency.

Read Performance Optimizations

Although MOR requires merging, Paimon employs multiple techniques to significantly optimize read performance (illustrated in the article's diagram).

Compaction: LSM's Metabolism

Compaction is the key process maintaining LSM health — like human metabolism, periodically cleaning garbage and merging redundancy to keep the system running efficiently.

Why Compaction?

As writes continue, L0 files accumulate, causing:

Read amplification : Reading a key may require checking all L0 files.

Space amplification : Multiple versions of the same key consume extra storage.

Metadata bloat : Manifest files grow, slowing metadata operations.

Analogy: If the library never organizes, the temporary reading area piles up; readers must search every shelf, efficiency plummets.

Compaction Triggers

Coordinated by MergeTreeCompactManager, two main triggers:

Condition 1: Sorted Run Count Threshold

-- Compaction trigger configuration
ALTER TABLE orders SET (
  'num-sorted-run.compaction-trigger' = '5',   -- Trigger threshold
  'num-sorted-run.stop-trigger' = '10'         -- Write pause threshold
);

When L0 Sorted Run count reaches compaction-trigger, compaction is triggered; if stop-trigger is reached, writes pause until compaction completes.

Condition 2: Delta Commits Full Compaction

'full-compaction.delta-commits' = '10'

Every N commits, a full compaction is forced, ensuring data eventually merges to the highest level. Critical for query-performance-sensitive scenarios.

Compaction Execution Flow

(Illustrated in the article's diagram.)

Three Merge Modes Comparison

Paimon primary key tables support three merge strategies for different scenarios (illustrated in the article's diagram). Recommendations:

Real-time CDC ingestion: prefer MOR, leverage off-peak full compaction.

ADS layer with latency sensitivity: configure full-compaction.delta-commits for more frequent full merges.

Financial transactions requiring strong consistency: MOR always guarantees reading the latest data.

Changelog Production: LSM's Hidden Superpower

A key differentiator between Paimon and other lake formats (Iceberg/Delta) is that the LSM structure natively supports incremental changelog production.

Three Changelog Production Modes

Configured via changelog-producer (illustrated in the article's diagram). The article states: "Paimon is the only lake format implementing 'Stream-Native' design. Its LSM structure lets data generate complete changelogs upon ingestion, allowing downstream Flink jobs to consume Paimon increments like Kafka."

Incremental Reading Example

-- Stream read Paimon table incremental changes
SELECT * FROM ods_orders
/*+ OPTIONS('scan.mode' = 'latest') */;
-- Combined with Flink window computation
SELECT
  TUMBLE_START(order_time, INTERVAL '5' MINUTE) as window_start,
  count(*) as order_count,
  sum(amount) as total_amount
FROM ods_orders /*+ OPTIONS('scan.mode' = 'latest') */
GROUP BY TUMBLE(order_time, INTERVAL '5' MINUTE);

LSM Performance Tuning Best Practices

Write Performance Tuning

-- High-throughput write configuration template
ALTER TABLE orders SET (
  'write-buffer-size' = '256MB',           -- Increase write buffer
  'compaction.async' = 'true'              -- Async compaction
);

Tip: For write peaks, set num-sorted-run.stop-trigger = 2147483647 to let Paimon generate more files during peaks and merge them slowly during off-peak.

Read Performance Tuning

-- High-performance query configuration template
ALTER TABLE orders SET (
  'full-compaction.delta-commits' = '5',   -- More frequent full compaction
  'compaction.target-file-size' = '256MB'  -- Increase target file size
);

Bucket Count Planning

Bucket count is a key parameter affecting LSM performance:

Too few buckets : Each bucket's data volume too large, compaction and read performance degrade.

Too many buckets : Total file count explodes, metadata management cost rises.

Rule of thumb: Keep each bucket's data volume between 200MB-1GB, derive bucket count from daily data increment.

Summary: Why LSM is the Best Choice for Lakehouse

The article summarizes Paimon LSM storage model's core value in a table (illustrated). Final analogy: If the data lake is a city, the LSM tree is its "intelligent traffic system" — new data enters like new vehicles (write path); periodic road cleaning and traffic control keep flow smooth (compaction); at any moment, complete traffic conditions are queryable (Merge-On-Read); and full vehicle trajectory records are available (changelog). Paimon's LSM model becomes the core lakehouse technology because it perfectly balances write performance, update capability, query efficiency, and streaming consumption.