Schedule - PGConf.EU 2023
Counting things at the speed of light with roaring bitmaps
Applications often need to display counts of search results broken down by attributes to make it easier for users to narrow down their search conditions. For example when looking for pink shoes, a system might show how many of sneaker, loafers and wellies are available in pink, as well as how many of each size and how many from each manufacturer. This is known as faceting, and it might be relatively simple for shoes, but can get very slow when dealing with tens of millions of records. In this talk we will take a look at how to implement faceting in PostgreSQL. Starting from simple SQL implementation, and building up to the approach that pgfaceting offers users. On the way we will see how to use parallelism, roaring bitmaps, TOAST tuning, and other performance tricks to make PostgreSQL perform on a level that once needed purpose built tools.