pg_bleve - PostgreSQL Extension for Bleve Search

A PostgreSQL extension that integrates the Bleve search engine to provide full-text search capabilities similar to ParadeDB.

Features

• CREATE INDEX Support: Create Bleve indexes using PostgreSQL's CREATE INDEX syntax
• Full-Text Search Operators: Multiple search operators for different use cases
• Functional API: Direct function calls for index management and search
• Docker Support: Complete Docker setup for easy testing and deployment

Installation

Using Docker (Recommended)

# Clone the repository
git clone <repository-url>
cd pg_bleve

# Build and start PostgreSQL with the extension
docker-compose up --build -d

# Connect to the database
docker exec -it pg_bleve_test psql -U test_user -d test_db

Manual Installation

1. Install PostgreSQL 17 development headers
2. Install Go 1.24+
3. Build the extension:

   make

4. Install the extension files to your PostgreSQL installation
5. Create the extension in your database:

   CREATE EXTENSION pg_bleve;

Usage

Creating Indexes

Method 1: CREATE INDEX (Recommended)

-- Create a Bleve index on a table
CREATE INDEX search_idx ON mock_items USING bleve (id, description, category) WITH (key_field='id');

-- Populate the index with existing data
SELECT bleve_populate_index('search_idx', 'mock_items');

Method 2: Functional API

-- Create index programmatically
SELECT bleve_create_index('my_index', '{"key_field": "id"}');

-- Populate with existing data
SELECT bleve_populate_index('my_index', 'table_name');

Search Operators

The extension provides three main search operators, each returning full JSON search results:

1. @@@ - General Search

Returns documents matching the query using Bleve's default search behavior.

SELECT 'index_name' @@@ 'query';

Example:

SELECT 'search_idx' @@@ 'Electronics';
-- Returns: {"results":[{"id":"(0,1)","score":0.189}],"total":1}

2. ||| - Match Disjunction

Finds documents containing ANY of the terms (OR logic).

SELECT 'index_name' ||| 'term1 term2 term3';

Example:

SELECT 'search_idx' ||| 'iPhone Electronics';
-- Returns documents with either "iPhone" OR "Electronics"
-- Result: {"results":[...],"total":3}

3. &&& - Match Conjunction

Finds documents containing ALL of the terms (AND logic).

SELECT 'index_name' &&& 'term1 term2 term3';

Example:

SELECT 'search_idx' &&& 'iPhone Electronics';
-- Returns documents with both "iPhone" AND "Electronics"
-- Result: {"results":[...],"total":1}

Extracting Information from Results

Since all operators return JSON, you can extract specific information:

-- Get total number of results
SELECT ('search_idx' @@@ 'query')::jsonb->>'total' as total_results;

-- Get first result ID
SELECT ('search_idx' @@@ 'query')::jsonb->'results'->0->>'id' as first_result_id;

-- Get all result IDs
SELECT jsonb_array_elements(('search_idx' @@@ 'query')::jsonb->'results')->>'id' as result_id;

-- Check if results exist
SELECT (('search_idx' @@@ 'query')::jsonb->>'total')::int > 0 as has_results;

Functional API

Index Management

-- Create index
SELECT bleve_create_index('index_name', '{"key_field": "id"}');

-- Index individual document
SELECT bleve_index_document('index_name', 'doc_id', '{"id": "1", "title": "Document"}');

-- Search documents
SELECT bleve_search('index_name', 'query');

Population Functions

-- Standard population (with limits and error handling)
SELECT bleve_populate_index('index_name', 'table_name');

-- Batch population (more efficient for large tables)
SELECT bleve_populate_index_batch('index_name', 'table_name', 100);

Complete Example

-- 1. Create extension
CREATE EXTENSION pg_bleve;

-- 2. Create a test table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    category TEXT
);

-- 3. Insert sample data
INSERT INTO products (name, description, category) VALUES
    ('iPhone 15', 'Latest smartphone with advanced features', 'Electronics'),
    ('Running Shoes', 'Comfortable athletic footwear', 'Sports'),
    ('Coffee Maker', 'Programmable coffee machine', 'Home');

-- 4. Create Bleve index
CREATE INDEX products_idx ON products USING bleve (id, name, description, category) WITH (key_field='id');

-- 5. Populate index
SELECT bleve_populate_index('products_idx', 'products');

-- 6. Search with different operators
SELECT 'products_idx' @@@ 'smartphone';                    -- General search
SELECT 'products_idx' ||| 'Electronics Sports';            -- Disjunction (ANY terms)
SELECT 'products_idx' &&& 'Electronics smartphone';        -- Conjunction (ALL terms)

-- 7. Extract specific information
SELECT ('products_idx' @@@ 'smartphone')::jsonb->>'total' as total_results;

Operator Comparison

Operator	Description	Logic	Example
@@@	General search	Default Bleve behavior	'index' @@@ 'query'
`			`
&&&	Match conjunction	ALL terms (AND)	'index' \&\&\& 'term1 term2'

Data Types Supported

The extension supports operator classes for:

• text
• varchar
• int4 (integer)
• int8 (bigint)

Performance Considerations

• Index Population: Use bleve_populate_index_batch() for large tables
• Search Performance: Bleve indexes are stored in /tmp/pg_bleve_indexes/ and persist across sessions
• Memory Usage: Each search operation loads the index into memory temporarily

Troubleshooting

Common Issues

1. "Failed to open index": Index files may have been lost. Recreate the index:

   SELECT bleve_create_index('index_name', '{"key_field": "id"}');
   SELECT bleve_populate_index('index_name', 'table_name');

2. bleve_populate_index hangs: The function now has built-in limits and delays. For very large tables, use the batch version.

3. Operator not found: Ensure the extension is properly loaded:

   DROP EXTENSION IF EXISTS pg_bleve;
   CREATE EXTENSION pg_bleve;

Logs

Check PostgreSQL logs for detailed error information:

docker logs pg_bleve_test

Development

Building from Source

# Install dependencies
go mod download

# Build Go archive
go build -buildmode=c-archive -o pg_bleve_go.a main.go

# Build PostgreSQL extension
make

Testing

The Docker setup includes automatic testing via init-test.sql. To run manual tests:

docker exec pg_bleve_test psql -U test_user -d test_db -f /path/to/test.sql

License

AGPL-3.0