---
metadata:
  - name: generator
    content: Diplodoc Platform v5.56.0
alternate:
  - https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main
  - https://ydb.tech/docs/ru/dev/bloom-skip-indexes.md?version=main
  - href: en/dev/bloom-skip-indexes.md
    type: text/markdown
    title: Markdown version
  - href: ../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/dev/bloom-skip-indexes.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Bloom skip indexes

Bloom skip indexes are [local](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) auxiliary structures based on a [Bloom filter](https://en.wikipedia.org/wiki/Bloom_filter) that speed up selective queries by skipping data fragments that cannot contain the requested value. Unlike global [secondary indexes](https://ydb.tech/docs/en/concepts/glossary.md?version=main#secondary-index), they act as read-time filters on the main table and reduce the amount of data that must be actually read.

## Types {#types}

YDB supports two types:

* `bloom_filter`: a filter over exact values of the indexed column; use for equality and `IN` (see [when to use which](https://ydb.tech/docs/en/concepts/query_execution/local_indexes.md?version=main#bloom-skip-indexes)).
* `bloom_ngram_filter`: a filter over n-grams of a string column (`String`, `Utf8`); use for substring and `LIKE` pattern search on [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table).

### Local bloom skip indexes {#row-vs-column}

The `bloom_filter` type is supported on both [row-oriented](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table) (OLTP) and [column-oriented](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table) (OLAP) tables, but the underlying implementation differs:

* On row-oriented tables, the filter is built as a prefix bloom filter over a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). The indexed columns must be a contiguous leading subset of the primary key columns. This accelerates point lookups and range scans that constrain the leading key columns. Use [ALTER TABLE ... ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom) to create a prefix bloom filter and [ALTER TABLE ... DROP INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#drop-index) to remove it.
* On column-oriented tables, the filter is built over the indexed column values in each data fragment (portion). It is applied during analytical scans to skip fragments that cannot contain the requested value.

`bloom_ngram_filter` is supported only on column-oriented tables.

## Parameters and defaults {#parameters}

Full list of `WITH (...)` parameters and defaults:

<!-- source: en/yql/reference/syntax/_includes/bloom_skip_index_parameters.md -->
* `bloom_filter`
  * `false_positive_probability`: Target [false-positive rate](https://en.wikipedia.org/wiki/Bloom_filter#Probability_of_false_positives) of the filter: the fraction of fragments that are not skipped even though the requested value is not there (range `(0, 1)`). A lower value reduces extra reads but increases index size.
    * If omitted, the default is `0.0001` for [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table) and `0.1` for [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table). The stricter default on row-oriented tables matches OLTP point lookups; on column-oriented tables it reflects analytical scans, where a larger trade-off between index size and fragment skipping is acceptable.
  * Indexed columns: YQL types that support equality comparison, except `Yson`, `Json`, and `JsonDocument` (see [comparison operators](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#comparison-operators)). One column in a column-oriented table; multiple columns in a row-oriented table. On row-oriented tables, the indexed columns must form a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key).
* `bloom_ngram_filter` (`String` and `Utf8` columns; [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table) only)
  * `ngram_size`: N-gram length, an integer from `3` to `8` (default `3`).
  * `false_positive_probability`: Target [false-positive rate](https://en.wikipedia.org/wiki/Bloom_filter#Probability_of_false_positives) (range `(0, 1)`; default `0.1`).
  * `case_sensitive`: Whether n-grams respect character case: `true` or `false` (default `true`).
<!-- endsource: en/yql/reference/syntax/_includes/bloom_skip_index_parameters.md -->

Creation syntax: [CREATE TABLE](https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main), [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom).

To change parameters after creation, use [ALTER INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#alter-index).

## Examples {#examples}

Create a table with a `bloom_filter` index:

```yql
CREATE TABLE events (
    id Uint64,
    resource_id Utf8,
    message Utf8,
    PRIMARY KEY (id),
    INDEX idx_bloom LOCAL USING bloom_filter
        ON (resource_id)
        WITH (false_positive_probability = 0.01)
);
```

Add a `bloom_ngram_filter` index to an existing table:

```yql
ALTER TABLE events
  ADD INDEX idx_ngram LOCAL USING bloom_ngram_filter
  ON (message)
  WITH (
    ngram_size = 3,
    false_positive_probability = 0.01,
    case_sensitive = true
  );
```

Create a row-oriented (OLTP) table with prefix bloom filters on primary-key prefixes. On row-oriented tables the indexed columns must be a left prefix of the primary key:

```yql
CREATE TABLE orders (
    customer_id Utf8,
    order_id Utf8,
    amount Decimal(10,2),
    PRIMARY KEY (customer_id, order_id),
    -- Prefix bloom filter on the first key column
    INDEX idx_customer LOCAL USING bloom_filter
        ON (customer_id)
        WITH (false_positive_probability = 0.001),
    -- Prefix bloom filter on the full primary key
    INDEX idx_full_key LOCAL USING bloom_filter
        ON (customer_id, order_id)
);
```

Alter index parameters:

```yql
ALTER TABLE events ALTER INDEX idx_ngram SET (
    ngram_size = 4,
    false_positive_probability = 0.005,
    case_sensitive = false
);
```

## Parameter tuning {#tuning}

If queries still read too much data after deploying the index, try lowering `false_positive_probability`. If the index uses too much space, try raising it.

Use [`ALTER INDEX`](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#alter-index) to change values, as in the [example above](#examples).

Tips:

* Start with `false_positive_probability = 0.01`, then adjust based on real read metrics and index size.
* For `bloom_ngram_filter`, `ngram_size` typically starts at `3`; increasing it can make filtering stricter for longer substrings.
* Change one parameter at a time and compare results under the same workload.

## Behavior and limitations {#limitations}

<!-- source: en/yql/reference/syntax/_includes/bloom_skip_index_features.md -->
* The index is always [local](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) (`LOCAL`); there is no [global](https://ydb.tech/docs/en/concepts/glossary.md?version=main#secondary-index) variant.
* Queries do not use the `VIEW <index>` syntax (unlike, for example, [fulltext indexes](https://ydb.tech/docs/en/dev/fulltext-indexes.md?version=main)).
* The filter is applied on read only to data fragments where an index block was already stored during a write or [portion merge](https://ydb.tech/docs/en/concepts/glossary.md?version=main#compaction), other fragments are not skipped by this index until merge.
* On [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table), `bloom_filter` is implemented as a **prefix bloom filter**: the indexed columns must form a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). The filter is built over that key prefix and accelerates point lookups and range scans that constrain the prefix. Multiple bloom indexes over the same prefix length are not allowed.
<!-- endsource: en/yql/reference/syntax/_includes/bloom_skip_index_features.md -->

{% note info "Limitations" %}

<!-- source: en/yql/reference/syntax/_includes/bloom_skip_index_limitations.md -->
* `COVER (...)` and extra index columns are not supported.
* Column-oriented tables allow only one indexed column. Row-oriented tables allow multiple indexed columns.
* `bloom_ngram_filter` is not supported on row-oriented tables.
* On row-oriented tables, the indexed columns of a `bloom_filter` must be a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key). Non-prefix column sets are rejected.
* On row-oriented tables, two `bloom_filter` indexes cannot share the same prefix length (the same set of leading primary-key columns).
<!-- endsource: en/yql/reference/syntax/_includes/bloom_skip_index_limitations.md -->

{% endnote %}

## Additional Materials {#see-also}

* [Secondary indexes](https://ydb.tech/docs/en/dev/secondary-indexes.md?version=main)
* [YQL reference: CREATE TABLE](https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main)
* [YQL reference: SELECT](https://ydb.tech/docs/en/yql/reference/syntax/select/index.md?version=main)
* [YQL reference: ALTER TABLE](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom)
* [Quickstart](https://ydb.tech/docs/en/recipes/bloom-skip-indexes/bloom-skip-index-quickstart.md?version=main)