API Reference

Complete reference for Biscuit index SQL API, functions, and operators.

---

Extension Management

CREATE EXTENSION

Creates the Biscuit extension in the current database.

Syntax:

CREATE EXTENSION biscuit [ WITH ] [ SCHEMA schema_name ] [ VERSION version ];

Parameters:

• schema_name - Schema to install extension (default: current schema)

• version - Specific version to install (default: latest)

Example:

-- Install in public schema
CREATE EXTENSION biscuit;

-- Install in specific schema
CREATE EXTENSION biscuit SCHEMA extensions;

-- Install specific version
CREATE EXTENSION biscuit VERSION '1.0';

Returns: Nothing on success

Errors:

• ERROR: extension "biscuit" already exists - Extension already installed

• ERROR: could not open extension control file - Installation files missing

---

DROP EXTENSION

Removes the Biscuit extension from the database.

Syntax:

DROP EXTENSION [ IF EXISTS ] biscuit [ CASCADE | RESTRICT ];

Parameters:

• IF EXISTS - Don’t error if extension doesn’t exist

• CASCADE - Drop dependent objects (including indexes)

• RESTRICT - Refuse if dependent objects exist (default)

Example:

-- Drop extension (fails if indexes exist)
DROP EXTENSION biscuit;

-- Drop extension and all Biscuit indexes
DROP EXTENSION biscuit CASCADE;

-- Drop only if exists
DROP EXTENSION IF EXISTS biscuit CASCADE;

Side Effects:

• All Biscuit indexes are dropped (with CASCADE)

• Index cache is cleared

• Memory is freed

---

Index Operations

CREATE INDEX

Creates a Biscuit index on one or more columns.

Syntax:

CREATE INDEX [ CONCURRENTLY ] [ IF NOT EXISTS ] index_name
    ON table_name
    USING biscuit ( column_name [, ...] )
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ WHERE predicate ];

Parameters:

• index_name - Name of the index to create

• table_name - Table to index

• column_name - Column(s) to index (1 or more)

• CONCURRENTLY - Build without blocking writes

• IF NOT EXISTS - Don’t error if index exists

• UNIQUE - Not supported (will error)

• WHERE predicate - Create partial index

Supported Column Types:

• TEXT, VARCHAR, CHAR - Direct indexing

Examples:

-- Single column index
CREATE INDEX idx_products_name 
ON products USING biscuit (name);

-- Multi-column index
CREATE INDEX idx_products_search 
ON products USING biscuit (name, sku, category);

-- Concurrent build (non-blocking)
CREATE INDEX CONCURRENTLY idx_products_name 
ON products USING biscuit (name);

-- Partial index (filtered)
CREATE INDEX idx_active_products 
ON products USING biscuit (name)
WHERE status = 'active';

---

DROP INDEX

Removes a Biscuit index.

Syntax:

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] index_name [, ...] [ CASCADE | RESTRICT ];

Example:

-- Drop index
DROP INDEX idx_products_name;

-- Drop without blocking
DROP INDEX CONCURRENTLY idx_products_name;

-- Drop multiple indexes
DROP INDEX idx_products_name, idx_products_sku;

---

REINDEX

Rebuilds a Biscuit index.

Syntax:

REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;

Example:

-- Rebuild single index
REINDEX INDEX idx_products_name;

-- Rebuild all indexes on table
REINDEX TABLE products;

-- Rebuild concurrently (PG14+)
REINDEX INDEX CONCURRENTLY idx_products_name;

When to Reindex:

• After massive data changes (>50% rows)

• Tombstone count exceeds 5000

• Performance degradation detected

• After pg_upgrade

---

Query Operators

LIKE Operator

Pattern matching operator supported by Biscuit indexes.

Syntax:

column_name LIKE pattern [ ESCAPE escape_character ]

Pattern Syntax:

• % - Matches zero or more characters

• _ - Matches exactly one character

• Other characters match themselves

• Case-sensitive matching

Examples:

-- Prefix match
SELECT * FROM products WHERE name LIKE 'Wireless%';

-- Suffix match
SELECT * FROM products WHERE name LIKE '%Mouse';

-- Substring match
SELECT * FROM products WHERE name LIKE '%gaming%';

-- Underscore wildcard
SELECT * FROM products WHERE sku LIKE 'PROD-____-2024';

-- Exact match
SELECT * FROM products WHERE name LIKE 'Wireless Mouse';

-- Complex pattern
SELECT * FROM products WHERE name LIKE 'Wireless%RGB%Gaming%';

-- With ESCAPE character
SELECT * FROM products WHERE name LIKE 'Price: 100\%' ESCAPE '\';

Performance: See Pattern Syntax Guide for optimization details

---

NOT LIKE Operator

Negated pattern matching.

Syntax:

column_name NOT LIKE pattern

Example:

-- Find products NOT containing "test"
SELECT * FROM products WHERE name NOT LIKE '%test%';

---

ILIKE Operator

Case-insensitive pattern matching is supported from versions >= 2.1.0

Workaround:

-- Create index
CREATE INDEX idx_products
ON products USING biscuit (name);

-- Query 
SELECT * FROM products WHERE name ILIKE '%wireless%';

---

NOT ILIKE Operator

Case-insensitive pattern matching is supported from versions >= 2.1.0

Workaround:

-- Create index
CREATE INDEX idx_products
ON products USING biscuit (name);

-- Query 
SELECT * FROM products WHERE name NOT ILIKE '%wireless%';

---

Multi-Column Queries

Query multiple columns with automatic optimization.

Syntax:

WHERE column1 LIKE pattern1
  AND column2 LIKE pattern2
  AND column3 LIKE pattern3;

Example:

-- Automatic predicate reordering
SELECT * FROM products 
WHERE name LIKE '%laptop%'          -- Priority: 50 (substring)
  AND brand LIKE 'Dell%'            -- Priority: 20 (prefix)
  AND category LIKE 'Computer%';    -- Priority: 21 (prefix)

-- Execution order: brand → category → name

Performance: Biscuit automatically reorders predicates by selectivity for optimal performance.

---

Build Diagnostic Functions

biscuit_has_roaring()

Checks if the extension was compiled with CRoaring bitmap support.

Syntax:

biscuit_has_roaring() RETURNS boolean

Parameters: None

Returns: true if compiled with Roaring support, false otherwise

Example:

-- Check Roaring support
SELECT biscuit_has_roaring();

-- Conditional query
SELECT 
    CASE 
        WHEN biscuit_has_roaring() THEN 'Optimal performance'
        ELSE 'Using fallback implementation'
    END as performance_status;

Performance Impact:

• true: High-performance CRoaring bitmaps

• false: Fallback implementation with high memory footprint

When to Rebuild: If this returns false, install CRoaring and rebuild:

# Debian/Ubuntu
sudo apt-get install libroaring-dev
make clean && make && sudo make install

# macOS
brew install croaring
make clean && make && sudo make install

---

biscuit_version()

Returns the Biscuit extension version string.

Syntax:

biscuit_version() RETURNS text

Parameters: None

Returns: Version string (e.g., "1.0.0")

Example:

-- Get version
SELECT biscuit_version();

-- Check minimum version
SELECT biscuit_version() >= '1.0.0' as meets_requirement;

---

biscuit_roaring_version()

Returns the CRoaring library version if available.

Syntax:

biscuit_roaring_version() RETURNS text

Parameters: None

Returns:

• Roaring version string (e.g., "2.0.4") if compiled with Roaring

• NULL if not compiled with Roaring support

Example:

-- Get Roaring version
SELECT biscuit_roaring_version();

-- Check if Roaring is available
SELECT biscuit_roaring_version() IS NOT NULL as has_roaring;

-- Full version report
SELECT 
    biscuit_version() as extension_version,
    biscuit_roaring_version() as roaring_version,
    CASE 
        WHEN biscuit_roaring_version() IS NOT NULL THEN 'Optimal'
        ELSE 'Fallback'
    END as performance_mode;

Output:

 extension_version | roaring_version | performance_mode
-------------------+-----------------+------------------
 2.1.5            | 2.0.4           | Optimal

---

biscuit_build_info()

Returns detailed build-time configuration information.

Syntax:

biscuit_build_info() RETURNS TABLE (
    feature text,
    enabled boolean,
    description text
)

Parameters: None

Returns: Table with build configuration details

Columns:

• feature - Feature or library name

• enabled - Whether the feature is enabled

• description - Detailed description

Example:

-- Get all build information
SELECT * FROM biscuit_build_info();

-- Check specific features
SELECT feature, enabled 
FROM biscuit_build_info() 
WHERE feature = 'CRoaring Bitmaps';

-- Filter enabled features only
SELECT feature, description 
FROM biscuit_build_info() 
WHERE enabled = true;

Output:

      feature       | enabled |              description
--------------------+---------+---------------------------------------
 CRoaring Bitmaps   | t       | High-performance bitmap operations...
 PostgreSQL         | t       | Compiled for PostgreSQL 16.1

---

biscuit_build_info_json()

Returns build configuration as a JSON string for automation and scripting.

Syntax:

biscuit_build_info_json() RETURNS text

Parameters: None

Returns: JSON-formatted string with build information

Example:

-- Get JSON build info
SELECT biscuit_build_info_json();

-- Parse as JSON
SELECT biscuit_build_info_json()::json;

-- Extract specific fields
SELECT 
    (biscuit_build_info_json()::json)->>'version' as version,
    (biscuit_build_info_json()::json)->>'roaring_enabled' as roaring,
    (biscuit_build_info_json()::json)->>'postgres_version' as pg_version;

Output Format:

{
  "version": "1.0.0",
  "roaring_enabled": true,
  "roaring_version": "2.0.4",
  "postgres_version": "16.1",
  "build_date": "Dec 16 2025 10:30:00"
}

Use Cases:

• CI/CD validation scripts

• Automated deployment checks

• Monitoring systems

• Version compatibility verification

Shell Script Example:

#!/bin/bash
# Check if Roaring is enabled
ROARING=$(psql -t -c "SELECT (biscuit_build_info_json()::json)->>'roaring_enabled';")

if [ "$ROARING" != "true" ]; then
    echo "Warning: Roaring not enabled. Installing CRoaring..."
    sudo apt-get install libroaring-dev
    make clean && make && sudo make install
fi

---

biscuit_check_config()

Performs a comprehensive configuration health check and provides recommendations.

Syntax:

biscuit_check_config() RETURNS TABLE (
    check_name text,
    status text,
    recommendation text
)

Parameters: None

Returns: Table with configuration checks and recommendations

Columns:

• check_name - Name of the configuration check

• status - Current status with visual indicators

• recommendation - Action recommendation

Example:

-- Run full configuration check
SELECT * FROM biscuit_check_config();

-- Check for issues
SELECT * FROM biscuit_check_config() 
WHERE status NOT LIKE '✓%';

-- Save check results
CREATE TABLE biscuit_health_log AS
SELECT now() as checked_at, * 
FROM biscuit_check_config();

Output:

   check_name     |      status       |                recommendation
------------------+-------------------+----------------------------------------------
 Roaring Support  | ✓ Enabled         | Optimal configuration (v2.0.4)
 Extension Ver... | 1.0.0             | Current version
 Active Indexes   | 5                 | Indexes are active

biscuit_index_stats()

Returns detailed statistics about a Biscuit index.

Syntax:

biscuit_index_stats(index_oid regclass) RETURNS text

Parameters:

• index_oid - OID or name of the Biscuit index

Returns: Multi-line text report with statistics

Example:

-- Get statistics for an index
SELECT biscuit_index_stats('idx_products_name'::regclass);

-- With formatted output
\x
SELECT biscuit_index_stats('idx_products_name'::regclass);
\x

Output Format:

Biscuit Index Statistics (FULLY OPTIMIZED)
==========================================
Index: idx_products_name
Active records: 50000
Total slots: 50245
Free slots: 245
Tombstones: 0
Max length: 128
------------------------
CRUD Statistics:
  Inserts: 50245
  Updates: 1205
  Deletes: 245
------------------------
Active Optimizations:
  ✓ 1. Skip wildcard intersections
  ✓ 2. Early termination on empty
  [... 12 optimizations listed ...]

Metrics Explained:

Metric	Description	Ideal Value
Active records	Current valid records	= table row count
Total slots	Allocated memory slots	≥ active records
Free slots	Reusable slots from deletes	< 1000
Tombstones	Deleted but not cleaned	< 1000
Inserts	Total insert operations	Monotonic increase
Updates	Total update operations	-
Deletes	Total delete operations	-

---

biscuit_index_memory_size()

Returns the in-memory footprint of a Biscuit index in bytes.

Syntax:

biscuit_index_memory_size(index_oid oid) RETURNS bigint
biscuit_index_memory_size(index_name text) RETURNS bigint

Parameters:

• index_oid — OID of the Biscuit index

• index_name — Name of the Biscuit index

Returns: Memory usage in bytes for the specified Biscuit index.

Example:

-- Get memory usage using index OID
SELECT biscuit_index_memory_size('idx_products_name'::regclass::oid);

-- Get memory usage using index name
SELECT biscuit_index_memory_size('idx_products_name');

---

biscuit_size_pretty()

Returns a human-readable representation of the in-memory footprint of a Biscuit index.

Syntax:

biscuit_size_pretty(index_name text) RETURNS text

Parameters:

• index_name — Name of the Biscuit index

Returns: Formatted memory usage (bytes, KB, MB, or GB).

Example:

SELECT biscuit_size_pretty('idx_products_name');

Output Format:

128 MB (134217728 bytes)

---

Diagnostic Views

biscuit_status

Quick overview of extension status and configuration.

Definition:

biscuit_status

Columns:

• version - Extension version

• roaring_enabled - Whether CRoaring is enabled

• bitmap_implementation - Current bitmap backend

• total_indexes - Number of active Biscuit indexes

• total_index_size - Combined size of all indexes

Example:

-- View current status
SELECT * FROM biscuit_status;

-- Monitor status changes
CREATE TABLE biscuit_status_history AS
SELECT now() as timestamp, * FROM biscuit_status;

Output:

 version | roaring_enabled | bitmap_implementation  | total_indexes | total_index_size
---------+-----------------+------------------------+---------------+------------------
 1.0.0   | t               | Optimal (CRoaring)     | 5             | 245 MB

---

biscuit_memory_usage

Displays memory and disk usage for all Biscuit indexes in the current database.

Definition:

biscuit_memory_usage

Columns:

• schemaname — Schema containing the index

• tablename — Table on which the index is defined

• indexname — Name of the Biscuit index

• bytes — In-memory size (bytes)

• human_readable — Formatted memory usage

• disk_size — On-disk size (pg_relation_size)

Example:

-- List all Biscuit indexes with memory usage
SELECT * FROM biscuit_memory_usage;

Usage:

-- Identify largest Biscuit indexes in memory
SELECT
    indexname,
    human_readable
FROM biscuit_memory_usage
ORDER BY bytes DESC
LIMIT 5;

Notes

• pg_relation_size() and pg_size_pretty() report only the on-disk footprint of a Biscuit index.

• Biscuit maintains its primary data structures in memory for performance; disk size may significantly underrepresent total runtime usage.

• Reported values reflect the current in-memory state and may change over time.

---

Configuration Parameters

Server Parameters

These PostgreSQL parameters affect Biscuit performance:

shared_buffers

Description: Memory for caching index data

Syntax:

-- In postgresql.conf
shared_buffers = '4GB'

-- Or per session
SET shared_buffers = '4GB';  -- Requires restart

Recommendation: 25% of system RAM

---

work_mem

Description: Memory for sorting and bitmap operations

Syntax:

-- In postgresql.conf
work_mem = '256MB'

-- Or per session
SET work_mem = '256MB';

Recommendation:

• Small queries: 64MB

• Medium queries: 256MB

• Large queries: 512MB-1GB

---

maintenance_work_mem

Description: Memory for index building

Syntax:

-- In postgresql.conf
maintenance_work_mem = '1GB'

-- Or for current session
SET maintenance_work_mem = '1GB';

---

effective_cache_size

Description: Hint to planner about available cache

Syntax:

-- In postgresql.conf
effective_cache_size = '12GB'

---

enable_seqscan

Description: Allow/disallow sequential scans

Syntax:

-- Disable sequential scans (testing only!)
SET enable_seqscan = off;

-- Re-enable
SET enable_seqscan = on;

Use Case: Force index usage during testing

---

max_parallel_workers_per_gather

Description: Workers for parallel bitmap scans

Syntax:

-- In postgresql.conf
max_parallel_workers_per_gather = 4

-- Or per session
SET max_parallel_workers_per_gather = 4;

Recommendation: 2-4 for most workloads

---

Session Parameters

Parameters you can set per connection:

-- Increase work memory for this session
SET work_mem = '512MB';

-- Enable query timing
\timing on

-- Show query plans
SET client_min_messages = INFO;

-- Force index usage (testing)
SET enable_seqscan = off;

-- Enable parallel queries
SET max_parallel_workers_per_gather = 4;

-- Reset all to defaults
RESET ALL;

---

System Views

pg_index

Check if an index is a Biscuit index:

SELECT 
    indexrelid::regclass as index_name,
    indrelid::regclass as table_name,
    indnatts as num_columns
FROM pg_index
WHERE indexrelid::regclass::text LIKE '%biscuit%';

---

pg_stat_user_indexes

Monitor index usage:

SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%biscuit%';

---

pg_indexes

View index definitions:

SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE indexdef LIKE '%biscuit%';

---

Error Messages

Common errors and solutions:

ERROR: access method “biscuit” does not exist

Cause: Extension not installed

Solution:

CREATE EXTENSION biscuit;

---

ERROR: data type X is not supported for biscuit index

Cause: Attempting to index unsupported type

Solution: Cast to TEXT or use expression index

CREATE INDEX idx ON table1 USING biscuit (column::TEXT);

---

ERROR: could not open relation with OID

Cause: Index cache corruption after crash

Solution:

REINDEX INDEX idx_name;

---

WARNING: Biscuit: Index cache miss

Cause: Normal - index loaded on first use

Solution: No action needed (informational only)

---

ISSUE: biscuit_has_roaring() returns false

Symptoms:

SELECT biscuit_has_roaring();
-- Returns: false

Diagnosis:

SELECT * FROM biscuit_check_config();
-- Shows: Roaring Support | ✗ Disabled | Install CRoaring...

Solution:

# Install CRoaring development library
# Debian/Ubuntu
sudo apt-get update
sudo apt-get install libroaring-dev

# Verify installation
dpkg -L libroaring-dev | grep roaring.h

# Rebuild extension
cd /path/to/biscuit
make clean
make
sudo make install

# Restart PostgreSQL
sudo systemctl restart postgresql

# Verify in PostgreSQL
psql -c "DROP EXTENSION biscuit CASCADE;"
psql -c "CREATE EXTENSION biscuit;"
psql -c "SELECT biscuit_has_roaring();"
-- Should return: true

Next Steps

• Learn how it works: Architecture Guide

• Master patterns: Pattern Syntax

• Optimize queries: Performance Tuning

• Get help: FAQ