Skip to content

DuckDB Extension

Bitfilters

Build probabilistic membership filters in DuckDB. Use compact filters to reject keys that are absent before checking possible matches against the original data. Filters accept values of any type, and the Bloom filter matches the ones Parquet files store.

1,761,085
extension loads · last 90 days
On this page
Sample data for the examples

Each browser example starts with these sample tables; sample files are shared across examples on this page. Run this setup first when trying the examples in your own DuckDB session.

-- Fixtures for bitfilters extension SQL examples.
-- Provides small, deterministic source data and derived filters for each
-- independently runnable browser example.

-- Source set of ids referenced by xor8/xor16/binary_fuse8/binary_fuse16
-- examples and by the quotient_filter cookbook example. Includes a
-- `bucket` column for the per-partition cookbook example.
CREATE OR REPLACE TABLE ids AS
SELECT i AS id, (i % 4) AS bucket
FROM range(0, 50) t(i);

-- Allowed ids (bucketed) for the "Build a Filter" cookbook snippet.
CREATE OR REPLACE TABLE allowed_ids AS
SELECT i AS id, (i % 4) AS bucket
FROM range(0, 40) t(i);

-- Interesting keys for the "Pre-Filter an Expensive Join" snippet.
CREATE OR REPLACE TABLE interesting_keys AS
SELECT i AS key
FROM range(0, 30) t(i);

-- Events table — referenced with .key, .day, .user_id, .bucket columns.
CREATE OR REPLACE TABLE events AS
SELECT i              AS key,
       (i % 7)        AS day,
       (i % 13)       AS user_id,
       (i % 4)        AS bucket
FROM range(0, 80) t(i);

-- Source set / unrelated set for FPR-verification snippet.
CREATE OR REPLACE TABLE source_set AS
SELECT i AS id FROM range(0, 50) t(i);

CREATE OR REPLACE TABLE unrelated_set AS
SELECT i AS id FROM range(1000, 1050) t(i);

-- Keys table for the DuckDB-compatible Bloom filter cookbook example.
CREATE OR REPLACE TABLE keys AS
SELECT i AS key, (i % 4) AS bucket
FROM range(0, 40) t(i);

-- A pre-built filter referenced by `the_filter` in the FPR snippet.
-- Stored as a single-row table containing one xor8 filter blob.
CREATE OR REPLACE TABLE the_filter AS
SELECT xor8_filter(id) AS filter FROM source_set;

-- Typed filter columns referenced by *_contains examples in functions.json.
-- Filter blobs are family/width-specific: passing an xor8 blob to a
-- binary_fuse16 probe raises an error.
CREATE OR REPLACE TABLE filter_table AS
SELECT xor8_filter(id)              AS xor8_filter_value,
       xor16_filter(id)             AS xor16_filter_value,
       binary_fuse8_filter(id)      AS binary_fuse8_filter_value,
       binary_fuse16_filter(id)     AS binary_fuse16_filter_value,
       quotient_filter(16, 8, id)   AS quotient_filter_value,
       bloom_filter(1000, 0.01, id) AS bloom_filter_value
FROM ids;

-- Derived tables for examples that query existing filters. Recipes that
-- rebuild these tables use CREATE OR REPLACE so the same setup works there.
CREATE OR REPLACE TABLE interesting_filter AS
SELECT xor8_filter(key) AS filter FROM interesting_keys;

CREATE OR REPLACE TABLE partition_filters AS
SELECT day,
       binary_fuse8_filter(user_id) AS users_in_partition
FROM events
GROUP BY day;

-- Quotient-filter example references `series_data` and groups by id % 2.
CREATE OR REPLACE TABLE series_data AS
SELECT i AS id FROM range(0, 50) t(i);

-- `filters` (per-remainder quotient filters) for the quotient_filter merge /
-- quotient_filter_contains examples and the generic bitfilter_* examples.
CREATE OR REPLACE TABLE filters AS
SELECT id % 2 AS remainder,
       quotient_filter(16, 8, id) AS filter
FROM series_data
GROUP BY id % 2;

-- Users / new_signups for the metadata.json quickStart snippet.
CREATE OR REPLACE TABLE users AS
SELECT 'user' || i || '@example.com' AS email
FROM range(0, 30) t(i);

CREATE OR REPLACE TABLE new_signups AS
SELECT 'user' || i || '@example.com' AS email
FROM range(20, 40) t(i);

-- Generic `tbl` referenced by the bitfilters_duckdb_hash and
-- bitfilters_duckdb_bloom_filter_create examples the extension ships in
-- duckdb_functions().
CREATE OR REPLACE TABLE tbl AS
SELECT i AS key, i AS key_col, i AS col1, i * 10 AS col2
FROM range(0, 20) t(i);

-- Per-bucket DuckDB-compatible Bloom filters for the cookbook snippets,
-- built the same way as the cookbook's CREATE TABLE.
CREATE OR REPLACE TABLE zone_filters AS
SELECT bucket,
       bitfilters_duckdb_bloom_filter_create(
         'v2.0', 16384, bitfilters_duckdb_hash('v2.0', key)) AS bf
FROM keys
GROUP BY bucket;

-- Per-bucket split block Bloom filters for the bloom_filter_merge /
-- bloom_filter_contains examples.
CREATE OR REPLACE TABLE bucket_blooms AS
SELECT bucket,
       bloom_filter(1000, 0.01, key) AS bf
FROM keys
GROUP BY bucket;

Rule out keys before doing expensive work

A membership filter is a compact summary of a set. It can tell you that a value is definitely absent or might be present. Bitfilters builds these summaries from SQL columns and stores them as BLOBs that you can save and query later.

Use a filter to skip irrelevant partitions or reduce the rows sent into an expensive join. A possible match still needs an exact check because filters can produce false positives. The extension offers several filter families with different memory and update trade-offs.

Example

Test whether a key might be present

Build an XOR filter from three IDs and test one of the IDs that went into it.

Tested with DuckDB 1.5.6. Browser examples use Haybarn-Wasm 2.0.0-rc12.

SET allow_extension_repositories = 'allowed';
CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
  WITH PREFIX 'https://duckdb-extensions.query.farm';
INSTALL bitfilters FROM query_farm;
LOAD bitfilters FROM query_farm;

WITH known_ids(id) AS (VALUES (101), (102), (103)),
filters AS (
  SELECT xor8_filter(id) AS filter FROM known_ids
)
SELECT xor8_filter_contains(filter, 102) AS might_contain_102
FROM filters;

Expected result

might_contain_102
true

A positive result means “might be present.” Filters can return false positives, so confirm matches against the original set. Values are hashed for you; probe with values of the same type the filter was built from.

Common tasks

Deep Dive

Technical Details

Reference

Extension Contents

Quick reference to all available functions and settings organized by category.

Binary Fuse Filters
binary_fuse16_filter() Object type: Aggregate function Aggregate that builds a 16-bit Binary Fuse filter — same family as binary_fuse8 but with much lower false-positive rate at the cost of ~2× the memory.
binary_fuse16_filter_contains() Object type: Scalar function Test whether a value is a member of a 16-bit Binary Fuse filter.
binary_fuse8_filter() Object type: Aggregate function Aggregate that builds an 8-bit Binary Fuse filter.
binary_fuse8_filter_contains() Object type: Scalar function Test whether a value is a member of an 8-bit Binary Fuse filter.
Bloom Filters
bloom_filter() Object type: Aggregate function Aggregate that builds a split block Bloom filter — the Parquet Bloom filter format — sized for ndv distinct values at false positive rate fpp.
bloom_filter_contains() Object type: Scalar function Test whether a value might be a member of a Bloom filter built by bloom_filter or bloom_filter_merge.
bloom_filter_merge() Object type: Aggregate function Aggregate that merges Bloom filters into one filter containing every value of the inputs (a bitwise OR).
DuckDB Bloom Filters
bitfilters_duckdb_bloom_filter_create() Object type: Aggregate function Build a Bloom filter in DuckDB's internal join filter format from precomputed UBIGINT hashes.
bitfilters_duckdb_bloom_filter_probe() Object type: Scalar function Test whether an original value, or combination of values, might belong to a DuckDB-compatible Bloom filter.
bitfilters_duckdb_hash() Object type: Scalar function Compute DuckDB-version-specific hashes for one or more original values.
Inspection
binary_fuse16_filter_info() Object type: Scalar function Describe a binary_fuse16_filter BLOB.
binary_fuse16_filter_is_valid() Object type: Scalar function Return whether a BLOB is a structurally valid binary_fuse16_filter, instead of raising the error binary_fuse16_filter_contains would.
binary_fuse8_filter_info() Object type: Scalar function Describe a binary_fuse8_filter BLOB.
binary_fuse8_filter_is_valid() Object type: Scalar function Return whether a BLOB is a structurally valid binary_fuse8_filter, instead of raising the error binary_fuse8_filter_contains would.
bitfilter_contains() Object type: Scalar function Test whether any bitfilters filter may contain a value.
bitfilter_info() Object type: Scalar function Describe any bitfilters filter BLOB: what kind of filter it is, how its values became keys, its size and its expected false positive rate.
bitfilter_is_valid() Object type: Scalar function Return whether a BLOB is a structurally valid bitfilters filter with a header, instead of raising the error a probe would.
bloom_filter_info() Object type: Scalar function Describe a bloom_filter BLOB.
bloom_filter_is_valid() Object type: Scalar function Return whether a BLOB is a structurally valid bloom_filter, instead of raising the error bloom_filter_contains would.
quotient_filter_info() Object type: Scalar function Describe a quotient_filter BLOB.
quotient_filter_is_valid() Object type: Scalar function Return whether a BLOB is a structurally valid quotient_filter, instead of raising the error quotient_filter_contains would.
xor16_filter_info() Object type: Scalar function Describe a xor16_filter BLOB.
xor16_filter_is_valid() Object type: Scalar function Return whether a BLOB is a structurally valid xor16_filter, instead of raising the error xor16_filter_contains would.
xor8_filter_info() Object type: Scalar function Describe a xor8_filter BLOB.
xor8_filter_is_valid() Object type: Scalar function Return whether a BLOB is a structurally valid xor8_filter, instead of raising the error xor8_filter_contains would.
Quotient Filter
quotient_filter() Object type: Aggregate function Aggregate that builds a Quotient filter.
quotient_filter_contains() Object type: Scalar function Test whether a value is a member of a Quotient filter.
XOR Filters
xor16_filter() Object type: Aggregate function Aggregate that builds a 16-bit XOR filter.
xor16_filter_contains() Object type: Scalar function Test whether a value is a member of a 16-bit XOR filter.
xor8_filter() Object type: Aggregate function Aggregate that builds an 8-bit XOR filter.
xor8_filter_contains() Object type: Scalar function Test whether a value is a member of an 8-bit XOR filter.

API Reference

Function Reference

Practical Examples

Cookbook

Real-world recipes and patterns for common use cases.

Platform Support

Compatibility

Extension availability may vary by platform and DuckDB version. Check below to ensure this extension supports your environment before installation.

Quick Facts

Release status Stable
Software License MIT
Pricing Free
Written In C++
Source Available Yes
View on GitHub
Usage
1,761,085
loads · last 90 days

Platforms

DuckDB 2.0.0

  • Linux x86_64aarch64
  • Linux (musl) Not available
  • macOS Apple Silicon
  • Windows x86_64x86_64 (MinGW)
  • WASM Not available
Compiled binary sizes
Platform Architecture Size
Linux x86_64 4.07 MB
Linux aarch64 3.42 MB
macOS Apple Silicon 2.89 MB
Windows x86_64 10.26 MB
Windows x86_64 (MinGW) 17.90 MB

Compressed download sizes for the release shown above.

DuckDB & Haybarn

Release calendar
  • DuckDB v2.0.0 Query.Farm repository Supported
  • Haybarn v1.5.6 Built-in community repository Supported

Browser examples use Haybarn-Wasm 2.0.0-rc12.

Checked .

Learn and explore

Tutorials and articles

Writing about Bitfilters: how it works, what changed, and why.