Bloom
Probabilistic index that can be useful for columns with many distinct values.
The Bloom Index in PostgreSQL is a type of index that can be useful for columns with many distinct values. It is particularly effective when searching across multiple indexed columns using equality queries.
Your Nile database arrives with bloom
extension already enabled, so there’s no need to run create extension
.
Creating and Populating Sample Table
Before creating the index, let’s create a sample table and populate it with data:
Creating a Bloom Index
A Bloom index is most useful for queries that filter on multiple columns. You need to specify the indexed columns and configure the number of bits per column (colN
):
Note: USING bloom
specifies that this is a Bloom filter index and col1 = 4, col2 = 4
defines the number of bits per column to be used in the index (default is 4).
Querying with Bloom Index
Once the Bloom index is created, it can be used to optimize queries with equality conditions on indexed columns:
Limitations
- Bloom filters are probabilistic and can produce false positives, meaning they may return more results than expected.
- They are best suited for queries filtering multiple indexed columns using equality conditions (
=
). - Unlike B-tree indexes, they do not support range queries (
<
,>
,BETWEEN
).
Removing a Bloom Index
If you need to remove a Bloom index:
Conclusion
Bloom indexes in PostgreSQL are useful for multi-column searches with high-cardinality data. They offer space efficiency but come with some trade-offs, such as potential false positives and lack of range query support.
For more details, refer to the PostgreSQL documentation.