StarRocks Query Acceleration on Paimon: Production Tuning & Best Practices

Overall Architecture

StarRocks reads Paimon metadata and data files directly from object storage or HDFS via a Paimon External Catalog, without importing data into StarRocks storage. Computation occurs on BE nodes, which handle file reading, filtering, and aggregation. Advantages include a single data copy for both batch and streaming workloads and no dual-write requirement. Production pain points are file fragmentation, heavy metadata loading, predicate pushdown failures, dirty data causing query errors, high BE I/O pressure, and day/night query latency fluctuations.

Table Design (Paimon Side)

2.1 Primary Key vs Append-Only Tables

Primary Key Table : Used for CDC synchronization, real-time updates/deletes, written by Flink CDC. Underlying LSM structure generates many delete and changelog files; fragmentation is the primary cause of slow queries. StarRocks must merge incremental files, and performance degrades as file count grows. Production mandate: configure reasonable bucket count and enable auto compaction to prevent small file explosion.

Append-Only Non-Primary Key Table : Suited for logs and append-only fact tables without updates/deletes. Files are mainly Parquet data files with no delete files; StarRocks query performance is far superior to primary key tables.

2.2 Bucket Design

Bucket count sets the parallelism baseline and directly determines StarRocks scan parallelism. Principles:

Flink write parallelism = Paimon bucket count.

Single bucket data volume recommended at 5–20 GB; billion-row tables should have at least 32 buckets.

Primary key table bucket key must exactly match the primary key.

CREATE TABLE paimon.ods.order (</code><code>  id BIGINT,</code><code>  order_no STRING,</code><code>  dt STRING,</code><code>  PRIMARY KEY (id,dt) NOT ENFORCED</code><code>) WITH (</code><code>  'bucket' = '32',</code><code>  'bucket-key' = 'id,dt',</code><code>  -- primary key must enable</code><code>  'delete-vector.enabled' = 'true'</code><code>);

2.3 Partition Design (Predicate Pushdown Core)

Prefer time partitioning on dt / ds; query SQL must include partition filter conditions.

Partition granularity: daily preferred; high throughput can use hourly; prohibit non-partitioned huge tables .

Paimon partition fields should use string type to avoid implicit conversion from temporal types causing predicate pushdown failure .

❌ Pitfall: where cast(dt as date) = '2026-09-05' wraps the partition column in a function, pushdown fails, full table scan. ✅ Correct: where dt = '20260905'.

2.4 Key Paimon Table Properties for StarRocks Optimization

-- Enable auto compaction to control small file count</code><code>'write-only-compaction' = 'true',</code><code>'compaction.min-file-num' = 5,</code><code>'compaction.max-file-num' = 20,</code><code>-- Snapshot retention to control metadata bloat, do not retain snapshots indefinitely</code><code>'snapshot-time-retained' = '24h',</code><code>-- Parquet file sizing: aim for 128–256 MB files to reduce file count</code><code>'parquet.row-group-size' = '134217728',</code><code>'parquet.file.max-size' = '268435456',</code><code>-- Disable unnecessary changelog files to reduce scanned files</code><code>'changelog-producer' = 'none'

Dirty Data Pre-processing (High-Frequency Production Errors)

Symptoms: StarRocks queries on Paimon external tables throw parse exceptions, type mismatches, dirty row errors, array parsing failures; some shards fail while others succeed.

Common Dirty Data Sources

Flink CDC sync with upstream MySQL DDL changes causing schema evolution; old Parquet files have field types inconsistent with new schema.

Upstream writes null values, illegal encoding, or corrupted Parquet files.

Abnormal delete files or snapshot corruption.

Pre-processing Measures

Enable Paimon schema evolution, forbid arbitrary field changes: 'schema.evolution.enabled' = 'true' Flink write-side data cleaning: non-null validation, field type conversion, filter dirty rows — do not let dirty data land in the lake table .

Regularly verify Paimon file integrity using pypaimon / paimon-cli:

# pypaimon validate table files, identify corrupted Parquet files</code><code>paimon-cli.sh dump-table-meta --warehouse hdfs://xxx/paimon --database ods --table order

For corrupted files: if data can be reprocessed, rerun Flink job and compaction to rewrite files; if not, use paimon-cli to skip corrupted snapshots or roll back to a historical snapshot.

StarRocks side should not enable dirty data skipping, as it leads to inaccurate results. Resolve dirty data at the lake layer, do not push compatibility to the query layer.

StarRocks Catalog Creation Best Practices

4.1 Create Paimon External Catalog

CREATE EXTERNAL CATALOG paimon_catalog</code><code>PROPERTIES(</code><code>  "type" = "paimon",</code><code>  "paimon.catalog.type" = "hdfs",</code><code>  "paimon.warehouse" = "hdfs://nameservice/paimon-warehouse",</code><code>  "hadoop.conf.dir" = "/etc/hadoop/conf",</code><code>  -- Metadata cache to reduce repeated metadata loading overhead</code><code>  "paimon.metastore.cache-ttl-ms" = "60000"</code><code>);

Production note: all BE nodes must have HDFS/object storage permissions and Hadoop configuration files; Kerberos clusters require krb5.conf and keytab.

4.2 Verify Predicate Pushdown (Most Critical Tuning Step)

Run explain sql and inspect the execution plan:

Under OlapScanNode check the PaimonScan node for predicates.

If no pushdown conditions appear, the scan reads all files, resulting in terrible performance.

High-Frequency Causes of Predicate Pushdown Failure

Query condition applies a function to the column (e.g., where date(dt)='xxx').

Partition field type mismatch.

Missing statistics.

Use of unsupported functions.

Paimon primary key table with many delete files; some filters cannot be pushed down.

✅ Optimization: filter on bare columns, avoid wrapping fields in functions in the WHERE clause.

SQL Query: Partition + Index Optimization Practical Examples

Paimon provides no secondary indexes; acceleration relies on partition pruning, bucket filtering, Parquet column pruning, and Parquet page indexes . StarRocks external tables cannot use local indexes; all performance depends on the lake table's native file capabilities.

Example Table Baseline

Table: paimon_catalog.ods.order Partition field: dt string (format yyyyMMdd)

Bucket key: id,dt, bucket=32

Core fields: id, order_no, user_id, amount, dt, status

❌ Negative Example 1: Partition Field Wrapped in Function → Partition Pruning Fails, Full Table Scan

-- Wrong: using date() on dt prevents predicate pushdown, scans all partition files</code><code>SELECT order_no,amount FROM paimon_catalog.ods.order</code><code>WHERE date(dt) >= '2026-09-01' AND date(dt) <= '2026-09-05';

✅ Positive Example 1: Bare Partition Filter Triggers Partition Pruning

-- Correct: direct use of raw partition string triggers partition pruning, scans only 5 days of files</code><code>SELECT order_no,amount FROM paimon_catalog.ods.order</code><code>WHERE dt BETWEEN '20260901' AND '20260905';

❌ Negative Example 2: Large IN Query with Thousands of order_no Scans All Buckets

SELECT * FROM paimon_catalog.ods.order</code><code>WHERE dt='20260905' AND order_no IN ('no1','no2',......);

✅ Positive Example 2: Primary Key / Bucket Field Filter Triggers Bucket Pruning

Bucket key is id,dt; including id+dt in the filter allows Paimon to skip non-matching bucket files, reducing scanned file count.

SELECT order_no,amount FROM paimon_catalog.ods.order</code><code>WHERE dt='20260905' AND id IN (1001,1002,1003);

✅ Positive Example 3: Combined Conditions — Partition + Bucket Field + Regular Field

SELECT order_no,amount,status</code><code>FROM paimon_catalog.ods.order</code><code>WHERE dt >= '20260901'</code><code>  AND dt <= '20260905'</code><code>  AND id >= 10000</code><code>  AND status = 'SUCCESS';

✅ Positive Example 4: Column Pruning — Avoid SELECT *, Leverage Parquet Column Index

-- Only query required business fields; Parquet reads only those columns, reducing I/O</code><code>SELECT id,order_no,amount,dt</code><code>FROM paimon_catalog.ods.order</code><code>WHERE dt='20260905' AND status='SUCCESS';

✅ Positive Example 5: Range Time Partition — Avoid Single Large Span Query, Split SQL

-- Not recommended to query 30 days at once; split into shards to reduce single SQL BE I/O pressure</code><code>-- Can loop by dt on business side or use materialized view for pre-aggregation</code><code>SELECT dt, sum(amount) as total_amount</code><code>FROM paimon_catalog.ods.order</code><code>WHERE dt BETWEEN '20260901' AND '20260903'</code><code>GROUP BY dt;

Verify Pruning Effectiveness

Use explain to inspect the PaimonScan node:

explain SELECT id,order_no FROM paimon_catalog.ods.order WHERE dt='20260905' AND id>1000;

See predicates: carrying dt and id filter conditions → pushdown successful.

Check scanned file count; a significant drop confirms partition/bucket pruning is effective.

Supplementary: Statistics Collection for CBO Optimizer

ANALYZE TABLE paimon_catalog.ods.order;

Collects Paimon table statistics (row count, partition stats) to help StarRocks choose a reasonable scan parallelism.

Query Method Selection

Method 1: Direct Query Paimon External Table (Simple Reports, Low-Frequency Large Queries)

select * from paimon_catalog.ods.order where dt='20260905';

Pros: no extra storage.

Cons: every query scans lake files; high concurrent QPS scenarios suffer pressure; fragmentation causes day/night performance jitter.

Method 2: StarRocks Async Materialized View (Production Recommended, Accelerates High-Frequency Queries)

Core idea: asynchronously materialize Paimon external query results into a StarRocks internal table; queries hit local storage for sub-second response while the underlying lake table retains raw data.

CREATE ASYNC MATERIALIZED VIEW mv_ods_order</code><code>REFRESH EVERY 1 HOUR</code><code>AS</code><code>select id,order_no,dt,amount from paimon_catalog.ods.order where dt >= '20260901';

Suitable for: high-frequency business queries, high QPS, tolerating minute-to-hour latency.

Unsuitable for: sub-second real-time freshness requirements.

Note: partition-pruned refresh — only new partitions are refreshed, avoiding full recomputation.

Method 3: Hot Data Dual-Write to StarRocks Primary Key Internal Table, Cold Data Query Paimon External Table

Hot data (recent 7–30 days): Flink dual-writes to StarRocks primary key table.

Cold historical data: StarRocks queries Paimon external table.

Use UNION ALL to merge cold and hot queries.

This architecture is the classic Fluss + StarRocks + Paimon cold/hot tiering pattern.

Production Tuning (Paimon Side & StarRocks Side)

🔹 Paimon Side Tuning (Root Cause, Priority)

Control compaction to eliminate massive small files . For real-time primary key tables, enable auto compaction; during high write pressure, increase compaction thresholds and run compaction during low-traffic periods. Phenomenon: morning queries fast, daytime write peak causes small file surge and query slowdown — typical compaction lagging behind write speed.

Set reasonable snapshot retention ( snapshot-time-retained); do not retain snapshots indefinitely. Excessive snapshots cause slow metadata loading.

Prohibit small buckets; too few buckets generate massive small files.

🔹 StarRocks BE Side Tuning

Adjust external scan threads to control I/O concurrency, avoiding BE disk/network saturation:

-- session level, or configure in be.conf</code><code>set global external_scan_thread_num_per_be = 16;

Tune Parquet read memory to avoid OOM. Recommendations:

Filter data early using starrocks.filter.query to reduce read volume.

Ensure partition and bucket pruning are effective.

Apply column pruning — select only needed columns. query_mem_limit: BE query memory limit (default 0 = no limit, but bounded by BE total memory). Increasing may help if Parquet reading causes OOM. max_hdfs_scanner_num: limits concurrent scanners for external tables; lowering reduces concurrent Parquet block reads and memory consumption, at some performance cost. pipeline_connector_scan_thread_num_per_cpu: controls scan threads per CPU core for pipeline connector; adjusting affects parallelism and memory usage. parquet_late_materialization_enable: boolean to enable Parquet late materialization for performance; usually recommended to keep enabled.

Collect statistics to assist CBO optimizer in generating better plans:

explain analyze select id,name from paimon_catalog.ods.order where id='111';

🔹 SQL Side Tuning

Force partition filter conditions; prohibit full table scans without partition.

Only select required columns; avoid SELECT * to leverage Parquet column pruning.

Split large queries; avoid single SQL scanning massive file sets.

Avoid large IN lists; rewrite as JOIN.

Prefer partition and bucket fields in filters to achieve partition/bucket pruning.

Reduce LIKE '%xx%' operations.

Avoid IS NULL / IS NOT NULL (handle at processing layer or fill with sentinel values).

Common Production Troubleshooting Checklist

Production Landing Red Lines

Do not expose high-QPS business directly to raw Paimon external tables; prioritize materialized views or cold/hot tiered architecture.

Paimon primary key tables must control compaction; small files are the performance killer.

Handle dirty data at the lake write layer; do not rely on StarRocks query-side fault tolerance.

Partition fields must not be wrapped in functions; ensure predicate pushdown works.

Monitor metrics: Paimon table file count, compaction task status, StarRocks external query p99 latency, BE IOPS.