Mastering PostgreSQL 9.6

Applying bloom filters

Since PostgreSQL 9.6, it is possible to add index types on the fly using extensions. The new CREATE ACCESS METHOD command as well as some additional features made it possible to create fully functional and transaction logged indexes types on the fly.

The bloom extension provides PostgreSQL users with bloom filters, which are a pre-filter, which helps to efficiently reduce the amount of data as soon as possible . The idea behind a bloom filter is to calculate a bit mask and to compare the bit mask to the query. The bloom filter might produce some false positives but still reduce the amount of data dramatically.

It is especially useful when a table consists of hundreds of columns and millions of rows. It is not possible ...