Quick Start Tutorial

Get started with Biscuit in 5 minutes! This tutorial demonstrates creating your first Biscuit index and running optimized pattern matching queries.

Step 1: Create Sample Data

Let’s create a realistic dataset - an e-commerce product catalog:

-- Create products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    sku TEXT NOT NULL,
    category TEXT,
    description TEXT
);

-- Insert sample data
INSERT INTO products (name, sku, category, description)
VALUES
    ('Wireless Mouse Pro', 'MOUSE-001', 'Electronics', 'Ergonomic wireless mouse'),
    ('Gaming Keyboard RGB', 'KEY-GAME-002', 'Electronics', 'Mechanical gaming keyboard'),
    ('USB-C Cable 2m', 'CABLE-USB-003', 'Accessories', 'Fast charging cable'),
    ('Laptop Stand Aluminum', 'STAND-LAP-004', 'Accessories', 'Adjustable laptop stand'),
    ('Webcam HD 1080p', 'CAM-HD-005', 'Electronics', 'High definition webcam'),
    ('Mousepad Extended', 'PAD-MOUSE-006', 'Accessories', 'Large gaming mousepad'),
    ('Wireless Headphones', 'HEAD-WIRE-007', 'Electronics', 'Noise-canceling headphones'),
    ('Phone Charger Fast', 'CHRG-FAST-008', 'Accessories', 'Quick charge adapter'),
    ('Monitor 27 inch 4K', 'MON-27-4K-009', 'Electronics', 'Ultra HD monitor'),
    ('Desk Lamp LED', 'LAMP-LED-010', 'Accessories', 'Adjustable LED desk lamp');

-- Add more realistic data
INSERT INTO products (name, sku, category, description)
SELECT 
    'Product ' || i,
    'SKU-' || LPAD(i::TEXT, 6, '0'),
    CASE (i % 3)
        WHEN 0 THEN 'Electronics'
        WHEN 1 THEN 'Accessories'
        ELSE 'Office Supplies'
    END,
    'Description for product ' || i
FROM generate_series(11, 10000) i;

Step 2: Measure Baseline Performance

First, let’s see how PostgreSQL handles pattern matching without Biscuit:

-- Enable timing
\timing on

-- Test various LIKE patterns
SELECT COUNT(*) FROM products WHERE name LIKE '%Mouse%';
SELECT COUNT(*) FROM products WHERE name LIKE 'Wireless%';
SELECT COUNT(*) FROM products WHERE sku LIKE '%USB%';
SELECT COUNT(*) FROM products WHERE description LIKE '%gaming%';

Check the query plan:

EXPLAIN ANALYZE
SELECT * FROM products WHERE name LIKE '%Mouse%';

Output shows Sequential Scan:

Seq Scan on products  (cost=0.00..180.00 rows=10 width=...)
  Filter: (name ~~ '%Mouse%'::text)
  Rows Removed by Filter: 9990
Planning Time: 0.123 ms
Execution Time: 45.678 ms

Step 3: Create Biscuit Index

Now create a Biscuit index on the name column:

-- Create single-column Biscuit index
CREATE INDEX idx_products_name ON products USING biscuit (name);

Check index size:

SELECT 
    pg_size_pretty(pg_relation_size('idx_products_name')) AS index_size;

Step 4: Run Optimized Queries

Re-run the same queries with the index:

-- Same queries as before
SELECT COUNT(*) FROM products WHERE name LIKE '%Mouse%';
SELECT COUNT(*) FROM products WHERE name LIKE 'Wireless%';
SELECT COUNT(*) FROM products WHERE name LIKE '%Laptop%Stand%';

Check the new query plan:

EXPLAIN ANALYZE
SELECT * FROM products WHERE name LIKE '%Mouse%';

Output shows Index Scan using Biscuit:

Index Scan using idx_products_name on products
  (cost=0.00..8.27 rows=2 width=...)
  Index Cond: (name ~~ '%Mouse%'::text)
Planning Time: 0.089 ms
Execution Time: 1.234 ms 

Step 5: Try Different Pattern Types

Biscuit optimizes different LIKE patterns differently:

Prefix Patterns (Fastest)

-- Matches strings starting with "Wireless"
SELECT * FROM products WHERE name LIKE 'Wireless%';

Suffix Patterns

-- Matches strings ending with "Cable"
SELECT * FROM products WHERE name LIKE '%Cable';

Substring Patterns

-- Matches strings containing "gaming"
SELECT * FROM products WHERE name LIKE '%gaming%';

Complex Patterns

-- Multiple wildcards with concrete characters
SELECT * FROM products WHERE name LIKE '%USB%C%';
SELECT * FROM products WHERE name LIKE 'Wireless%Mouse%';

Underscore Wildcards

-- _ matches exactly one character
SELECT * FROM products WHERE sku LIKE 'MOUSE-00_';
SELECT * FROM products WHERE name LIKE 'W_reless%';

Step 6: Multi-Column Indexes

Create an index on multiple columns for even more powerful queries:

-- Create multi-column Biscuit index
CREATE INDEX idx_products_multi ON products 
USING biscuit (name, sku, category);

Query across all indexed columns:

-- Biscuit automatically optimizes predicate order
SELECT * FROM products 
WHERE name LIKE '%Mouse%'
  AND sku LIKE 'MOUSE%'
  AND category LIKE 'Elect%';

Automatic optimization: Biscuit reorders predicates by selectivity for best performance!

Step 7: Aggregate Queries (Special Optimization)

Biscuit has special optimizations for COUNT(*) and EXISTS:

-- COUNT optimization (no tuple fetching, no sorting)
SELECT COUNT(*) FROM products WHERE name LIKE '%Wireless%';

-- EXISTS optimization
SELECT EXISTS(
    SELECT 1 FROM products WHERE name LIKE '%Gaming%'
);

-- Aggregate with GROUP BY
SELECT category, COUNT(*) 
FROM products 
WHERE name LIKE '%Pro%'
GROUP BY category;

Step 8: Monitor Index Statistics

Check your index health:

-- View detailed statistics
SELECT biscuit_index_stats('idx_products_name'::regclass);

Output:

Biscuit Index Statistics (FULLY OPTIMIZED)
==========================================
Index: idx_products_name
Active records: 10000
Total slots: 10000
Free slots: 0
Tombstones: 0
Max length: 45
------------------------
CRUD Statistics:
  Inserts: 10000
  Updates: 0
  Deletes: 0
------------------------
Active Optimizations:
  ✓ 1. Skip wildcard intersections
  ✓ 2. Early termination on empty
  ✓ 3. Avoid redundant copies
  ✓ 4. Optimized single-part patterns
  ✓ 5. Skip unnecessary length ops
  ✓ 6. TID sorting for sequential I/O
  ✓ 7. Batch TID insertion
  ✓ 8. Direct bitmap iteration
  ✓ 9. Parallel bitmap scan support
  ✓ 10. Batch cleanup on threshold
  ✓ 11. Skip sorting for bitmap scans
  ✓ 12. LIMIT-aware TID collection

Step 9: Test CRUD Operations

Biscuit supports full CRUD with automatic index updates:

INSERT

INSERT INTO products (name, sku, category, description)
VALUES ('New Wireless Mouse', 'MOUSE-999', 'Electronics', 'Latest model');

-- Index automatically updated
SELECT * FROM products WHERE name LIKE '%New Wireless%';

UPDATE

UPDATE products 
SET name = 'Premium Wireless Mouse'
WHERE sku = 'MOUSE-999';

-- Index reflects changes immediately
SELECT * FROM products WHERE name LIKE '%Premium%';

DELETE

DELETE FROM products WHERE sku = 'MOUSE-999';

-- Tombstone tracking for efficient cleanup
SELECT biscuit_index_stats('idx_products_name'::regclass);

Best Practices

✅ DO:

Use Biscuit for frequent LIKE queries
Create multi-column indexes for complex filters
Monitor index statistics regularly
Use for high-cardinality string columns

❌ DON’T:

Index very long text (>256 chars truncated)
Use for full-text search (use tsvector)
Create redundant indexes
Insert into an already-indexed table during a bulk load — build the index afterwards

Next Steps

🎉 Congratulations! You’ve created your first Biscuit index!

Continue learning:

Pattern Syntax Guide - Master all pattern types
Multi-Column Indexes - Advanced techniques
Performance Tuning - Optimize further
Architecture Deep Dive - How it works

Try advanced features:

-- Type conversion support (integers, dates, timestamps)
CREATE INDEX idx_created ON orders USING biscuit (created_at);
SELECT * FROM orders WHERE created_at::TEXT LIKE '2024-01%';

-- Complex multi-column queries
CREATE INDEX idx_users_all ON users USING biscuit (email, username, department);
SELECT * FROM users 
WHERE email LIKE '%@company.com'
  AND username LIKE 'admin%'
  AND department LIKE '%eng%';

Common Questions

Q: Can I use Biscuit for case-insensitive matching?

A: Yes. The default biscuit_ops operator class builds case-insensitive structures alongside the case-sensitive ones, so ILIKE is served by the same index. For anchored patterns it performs comparably to LIKE. If a column is only ever queried one way, biscuit_like_ops or biscuit_ilike_ops avoids building the unused structure set.

Q: Does the index survive a restart or a crash?

A: Yes. Index state is WAL-logged and stored in the index relation’s own pages, so it is recovered by PostgreSQL’s ordinary crash recovery and replicates to standbys. Each backend still loads its own copy into session-local memory on first use, so the first query in a new connection pays a load cost.

Need help? Check the FAQ or open an issue on GitHub.