> ## Documentation Index
> Fetch the complete documentation index at: https://docs-relytone.data.cloud/llms.txt
> Use this file to discover all available pages before exploring further.

# Full-Text Search

> Get started with pg_search, Relyt ONE's built-in BM25 full-text search extension based on the open-source ParadeDB project with enhanced crash recovery.

Relyt ONE comes with the built-in `pg_search` extension, providing powerful full-text search capabilities based on BM25 ranking.

The extension is built on the open-source [ParadeDB project](https://github.com/paradedb/paradedb), a modern Elasticsearch alternative for Postgres. Relyt has enhanced ParadeDB with **Write-Ahead Logging (WAL) support for crash recovery**, ensuring production-grade reliability and full recoverability of search indexes. Our enhancements are also open-sourced at [github](https://github.com/relytcloud/paradedb).

## Key Features

* **Real-time Search**: Immediate visibility with zero indexing delay
* **High Performance**: Parallel BM25 index creation and query execution
* **ACID Compliance**: Full transactional guarantees
* **Crash Recovery**: WAL-based backup and recovery support
* **Custom Tokenization**: User-defined tokenizers for different languages and use cases

## Quick Start

### Step 1: Create a Table and Insert Sample Data

Use the helper function to create a test table with sample data:

```sql theme={null}
CALL pgsearch.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'mock_items'
);
```

This creates a `mock_items` table and populates it with sample product data for testing.

### Step 2: Create a BM25 Index

Create a full-text search index on the desired columns:

```sql theme={null}
CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, category, rating, in_stock, created_at, metadata)
WITH (key_field='id');
```

The `key_field` parameter specifies the primary key column used for document identification.

### Step 3: Query Using the BM25 Index

The `@@@` operator is used for full-text search queries.

#### Basic Search - Single Term

```sql theme={null}
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes';
```

#### Multiple Terms

```sql theme={null}
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'keyboard' OR category @@@ 'toy';
```

#### Search with Filters

Combine full-text search with structured filters:

```sql theme={null}
-- Using @@@ operator for numeric comparison
SELECT id, description, rating
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ '>3'
ORDER BY id;

-- Using standard SQL operators
SELECT id, description, rating
FROM mock_items
WHERE description @@@ 'shoes' AND rating > 3;

-- Exact match
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ '4';

-- Date filtering
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND created_at @@@ '"2023-04-20T16:38:02Z"';

-- Boolean filtering
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND in_stock @@@ 'true';

-- Range queries
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ '[1 TO 4]';

-- Term range
SELECT description, rating, category
FROM mock_items
WHERE description @@@ '[book TO camera]';

-- Set filtering
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes' AND rating @@@ 'IN [2 3 4]';
```

### Step 4: Advanced Search Features

#### Proximity Search

Use the tilde (`~`) operator for phrase proximity matching:

```sql theme={null}
SELECT description, rating, category
FROM mock_items
WHERE description @@@ '"ergonomic keyboard"~1';
```

**Output:**

```
       description        | rating |  category   
--------------------------+--------+-------------
 Ergonomic metal keyboard |      4 | Electronics
(1 row)
```

The `~1` allows one word between the search terms.

#### Highlighting

Highlight matching terms in search results:

```sql theme={null}
SELECT id, pgsearch.snippet(description)
FROM mock_items
WHERE description @@@ 'shoes'
LIMIT 5;
```

#### Relevance Scoring

Retrieve relevance scores for search results:

```sql theme={null}
SELECT description, rating, category, pgsearch.score(id) AS score
FROM mock_items
WHERE description @@@ 'shoes'
ORDER BY score DESC
LIMIT 5;
```

#### Field Boosting

Apply different weights to search terms using the caret (`^`) operator:

```sql theme={null}
SELECT id, pgsearch.score(id) AS score
FROM mock_items
WHERE description @@@ 'shoes^2' OR category @@@ 'footwear'
ORDER BY score DESC
LIMIT 5;
```

The `^2` boosts the importance of matches in the description field by a factor of 2.

#### Match Function

Use the `pgsearch.match()` function for more control over search behavior:

```sql theme={null}
-- Simple match
SELECT description, rating, category
FROM mock_items
WHERE id @@@ pgsearch.match('description', 'running shoes');

-- JSON-based query
SELECT description, rating, category
FROM mock_items
WHERE id @@@ '{
  "match": {
    "field": "description",
    "value": "running shoes"
  }
}'::jsonb;

-- Custom tokenizer
SELECT description, rating, category
FROM mock_items
WHERE id @@@ pgsearch.match(
  'description',
  'running shoes',
  tokenizer => pgsearch.tokenizer('whitespace')
);
```

***

For more advanced features including custom tokenizers, aggregations, fuzzy search, hybrid search, and query optimization techniques, please refer to the [ParadeDB documentation](https://docs.paradedb.com/documentation/overview). Since Relyt ONE's `pg_search` is based on ParadeDB, most features and syntax are fully compatible.