Building Hybrid Search with PostgreSQL, pgvector, and Citus

Search looks simple from the outside.

A user types something like:

short-range copper module

and expects the system to return the right product, maybe even an exact SKU like:

QDD-2Q200-CU3M

But when you work with a real product catalog, especially something technical like networking hardware, search becomes much harder.

You are not only searching titles. You are searching SKUs, brands, specs, categories, descriptions, compatibility notes, datasheets, and sometimes even weird abbreviations that only people in that industry understand.

For simple websites, keyword search is usually enough. But for a serious catalog with hundreds of thousands of products, keyword search alone starts to break down.

That is where hybrid search becomes interesting.

In this article, I want to explain how I would design a high-performance hybrid search system using:

• PostgreSQL
• pgvector
• full-text search
• HNSW indexes
• Reciprocal Rank Fusion, or RRF
• Citus for scaling later

The goal is not to make the architecture more complicated than necessary. The goal is to keep the system practical, fast, and maintainable.

Why keyword search is not enough

Traditional search usually works by matching words.

For example, if the user searches:

Cisco Catalyst 9300 48-port PoE

keyword search can do a good job because the query contains exact terms like Cisco, Catalyst, 9300, and PoE.

But what happens when the user searches like this?

short range copper module

Maybe the product description does not use those exact words. Maybe it says direct attach cable, DAC, or only contains the SKU and technical specs.

A pure keyword engine may miss good results because the words do not match exactly.

This is the main reason vector search is useful. Vector search is not only looking at exact words. It tries to understand the meaning behind the query.

What pgvector gives us

pgvector allows PostgreSQL to store and search embeddings directly inside the database.

An embedding is basically a list of numbers that represents the meaning of a text.

For example, this text:

Cisco 10G short range SFP transceiver

can be converted by an embedding model into a vector like this:

[0.021, -0.182, 0.441, ...]

Of course, real vectors usually have hundreds or thousands of dimensions.

The nice part is that PostgreSQL can now compare these vectors and find the closest products to the user's query.

So instead of only asking:

Which rows contain these exact words?

we can also ask:

Which rows are semantically close to this query?

This is very powerful for technical catalogs.

Why I still prefer hybrid search

Vector search is powerful, but it is not perfect.

One of its weaknesses is exact matching.

For example, if someone searches:

Catalyst 9300 48-port PoE

a vector search might return products that are semantically close, such as:

• Catalyst 9300 24-port models
• Catalyst 9400 models
• non-PoE switches
• similar Cisco switches

From a semantic point of view, these products are close.

But from a buyer's point of view, they may be wrong.

If the user typed 9300, 48-port, and PoE, those details matter.

This is why I would not build this system with vector search alone.

A better approach is hybrid search:

1. Use PostgreSQL full-text search for exact and lexical matching.
2. Use pgvector for semantic matching.
3. Merge both result lists into one final ranking.

This gives us both precision and recall.

Choosing the distance metric

When we compare vectors, we need a distance metric.

pgvector supports multiple options, but the most common ones are:

• Euclidean distance
• Cosine distance
• Inner product

For text embeddings, cosine similarity is usually a strong default because it focuses more on the direction of the vectors instead of their size.

That matters because a long product description and a short user query can have very different vector magnitudes.

In pgvector, cosine distance uses the <=> operator:

ORDER BY embedding <=> $1

If the embedding model already outputs normalized vectors, inner product can also be a good option because it is usually cheaper to compute.

But for a simple and safe implementation, I would start with cosine distance and benchmark from there.

Picking an embedding model

The embedding model is one of the most important decisions in the whole system.

A generic model may understand normal English very well, but it may not understand the difference between technical networking terms like:

• SMF and MMF
• SFP and QSFP
• DAC and AOC
• 10G, 25G, 40G, and 100G
• similar SKUs that differ by only a few characters

For this kind of catalog, I would choose a model that performs well on technical and retrieval tasks.

Two interesting options are:

• Qwen embedding models
• EmbeddingGemma-style compact embedding models

A large model can give better semantic quality, but it also needs more infrastructure. For many real systems, a smaller model with good retrieval quality is easier to operate.

For a catalog around 40,000 products, I would probably start with a 512-dimensional or 768-dimensional embedding model and measure quality before jumping to a very large model.

The practical question is not only:

Which model has the best benchmark score?

The real question is:

Which model gives good search quality with acceptable latency, storage, and operational cost?

Preparing product data before embedding

One mistake I often see is embedding only the raw description.

For e-commerce search, that is usually not enough.

A product has more useful context:

• SKU
• brand
• category
• product family
• technical specs
• compatibility
• short description
• long description

Before creating the embedding, I would build a clean text payload like this:

Category: Optical Transceivers
Brand: Cisco
SKU: QDD-2Q200-CU3M
Product Family: QSFP-DD
Features: short range copper module, 200G, direct attach cable
Description: ...

This helps the embedding model understand the product better.

It also makes similar products cluster closer together in vector space.

For long descriptions or datasheets, I would not blindly split every 500 characters. That can break important context.

Instead, I would use chunks with overlap.

For example:

• chunk size: 500 to 800 tokens
• overlap: 100 to 150 tokens

The overlap helps preserve context between chunks.

A simple PostgreSQL schema

Here is a simplified schema for this kind of system:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE network_equipment (
    product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(100) UNIQUE NOT NULL,
    manufacturer VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10, 2),
    stock_quantity INT DEFAULT 0,
    description TEXT,

    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(sku, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(manufacturer, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'C')
    ) STORED,

    embedding vector(768)
);

CREATE INDEX idx_network_category
ON network_equipment (category_id);

CREATE INDEX idx_network_search
ON network_equipment
USING GIN (search_vector);

The search_vector column is for PostgreSQL full-text search.

The embedding column is for semantic search with pgvector.

I also like this design because the product data and the vector live in the same database. That means fewer synchronization problems compared to pushing everything into a separate vector database.

Of course, external vector databases can be useful. But for many backend systems, keeping this inside PostgreSQL is simpler and more than enough.

IVFFlat vs HNSW

pgvector supports approximate nearest neighbor indexes.

The two important options are:

• IVFFlat
• HNSW

IVFFlat

IVFFlat works by dividing the vector space into lists or clusters.

It is usually faster to build and smaller in memory, but it has one important problem: it depends heavily on the data distribution at the time the index is created.

If your product catalog changes a lot, recall can degrade over time.

So IVFFlat can be useful, but I would be careful with it in a dynamic e-commerce system.

HNSW

HNSW builds a graph of vectors.

At query time, it navigates through this graph to find close neighbors quickly.

It usually has:

• better recall
• faster queries
• larger index size
• slower index build time

For a 40,000-row catalog, I would usually choose HNSW unless there is a strong memory limitation.

A typical index could look like this:

CREATE INDEX CONCURRENTLY idx_network_embedding_hnsw
ON network_equipment
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

At query time, we can tune recall and latency using:

SET hnsw.ef_search = 64;

Higher ef_search usually means better recall but slower queries.

This is something I would always benchmark with real data.

Hybrid search with RRF

Now comes the important part: merging keyword search and vector search.

The problem is that full-text search and vector search produce different scores.

BM25 or ts_rank_cd scores are not directly comparable with cosine distance.

So instead of trying to add raw scores together, we can use Reciprocal Rank Fusion, or RRF.

RRF uses the rank position of each result, not the raw score.

The idea is simple:

• If a product ranks high in vector search, it gets points.
• If it ranks high in keyword search, it gets points.
• If it ranks high in both, it gets more points.

Here is a simplified query:

WITH vector_search AS (
    SELECT
        product_id,
        ROW_NUMBER() OVER (
            ORDER BY embedding <=> $1::vector
        ) AS rank_vector
    FROM network_equipment
    WHERE category_id = $2
    ORDER BY embedding <=> $1::vector
    LIMIT 100
),
keyword_search AS (
    SELECT
        product_id,
        ROW_NUMBER() OVER (
            ORDER BY ts_rank_cd(
                search_vector,
                websearch_to_tsquery('english', $3)
            ) DESC
        ) AS rank_keyword
    FROM network_equipment
    WHERE search_vector @@ websearch_to_tsquery('english', $3)
      AND category_id = $2
    ORDER BY ts_rank_cd(
        search_vector,
        websearch_to_tsquery('english', $3)
    ) DESC
    LIMIT 100
)
SELECT
    e.product_id,
    e.sku,
    e.manufacturer,
    e.description,
    e.price,
    COALESCE(1.0 / (60 + v.rank_vector), 0.0) +
    COALESCE(1.0 / (60 + k.rank_keyword), 0.0) AS rrf_score
FROM vector_search v
FULL OUTER JOIN keyword_search k
    ON v.product_id = k.product_id
JOIN network_equipment e
    ON e.product_id = COALESCE(v.product_id, k.product_id)
ORDER BY rrf_score DESC
LIMIT 15;

This query does two searches:

1. Finds the top semantic matches using pgvector.
2. Finds the top keyword matches using PostgreSQL full-text search.

Then it combines them using RRF.

The result is usually much better than using only one search method.

Filtering matters a lot

One thing that can make or break performance is filtering.

If the user is clearly searching inside one category, do not run vector search across the whole table.

For example, if the query is about transceivers, there is no reason to compare it against server racks or firewalls.

This is why I would always try to apply filters before ranking:

WHERE category_id = $2

In larger systems, I would also consider partitioning by category or tenant.

This reduces the search space and keeps latency more predictable.

What about Citus?

For 40,000 products, a single well-tuned PostgreSQL instance can be enough.

But if the catalog grows to tens of millions of products, or if the system becomes multi-tenant, we may need horizontal scaling.

This is where Citus can help.

Citus distributes PostgreSQL tables across worker nodes.

For example:

SELECT create_distributed_table('network_equipment', 'product_id');

With this setup, each worker can search its own shard, and the coordinator can merge the results.

For multi-tenant systems, distributing by tenant_id may be even better because most queries are usually scoped to one tenant.

The important point is that we can start simple with PostgreSQL and pgvector, then scale out with Citus when the data size really requires it.

My practical recommendation

If I were building this for a real technical e-commerce catalog, I would start with this architecture:

• PostgreSQL as the main database
• pgvector for semantic search
• PostgreSQL full-text search for exact keyword search
• HNSW index for vector search
• RRF to merge vector and keyword results
• metadata-enriched embedding text
• category or tenant filtering before ranking
• Citus only when a single node is no longer enough

This gives a good balance between performance, search quality, and operational simplicity.

I would not start with a complicated distributed system on day one.

First, I would make the search quality good on one node. Then I would benchmark with real queries, real products, and real user behavior.

Only after that, I would scale the architecture.

Conclusion

Hybrid search is one of the most practical ways to improve product discovery.

Keyword search gives us precision.

Vector search gives us semantic understanding.

RRF gives us a clean way to combine both without fighting with incompatible scoring systems.

And pgvector makes the whole architecture easier because we can keep product data, metadata, full-text search, and embeddings inside PostgreSQL.

For a technical catalog like networking hardware, this approach is especially useful because users often search in different ways:

• exact SKUs
• product families
• technical specs
• short descriptions
• vague concepts
• compatibility terms

A good search engine should handle all of them.

For me, the best architecture is not the one with the most tools. It is the one that gives accurate results, keeps latency low, and stays simple enough to operate in production.