bloom

What bloom does

A bloom filter is a compact data structure that answers one question: "is this value definitely NOT in the set, or possibly in the set?" The bloom extension applies this concept to indexing. Each row in the table is represented by a fixed-length bit signature in the index, with each indexed column contributing a configurable number of bits.

When a query filters on any combination of the indexed columns using equality (=), PostgreSQL checks each signature against the filter. Rows whose signatures do not match are eliminated immediately. Rows whose signatures do match are candidates — but because bloom filters allow false positives, PostgreSQL rechecks each candidate against the actual heap tuple to confirm the match.

The result is always correct. The false positives just mean the index scan reads a few extra heap pages that turn out not to match. In practice, with reasonable parameter settings, the false positive rate is low enough that the trade-off is worthwhile — particularly when the alternative is maintaining four, six, or eight separate indexes, each occupying space and demanding attention on every write.

When to use bloom

Bloom indexes solve a specific problem: queries that filter on many columns with equality conditions, where the column combinations are unpredictable.

• Ad-hoc multi-column filtering — dashboards or search interfaces where users can filter on any combination of fields (region, status, category, priority, etc.)
• Replacing many single-column B-tree indexes — if you have a wide table and maintain a separate B-tree on each filterable column, a single bloom index can replace all of them with a fraction of the storage
• Low-cardinality columns — bloom filters work especially well on columns with a moderate number of distinct values, where each value appears in many rows
• Read-heavy, write-moderate workloads — bloom indexes are cheaper to maintain than multiple B-tree indexes on writes, since only one index needs updating per row change

Bloom indexes are not appropriate when you need range queries (>, <, BETWEEN), pattern matching (LIKE), ordering, or uniqueness constraints. They only support the = operator.

Installation and setup

bloom is a contrib module that ships with PostgreSQL — no additional packages required.

-- bloom is a contrib module — ships with PostgreSQL
CREATE EXTENSION bloom;

One statement. No shared library preloading, no package manager. Once installed, you can create bloom indexes on any table using the USING bloom clause.

Creating a bloom index

-- Create a bloom index on multiple columns
CREATE INDEX idx_orders_bloom ON orders USING bloom (region, status, category, priority)
  WITH (length=80, col1=2, col2=2, col3=2, col4=2);

The WITH clause accepts the following parameters:

• length — total signature size in bits (default 80, maximum 4096, rounded up to the nearest multiple of 16). Larger signatures reduce false positives but increase index size.
• col1, col2, ... colN — bits allocated to each column (default 2, maximum 4095). More bits per column improve selectivity for that column at the cost of a larger signature.

The defaults (80-bit signatures, 2 bits per column) are reasonable for most use cases. Increase length if you are indexing many columns or need fewer false positives.

Query examples

A bloom index benefits any query that filters on a subset of the indexed columns with equality conditions. Unlike a composite B-tree index, there is no dependency on column order — any combination works. If you have ever agonized over the leading column of a composite B-tree, you will appreciate the absence of that particular decision here.

-- Queries that filter on any combination of indexed columns benefit
SELECT * FROM orders
WHERE region = 'us-east' AND status = 'shipped' AND category = 'electronics';

-- Works with any subset — no column-order dependency
SELECT * FROM orders
WHERE status = 'shipped' AND priority = 'high';

Size comparison with B-tree

The primary advantage of bloom is size. A single bloom index covering four columns is typically much smaller than four separate B-tree indexes on the same columns. The exact ratio depends on row count, data types, and parameter tuning, but order-of-magnitude savings are common on wide tables. A well-run household does not employ four footmen where one will do.

-- Compare bloom index size vs. multiple B-tree indexes
-- Bloom: one compact index covers all columns
CREATE INDEX idx_bloom ON orders USING bloom (region, status, category, priority);

-- B-tree equivalent: one index per column
CREATE INDEX idx_region ON orders (region);
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_category ON orders (category);
CREATE INDEX idx_priority ON orders (priority);

-- Check sizes
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'orders';

Cloud availability

How Gold Lapel relates

Gold Lapel observes your query patterns at the proxy level and generates index recommendations based on what it actually sees in production. When it notices queries filtering on many columns with equality conditions — particularly when no single composite B-tree could serve all the observed combinations — a bloom index is among the recommendations it may offer.

I should note that most teams, left to their own instincts, will create one B-tree per column and move on. It works. But it is rather like hiring four specialists when one versatile member of staff would manage the job with less overhead and fewer conflicts at the door. Gold Lapel can identify these cases and suggest the more composed alternative.