A PostgreSQL extension that integrates the Bleve search engine to provide full-text search capabilities similar to ParadeDB.
- CREATE INDEX Support: Create Bleve indexes using PostgreSQL's
CREATE INDEXsyntax - 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
# 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- Install PostgreSQL 17 development headers
- Install Go 1.24+
- Build the extension:
make
- Install the extension files to your PostgreSQL installation
- Create the extension in your database:
CREATE EXTENSION pg_bleve;
-- 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');-- Create index programmatically
SELECT bleve_create_index('my_index', '{"key_field": "id"}');
-- Populate with existing data
SELECT bleve_populate_index('my_index', 'table_name');The extension provides three main search operators, each returning full JSON search results:
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}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}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}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;-- 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');-- 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);-- 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 | Description | Logic | Example |
|---|---|---|---|
@@@ |
General search | Default Bleve behavior | 'index' @@@ 'query' |
| ` | ` | ||
&&& |
Match conjunction | ALL terms (AND) | 'index' \&\&\& 'term1 term2' |
The extension supports operator classes for:
textvarcharint4(integer)int8(bigint)
- 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
-
"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');
-
bleve_populate_indexhangs: The function now has built-in limits and delays. For very large tables, use the batch version. -
Operator not found: Ensure the extension is properly loaded:
DROP EXTENSION IF EXISTS pg_bleve; CREATE EXTENSION pg_bleve;
Check PostgreSQL logs for detailed error information:
docker logs pg_bleve_test# Install dependencies
go mod download
# Build Go archive
go build -buildmode=c-archive -o pg_bleve_go.a main.go
# Build PostgreSQL extension
makeThe 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.sqlAGPL-3.0