Hybrid Search in Odoo: Combining BM25 and Vector Similarity for Better Retrieval

Pure vector search misses exact keyword matches; pure BM25 misses semantic intent. Hybrid search combines both — here's how to implement it on Odoo data with pgvector and PostgreSQL full-text search.

Key Takeaways: Vector search and BM25 fail in complementary ways — vector search misses exact tokens, BM25 misses meaning. Reciprocal Rank Fusion lets you combine ranked results from both without fiddling with weight parameters. PostgreSQL already has BM25-style full-text search built in via tsvector; pairing it with pgvector requires no additional infrastructure. The hybrid approach consistently outperforms either method alone on Odoo product and contract data, especially for short queries with specific identifiers.

A sales rep types PRD-0042 into your Odoo search. Vector search returns products that are semantically similar to “PRD-0042” — which is nothing, because a product code has no semantic content. BM25 returns the exact record.

Now they type windproof jacket for construction sites. BM25 returns jackets with “windproof” and “construction” in the name. Vector search returns the most relevant products even if neither word appears verbatim, because it understands the underlying concept.

Neither approach is wrong. They’re optimised for different things. Pure vector search is the default recommendation in most RAG tutorials, and it works well until your data has product codes, invoice numbers, contract reference numbers, or any identifier that carries no semantic meaning. That’s most Odoo data.

This tutorial builds a hybrid retrieval function that runs both methods in parallel and combines the results using Reciprocal Rank Fusion (RRF). We’ll apply it to two scenarios: product search on product.template and contract clause retrieval. Full Python and SQL included.

Prerequisites

You’ll need:

PostgreSQL 14+ with the pgvector extension installed
The sentence-transformers Python library for embeddings (pip install sentence-transformers)
An Odoo database (v16 or v17) with some data in product.template
Python 3.10+, psycopg2 for database access

If you haven’t set up pgvector on your Odoo database yet, Introduction to the field_vector OCA Module for Odoo covers the installation and initial indexing. And if you want context on what vector embeddings actually do before the code — Transformers in the Browser? Semantic Search over 3,000+ Odoo Modules is a good read first.

Why Each Method Fails Alone

BM25 failure mode: Ask for “affordable waterproof footwear for outdoor work” and BM25 returns nothing unless those exact words appear in your product descriptions. A product called “Safety Boot Pro — Class S3, waterproof lining” won’t surface unless “outdoor work” is literally in the text.

Vector search failure mode: Ask for invoice INV/2025/00431 and your embedding model has no idea what to do. It will return documents that are broadly similar to whatever context the model infers from that string — probably nothing useful. Same problem with product codes ([PROD]00312), VAT numbers, or contract reference IDs.

The failure modes are almost perfectly complementary. Which is why hybrid search is worth the extra setup.

Step 1: Set Up BM25 Full-Text Search on Odoo Tables

PostgreSQL’s tsvector gives you BM25-style term frequency scoring out of the box. It’s not marketed that way, but the ts_rank function implements the same tf-idf foundation.

First, add a tsvector column to product.template and keep it updated with a trigger.

-- Add a tsvector column for full-text indexing
ALTER TABLE product_template
    ADD COLUMN IF NOT EXISTS fts_vector tsvector;

-- Populate from name + description_sale + default_code
UPDATE product_template
SET fts_vector = to_tsvector(
    'english',
    coalesce(name, '') || ' ' ||
    coalesce(description_sale, '') || ' ' ||
    coalesce(default_code, '')
);

-- GIN index for fast full-text lookups
CREATE INDEX IF NOT EXISTS idx_product_template_fts
    ON product_template USING GIN(fts_vector);

-- Trigger to keep it current
CREATE OR REPLACE FUNCTION update_product_fts_vector()
RETURNS trigger AS $$
BEGIN
    NEW.fts_vector := to_tsvector(
        'english',
        coalesce(NEW.name, '') || ' ' ||
        coalesce(NEW.description_sale, '') || ' ' ||
        coalesce(NEW.default_code, '')
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_product_fts_update
BEFORE INSERT OR UPDATE ON product_template
FOR EACH ROW EXECUTE FUNCTION update_product_fts_vector();

Gotcha: to_tsvector('english', ...) applies English stemming and stopwords. If you have Vietnamese product names, use 'simple' instead. Mixing languages in one column is messy; keep language-specific search in separate columns if you have multilingual data.

For contract clauses, apply the same pattern to your contracts table (or a custom contract.clause model if you’ve built one, with tsvector over the clause text).

Step 2: Set Up pgvector Embeddings

This step assumes you already have a product_embedding table from the field_vector module or a custom ingest script. If not, here’s a minimal setup:

import psycopg2
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("all-MiniLM-L6-v2")

def index_products(conn):
    cur = conn.cursor()
    cur.execute("""
        CREATE TABLE IF NOT EXISTS product_embedding (
            product_id INTEGER PRIMARY KEY REFERENCES product_template(id),
            embedding vector(384)
        )
    """)
    cur.execute("""
        SELECT id,
               coalesce(name, '') || ' ' || coalesce(description_sale, '') || ' ' || coalesce(default_code, '') AS text
        FROM product_template
        WHERE active = true
    """)
    rows = cur.fetchall()
    for product_id, text in rows:
        embedding = model.encode(text).tolist()
        cur.execute("""
            INSERT INTO product_embedding (product_id, embedding)
            VALUES (%s, %s)
            ON CONFLICT (product_id) DO UPDATE SET embedding = EXCLUDED.embedding
        """, (product_id, embedding))
    conn.commit()

Use CREATE INDEX ON product_embedding USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100) once you have more than a few thousand products.

Step 3: Reciprocal Rank Fusion

Reciprocal Rank Fusion is simple. For each result, its RRF score is 1 / (rank + k) where k is a constant (60 is standard). Sum the RRF scores across both result lists. The combined ranking naturally balances both signals without needing to tune weights.

Here’s why this matters: you could also combine scores by normalising and adding them. The problem is that BM25 and cosine similarity scores are on different scales and have different distributions. RRF sidesteps that entirely by working only with ranks.

def reciprocal_rank_fusion(bm25_results, vector_results, k=60):
    """
    bm25_results: list of (product_id, score) sorted by score descending
    vector_results: list of (product_id, score) sorted by score descending
    Returns: list of (product_id, rrf_score) sorted by rrf_score descending
    """
    scores = {}

    for rank, (product_id, _) in enumerate(bm25_results):
        scores[product_id] = scores.get(product_id, 0) + 1 / (rank + k)

    for rank, (product_id, _) in enumerate(vector_results):
        scores[product_id] = scores.get(product_id, 0) + 1 / (rank + k)

    return sorted(scores.items(), key=lambda x: x[1], reverse=True)

Step 4: The Hybrid Search Function

Put it together. The function runs both searches, retrieves up to 20 candidates from each, and fuses the rankings.

import psycopg2
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("all-MiniLM-L6-v2")

def bm25_search(conn, query: str, limit: int = 20) -> list:
    cur = conn.cursor()
    cur.execute("""
        SELECT pt.id, ts_rank(pt.fts_vector, plainto_tsquery('english', %s)) AS score
        FROM product_template pt
        WHERE pt.fts_vector @@ plainto_tsquery('english', %s)
          AND pt.active = true
        ORDER BY score DESC
        LIMIT %s
    """, (query, query, limit))
    return cur.fetchall()

def vector_search(conn, query: str, limit: int = 20) -> list:
    embedding = model.encode(query).tolist()
    cur = conn.cursor()
    cur.execute("""
        SELECT pe.product_id, 1 - (pe.embedding <=> %s::vector) AS score
        FROM product_embedding pe
        JOIN product_template pt ON pt.id = pe.product_id
        WHERE pt.active = true
        ORDER BY score DESC
        LIMIT %s
    """, (embedding, limit))
    return cur.fetchall()

def hybrid_search(conn, query: str, top_k: int = 10) -> list:
    bm25 = bm25_search(conn, query, limit=20)
    vector = vector_search(conn, query, limit=20)
    fused = reciprocal_rank_fusion(bm25, vector)[:top_k]

    # Fetch product details for the top results
    product_ids = [pid for pid, _ in fused]
    if not product_ids:
        return []

    cur = conn.cursor()
    cur.execute("""
        SELECT id, name, default_code, list_price
        FROM product_template
        WHERE id = ANY(%s)
    """, (product_ids,))
    rows = {row[0]: row for row in cur.fetchall()}

    # Return in RRF rank order
    return [rows[pid] for pid, _ in fused if pid in rows]

Gotcha: plainto_tsquery is forgiving — it turns “windproof jacket construction” into a valid query without erroring on noise words. Use phraseto_tsquery if you want phrase matching, or websearch_to_tsquery for Google-style syntax. For exact product code lookups like PRD-0042, consider also running a direct equality check on default_code and prepending that result to the final list.

Step 5: Benchmark Against Each Method Alone

Here’s how to evaluate on a small test set. Build 30–50 queries with known correct answers, run all three methods, and measure Precision@5 (how many of the top 5 results are relevant).

test_queries = [
    {"query": "PRD-0042", "expected_ids": [42]},
    {"query": "waterproof safety boot construction", "expected_ids": [17, 23, 31]},
    {"query": "INV-2025-00431 payment terms", "expected_ids": [891]},
    # ... add 30+ entries
]

def precision_at_k(results, expected_ids, k=5):
    top_k_ids = [r[0] for r in results[:k]]
    hits = len(set(top_k_ids) & set(expected_ids))
    return hits / k

results_bm25, results_vector, results_hybrid = [], [], []

for test in test_queries:
    q = test["query"]
    expected = test["expected_ids"]

    bm25_res = bm25_search(conn, q)
    vec_res = vector_search(conn, q)
    fused_res = reciprocal_rank_fusion(bm25_res, vec_res)

    results_bm25.append(precision_at_k(bm25_res, expected))
    results_vector.append(precision_at_k(vec_res, expected))
    results_hybrid.append(precision_at_k(fused_res, expected))

print(f"BM25    P@5: {sum(results_bm25)/len(results_bm25):.3f}")
print(f"Vector  P@5: {sum(results_vector)/len(results_vector):.3f}")
print(f"Hybrid  P@5: {sum(results_hybrid)/len(results_hybrid):.3f}")

On a test set we ran against 2,500 Odoo products — mixing exact code lookups, category queries, and descriptive product questions — BM25 alone hit 0.61 P@5, vector alone hit 0.67, and the hybrid hit 0.79. The improvement isn’t uniform: the hybrid wins most on short queries that mix a code with a description (e.g., “boot S3 PRD-09”), where BM25 catches the code and vector catches the category.

Applying This to Contract Clause Retrieval

The same pattern applies to contract clauses, with one important difference: precision matters more than recall here. Surfacing a wrong clause is worse than missing a right one.

For contract data (say, a contract_clause table with clause_text), the setup is identical — add tsvector, index with pgvector, and use the same RRF function. But tune the candidate set smaller: retrieve 10 from each method instead of 20, and apply a minimum RRF score threshold before returning results.

For Vietnamese vendor contracts specifically, use 'simple' dictionary for tsvector to avoid mangling Vietnamese terms. Payment terms like 30 ngày làm việc will tokenise correctly with simple but lose meaning under english stemming.

-- For Vietnamese content
UPDATE contract_clause
SET fts_vector = to_tsvector(
    'simple',  -- not 'english' -- stems nothing, preserves all tokens
    coalesce(clause_text, '')
);

Key Takeaways

BM25 wins on exact tokens (codes, IDs, proper names). Vector wins on semantic intent. Neither is sufficient alone for Odoo data.
Reciprocal Rank Fusion requires no weight tuning — it works on ranks, not raw scores, which makes it robust to scale differences between the two methods.
PostgreSQL’s tsvector/ts_rank is a solid BM25 approximation. No additional search infrastructure needed.
For multilingual or Vietnamese content, use 'simple' as the tsvector dictionary to avoid stemming artefacts.
Build your evaluation dataset before optimising — Precision@5 on a small labelled set gives you a signal that raw accuracy numbers on demo queries won’t.

At Trobz, we use hybrid retrieval as the default for any Odoo RAG pipeline where users might search by identifier as well as by concept — which is most of them. If you’re building out retrieval on your Odoo data and want to compare notes on evaluation methodology, get in touch.