# pg_bleve - PostgreSQL Extension for Bleve Search

A PostgreSQL extension that integrates the [Bleve search engine](https://github.com/blevesearch/bleve) 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)

```bash
# 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:
   ```bash
   make
   ```
4. Install the extension files to your PostgreSQL installation
5. Create the extension in your database:
   ```sql
   CREATE EXTENSION pg_bleve;
   ```

## Usage

### Creating Indexes

#### Method 1: CREATE INDEX (Recommended)

```sql
-- 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

```sql
-- 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.

```sql
SELECT 'index_name' @@@ 'query';
```

**Example:**
```sql
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).

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

**Example:**
```sql
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).

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

**Example:**
```sql
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:

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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 disjunction | ANY terms (OR) | `'index' \|\|\| 'term1 term2'` |
| `&&&` | 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:
   ```sql
   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:
   ```sql
   DROP EXTENSION IF EXISTS pg_bleve;
   CREATE EXTENSION pg_bleve;
   ```

### Logs

Check PostgreSQL logs for detailed error information:
```bash
docker logs pg_bleve_test
```

## Development

### Building from Source

```bash
# 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:

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

## License

AGPL-3.0