Bloom Filters in Analytical Databases, Part 3: ClickHouse vs StarRocks

Series: Bloom Filters in ClickHouse and StarRocks Part 1: ClickHouse · Part 2: StarRocks · Part 3: Side-by-side (you are here)

In Part 1 we went deep on ClickHouse's Bloom filter skip indexes. In Part 2 we did the same for StarRocks: page-level indexes on disk and runtime filters in joins.

This post puts them next to each other. It's not a "which database is faster" post. Both use the same data structure, with the same math and the same limits. What differs is where they put it, how much control they give you, and how they tell you whether it worked. Those differences decide which one is easier to get right for a given workload.

At a glance

The rest of the post walks through each row and what it means in practice.

1. The unit of skipping: granules vs pages

Both systems keep one Bloom filter per chunk of a column and skip the chunks whose filter says "no". The difference is how that chunk is defined.

• ClickHouse counts rows. One filter covers GRANULARITY granules of 8,192 rows each. You decide: GRANULARITY 1 for fine skipping, a larger value for less index metadata.
• StarRocks counts bytes. One filter covers one data page of about 64 KB. How many rows that is depends on the column: a narrow BIGINT fits many more values per page than a 32-character VARCHAR.

What this means in practice:

• With StarRocks, wide string IDs automatically get finer filters than narrow numeric ones, without you doing anything.
• With ClickHouse, you get predictable, tunable blocks, but you have to pick the size. A large GRANULARITY on a high-cardinality column silently makes each filter fuller and less useful.

Neither is better in general. ClickHouse gives you the knob; StarRocks picks a sensible default and doesn't let you change it.

2. Stored indexes: control vs convenience

False positive rate

ClickHouse lets you choose per index: bloom_filter(0.01) gives a ~1% false positive rate at ~9.6 bits per distinct value. The default is 2.5%.

StarRocks' classic index has no such setting. Under the hood it targets 5% (~6.2 bits per distinct value). That's a deliberate trade: smaller filters, a bit more wasted reads. For most ID lookups, the difference between 1% and 5% matters much less than whether the data is clustered (section 6).

What you can index

This is where ClickHouse is clearly more flexible:

-- ClickHouse: index an expression, an array, or map keys/values
INDEX idx_email_lower lower(email)             TYPE bloom_filter(0.01) GRANULARITY 1,
INDEX idx_tags        tags                     TYPE bloom_filter(0.01) GRANULARITY 1,
INDEX idx_attr_keys   mapKeys(LogAttributes)   TYPE bloom_filter(0.01) GRANULARITY 1

StarRocks indexes plain columns only, from a fixed list of types (SMALLINT, INT, BIGINT, LARGEINT, CHAR, VARCHAR, STRING, DATE, DATETIME). There's no Bloom filter on arrays, maps or expressions. On Aggregate and Unique Key tables, only key columns can have one.

If your schema leans on Map attributes (typical for OpenTelemetry-style logs and traces), ClickHouse has a real advantage here.

Adding an index to a table that already has data

Each system has its own trap:

3. Text search: both are moving past Bloom filters

Both systems started with Bloom filter variants for text and are now moving to real inverted indexes.

• ClickHouse had tokenbf_v1 (whole words) and ngrambf_v1 (substrings). The docs now mark both as deprecated for full-text search and recommend the text index, a real inverted index that is GA since 26.2. It supports several tokenizers, including ngrams(N) for substring-style search.
• StarRocks has the NGRAMBF index for LIKE and ngram_search (Beta), and a full-text inverted index (GIN, since 3.3, also Beta).

The shared lesson: Bloom filters are a weak fit for text. Every row produces many tokens or ngrams, so the filters fill up quickly and false positives climb. For anything beyond occasional LIKE on short strings, use the inverted index on either system. On today's versions, ClickHouse's is the more mature of the two.

4. Join runtime filters: StarRocks' head start, ClickHouse is catching up

In Part 2, the runtime filter section was where StarRocks stood out: when a big table joins a small, filtered one, StarRocks builds a filter from the small side's join keys and pushes it to the big side's scan. No index needed.

ClickHouse now does this too. It added join runtime filters in 25.10, turned them on by default in 26.2, and marked them production-ready in 26.9. If you're on a recent ClickHouse, you're probably already using them.

On ClickHouse, the plan looks roughly like this (abbreviated):

Join
  Expression
    ReadFromMergeTree (orders)
    Prewhere filter column: __filterContains(...)
  BuildRuntimeFilter
    ReadFromMergeTree (customer)

My take: on a single node, the two are now close. StarRocks' advantage is maturity and distributed execution: its global runtime filters were designed for shuffle joins across many BEs from the start. If your workload is multi-table star-schema joins on a cluster, StarRocks is still the more predictable choice here. If you run mostly wide, denormalized tables on ClickHouse, you'll rarely depend on runtime filters anyway.

5. Verifying it worked: before vs after

This is the most practical day-to-day difference.

ClickHouse tells you before running the query. EXPLAIN indexes = 1 asks the index directly and reports how many granules it would skip:

Skip
  Name: idx_trace_id
  Description: bloom_filter GRANULARITY 1
  Parts: 2/24
  Granules: 3/244141

You can check a new index in seconds, without scanning anything. For a hard guarantee, force_data_skipping_indices = 'idx_trace_id' makes a query fail if it doesn't use the index.

StarRocks tells you after running the query. The stored index doesn't show up in EXPLAIN. You run the query with enable_profile = true (or use EXPLAIN ANALYZE) and look at the scan's counters:

SegmentInit:
  ShortKeyFilterRows: 0
  ZoneMapIndexFilterRows: 0
  BloomFilterFilterRows: 41.93M

That's richer in one way: you see how much each stage removed (short key, zone map, Bloom filter). But it costs a real execution, and you get rows filtered, not a simple "3 out of 244,141 blocks".

6. What doesn't change: your data layout

After three posts, this is still the most important point: a Bloom filter index only skips data where the value isn't. Whether that's a ClickHouse granule or a StarRocks page doesn't matter. If a value is spread across every block, every filter says "maybe" and nothing is skipped (see the diagram in Part 1, section 6).

So before you compare index options between the two systems, ask the same questions in both:

The answers map almost one-to-one:

7. Decision guide: which one fits your workload?

TL;DR

• Same structure, same math, same limits. No false negatives, some false positives, equality only, and useless when values are scattered.
• ClickHouse gives you control: tunable FPR and GRANULARITY, expressions, arrays and maps, and an EXPLAIN that tells you the result before you run the query.
• StarRocks gives you defaults: page-level filters sized by bytes, a fixed 5% FPR, automatic rebuilds, and very mature distributed join runtime filters.
• Text search is moving to inverted indexes on both; ClickHouse's text index is GA, StarRocks' is Beta.
• Join runtime filters are no longer StarRocks-only. ClickHouse has had them on by default since 26.2, but StarRocks still leads on distributed joins.
• Fix the data layout first. The best Bloom filter in either system can't skip a value that's in every block.

Thanks for following the series. If you have questions or real-world numbers from your own workloads, I'd love to hear them.

References

• ClickHouse docs: Data skipping indexes
• ClickHouse docs: Text indexes
• ClickHouse docs: JOIN runtime filter settings
• ClickHouse docs: enable_join_runtime_filters
• ClickHouse blog: Release 25.10 (join runtime filters)
• StarRocks docs: Bloom filter indexes
• StarRocks docs: N-gram Bloom filter indexes
• StarRocks docs: Full-text inverted index
• StarRocks docs: System variables
• Part 1: ClickHouse
• Part 2: StarRocks