BISCUIT
Contents:
Installation Guide
Prerequisites
Check PostgreSQL Version
Installation Methods
Method 1: From Source (Recommended)
Step 1: Install Build Dependencies
Step 2: Clone Repository
Step 3: Build and Install
Step 4: Verify Installation
Method 2: With CRoaring Support (Enhanced Performance)
Install CRoaring Library
Build Biscuit with Roaring
Enable Extension in Database
Post-Installation Configuration
Set Appropriate Memory Limits
Enable Query Logging (Optional)
Verify Installation with Test
Troubleshooting
Error: “could not load library”
Error: “extension does not exist”
Build Errors with Roaring
Uninstallation
Next Steps
Platform-Specific Notes
Windows
Docker
Getting Help
Quick Start Tutorial
Step 1: Create Sample Data
Step 2: Measure Baseline Performance
Step 3: Create Biscuit Index
Step 4: Run Optimized Queries
Step 5: Try Different Pattern Types
Prefix Patterns (Fastest)
Suffix Patterns
Substring Patterns
Complex Patterns
Underscore Wildcards
Step 6: Multi-Column Indexes
Step 7: Aggregate Queries (Special Optimization)
Step 8: Monitor Index Statistics
Step 9: Test CRUD Operations
INSERT
UPDATE
DELETE
Best Practices
✅ DO:
❌ DON’T:
Next Steps
Common Questions
API Reference
Extension Management
CREATE EXTENSION
DROP EXTENSION
Index Operations
CREATE INDEX
DROP INDEX
REINDEX
Query Operators
LIKE Operator
NOT LIKE Operator
ILIKE Operator
NOT ILIKE Operator
Multi-Column Queries
Build Diagnostic Functions
biscuit_has_roaring()
biscuit_version()
biscuit_roaring_version()
biscuit_build_info()
biscuit_build_info_json()
biscuit_check_config()
biscuit_index_stats()
biscuit_index_memory_size()
biscuit_size_pretty()
Diagnostic Views
biscuit_status
biscuit_memory_usage
Notes
Configuration Parameters
Server Parameters
shared_buffers
work_mem
maintenance_work_mem
effective_cache_size
enable_seqscan
max_parallel_workers_per_gather
Session Parameters
System Views
pg_index
pg_stat_user_indexes
pg_indexes
Error Messages
ERROR: access method “biscuit” does not exist
ERROR: data type X is not supported for biscuit index
ERROR: could not open relation with OID
WARNING: Biscuit: Index cache miss
ISSUE: biscuit_has_roaring() returns false
Next Steps
Biscuit Index Architecture
Overview
Core Design Principles
1.
UTF-8 Character-Level Position-Based Indexing
2.
Dual Indexing (Forward + Reverse)
3.
Roaring Bitmap Compression
4.
Dual Case Sensitivity Architecture
Index Structure
Main Index Object
Character Index Structure
Length Bitmaps (Dual Architecture)
UTF-8 Character Handling
Character Counting
Multi-Byte Character Indexing
Query Processing
1.
UTF-8-Aware Pattern Analysis
2.
Query Optimization (Multi-Column)
3.
Fast Paths (UTF-8-Aware)
4.
Windowed Matching (Complex Patterns)
ILIKE Implementation
Architecture
Case Folding
Data Flow
Performance Optimizations
1.
Skip Wildcard Intersections
2.
Early Termination
3.
Character vs. Byte Length Separation
4.
TID Sorting for Sequential I/O
5.
Skip Sorting for Aggregates
6.
Parallel TID Collection
7.
LIMIT-Aware Collection
Multi-Column Support
Architecture
Query Execution
Strategy Routing
CRUD Operations
Insert
Delete (Lazy + Cleanup)
Update
Memory Management
Cache Strategy
Size Calculation
Cleanup
Disk Persistence
Metadata Only
Rebuild Strategy
Limitations & Tradeoffs
What Biscuit Does Well
What Biscuit Doesn’t Do
Code Navigation
Critical UTF-8 Functions
Index Build
Query Processing
CRUD
Diagnostics
Pattern Syntax Guide
Wildcard Characters
Pattern Types
1. Exact Match (Fastest)
2. Prefix Match (Very Fast)
3. Suffix Match (Very Fast)
4. Substring Match (Moderate Speed)
5. Infix Match (Fast)
6. Complex Multi-Part Patterns
7. Underscore Patterns (Position-Specific)
Pure Wildcard Patterns (Fastest Special Cases)
Empty Pattern
Single Percent
Pure Underscores
Mixed Pure Wildcards
Pattern Optimization Strategies
Strategy 1: Maximize Concrete Characters
Strategy 2: Use Strong Anchors
Strategy 3: Minimize Partitions
Strategy 4: Combine with Other Indexes
Pattern Examples by Use Case
Email Filtering
SKU/Product Code Matching
Log Searching
URL/Path Matching
Case Sensitivity
Pattern Performance Hierarchy
Next Steps
Multi-Column Indexes
Overview
Creating Multi-Column Indexes
Basic Syntax
Example: E-Commerce Product Search
How Query Optimization Works
Automatic Reordering Example
Query Planning Deep Dive
Priority Calculation
Selectivity Scoring
Viewing Query Plans
Best Practices
1. Order Columns by Query Frequency
2. Mix Selective and General Columns
3. Consider Column Cardinality
4. Avoid Redundant Indexes
Advanced Patterns
Pattern 1: User Search with Email Domain
Pattern 2: Product Search with Multiple Attributes
Pattern 3: Log Analysis
Performance Characteristics
Query Complexity vs. Performance
Early Termination Example
LIMIT Optimization
Monitoring and Diagnostics
Check Index Statistics
Query Performance Analysis
Common Pitfalls
Pitfall 1: Too Many Columns
Pitfall 2: Low Selectivity Patterns
Pitfall 3: Not Using EXPLAIN
Migration from Single-Column
Next Steps
Biscuit Performance Benchmark — Fallback Bitmaps
Executive Summary
Key Findings
Summary:
Introduction
Problem Statement
Research Questions
Benchmark Scope
Methodology
Design Principles
1. Complete Isolation
2. Cache State Control
3. Forced Index Usage
4. Statistical Rigor
5. Randomization
6. Comprehensive Metrics
Test Environment
Dataset
Hardware Configuration
Software Stack
Database Configuration
Dataset Characteristics
Query Coverage Analysis
Overview
Coverage by Pattern Structure
1. Basic Wildcard Patterns (24 queries)
2. Underscore Wildcards (12 queries)
3. Case-Insensitive Patterns (ILIKE) (20 queries)
4. Negation Patterns (NOT LIKE / NOT ILIKE) (16 queries)
5. Boolean Combinations (48 queries)
6. Edge Cases and Special Patterns (20 queries)
7. Real-World Query Patterns (20 queries)
8. Selectivity Spectrum (10 queries)
9. Special Characters & Escaping (4 queries)
10. ORDER BY + LIMIT (Pagination) (4 queries)
Coverage Summary Table
Selectivity Distribution
Performance Results
Overall Performance Summary (Warm Cache)
Cold Cache vs. Warm Cache
Cache Hit Ratios
Statistical Significance Testing
Statistical Analysis
Distribution Analysis
Execution Time Distributions
Consistency Analysis (Coefficient of Variation)
Statistical Analysis
Consistency Analysis (Coefficient of Variation)
Outlier Analysis
Pattern-Specific Performance
By Wildcard Pattern Type
By Selectivity Level
By Boolean Complexity
Correctness Verification
Dual-Level Verification Protocol
Level 1: Cross-Index Consistency
Level 2: Cross-Iteration Consistency
Overall Verification Summary
Correctness Implications
Index Usage Analysis
Query Execution Strategy Breakdown
Execution Plan Analysis
Biscuit: Dominant Index Scan Usage
Trigram: Bitmap-Heavy Approach
B-tree: Sequential Scan Dominant
Buffer I/O Analysis
Real-World Scenarios
Scenario 1: User Search / Autocomplete
Scenario 2: Geographic Filtering
Scenario 3: Content Moderation
Scenario 4: Analytics Dashboard
Scenario 5: Pagination
Trade-off Analysis
Performance vs. Storage
Decision Matrix
Total Cost of Ownership (5-Year Estimate)
Limitations and Future Work
Current Limitations
1. Write Performance Not Tested
2. Single Hardware Configuration
3. Forced Index Usage
4. Single Dataset Size
5. No Concurrency Testing
Threats to Validity
Internal Validity
External Validity
Recommendations for Practitioners
Conclusions
Summary of Findings
Practical Recommendations
Research Contributions
Final Verdict
Statistical Methods
Biscuit Performance Benchmark — Roaring Bitmaps
Executive Summary
Key Findings
Introduction
Problem Statement
Research Questions
Benchmark Scope
Methodology
Design Principles
1. Complete Isolation
2. Cache State Control
3. Forced Index Usage
4. Statistical Rigor
5. Randomization
6. Comprehensive Metrics
Test Environment
Dataset
Hardware Configuration
Software Stack
Database Configuration
Dataset Characteristics
Query Coverage Analysis
Overview
Coverage by Pattern Structure
1. Basic Wildcard Patterns (24 queries)
2. Underscore Wildcards (12 queries)
3. Case-Insensitive Patterns (ILIKE) (20 queries)
4. Negation Patterns (NOT LIKE / NOT ILIKE) (16 queries)
5. Boolean Combinations (48 queries)
6. Edge Cases and Special Patterns (20 queries)
7. Real-World Query Patterns (20 queries)
8. Selectivity Spectrum (10 queries)
9. Special Characters & Escaping (4 queries)
10. ORDER BY + LIMIT (Pagination) (4 queries)
Coverage Summary Table
Selectivity Distribution
Performance Results
Overall Performance Summary (Warm Cache)
Cold Cache vs. Warm Cache
Cache Hit Ratios
Statistical Significance Testing
Statistical Analysis
Distribution Analysis
Execution Time Distributions
Consistency Analysis (Coefficient of Variation)
Outlier Analysis
Pattern-Specific Performance
By Wildcard Pattern Type (Warm Cache)
By Selectivity Level (Estimated from available data)
Correctness Verification
Dual-Level Verification Protocol
Level 1: Cross-Index Consistency
Level 2: Cross-Iteration Consistency
Overall Verification Summary
Correctness Implications
Index Usage Analysis
Query Execution Strategy Breakdown
Execution Plan Analysis
Biscuit: Dominant Index Scan Usage
Trigram: Bitmap-Heavy Approach
B-tree: Sequential Scan Dominant
Buffer I/O Analysis
Roaring Bitmap Optimization Impact
Storage Comparison
Performance Impact Analysis
Roaring Bitmap Benefits Summary
Why Roaring Bitmaps Work Well for Biscuit
Real-World Scenarios
Scenario 1: User Search / Autocomplete
Scenario 2: Geographic Filtering
Scenario 3: Content Moderation
Scenario 4: Analytics Dashboard
Trade-off Analysis
Performance vs. Storage
Decision Matrix
Total Cost of Ownership (5-Year Estimate)
Limitations and Future Work
Current Limitations
1. Write Performance Not Tested
2. Single Hardware Configuration
3. Forced Index Usage
4. Single Dataset Size
5. No Concurrency Testing
Recommendations for Practitioners
Conclusions
Summary of Findings
Practical Recommendations
Research Contributions
Final Verdict
Statistical Methods
Appendix: Verification Checklist
Publication Readiness Assessment
Data Quality Summary
Benchmark Environment
System Overview
Benchmark Configuration
Benchmark Methodology
Reproducibility Instructions
Notes
Performance Tuning Guide
Quick Performance Checklist
Understanding Biscuit Performance
Active Optimizations
PostgreSQL Configuration
Memory Settings
Planner Settings
Index Design Optimization
Choose Selective Columns
Multi-Column Order Strategy
Avoid Over-Indexing
Query Optimization
Pattern Design
Maximize Concrete Characters
Use Strong Anchors
Minimize Pattern Complexity
Combine with Other Filters
Aggregate Query Optimization
LIMIT Query Optimization
Maintenance and Monitoring
Regular Statistics Updates
Monitor Index Health
Cleanup Tombstones
Index Rebuild Strategy
Hardware Considerations
Memory Requirements
CPU Considerations
Benchmarking Your Setup
Create Benchmark Suite
Troubleshooting Performance Issues
Issue 1: Index Not Being Used
Issue 2: Slow Query Despite Index
Issue 3: High Memory Usage
Issue 4: Slow Index Builds
Advanced Tuning
Custom Cost Parameters
Parallel Query Configuration
Performance Monitoring Dashboard
Next Steps
Tribute to Trigrams and Trees
Acknowledging PostgreSQL’s Pattern Matching Heritage
pg_trgm: The Swiss Army Knife of Text Search
What pg_trgm Does Brilliantly
1.
Fuzzy Matching & Similarity Search
2.
Full-Text Search Integration
3.
Regular Expression Support
4.
Persistent Storage
When to Use pg_trgm Instead of Biscuit
B-tree: The Foundation of Database Indexing
What B-tree Does Brilliantly
1.
Exact Equality & Range Queries
2.
Sorted Data Access
3.
Space Efficiency
4.
Universal Compatibility
5.
Lock-Free Concurrent Access
When to Use B-tree Instead of Biscuit
The Complementary Index Strategy
Honest Trade-off Summary
Biscuit’s Strengths
Biscuit’s Weaknesses
When NOT to Use Biscuit
Acknowledgments
Recommendation Matrix
Summary
Frequently Asked Questions
General Questions
What is Biscuit?
When should I use Biscuit?
Is Biscuit production-ready?
How much memory does Biscuit use?
Installation & Setup
How do I install Biscuit?
Installation fails with “could not load library”
Extension creation fails: “extension does not exist”
Should I install with CRoaring support?
Index Creation
How do I create a Biscuit index?
How long does index creation take?
Can I create partial Biscuit indexes?
How many columns can I index?
Query Performance
Why is my query not using the index?
How can I measure query performance?
Does Biscuit support case-insensitive search?
Can I use OR conditions with Biscuit?
Does Biscuit support regular expressions?
Does Biscuit optimize COUNT(*) queries?
Can I use LIMIT with Biscuit?
Index Maintenance
Do I need to manually maintain the index?
What are tombstones?
When should I rebuild the index?
How do I monitor index health?
Does Biscuit support parallel operations?
Troubleshooting
Queries are slower than expected
Index build fails with out of memory
Crashes or unexpected restarts
“Index is not valid” error
Pattern not matching expected rows
Advanced Topics
Can I use Biscuit with partitioned tables?
Does Biscuit work with replication?
Can I use Biscuit in read replicas?
How does Biscuit handle NULL values?
Does Biscuit support multi-byte characters (Unicode)?
Migration & Compatibility
How do I migrate from GIN trigram?
Can I have both B-tree and Biscuit on same column?
How do I uninstall Biscuit?
Getting Help
Where can I get support?
How do I report a bug?
How can I contribute?
Is there a Slack/Discord community?
Roadmap
What features are being considered?
Performance Comparisons
How does Biscuit compare to other indexes?
Still Have Questions?
BISCUIT
Index
Index