Mastering PostgreSQL 12 - Third Edition

Applying bloom filters

Since PostgreSQL 9.6, it has been possible to add index types on the fly using extensions. The new CREATE ACCESS METHOD command, along with some additional features, has made it possible for us to create fully functional and transaction-logged index types on the fly.

The bloom extension provides PostgreSQL users with bloom filters, which are prefilters that help us efficiently reduce the amount of data as soon as possible. The idea behind a bloom filter is that we can calculate a bitmask and compare the bitmask with the query. The bloom filter may produce some false positives, but it will still reduce the amount of data dramatically.

This is especially useful when a table consists of hundreds of columns and millions ...