Build Hybrid Search_ Vector + BM25 Tutorial

Architectural Prerequisites: The Multi-Model Foundation

Implementing a hybrid search pipeline requires reconciling the semantic recall of vector similarity with the keyword precision of lexical matching. This architecture introduces a critical consistency challenge: keeping the transactional system of record synchronized with the dual-index retrieval layer. While many enterprises deploy separate vector databases for embeddings, this approach increases infrastructure complexity and data synchronization latency.

KingbaseES (KES) V9 is a commercial database product that natively supports vector data types. This capability allows enterprises to store relational metadata, text content, and vector embeddings within a single commercial database system. By leveraging the native vector support in KingbaseES V9, you can simplify the stack and reduce the operational overhead associated with external vector stores. However, native support for vector data does not automatically imply a complete, out-of-the-box hybrid search engine. You must still architect the orchestration layer for keyword (BM25) indexing and score fusion.

Before beginning implementation, ensure your environment meets these prerequisites:

• Database Version: KingbaseES V9 or later, which includes native vector data type support.
• Ecosystem Components: KFS Ultra or compatible synchronization drivers for handling vector data workflows.
• Authentication: Configuration for national cryptographic (Guomi) authentication if operating in regulated sectors.
• Performance Tools: Access to the KingbaseES Performance Inspection Platform for slow query management and monitoring.

Verify that your instance is licensed as commercial software and that the vector extension is enabled. Do not assume open-source compatibility or community support mechanisms apply to this commercial product.

Data Modeling: Storing Embeddings and Text in KingbaseES

Effective hybrid search relies on a schema that efficiently stores three distinct data types: relational metadata, unstructured text for keyword indexing, and high-dimensional vectors for semantic search. KingbaseES V9 supports these models within a single system, eliminating the need for polyglot persistence for these specific data types.

Design your table schema to accommodate vector embeddings directly. The vector data type in KingbaseES V9 supports standard floating-point arrays.

Step 1: Create the Document Table

Define a table that holds the primary key, text content, metadata, and the vector column.

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(50),
    publish_date DATE,
    embedding VECTOR(768)  -- Adjust dimension based on your model
);

Step 2: Insert Data with Embeddings

Populate the table with text and pre-generated embeddings. Ensure the embedding service generates vectors that match the dimension specified in the schema (e.g., 768 dimensions for common transformer models).

INSERT INTO documents (title, content, category, publish_date, embedding)
VALUES (
    'Enterprise Data Governance',
    'Best practices for managing data quality...',
    'Governance',
    '2023-10-01',
    '[0.036124361, 0.06381883, 0.006241124, ...]' -- Truncated for brevity
);

Step 3: Verify Data Integrity

Check that the vector data is stored correctly and that text fields are accessible for keyword matching.

SELECT id, title, length(content) as content_length
FROM documents
WHERE category = 'Governance'
LIMIT 5;

This schema supports the dual-index requirement by keeping all data in one place. The content and title fields serve as the source for BM25 indexing, while the embedding field serves the vector index.

Indexing Strategy: Dual-Index Maintenance

Hybrid search requires two distinct indexing structures. The vector index supports similarity searches, while the text index supports keyword matching. Maintaining both without degrading write performance is a key architectural challenge.

KingbaseES supports standard SQL indexing for text and relational data. For vector similarity, specific vector index types are required. Note that specific vector index creation syntax may vary by version; consult the vendor documentation for the exact command for your KingbaseES V9 build.

Step 1: Create Text Index for BM25

Create a standard index on the text fields to support keyword retrieval. For BM25 scoring, full-text search indexes are typically used.

-- Vendor-Specific: Verify exact syntax for full-text search index creation in KingbaseES V9
CREATE INDEX idx_doc_content_text ON documents USING gin(to_tsvector('english', content));
CREATE INDEX idx_doc_title_text ON documents USING gin(to_tsvector('english', title));

Step 2: Create Vector Index

Create an index optimized for vector distance calculations. The exact index type (e.g., HNSW, IVFFlat) depends on your latency and accuracy requirements.

-- Vendor-Specific: Verify exact index type and operator class for your KingbaseES version
CREATE INDEX idx_doc_embedding ON documents USING <vector_index_type>(embedding vector_cosine_ops);

Step 3: Manage Index Maintenance

Index maintenance impacts write performance. Schedule index rebuilds or updates during low-traffic windows. Use KingbaseES performance inspection tools to monitor the impact of index operations on system load.

Retrieval Execution: Parallel Vector and Keyword Queries

To meet latency requirements, execute vector similarity and keyword searches in parallel. This approach minimizes the total retrieval time by leveraging the database’s ability to process multiple queries simultaneously.

Since native hybrid search orchestration is not explicitly documented for KingbaseES, you must execute separate queries and merge the results in the application layer or via stored procedures.

Step 1: Execute Vector Similarity Query

Retrieve documents based on vector distance. Use cosine similarity for normalized results.

-- Example: Find top 10 most similar documents
SELECT id, title, embedding <-> '[0.01, 0.02, ...]' AS distance
FROM documents
ORDER BY distance ASC
LIMIT 10;

Step 2: Execute Keyword Query

Retrieve documents based on keyword matching using full-text search.

-- Vendor-Specific: Verify tokenization settings for 'english' in KingbaseES
-- Example: Find documents matching specific terms
SELECT id, title, ts_rank(to_tsvector('english', content), query) AS rank
FROM documents, to_tsquery('english', 'data AND governance') query
WHERE to_tsvector('english', content) @@ query
ORDER BY rank DESC
LIMIT 10;

Step 3: Combine Results

Merge the two result sets. Ensure you handle duplicate IDs and normalize scores before fusion. This step is critical for accurate hybrid ranking.

Score Fusion: Implementing Reciprocal Rank Fusion (RRF)

Score fusion combines the ranked lists from vector and keyword searches into a single, coherent ranking. Reciprocal Rank Fusion (RRF) is a widely used method that is robust to differences in score distributions between the two retrieval streams.

KingbaseES does not provide a native "hybrid search" function that automatically applies RRF. You must implement the fusion logic using PL/pgSQL or in the application layer.

RRF Formula

$$ RRF(d) = \sum_{r \in R} \frac{1}{k + r(d)} $$

Where:

• $R$ is the set of retrieval lists (vector and keyword).
• $r(d)$ is the rank of document $d$ in list $R$.
• $k$ is a constant, typically 60.

Step 1: Implement Fusion Logic in PL/pgSQL

Create a stored procedure to merge the results. This example assumes you have two temporary tables or CTEs containing the ranked IDs from the vector and keyword queries.

CREATE OR REPLACE FUNCTION hybrid_search_fusion(
    p_query_embedding VECTOR(768),
    p_keywords TEXT
) RETURNS TABLE (id INT, title TEXT, score FLOAT) AS $$
DECLARE
    k CONSTANT INT := 60;
    v_score FLOAT;
BEGIN
    -- This is a conceptual implementation.
    -- In practice, you would execute the two queries separately,
    -- collect the IDs and ranks, and then compute the RRF sum.

    -- Example: Select IDs and compute RRF score
    -- Note: Full implementation requires complex SQL logic or application-side processing.

    RETURN QUERY
    SELECT
        d.id,
        d.title,
        -- Placeholder for RRF calculation logic
        0.0 AS score
    FROM documents d
    WHERE d.id IN (
        -- Subquery for vector results
        SELECT id FROM documents ORDER BY embedding <-> p_query_embedding LIMIT 10
        UNION
        -- Subquery for keyword results
        SELECT id FROM documents WHERE content ILIKE '%' || p_keywords || '%' LIMIT 10
    );
END;
$$ LANGUAGE plpgsql;

Step 2: Verify Fusion Accuracy

Test the fusion function with known queries. Compare the results against manual relevance judgments to ensure that the RRF weighting aligns with user expectations. Adjust the constant $k$ if necessary to balance the influence of vector vs. keyword results.

Synchronization & Freshness: Keeping the Indexes Alive

Data freshness is a critical constraint in hybrid search. Stale embeddings or outdated text indexes lead to poor search results. KingbaseES supports triggers and synchronization mechanisms to keep the retrieval layer in sync with the transactional system of record.

Step 1: Implement Triggers for Index Updates

Use database triggers to update the vector and text indexes when the underlying data changes. This ensures that the retrieval layer reflects the current state of the transactional data.

CREATE OR REPLACE FUNCTION update_embedding()
RETURNS TRIGGER AS $$
BEGIN
    -- Regenerate embedding if content changes
    -- Note: Embedding generation usually happens in the application layer
    -- or via an external service. This trigger can signal the update.
    NEW.embedding := generate_embedding(NEW.content); -- Placeholder function
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_embedding
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_embedding();

Step 2: Leverage KFS Ultra for Vector Synchronization

For large-scale deployments, use KFS Ultra to handle vector data synchronization. KFS Ultra natively supports vector data types and can synchronize embedded vectors to AI/ML applications efficiently.

Step 3: Monitor Index Latency

Use the KingbaseES Performance Inspection Platform to monitor slow queries and index update latencies. Configure crontab jobs for regular collection of performance metrics.

Verification & Troubleshooting: Performance and Failure Modes

Validating hybrid search performance requires checking both accuracy and latency. Common failure modes include stale data, high latency due to complex fusion logic, and poor score fusion.

Verification Checklist

1. Data Consistency: Verify that updated documents appear in search results within the expected latency window.
2. Index Freshness: Check that vector embeddings are regenerated and updated when text content changes.
3. Score Fusion Accuracy: Validate that RRF produces relevant results by comparing with ground-truth rankings.
4. Latency Monitoring: Ensure that parallel query execution and fusion logic do not exceed latency thresholds (e.g., <200ms for search response).
5. Performance Inspection: Use KingbaseES slow query management to identify bottlenecks in vector distance calculations or text indexing.

Troubleshooting Common Issues

• Stale Results: If search results do not reflect recent updates, check trigger configurations and KFS Ultra synchronization status.
• High Latency: If queries are slow, analyze the execution plan for vector and text indexes. Consider optimizing index types or reducing the number of documents scanned.
• Poor Fusion: If results are irrelevant, review the RRF constant $k$ and the weighting of vector vs. keyword scores.

FAQ

What are the trade-offs between Reciprocal Rank Fusion (RRF) and weighted sum for hybrid search?

RRF is robust to differences in score distributions and does not require normalization of scores from different retrieval streams. Weighted sum requires careful normalization and tuning of weights but can be more interpretable. RRF is often preferred for its simplicity and stability.

How to validate hybrid search accuracy and latency in a production-like environment?

Use the KingbaseES Performance Inspection Platform to monitor query execution times and resource usage. Conduct A/B testing with real user queries to compare the relevance of hybrid search results against keyword-only or vector-only baselines.

What are the common failure modes in hybrid search pipelines and how to troubleshoot them?

Common failures include stale indexes, high latency due to complex fusion, and poor relevance. Troubleshoot by checking trigger configurations, optimizing index types, and validating the RRF implementation. Use slow query logs to identify performance bottlenecks.

💡 More Resources

If you would like to dive deeper into KingbaseES and its application practices across various industries, we have compiled the following official resources to help you get started quickly and develop and operate with efficiency:

• Kingbase Community: A one-stop interactive platform for technical exchanges, Q&A, and experience sharing—join forces with fellow DBAs and developers.
• Kingbase Solutions: One-stop full-stack database migration and cloud-native solutions, supporting smooth migration of multi-source heterogeneous data, ensuring high availability, real-time integration, and sustained high performance.
• Kingbase Case Studies: Real-world user scenarios and implementation outcomes, showcasing KingbaseES’s outstanding capabilities in high availability, high performance, and IT adaptation.
• Kingbase Documentation: Authoritative and comprehensive product manuals and technical guides, covering the entire lifecycle from installation and deployment to development, programming, and operations management.
• Free Download: Get the latest installation packages, drivers, tools, and patches, supporting multiple platforms and domestic chip architectures.
• Digital Construction Encyclopedia: Covers digital strategy planning, data integration, metrics management, database visualization applications, and more to empower enterprise digital transformation.

Open Source Resources:

• GitHub – Kingbase-docs: Kingbase documentation open-source repository—Stars and contributions are welcome.
• Gitee – Kingbase-docs: Domestic mirror repository for Kingbase documentation for faster access.

Welcome to explore the resources above and begin your Kingbase journey!