Biscuit is a specialized PostgreSQL index for fast pattern matching LIKE queries
5 days ago
- #PostgreSQL
- #Pattern Matching
- #Database Optimization
- Biscuit is a PostgreSQL index access method optimized for fast LIKE and ILIKE queries with native multi-column support.
- It eliminates recheck overhead found in trigram indexes, improving performance on wildcard-heavy queries.
- Biscuit stands for Bitmap Indexed Searching with Comprehensive Union and Intersection Techniques.
- Added SQL functions for inspecting build-time configuration and CRoaring bitmap support.
- Includes diagnostic views for quick status checks on extension version, CRoaring enablement, and index statistics.
- Supports automatic conversion of various data types (text, numeric, date/time, boolean) to searchable text.
- Uses character-position bitmaps for efficient pattern matching, with optimizations for common query patterns.
- Features include early termination, batch operations, and direct bitmap iteration to minimize overhead.
- Automatically analyzes and reorders query predicates by selectivity for optimal execution.
- Benchmarks show significant performance improvements over pg_trgm, especially for multi-column and aggregate queries.
- Recommended for wildcard-heavy LIKE/ILIKE queries, multi-column searches, and exact result requirements.
- Limitations include no support for regular expressions or locale-specific collations.
- Open-source under MIT License, with contributions welcome for features like parallel index build and more data type support.