Skip to main content
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, 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.

​
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: 
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: 
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

SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes';

​
Multiple Terms

SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'keyboard' OR category @@@ 'toy';

​
Search with Filters

Combine full-text search with structured filters: 
-- 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

Use the tilde (~) operator for phrase proximity matching: 
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: 
SELECT id, pgsearch.snippet(description)
FROM mock_items
WHERE description @@@ 'shoes'
LIMIT 5;

​
Relevance Scoring

Retrieve relevance scores for search results: 
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: 
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: 
-- 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. Since Relyt ONE’s pg_search is based on ParadeDB, most features and syntax are fully compatible.