biscuit

Overview

Version

Build

You can build the RPM / DEB packages for pg_biscuit using pig build:

pig build pkg pg_biscuit         # build RPM / DEB packages

Install

You can install pg_biscuit directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_biscuit;          # Install for current active PG version

pig ext install -y pg_biscuit -v 18  # PG 18
pig ext install -y pg_biscuit -v 17  # PG 17
pig ext install -y pg_biscuit -v 16  # PG 16

dnf install -y pg_biscuit_18       # PG 18
dnf install -y pg_biscuit_17       # PG 17
dnf install -y pg_biscuit_16       # PG 16

apt install -y postgresql-18-biscuit   # PG 18
apt install -y postgresql-17-biscuit   # PG 17
apt install -y postgresql-16-biscuit   # PG 16

Create Extension:

CREATE EXTENSION biscuit CASCADE;  -- requires: plpgsql

Usage

Syntax:

CREATE EXTENSION biscuit;
CREATE INDEX idx_users_name ON users USING biscuit(name);
SELECT * FROM users WHERE name LIKE '%john%';

Sources: README, Docs

biscuit is a PostgreSQL index access method for fast LIKE and ILIKE pattern matching, including multi-column searches. The upstream project positions it as a deterministic bitmap index that avoids the false-positive recheck overhead common in trigram-based searches.

Quick Start

Create the extension and build a Biscuit index on one or more text columns:

CREATE EXTENSION biscuit;

CREATE INDEX idx_users_name ON users USING biscuit(name);

CREATE INDEX idx_products_search
ON products USING biscuit(name, description, category);

Basic wildcard queries work with the index:

SELECT * FROM users WHERE name LIKE '%john%';
SELECT * FROM users WHERE name NOT LIKE 'a%b%c';
SELECT COUNT(*) FROM users WHERE name LIKE '%test%';

SELECT *
FROM products
WHERE name LIKE '%widget%'
  AND description LIKE '%blue%'
  AND category LIKE 'electronics%'
LIMIT 10;

Index Behavior

Biscuit stores bitmap position indexes for each string and can match both forward and backward character positions. The upstream design highlights:

• positive indexes for characters at exact positions
• negative indexes for characters counted from the string end
• case-insensitive variants for ILIKE
• exact-length and minimum-length bitmaps for fast length filtering

For a pattern such as LIKE 'abc%def', Biscuit can intersect prefix and suffix bitmaps plus a minimum-length filter, producing exact matches without a heap recheck phase.

Pattern Cases

The implementation documents optimized paths for common pattern types:

• exact matches such as 'abc'
• prefix patterns such as 'abc%'
• suffix patterns such as '%xyz'
• substring patterns such as '%abc%'
• multi-column predicates, where Biscuit reorders predicates by estimated selectivity

Performance Notes

The upstream README emphasizes bitmap-only evaluation and several execution optimizations, including:

• early termination when an intermediate bitmap becomes empty
• direct use of roaring bitmaps for sparse and dense cases
• negative-position lookups for suffix predicates
• sorted TID output to improve heap access locality
• special handling for aggregate queries and LIMIT

The project README also includes a benchmark setup comparing Biscuit indexes with trigram-based approaches on a 1M-row table.

Requirements

The current upstream README lists these requirements for source builds:

• PostgreSQL 16 or newer
• standard build tools such as gcc, make, and pg_config
• optional CRoaring for improved performance

The project publishes packages on PGXN and maintains a dedicated documentation site on Read the Docs.