Event language
UI language
Wildcard pattern matching queries are common in many PostgreSQL-backed applications, yet they can be challenging to optimize. This is because BTrees can only handle prefix anchored queries, and pg_trgm is optimized for fuzzy matching. And, when single-character wildcards ( _ ) are involved, pg_trgm struggles to form meaningful trigrams leading to higher false positives. These result in expensive heap rechecks, which in return affect the query latency.<br><br>In this talk, I present <strong>Biscuit</strong>, an <strong>index access method implemented as an open-source PostgreSQL extension</strong> that explores an alternative indexing approach for pattern-matching queries. Biscuit is a lossless, in-memory index that decomposes any wildcard pattern into a set-operation expression on pre-computed character and length-based bitmaps. The evaluation of this expression yields deterministic results of records that match the pattern, thereby avoiding heap rechecks due to false positives.<br><br>We discuss the algorithm behind Biscuit, how it integrates with PostgreSQL's query planner, and also the benchmarks obtained. We shall also go ahead and discuss its practical applicability and limitations. This session aims to share practical insights from building an open-source database extension and to invite discussion around indexing strategies for text-heavy workloads.
