Query Optimization Techniques for Paimon Real-Time Data Lake

Paimon, as a real-time data lake framework, benefits from its LSM-Tree structure design, offering excellent query performance. This article introduces common concepts and methods for query optimization in Paimon.

Table Structure and Storage Optimization

Table Mode Selection

Paimon tables are divided into primary-key tables and non-primary-key tables.

Non-primary-key tables are similar to traditional Hive tables without primary keys. Notably, Paimon's Append tables commit data at checkpoints, ensuring exactly-once consistency even if a checkpoint fails.

The file structure of primary-key tables consists of multiple buckets, each being an independent LSM tree with several files. Flink checkpoints flush L0 files and may trigger compaction. Three modes exist:

MOR (Merge On Read): default mode, performs minor compaction, requires merging at read time.

COW (Copy On Write): set 'full-compaction.delta-commits' = '1' to perform full compaction synchronously during writes, suitable for write‑few‑read‑many scenarios.

MOW (Merge On Write): enable 'deletion-vectors.enabled' = 'true' to generate deletion vectors during writes, filtering unnecessary rows at read time, suitable for write‑many‑read‑few scenarios.

Table Structure Design

Reasonable Partitioning and Bucketing

Choose partition keys that are frequently used for filtering (e.g., time, region) to reduce scan range, and bucket high‑frequency join fields.

CREATE TABLE optimized_table (
    dt STRING,
    region STRING,
    id INT PRIMARY KEY,
    name STRING
) PARTITIONED BY (dt, region)  -- partition by date and region
WITH (
    'bucket' = '8'  -- adjust bucket count based on data volume and query concurrency
);

File Format and Compression

WITH (
    'file.format' = 'parquet',  -- Parquet is more suitable for Paimon than ORC
    'parquet.compression' = 'zstd'  -- compression algorithm: snappy/lz4/zstd
);

Query Parameter Optimization

Primary Key and Predicate Pushdown

For bucketed tables, filtering on the primary key greatly accelerates queries and reduces file reads. Designing appropriate primary key fields is effective for frequent point lookups.

Ensure early data filtering in SQL to minimize scanned data:

SELECT id, name 
FROM table 
WHERE dt = '2023-10-01' AND age > 18;  -- push down filter conditions

Read Parallelism Adjustment

SET 'table.exec.source.parallelism' = '16';  -- adjust according to cluster resources and data size

Batch Read Parameters

WITH (
    'scan.batch-size' = '1000',  -- number of rows per batch read
    'scan.partition-batch-size' = '10'  -- batch size per partition
);

Reasonable Index Design

Paimon provides various index mechanisms, including Bloom filters, partition indexes, and clustering indexes.

Bloom Filter Index

Bloom Filter can quickly determine if a file contains a specific column value, greatly improving scan efficiency for equality queries (e.g., WHERE id = 123). It is not suitable for range queries.

CREATE TABLE user (
    id INT PRIMARY KEY,
    name STRING,
    age INT
) WITH (
    'bloom-filter.columns' = 'id,name',  -- add Bloom filter for id and name
    'bloom-filter.fpp' = '0.01'  -- false positive probability, default 0.01
);

Partition Index

For tables partitioned by time, region, etc., the partition key automatically becomes an index, benefiting queries that include the partition key.

CREATE TABLE order_detail (
    dt STRING,  -- date partition key
    order_id STRING,
    amount DOUBLE
) PARTITIONED BY (dt)  -- partition by date
WITH (
    'bucket' = '4'  -- number of buckets
);

Clustering Index

Clustered storage of frequently co‑queried fields improves multi‑field query performance, e.g., filtering both category and price.

CREATE TABLE product (
    category STRING,
    price DOUBLE,
    name STRING
) WITH (
    'clustering' = 'category,price'  -- cluster by category and price
);

When querying both fields, data is physically closer, reducing I/O:

SELECT * FROM product WHERE category = 'electronics' AND price < 1000;

Additionally, monitoring read operator throughput and latency, and checking Paimon metrics such as scanned file count and filter rate, helps further tune performance.

Finally, you are welcome to join our knowledge community:

《300万字！全网最全大数据学习面试社区等你来》 .

If this article helped you, please remember to "watch", "like", and "bookmark" – the three‑click support!