BISCUIT Logo

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
  • Search

© Copyright 2025.

Built with Sphinx using a theme provided by Read the Docs.