PostgreSQL 9.6 `bloom`

is an extension contributed by Teodor Sigaev, Alexander Korotkov and Oleg Bartunov which provides a new index type for integer and text columns. There is some coverage on how to use it and how it works, which is good because documentation is scarse.

This blog entry describes briefly how the index works and discusses how to choose the parameters associated to this index, namely the *signature size* (default 80, allocated in chunks of 16 bits) and the *number of bits for each indexed column* (default 2), with a rule of thumb approach based on pretty rough approximations.

The short version is that there is a simple formula to suggest the number of bits of a column based on its entropy, and then another formula to compute the best signature size, the result of which varies widely from SSD to HDD. You will have to read further to get them:-)

## PostgreSQL Bloom Index

A Bloom *filter* is a compact bit array which represents a set of values. The structure allows to test whether an element *probably* belongs to the set, with some false positives but *no* false negatives: the test may return true although the element is not in the set, but cannot return false if it is.

A Bloom *index* created for a **set** of columns can be used to speed-up queries for a conjunction (`AND`

) of equalities (`=`

) on a **subset** of these columns:

The Bloom *index* is built by associating a small Bloom *filter* called a *signature* to each row of a table. For each indexed column value a few bits of the bit-array are turned on depending on the column number and its value passed through a hash function. These signatures are then aggregated and managed as an on-disk index.

When a search is performed with the index, the partial signature corresponding to the columns provided in the query is built, and then all rows the signature of which *contains* these bits are filtered out and checked. As the whole or a significant part of the index is scanned, it only makes sense if the index size is significantly smaller than the table size, which implies that the signature size must be significantly smaller than the row size.

## Signature Size

In order to choose the index parameters, we build a simple model to understand their effects. Let be the signature size, denote the index columns, the number of bits turned on for each column. Assuming that values in a column are evenly distributed and that the hash function is perfect, each bit of the signature may be 1 with probability:

The later approximation assumes that . Then when a query on columns is built, the selectivity of the index is the product of the probabilities of the signature bits that occur in the partial signature:

There is a trade-off between a large and precise Bloom index, which takes longer to be scanned but selects less rows to be examined later, and a smaller one which is scanned quickly but selects more rows to test further. With the cost of reading a page sequentially and the cost for a random page, with the page size in bits, the row pointer (eg page number and offset within), the number of rows, then the cost of a search (we neglect log factors, we assume that there is one useful row per loaded page) is:

The left term is the sequential scan of the index, the right term is the random accesses on table pages. By substituting, simplifying and zeroing the derivative of the formula, the cost is minimal for signature size:

with and the number of switched bits in the index and query columns. Constant 1.3 is an average value for with small. The ideal signature size is roughly proportional to the number of bits allocated to index columns , so as to maintain the selectivity. The more query column bits the smaller the signature, as more bits provide more filtering clues.

The ratio compares random and sequential page read performances which varies widely depending on the technology, from maybe 200 on HDD down to 4 or less on SSD, or even 1 for data in cache: signature sizes can be much smaller on SSD, as filtering there does not need to be as effective as on HDD because the penalty of loading useless random pages is much reduced. The dependency on page size may look strange, but it is really just a scaling parameter: if it is changed, then the sequential access cost would be changed proportionaly, thus the factor in the formula would be pretty constant as well as the best signature size.

Note that this best signature size does not account the cost of storing and maintaining the index: a smaller size, although less effective, may be a better compromise. Taking this into account, we would really like to find the smallest signature size for which the cost is close to the minimum. Assuming a reduction factor on the signature size, the cost of scanning the index is reduced by that but the cost of accessing the table is increased by . To limit arbitrarily this increase to 30%, we can choose down to:

Which leads to reduced signature size defined as:

In practice we find for typical query settings (a few columns, a few bits per columns).

## Number of Column Bits

The formula above gives an indication for the signature size, which depends both on the number of bits of the index and query columns. The next step is to choose how to allocate bits between index columns. The minimal search cost depends on so that the lower and larger the better, which leads to a trade-off as .

From an information theory perspective, the bits chosen in the signatures store part of the column values entropy that allows the selectivity of the index. Each bit is chosen within the bits of the signature, thus may store about bits of entropy (a little less really because of collisions with other bits, say , although is not known). For a given index column, there is no point in trying to store more entropy than the column intrinsically holds. For columns with an entropy smaller than , one bit transfers most of it into the index. Otherwise the more bits the more entropy is transfered, but with a decreassing efficiency. The following choice transfers about two-thirds of the column entropy to the index:

(More precisely for transfering fraction of entropy, under the assumption that . See end of page for a better approximation). Allocating bits in the signature for columns with small entropy, that is with few distinct or poorly distributed values, is probably not worth the space. The attentive reader will also have noticed the chicken or the egg issue brought with this formula: the number of bits per column depends on the signature size, but the signature size depends on the number of bits per column. Iterating once or twice may be necessary to converge.

Shannon entropy can be computed directly on a column by applying the famous formula:

A `UNIQUE`

column provides entropy, but as it is already indexed it does not need to appear again in a Bloom index.

A column holding uniformly distributed dates over 6 years, that is about 2200 distinct values, provides 11.1 bits of entropy. For the default 80 bits signature size which holds about bits of entropy per chosen bit, this suggest in bits could allocated to such a column. Good news, this is the default.

A 1,000,000 element column with uniformly distributed values drawn from 1,000,000 values, as used in PostgreSQL bloom filter documentation example, provides about 19.1 bits of entropy, slightly less than the 20 bits of a unique column of the same size. Thus, for the default 80 bits signature again, then bits should be allocated to such a column.

The relevant number of bits for a column is typically between 1 and 3. The resulting search cost, by substitution into the initial cost function and adjusting constant factors, is proportional to:

Now we are a little bit stuck, as the cost depends on the particulars of the indexed columns and of the queries. My advice is to explore starting from the entropy-derived value, and to check how changing the number of bits, especially those from columns less frequently occuring in queries, impacts the index size and search cost.

## Choosing Parameters

Let us consider the 9 random integer columns in the documentation example, with a query on 2 columns. By using the default of 2 bits per column we have and . On SSD, this leads to:

But on HDD, we have a less interesting result as the best size computed is nearly as large as a table row:

On HDD, if the default signature size is used instead, the search with the index is 10 times more expensive, although that might still be better than a full table scan.

Let us consider a more favorable example: if we have 5 random columns, 2 of which are used in queries, then with 3 bits per column on SSD:

And on HDD:

For this setting, using only 2 bits per column increases the signature size (61 on SSD and 134 on HDD vs 56 and 98 above) and the overall cost. However if the query would use 3 columns, the 2 bits per column would probably be better. The best choice is quite sensitive to the expected number of columns used in queries.

## Experiments

Let us create a 1,000,000 row 57 MB table with 5 random integer columns:

The following query takes 110.2 ms:

After creating a 13 MB Bloom index on the five columns:

The same query takes 9.3 ms:

The index provides a 10 to 20 speedup to these queries on my SSD laptop.

## Conclusion

The Bloom index benefit is sensitive to the number of query bits that take advantage of the filter selectivity. If the index selectivity is too low, for instance if only one column is used in the query, or if too few bits are allocated to index columns, the database will probably revert to scanning the whole table anyway.

The Bloom index can be quite effective if you have enough columns to index and you query on significant subsets of them. The main benefit is that one index is shared for searches on different columns, but it only works for a conjunction of equalities.

Finally this index advantage is enhanced on SSD where the random accesses penalty is reduced, which allows for smaller indexes.

*Updates since initial publication:* Add some links; Fix some formula; Better discuss the number of bits per column choice.

A more precise over-approximation of the entropy transfered per bit of signature is to compute the entropy of the number of bits probably turned on when trying all possible column values:

which is close to for a large enough column entropy.