Paimon Bloom Index Deep Dive: 25x Query Speedup from 38s to 1.5s with Production Best Practices

Introduction

The StarRocks + Paimon lakehouse architecture has become a core data infrastructure for many enterprises. Among optimization techniques, Paimon Bloom indexes stand out for their extremely low cost and high performance return, making them the undisputed cost-effectiveness champion.

Real-World Case: 38s to 1.5s Performance Leap

Before Optimization

Table structure: Paimon Append table, partitioned by dt (daily), bucket key shop_id.

Data scale: Single partition ~20GB (article says 400GB earlier but later clarifies 20GB per partition with 4000 files of ~100MB each).

Query:

SELECT * FROM order WHERE dt = '2026-05-22' AND shop_id = 1001

Execution time: 38 seconds.

Plan analysis: StarRocks scanned all 4000 data files, 400GB I/O, 95% time spent on OSS remote reads.

Optimization Action

Added Bloom index on shop_id via a single ALTER TABLE:

ALTER TABLE paimon_order SET TBLPROPERTIES (  'file-index.bloom-filter.columns' = 'shop_id');

After Optimization

Execution time: 1.5 seconds.

Files actually scanned: ~200.

I/O volume: ~20GB.

Speedup: 25x+.

Bloom Index Underlying Principles

What Is a Bloom Filter?

A Bloom filter, proposed by Burton Howard Bloom in 1970, is a space-efficient probabilistic data structure for testing set membership. It has two immutable mathematical properties:

No false negatives: If the filter says an element is not in the set, it is definitely not present.

Possible false positives: If the filter says an element may be in the set, it might not actually be there.

It also offers O(k) query time (k = number of hash functions) and tiny space overhead (~1MB for 1 million elements).

Paimon Bloom Index Implementation

Paimon's Bloom index is a file-level column index , which is the core design enabling massive performance gains.

Directory structure:

Paimon table directory├── snapshot/         # Snapshot metadata├── manifest/         # Manifest files├── index/            # Index files (core)│   ├── file-00001.bloom  # Bloom index for data file 00001│   ├── file-00002.bloom  # Bloom index for data file 00002│   └── ...└── data/             # Data files    ├── file-00001.parquet    ├── file-00002.parquet    └── ...

Full workflow:

Write phase: When Flink/Spark writes data, a separate Bloom filter is generated for each configured column per data file and stored under the index directory.

Query phase:

FE (Frontend) stage: Parses SQL, applies partition filters, lists matching manifest/data/index files, uses manifest statistics (min/max, null count) for initial file pruning, generates splits, assigns to CNs (Compute Nodes). FE only reads metadata; it does NOT download Bloom indexes or perform index checks.

CN (Backend) stage: Each CN receives splits and runs in parallel:

Downloads the corresponding Bloom index into CN memory.

Performs index filtering: shop_id=xxx → checks "possibly exists" vs "definitely not exists".

Reads data files only for "possibly exists" files (from object storage/HDFS).

Applies exact row-level filtering to discard false positive rows.

Why It Works Exceptionally Well in StarRocks

StarRocks deeply optimizes Paimon external tables, fully leveraging Paimon's native indexing:

Equality predicates in WHERE are pushed directly down to Paimon's index layer.

No need to load all file metadata into StarRocks memory.

Can skip the vast majority of irrelevant files without reading any data file content.

Index checks complete entirely in FE memory, extremely fast.

Quantitative Performance Analysis

Before Bloom Index (No Index)

Candidate files = 4000 (after partition filter)Actual files read = 4000 (no index filtering)Total I/O = 4000 × 100MB = 400GBQuery time = 38s (95% OSS read time)

After Bloom Index (With Index)

Paimon default false positive rate (FPP) = 3% (0.03). Calculation:

Files actually containing shop_id=1001 = 100False positive files = (4000 - 100) × 3% ≈ 117Actual files read = 100 + 117 = 217Total I/O = 217 × 100MB ≈ 21.7GBQuery time = 1.5s

Speedup Calculation

I/O reduction: 400GB ÷ 21.7GB ≈ 18.4x

Time reduction: 38s ÷ 1.5s ≈ 25.3x

The difference stems from network latency, CPU overhead, and other fixed costs, aligning closely with theoretical expectations.

False Positive Rate Deep Dive: Does Data Get Corrupted?

Core Conclusion

Absolutely no data distortion or loss. False positive rate only affects query performance and index storage overhead; it never impacts data correctness.

Paimon's Dual Verification Mechanism

Even if Bloom index yields false positives, Paimon provides a second layer of protection:

First layer: Bloom index filters to a candidate file list that "may contain the query value".

Second layer: StarRocks reads the actual content of those files and performs exact row-by-row matching.

Third layer: All non-matching rows are filtered out; final result is returned.

Final results are 100% accurate. False positives only cause a few extra files to be read, adding minor I/O overhead — no data errors or loss.

Default False Positive Rate Behavior

Paimon Bloom index default FPP is 0.1 (10%) — if not explicitly set, the default is 0.1. This is a battle-tested optimal balance from Alibaba's massive production environments.

Comprehensive Comparison of Different FPP Values

When to Manually Adjust FPP

✅ Scenarios to Lower FPP

Query latency must be under 1 second.

High-cardinality columns (e.g., order_id) where each value appears in very few files.

Extremely high query frequency (>1000/day).

Ample storage resources.

-- SparkALTER TABLE paimon_order SET TBLPROPERTIES (  'file-index.bloom-filter.order_id.fpp' = '0.01');

✅ Scenarios to Raise FPP

Storage-constrained, need to minimize index overhead.

Very low query frequency (<10/day).

Cold/archive tables.

Medium-cardinality columns where each value spans many files.

-- SparkALTER TABLE paimon_cold_data SET TBLPROPERTIES (  'file-index.bloom-filter.category_id.fpp' = '0.05');

❌ Extreme Values to Avoid

Do not set below 0.001 (0.1%): Index files become huge, write overhead spikes, performance gain negligible.

Do not set above 0.1 (10%): Excessive false positives cause many unnecessary file reads, degrading query performance.

Best Use Cases & Pitfall Guide

✅ Ideal Scenarios (Maximum Benefit)

High-cardinality equality queries (most important): user_id, order_id, shop_id, device_id. Higher cardinality → better filtering.

Queries returning tiny result sets: Result <1% of total data; Bloom index skips most files.

Large-table point/range queries: Table >100GB, query returns only thousands/tens of thousands of rows; speedup most dramatic.

Frequently filtered columns: Columns used in >90% of business queries.

❌ Anti-Patterns (Index Hurts Performance)

Low-cardinality columns: e.g., status (0/1/2), gender (M/F). Each value exists in nearly all files → Bloom index cannot skip files, adds storage and check overhead.

Range queries: e.g., price > 100, create_time BETWEEN .... Bloom filters only test existence, not ranges.

Fuzzy queries: e.g., name LIKE '%zhangsan%'. Only prefix match ( LIKE 'zhangsan%') might work in some cases but with limited effect.

Frequently updated columns: Frequent updates trigger Bloom index rebuilds, increasing write overhead.

Production Best Practices

6.1 Configure Bloom Index at Table Creation (Spark)

CREATE TABLE paimon_order (  order_id BIGINT,  shop_id BIGINT,  user_id BIGINT,  order_amount DECIMAL(10,2),  create_time TIMESTAMP,  dt STRING) PARTITIONED BY (dt)TBLPROPERTIES (  'bucket-key' = 'shop_id',  'bucket-num' = '8',  -- Create Bloom indexes for top 2-3 high-cardinality columns  'file-index.bloom-filter.columns' = 'shop_id,user_id',  -- Set different FPP per column  'file-index.bloom-filter.shop_id.fpp' = '0.03',  'file-index.bloom-filter.user_id.fpp' = '0.01');

6.2 Dynamically Modify Bloom Index on Existing Tables

-- Add Bloom index to existing tableALTER TABLE paimon_order SET TBLPROPERTIES (  'file-index.bloom-filter.columns' = 'shop_id,user_id');-- Important: Build index for all partitions (Spark engine)CALL sys.rewrite_file_index('my_catalog.my_db.paimon_order');

6.3 Must Combine with Partition Filtering

Bloom index is a file-level index within a partition . Partition filtering must first narrow the candidate file set for Bloom index to be maximally effective.

Wrong (cannot leverage Bloom index):

SELECT * FROM paimon_order WHERE shop_id = 1001;

Correct (partition + Bloom dual filtering):

SELECT * FROM paimon_orderWHERE dt = '2026-05-22' AND shop_id = 1001;

6.4 Limit Number of Bloom Indexes

Each Bloom index adds write and storage overhead.

Only index the top 2-3 most-used high-cardinality equality columns.

Too many Bloom indexes degrade write performance and bloat metadata.

6.5 Verify Bloom Index Effectiveness

Check StarRocks execution plan:

EXPLAIN SELECT * FROM paimon_orderWHERE dt = '2026-05-22' AND shop_id = 1001;

In the plan, if the PaimonScan operator's pushdownPredicates includes shop_id = 1001, the Bloom index is correctly pushed down and used.

Summary

Paimon Bloom index cuts query time from 38s to 1.5s because it skips the vast majority of irrelevant files without reading any data file content , reducing I/O by an order of magnitude.

Key Takeaways:

Data safety: False positives only affect performance, never cause data corruption or loss.

Default is good enough: Paimon's 3% FPP is a production-validated sweet spot; most scenarios need no tuning.

Best scenario: High-cardinality equality queries combined with partition filtering.

Pitfall avoidance: Do not create Bloom indexes on low-cardinality columns or range-query columns.

ROI: Highest cost-effectiveness optimization in StarRocks+Paimon architecture.

In lakehouse architectures, proper use of Paimon Bloom indexes delivers 10-100x query speedups — a must-have skill for every data engineer.