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.
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;
SET allow_extension_repositories = 'allowed';
CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
WITH PREFIX 'https://haybarn-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;
INSTALL bitfilters FROM community;
LOAD bitfilters;
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
What is a probabilistic filter?
A probabilistic set-membership filter answers “Is X in this set?” with two carefully chosen guarantees:
- No false negatives. If the filter says NO, the element is definitely not in the set.
- Bounded false positives. If the filter says YES, the element is probably in the set — with a measurable probability of being wrong.
That’s a powerful tradeoff. You give up exactness in one direction in exchange for:
- Memory — typically 1–2 bytes per element instead of storing the whole element.
- Speed — O(1) probe, often a single cache line read.
Use cases include join pre-filtering, data skipping, dedupe pre-checks, cache hit/miss prediction, and routing requests to the right shard.
Filter family comparison
| Family | Memory (≈, per element) | False positive rate | Mutation | Best for |
|---|---|---|---|---|
| Quotient | tunable (q, r) | ~2^(−r) | Mergeable (holds ≤ 2^q − 1 values) | Mergeable sets, self-sizing |
| XOR-8 | ~9 bits | ~0.39% | Static | General-purpose |
| XOR-16 | ~18 bits | ~0.0015% | Static | Very low FPR needed |
| Binary Fuse-8 | <9 bits | ~0.39% | Static | Smallest static filter |
| Binary Fuse-16 | ~18 bits | ~0.0015% | Static | Smallest very-low-FPR filter |
| Bloom (split block) | tunable (ndv, fpp) | tunable | Mergeable, never “full” | Incremental sets, Parquet compat |
| DuckDB Bloom | tunable | tunable | Static | DuckDB-native compat |
For most read-heavy workloads, Binary Fuse 8 is the right default — the smallest filter, but build-once. Switch to Bloom when you need to merge filters built separately, want to add to a set over time, or want filters compatible with Parquet Bloom filters; switch to Quotient for a mergeable filter that can size itself to its input; switch to a 16-bit variant when even 0.4% false positives are too many.
Values and keys
The XOR, Binary Fuse, Quotient and split block Bloom constructors accept supported scalar values directly, e.g. xor8_filter(email):
UBIGINTvalues are used as 64-bit keys exactly as given, for when you compute your own keys — for example withxxh3_64(value)fromhashfuncs. (The Bloom filter is the exception: it hashesUBIGINTvalues too, for Parquet compatibility.)- Every other type is hashed with XXH64 of a fixed byte encoding: the Parquet plain encoding where Parquet defines one (the same keys Parquet Bloom filters use), otherwise DuckDB’s on-disk representation.
- LIST, STRUCT, MAP, ARRAY, UNION and ENUM have no stable encoding and are rejected when the query is bound. Cast them to
VARCHAR, or compute your ownUBIGINTkey.
The encoding is deliberately stable across DuckDB versions. These filters do not use DuckDB’s hash(), which may change between versions — a filter you store today still answers correctly after you upgrade DuckDB.
The separate bitfilters_duckdb_* family uses DuckDB-version-specific hashes and its internal Bloom format. Create those filters from bitfilters_duckdb_hash results, then probe with the original values, the same value types and the same version string.
When you probe, the value is cast to the type the filter was built from: xor8_filter_contains(f, 42) and xor8_filter_contains(f, '42') both work on a BIGINT filter. A value that can’t be cast ('abc' against an integer filter) returns false, and a NULL probe returns NULL. Filters built from UBIGINT keys accept other integer probes but reject non-integer probes with an error.
Filters as columns
Every filter constructor is an aggregate function and produces a single BLOB. That makes filters first-class values you can:
- Store in a regular column (
CREATE OR REPLACE TABLE filters (bucket INT, filter BLOB)). - Persist in Parquet for cross-process reuse.
- Pass to other queries via CTE or table valued.
- Combine via UNION (build per-partition filters separately, then probe each), or merge Bloom and Quotient filters into one with
bloom_filter_merge/quotient_filter(q, r, filter).
There’s no special filter type — just a BLOB plus the matching *_contains function, or the generic bitfilter_contains.
The filter header
XOR, Binary Fuse, Quotient and split block Bloom filter BLOBs start with a 16-byte header: the magic bytes BFLT, a format version, the filter type, the key hash (none or XXH64) and the value type. That’s what lets bitfilter_contains, bitfilter_info and bitfilter_is_valid work across these families, and why passing one filter type to another’s function gives a clear error (the filter BLOB is a xor8_filter, not a quotient_filter). DuckDB-internal Bloom filters have a separate format and must be probed with bitfilters_duckdb_bloom_filter_probe.
- Filters are 16 bytes larger than before, and older versions of the extension can’t read them.
- BLOBs written by older versions (no header) are still read by the type-specific functions such as
xor8_filter_containsandxor8_filter_info(which reportsformat_version0), but not by the genericbitfilter_*functions.
Memory
Building a filter is charged against DuckDB’s memory_limit: a build that doesn’t fit raises an Out of Memory error instead of growing without bound. Grouped xor*, binary_fuse* and self-sizing quotient_filter(r, value) builds spill to disk when they don’t fit in memory.
When not to use a filter
- You actually need the elements, not membership. Filters can’t enumerate; they’re set-only.
- The set is tiny. A 10K-element set in a HashMap is faster than a filter.
- You can’t tolerate any false positives, including in a probe-then-verify pattern. (Though probe-then-verify makes filters exact for correctness purposes — you just pay the verify cost on FPR-rate of inputs.)
- The set churns continuously. Every filter has construction cost, and none of them support deleting a value; if you rebuild on every query, the filter isn’t earning its keep.
Reference
Extension Contents
Quick reference to all available functions and settings organized by category.
| Name | Type | Description |
|---|---|---|
|
Binary Fuse Filters
Binary Fuse is the modern best-in-class space-efficient family. <9 bits per key for the 8-bit variant, very fast construction and lookups. Static structure — pick this for read-heavy workloads where storage matters most. |
||
| 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
Split block Bloom filters — the Parquet Bloom filter format. Sized up front for an expected number of distinct values and a target false positive rate, mergeable with |
||
| 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
Bloom filters in DuckDB's native internal format. Use these when you need to materialize join filters or zone-map-style data-skipping structures that the DuckDB optimizer can consume directly. |
||
| 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
Generic functions that work on any filter BLOB with a bitfilters header — probe it, describe it, or check that it is valid — plus the per-type |
||
| 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 filters are mergeable — build per-partition filters, then combine them with |
||
| 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
XOR filters are static — built once, queried many times. ~9 bits per key for the 8-bit variant (0.39% FPR), ~18 bits per key for the 16-bit (0.0015% FPR). ~20% smaller than Bloom for the same false-positive rate. |
||
| 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. |
No extension contents match that search.
API Reference
Function Reference
Practical Examples
Cookbook
Real-world recipes and patterns for common use cases.
Pick the Right Filter
| Need | Pick |
|---|---|
| Static set, smallest possible memory | binary_fuse8_filter |
| Static set, very low FPR | binary_fuse16_filter or xor16_filter |
| Add to it over time, merge per-partition filters, read alongside Parquet Bloom filters | bloom_filter |
| Mergeable, sized to its input without guessing | quotient_filter(r, value) |
| Plug into DuckDB’s join optimizer | bitfilters_duckdb_bloom_filter_* |
In short: Bloom is incremental, mergeable and Parquet-compatible; XOR / Binary Fuse are the smallest but build-once; Quotient is mergeable and can size itself.
The XOR, Binary Fuse, Quotient and split block Bloom constructors accept supported scalar values directly. They hash values with XXH64 of a fixed encoding that stays the same across DuckDB versions, so you don’t need hash(). XOR, Binary Fuse and Quotient use UBIGINT values as keys exactly as given; split block Bloom hashes them too. The separate bitfilters_duckdb_bloom_filter_create constructor takes hashes produced by bitfilters_duckdb_hash.
Build a Filter
Filter constructors are aggregate functions — pass every member of the set and the result is a single BLOB you can store anywhere DuckDB stores BLOBs:
-- Materialize a filter per partitionCREATE TABLE allowed_filters ASSELECT bucket, binary_fuse8_filter(id) AS filterFROM allowed_idsGROUP BY bucket;The filter is self-contained: its header records the filter type, how values were hashed and the value type. At probe time just call the matching *_contains function (or bitfilter_contains) with the value — it is cast to the type the filter was built from, so 42 and '42' both work against a BIGINT filter.
Pre-Filter an Expensive Join
The classic use case — avoid an expensive lookup or cross-partition join when you can probabilistically rule rows out:
-- 1. Build a filter from "interesting" keys, onceCREATE OR REPLACE TABLE interesting_filter ASSELECT xor8_filter(key) AS filter FROM interesting_keys;
-- 2. Probe the filter to skip the expensive operation entirely for non-membersSELECT e.*FROM events e, interesting_filter fWHERE xor8_filter_contains(f.filter, e.key) -- fast: maybe-member AND e.key IN (SELECT key FROM interesting_keys); -- slow: confirm if maybeBecause filters never produce false negatives, the probe-then-verify pattern is exact — you only pay the expensive IN for rows that pass the cheap filter.
Per-Partition Data Skipping
Materialize one filter per logical partition; use it to skip whole partitions at query time:
-- Build filters at write timeCREATE OR REPLACE TABLE partition_filters ASSELECT day, binary_fuse8_filter(user_id) AS users_in_partitionFROM eventsGROUP BY day;
-- At query time, only scan partitions that might contain the userSELECT day, COUNT(*) AS hitsFROM eventsWHERE day IN ( SELECT day FROM partition_filters WHERE binary_fuse8_filter_contains(users_in_partition, 7)) AND user_id = 7GROUP BY day;For large fact tables this can skip 99%+ of partitions when looking for a specific user/key.
Verify the False-Positive Rate
If you want to confirm the FPR on your data, count probes that the filter says yes to but the source set doesn’t actually contain:
-- Real membersSELECT COUNT(*) AS true_positivesFROM source_set s, the_filter fWHERE xor8_filter_contains(f.filter, s.id);-- Non-members that the filter incorrectly acceptsSELECT COUNT(*) AS false_positivesFROM unrelated_set u, the_filter fWHERE xor8_filter_contains(f.filter, u.id);Both xor8 and binary_fuse8 have an expected false-positive rate of 1/256 (~0.39%); the 16-bit variants have a rate of 1/65,536 (~0.0015%). Binary Fuse uses less space at the same fingerprint width. bitfilter_info(filter).expected_fpr gives the rate the filter expects.
Inspect a Filter
bitfilter_info describes XOR, Binary Fuse, Quotient and split block Bloom filter BLOBs — type, key type, size, entries, expected false positive rate. It does not accept the separate DuckDB-internal Bloom format. Fields a filter type doesn’t have are NULL:
SELECT i.filter_type, i.key_type, i.size_bytes, i.expected_fprFROM the_filter, LATERAL (SELECT bitfilter_info(filter) AS i);Use bitfilter_is_valid (or the per-type *_is_valid) to find damaged or mismatched BLOBs without raising the error a probe would. Passing one filter type to another’s function fails with a clear error, e.g. the filter BLOB is a xor8_filter, not a quotient_filter.
Bloom Filters — Incremental and Parquet-Compatible
bloom_filter(ndv, fpp, value) builds a split block Bloom filter sized for ndv distinct values at false positive rate fpp. For the same values and sizing, its payload after the 16-byte Bitfilters header is byte-for-byte the Bloom filter blocks DuckDB writes into Parquet files.
-- One filter per bucket, each sized for ~1,000 keys at 1% FPRCREATE OR REPLACE TABLE bucket_blooms ASSELECT bucket, bloom_filter(1000, 0.01, key) AS bfFROM keysGROUP BY bucket;
-- Roll them up into one filter (bitwise OR)SELECT bloom_filter_merge(bf) AS all_keys FROM bucket_blooms;Filters merge when their payload sizes and value types match. Using the same ndv and fpp guarantees matching sizes; different settings can also round to the same size. A Bloom filter never gets “full” — insert more than ndv values and its false positive rate degrades gradually.
Quotient Filter — Mergeable Sets
Quotient filters can be merged, and can pick their own size. The q and r parameters tune the size/FPR tradeoff:
q— number of slot index bits. The filter holds at most 2^q − 1 values; inserting more is an error.r— remainder bits per slot. Higherr→ lower FPR, more memory.
Approximate FPR ≈ 2^(−r). At r = 4, expect ~6% FPR; at r = 8, ~0.4%.
SELECT bucket, quotient_filter(20, 8, id) AS filter -- up to ~1M values, ~0.4% FPRFROM idsGROUP BY bucket;Don’t know how many values you have? Leave out q and the filter sizes itself so the distinct values fill at most 3/4 of the slots:
SELECT bucket, quotient_filter(8, id) AS filter -- ~0.4% FPR, q chosen for youFROM idsGROUP BY bucket;To merge, pass filter BLOBs instead of values. The result has exactly the q and r you give, and each input needs at least q + r hash bits — so a merge can grow q only by giving up as many remainder bits:
-- Per-bucket (16, 8) filters merge into one (20, 4) filter — not (20, 8)WITH parts AS ( SELECT quotient_filter(16, 8, id) AS filter FROM ids GROUP BY bucket)SELECT quotient_filter(20, 4, filter) AS merged FROM parts;DuckDB-Compatible Bloom Filters
The bitfilters_duckdb_* family produces filters in DuckDB’s internal Bloom filter format — the same one the optimizer materializes during certain joins. Use these when you want to:
- Persist a filter and re-use it later in a join hint.
- Build per-zone Bloom filters for custom data-skipping logic.
All three functions take a constant version string (e.g. 'v2.0') as their first argument — bind-folded at parse time — so the produced hash and filter layout match the DuckDB build that will probe them later. Accepted versions are v1.4.0–v1.4.4, v1.5.0, v1.5.1, v1.6.0 and v2.0 (also spelled 2.0, v2.0.0 or 2.0.0); the leading v is optional throughout. Any other string is a binder error that lists the supported versions.
-- Build one DuckDB-shaped Bloom filter per bucket. The aggregate's middle-- argument (16384 here) is the sector count — must be a power of two.CREATE OR REPLACE TABLE zone_filters ASSELECT bucket, bitfilters_duckdb_bloom_filter_create( 'v2.0', 16384, bitfilters_duckdb_hash('v2.0', key) ) AS bfFROM keysGROUP BY bucket;
-- Probe one bucket's filter at scan time. The filter can be a constant-- BLOB, a scalar subquery (as here) or a per-row BLOB column.SELECT *FROM events eWHERE e.bucket = 2 AND bitfilters_duckdb_bloom_filter_probe( 'v2.0', (SELECT bf FROM zone_filters WHERE bucket = 2), e.key );The filter can also come from a column, so a join can probe each row against its own bucket’s filter. A NULL filter returns NULL, which WHERE treats as false:
-- Probe every event against the filter for its bucketSELECT e.bucket, count(*) AS maybe_presentFROM events eJOIN zone_filters z USING (bucket)WHERE bitfilters_duckdb_bloom_filter_probe('v2.0', z.bf, e.key)GROUP BY e.bucketORDER BY e.bucket;bitfilters_duckdb_hash and bitfilters_duckdb_bloom_filter_create produce hashes / sector layouts that match the DuckDB build named by the version string — pin it to your runtime’s exact version. DuckDB 2.0 kept the hash algorithms (Hash<T>, MurmurHash64, HashBytes, CombineHashScalar) and the Bloom filter sector/mask layout of v1.4–v1.6, so 'v2.0' maps to the same implementation: bitfilters_duckdb_hash('v2.0', x) equals DuckDB 2.0’s own hash(x), and filters built under a v1.x version string probe identically under 'v2.0' on little-endian platforms (x86-64, ARM64).
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
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.
-
Why we passed on more filters for DuckDB’s bitfilters
We evaluated BuRR, Taffy, Grafite, Diva++, and partitioned Binary Fuse for DuckDB’s bitfilters extension. See the memory, speed, and correctness tradeoffs.