Hybrid search with PostgreSQL, pgvector and TypeScript

In the last post I used vector search to find notes by meaning. This post is about searching products in a shop. I tried vector search on its own for that and it wasn’t enough. Normal keyword search on its own wasn’t enough either. Using both together worked much better. That is called hybrid search.

This post explains what the two kinds of search do, where each one fails, and how to combine them in PostgreSQL. At the end there is one TypeScript file with a products table, the search query, and a small HTTP API. No LangChain and no separate search server.

Keyword search

Keyword search looks for the words you typed. Postgres has this built in. It’s called full-text search. It does a bit more than a plain LIKE query. It makes everything lowercase. It removes common words like “the” and “and”. And it cuts words down to their root, so “refurbished” and “refurbish” count as the same word.

Here is what it does with one product description:

SELECT to_tsvector('english',
  'The most affordable iPhone. A15 Bionic chip, Touch ID and 5G in a compact body.');

'5g':11 'a15':5 'afford':3 'bionic':6 'bodi':15 'chip':7 'compact':14 'id':9 'iphon':4 'touch':8

This is a tsvector. It is the list of word roots and the position of each one. “The”, “most”, “and”, “in” and “a” are gone. “affordable” became “afford”. “iPhone” became “iphon”. The search text goes through the same steps. So if someone types “iPhones”, it becomes “iphon” and it matches.

Keyword search is good at exact things: names, model numbers, SKUs. What it can’t do is understand meaning. To keyword search, “iPhone” and “Apple smartphone” have nothing to do with each other.

Vector search

Vector search compares meaning. You send text to an embedding model and get back a list of numbers. Texts that mean similar things get similar numbers. You store one list per product. When someone searches, you turn the search text into numbers the same way and look for the products whose numbers are closest.

I explained embeddings and pgvector in the last post, so I’ll keep it short here. embedding <=> $1::vector gives the distance between two vectors. 0 means the same meaning. Around 1 means unrelated. Sorting by it gives you the closest products first.

Vector search is good at meaning. It is bad at exact things. “iPhone” and “Pixel” are both phones, so they look similar to it. And a SKU like PH-IP13R has no meaning, so vector search can’t do anything useful with it.

Where each one fails

The example below has ten products: seven phones, two accessories and an Apple Watch. Here is what each search does with a few queries. The keyword column is real output from the code in this post. The vector column is what I got with the embedding model I used.

Keyword search fails when the words don’t match. Vector search fails when you need an exact match. I didn’t want to use either one on its own.

Hybrid search

Hybrid search means running both searches and combining the two result lists. You take the top 20 from keyword search and the top 20 from vector search. Each product gets a score based on its position in each list. Then you sort by that score. A product that is high in both lists comes first. A product that only one search found still shows up, just lower down.

The combining step is called reciprocal rank fusion. It’s a small formula and I’ll show it below.

Postgres is a good place to do this because both searches can run in the same database. Full-text search is built in. pgvector adds vector search to the same table. So the combining step is a normal JOIN in one query, and the same WHERE filters apply to both sides. There is no second search server to run and keep in sync.

                        "cheap iphone"
                              │
                ┌─────────────┴─────────────┐
                ▼                           ▼
     websearch_to_tsquery            embeddings API
     'cheap' | 'iphon'               [0.012, -0.031, ...]
                │                           │
                ▼                           ▼
     search_vector @@ query          embedding <=> $2::vector
     ORDER BY ts_rank_cd DESC        ORDER BY distance
     LIMIT 20      (GIN index)       LIMIT 20      (HNSW index)
                │                           │
                └─────────────┬─────────────┘
                              ▼
                FULL OUTER JOIN on product id
          score = 1/(60 + keyword_rank) + 1/(60 + vector_rank)
                  ORDER BY score DESC LIMIT 10
                              │
                              ▼
                        JSON response

What you need

• Node.js 22.18 or newer. It runs .ts files directly, so there is no build step and no tsconfig.json. Check with node --version.
• Docker, to run Postgres with pgvector locally.
• An OpenRouter API key from openrouter.ai. Only the embedding model is used. Embedding ten products costs a fraction of a cent.
• Two npm packages, pg and openai, plus their types.

Step 1: Start Postgres

docker run --name shop-postgres -p 5432:5432 \
  -e POSTGRES_PASSWORD=postgres \
  -v "$(pwd)/pgdata:/var/lib/postgresql/data" \
  pgvector/pgvector:pg17

The pgvector/pgvector image is normal Postgres with the extension already installed. The -v part keeps your data in a pgdata folder so it survives restarts. To look inside later:

docker exec -it shop-postgres psql -U postgres

Step 2: Set up the project

mkdir hybrid-search && cd hybrid-search
npm init -y
npm pkg set type=module
npm install pg openai
npm install -D @types/pg @types/node

The types are only for your editor. Node removes them and runs the JavaScript. It doesn’t check them. If you want a real check, run npx tsc --noEmit --strict server.ts.

The settings go in a .env file this time. The finished thing is a server, and I don’t want a key inside a server file.

# .env
OPENROUTER_API_KEY=sk-or-v1-your-key-here
DATABASE_URL=postgres://postgres:postgres@localhost:5432/postgres

Add .env to .gitignore. Node can load it with node --env-file=.env server.ts. Nothing to install.

The pieces

Everything below ends up in one file, server.ts. Here it is one piece at a time.

The table

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS products (
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  sku text NOT NULL UNIQUE,
  name text NOT NULL,
  description text NOT NULL,
  category text NOT NULL,
  price_cents integer NOT NULL,
  embedding vector(1536),
  embedding_hash text,
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', sku), 'A') ||
    setweight(to_tsvector('english', name), 'A') ||
    setweight(to_tsvector('english', description), 'B')
  ) STORED
);

CREATE INDEX IF NOT EXISTS products_search_vector_idx
  ON products USING gin (search_vector);

CREATE INDEX IF NOT EXISTS products_embedding_idx
  ON products USING hnsw (embedding vector_cosine_ops);

Most of this is a normal products table. Three columns are special.

embedding holds the product’s vector. It can be NULL. A product gets inserted first and embedded later by a separate job. Until then, keyword search can still find it.

embedding_hash is how that job knows which products still need embedding. I’ll explain it in a moment.

search_vector holds the data for full-text search. It is a generated column. Postgres fills it in from the other columns every time a row is inserted or updated. So it is never out of date, and the application never has to think about it. setweight marks which field a word came from. A word in the name or SKU (weight A) counts more than a word in the description (weight B). The SKU uses the simple config, which only lowercases. I don’t want the stemmer changing part numbers.

Then there are two indexes. A GIN index for the tsvector, which is the normal index for full-text search. And an HNSW index for the vectors. With ten products Postgres won’t use either of them. It will just scan the table, and that is fine. They are there for when the table grows.

Products

The ten products are an array in the file. They get inserted with an upsert on sku:

await pool.query(
  `INSERT INTO products (sku, name, description, category, price_cents)
   VALUES ($1, $2, $3, $4, $5)
   ON CONFLICT (sku) DO UPDATE SET
     name = EXCLUDED.name,
     description = EXCLUDED.description,
     category = EXCLUDED.category,
     price_cents = EXCLUDED.price_cents`,
  [product.sku, product.name, product.description, product.category, product.priceCents],
);

If you run it twice you still have ten products. If you change a description in the array and run it again, that row gets updated. That matters for the next part.

Embedding the products

async function embed(texts: string[]): Promise<number[][]> {
  const response = await openrouter.embeddings.create({
    model: "openai/text-embedding-3-small",
    input: texts,
  });
  return response.data.map((entry) => entry.embedding);
}

This is the same helper as in the last post. input takes an array, so many products go to OpenRouter in one request.

The harder question is which products to embed. Embedding costs money and takes time. I only want to embed products that are new or that have changed. I use a hash for that:

SELECT id, name, description, category,
       md5(name || ' ' || description || ' ' || category) AS content_hash
FROM products
WHERE embedding IS NULL
   OR embedding_hash IS DISTINCT FROM md5(name || ' ' || description || ' ' || category)

When a product gets embedded, I save the hash of its text in embedding_hash. The next time the job runs, it computes the hash again from the current text and compares. If the hash is the same, nothing changed. If it is different, someone edited the product, so it gets embedded again. IS DISTINCT FROM is like != but it also works when one side is NULL. There are no triggers, no timestamps and no queue. You can run the job as often as you like.

The text I embed is the name, the category and the description joined together. I include the category because “phones” says a lot about what the product is.

The keyword search

SELECT name, ts_rank_cd(search_vector, query) AS score
FROM products, websearch_to_tsquery('english', 'cheap or iphone') AS query
WHERE search_vector @@ query
ORDER BY score DESC;

Three new things here.

websearch_to_tsquery turns the search text into a tsquery. It uses the same stemming as the tsvector. It also never throws an error on strange input, which is good for a search box.

@@ means “matches”.

ts_rank_cd gives each match a score. The score is higher when more of the search words appear, when they are close together, and when they are in a field with a higher weight.

The or in the search text is on purpose. By default websearch_to_tsquery joins words with AND. So cheap iphone becomes 'cheap' & 'iphon', and only products with both words match. That is too strict for us. It is also exactly why the phone case was the only result. In the code, a small function turns cheap iphone into cheap or iphone, which becomes 'cheap' | 'iphon'. Now any product with either word is a candidate. Products with both words still get a higher score.

Here is the result for the ten products:

             name                  |  score
-----------------------------------+--------
 iPhone 15 Silicone Case           |   1.8
 iPhone SE (3rd generation) 64GB   |   1.4
 iPhone 15 Pro 128GB               |   1.0
 iPhone 13 128GB (Refurbished)     |   1.0
 USB-C to Lightning Cable, 1m      |   0.4
 Apple Watch Series 9              |   0.4

The case is first. It is the only product with both words. The Pixel and the Moto are missing. They are cheap phones, but they don’t say “iPhone”. The scores are only useful for sorting. The numbers themselves don’t mean much. That matters later.

The vector search

const [queryEmbedding] = await embed(["cheap iphone"]);

SELECT name, embedding <=> $1::vector AS distance
FROM products
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1::vector
LIMIT 20;

The search text is embedded with the same model as the products. Then the vector goes in as $1. It is the same query as in the last post. This is what I got:

             name                  | distance
-----------------------------------+----------
 iPhone SE (3rd generation) 64GB   |   0.31
 iPhone 13 128GB (Refurbished)     |   0.33
 Google Pixel 8a                   |   0.41
 Motorola Moto G Power             |   0.43
 iPhone 15 Pro 128GB               |   0.44
 iPhone 15 Silicone Case           |   0.47
 USB-C to Lightning Cable, 1m      |   0.52
 Samsung Galaxy S24                |   0.53
 Apple Watch Series 9              |   0.55
 Fairphone 5                       |   0.58

This is better. The two cheap iPhones are on top and the case is sixth. But look at positions three and four. The model saw “cheap” and “phone” and picked the Pixel and the Moto. They are cheap phones, so it’s not wrong. It just doesn’t treat the word “iPhone” as strictly as a person would.

Also, every product is in the list. Vector search always returns something, because there is always a closest product. That is useful when keyword search finds nothing. But it also means a bad match can look like a real one.

Combining the two lists

Now I have two lists and want one. Adding the scores doesn’t work. 1.8 from ts_rank_cd and 0.31 from cosine distance are different kinds of numbers. For one, high is good. For the other, low is good. And neither one is comparable between different searches.

Reciprocal rank fusion ignores the scores and only uses the positions. For each list a product appears in, it gets:

1 / (60 + rank)

The final score is the sum. Rank 1 gives 1/61, rank 2 gives 1/62, and so on. If a product is missing from a list, that part is 0. The 60 comes from the paper that introduced this method. It keeps first place from being worth much more than second place. Here is the calculation for “cheap iphone” with the two lists above:

The two cheap iPhones are first and second. Neither one was first in both lists. They won because they were near the top of both. The case was first in keyword search but only sixth in vector search, so it drops to third. The Pixel and the Moto were only in the vector list, so they end up at the bottom.

That is the whole idea. A product that both searches like ranks above a product that only one search likes.

The query

Here is the same thing in SQL. It is the longest query in the post, so I go through it part by part below.

WITH keyword_matches AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY keyword_score DESC, id) AS keyword_rank
  FROM (
    SELECT id, ts_rank_cd(search_vector, query) AS keyword_score
    FROM products, websearch_to_tsquery('english', $1) AS query
    WHERE search_vector @@ query
      AND ($3::text IS NULL OR category = $3::text)
      AND ($4::integer IS NULL OR price_cents <= $4::integer)
    ORDER BY keyword_score DESC, id
    LIMIT 20
  ) AS best_keyword_matches
),
semantic_matches AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY distance) AS semantic_rank
  FROM (
    SELECT id, embedding <=> $2::vector AS distance
    FROM products
    WHERE embedding IS NOT NULL
      AND ($3::text IS NULL OR category = $3::text)
      AND ($4::integer IS NULL OR price_cents <= $4::integer)
    ORDER BY embedding <=> $2::vector
    LIMIT 20
  ) AS nearest_products
)
SELECT
  products.id,
  products.name,
  products.category,
  products.price_cents,
  (
    COALESCE(1.0 / (60 + keyword_rank), 0) +
    COALESCE(1.0 / (60 + semantic_rank), 0)
  )::float8 AS score,
  keyword_rank::integer,
  semantic_rank::integer
FROM keyword_matches
FULL OUTER JOIN semantic_matches USING (id)
JOIN products USING (id)
ORDER BY score DESC, products.id
LIMIT $5;

It has two CTEs and a join.

keyword_matches is the keyword search from before, with one more layer. The inner query finds the 20 best keyword matches. The outer query numbers them 1 to 20 with ROW_NUMBER(). I do it in two layers so the LIMIT happens before the numbering. Postgres then only ranks 20 rows, not the whole table.

semantic_matches does the same for the vector search. The inner query is the one from the last post. I kept it in exactly that form because that is the pattern the HNSW index can use: ORDER BY embedding <=> ... LIMIT n.

The $3 and $4 lines are the filters. They are in both CTEs, and that is needed. If you filtered after the join, you would only filter the 20 candidates each side already picked. A search for “cheap iphone” in accessories under $50 could come back empty even though there is a matching product at position 21. So filter first, then take the top 20. The $3::text IS NULL OR ... form means a NULL parameter turns the filter off. The SQL string itself never changes.

Then the join. FULL OUTER JOIN ... USING (id) gives every product that is in either list. Each one has the rank from the list it was in and NULL for the other. USING also merges the two id columns into one. The next JOIN products USING (id) uses that to fetch the name and price.

The score line is the formula from above. COALESCE(..., 0) turns a missing rank into 0. The ::float8 and ::integer casts are for node-postgres. ROW_NUMBER() returns a bigint and 1.0 / x is a numeric. The driver returns both as strings so no precision is lost. With the casts they come back as normal JavaScript numbers.

Note

Taking 20 candidates from each side is more than the 10 results the API returns. That is on purpose. A product at keyword rank 12 and vector rank 9 can still end up in the top 10 once the scores are added. With fewer candidates you lose that. With many more, you start ranking products that neither search thought were relevant.

The API

The HTTP part uses Node’s built-in http module. There is one route, so I didn’t add a framework.

const server = http.createServer(async (request, response) => {
  const url = new URL(request.url ?? "/", `http://${request.headers.host ?? "localhost"}`);
  // ... read ?q=, ?category=, ?maxPrice=, ?limit=
  const results = await hybridSearch(searchRequest);
  response.writeHead(200, { "content-type": "application/json" });
  response.end(JSON.stringify({ query, took_ms, results }, null, 2));
});

The full version checks the parameters, returns 400 when q is missing, caps limit at 50, and turns maxPrice=500 into cents. It’s plain code and there isn’t much to explain about it.

The whole thing: one file

Here is the full file. Save it as server.ts next to your .env. It is one file so you can read it from top to bottom. The comment lines are where I would split it into db.ts, embeddings.ts, search.ts and server.ts if it grew.

// server.ts
import http from "node:http";
import { Pool } from "pg";
import OpenAI from "openai";

// ---------------------------------------------------------------------------
// Config. Run with: node --env-file=.env server.ts <command>
// ---------------------------------------------------------------------------

const DATABASE_URL =
  process.env.DATABASE_URL ?? "postgres://postgres:postgres@localhost:5432/postgres";
const OPENROUTER_API_KEY = process.env.OPENROUTER_API_KEY;
const PORT = Number(process.env.PORT ?? 3000);

const EMBEDDING_MODEL = "openai/text-embedding-3-small";
const EMBEDDING_DIMENSIONS = 1536;
const CANDIDATES_PER_METHOD = 20; // how deep each search goes before fusing
const RRF_K = 60; // smoothing constant from the reciprocal rank fusion paper
const MAX_LIMIT = 50;

if (!OPENROUTER_API_KEY) {
  console.error("OPENROUTER_API_KEY is not set. Put it in .env and run with --env-file=.env");
  process.exit(1);
}

const pool = new Pool({ connectionString: DATABASE_URL });

const openrouter = new OpenAI({
  baseURL: "https://openrouter.ai/api/v1",
  apiKey: OPENROUTER_API_KEY,
});

// ---------------------------------------------------------------------------
// Schema
// ---------------------------------------------------------------------------

const SCHEMA_SQL = `
  CREATE EXTENSION IF NOT EXISTS vector;

  CREATE TABLE IF NOT EXISTS products (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sku text NOT NULL UNIQUE,
    name text NOT NULL,
    description text NOT NULL,
    category text NOT NULL,
    price_cents integer NOT NULL,
    embedding vector(${EMBEDDING_DIMENSIONS}),
    embedding_hash text,
    search_vector tsvector GENERATED ALWAYS AS (
      setweight(to_tsvector('simple', sku), 'A') ||
      setweight(to_tsvector('english', name), 'A') ||
      setweight(to_tsvector('english', description), 'B')
    ) STORED
  );

  CREATE INDEX IF NOT EXISTS products_search_vector_idx
    ON products USING gin (search_vector);

  CREATE INDEX IF NOT EXISTS products_embedding_idx
    ON products USING hnsw (embedding vector_cosine_ops);

  CREATE INDEX IF NOT EXISTS products_category_idx
    ON products (category);
`;

const SEED_PRODUCTS = [
  {
    sku: "PH-IP15P",
    name: "iPhone 15 Pro 128GB",
    category: "phones",
    priceCents: 99900,
    description:
      "Apple's flagship with the A17 Pro chip, a titanium frame and a 48MP camera system.",
  },
  {
    sku: "PH-IP13R",
    name: "iPhone 13 128GB (Refurbished)",
    category: "phones",
    priceCents: 44900,
    description:
      "Certified refurbished Apple smartphone with a new battery and a one-year warranty. A budget way into iOS.",
  },
  {
    sku: "PH-IPSE3",
    name: "iPhone SE (3rd generation) 64GB",
    category: "phones",
    priceCents: 42900,
    description:
      "The most affordable iPhone. A15 Bionic chip, Touch ID and 5G in a compact body.",
  },
  {
    sku: "PH-GS24",
    name: "Samsung Galaxy S24",
    category: "phones",
    priceCents: 79900,
    description:
      "Samsung's flagship Android phone with a 6.2-inch display and Galaxy AI features.",
  },
  {
    sku: "PH-PX8A",
    name: "Google Pixel 8a",
    category: "phones",
    priceCents: 49900,
    description:
      "Google's affordable Android phone with the Tensor G3 chip and seven years of updates.",
  },
  {
    sku: "PH-MGP",
    name: "Motorola Moto G Power",
    category: "phones",
    priceCents: 19900,
    description: "Low-cost Android phone with a two-day battery. Great value for basic use.",
  },
  {
    sku: "PH-FP5",
    name: "Fairphone 5",
    category: "phones",
    priceCents: 69900,
    description: "Modular, repairable Android smartphone built from fair-trade materials.",
  },
  {
    sku: "AC-IP15C",
    name: "iPhone 15 Silicone Case",
    category: "accessories",
    priceCents: 4900,
    description: "A cheap way to protect your iPhone 15. Soft-touch silicone in six colours.",
  },
  {
    sku: "AC-LTC",
    name: "USB-C to Lightning Cable, 1m",
    category: "accessories",
    priceCents: 1900,
    description: "Charging and sync cable for iPhone and iPad.",
  },
  {
    sku: "WT-AW9",
    name: "Apple Watch Series 9",
    category: "wearables",
    priceCents: 39900,
    description:
      "Apple's smartwatch. Pairs with your iPhone for calls, messages and health tracking.",
  },
];

async function setup(): Promise<void> {
  await pool.query(SCHEMA_SQL);

  for (const product of SEED_PRODUCTS) {
    await pool.query(
      `INSERT INTO products (sku, name, description, category, price_cents)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (sku) DO UPDATE SET
         name = EXCLUDED.name,
         description = EXCLUDED.description,
         category = EXCLUDED.category,
         price_cents = EXCLUDED.price_cents`,
      [product.sku, product.name, product.description, product.category, product.priceCents],
    );
  }

  console.log(`Schema ready. ${SEED_PRODUCTS.length} products upserted.`);
}

// ---------------------------------------------------------------------------
// Embeddings
// ---------------------------------------------------------------------------

async function embed(texts: string[]): Promise<number[][]> {
  const response = await openrouter.embeddings.create({
    model: EMBEDDING_MODEL,
    input: texts,
  });
  return response.data.map((entry) => entry.embedding);
}

type ProductToEmbed = {
  id: number;
  name: string;
  description: string;
  category: string;
  content_hash: string;
};

// The text the embedding model sees. Say what a shopper would say.
function embeddingTextFor(product: ProductToEmbed): string {
  return `${product.name}. ${product.category}. ${product.description}`;
}

function splitIntoBatches<Item>(items: Item[], batchSize: number): Item[][] {
  const batches: Item[][] = [];
  for (let start = 0; start < items.length; start += batchSize) {
    batches.push(items.slice(start, start + batchSize));
  }
  return batches;
}

// Embed every product that has no embedding yet, or whose text has changed
// since it was last embedded. Safe to run as often as you like.
async function syncEmbeddings(): Promise<void> {
  const { rows: staleProducts } = await pool.query<ProductToEmbed>(`
    SELECT id, name, description, category,
           md5(name || ' ' || description || ' ' || category) AS content_hash
    FROM products
    WHERE embedding IS NULL
       OR embedding_hash IS DISTINCT FROM md5(name || ' ' || description || ' ' || category)
  `);

  if (staleProducts.length === 0) {
    console.log("All embeddings are up to date.");
    return;
  }

  for (const batch of splitIntoBatches(staleProducts, 100)) {
    const embeddings = await embed(batch.map(embeddingTextFor));

    for (const [position, product] of batch.entries()) {
      await pool.query(
        "UPDATE products SET embedding = $1::vector, embedding_hash = $2 WHERE id = $3",
        [JSON.stringify(embeddings[position]), product.content_hash, product.id],
      );
    }
  }

  console.log(`Embedded ${staleProducts.length} products.`);
}

// ---------------------------------------------------------------------------
// Hybrid search
// ---------------------------------------------------------------------------

type SearchRequest = {
  searchText: string;
  category: string | null;
  maxPriceCents: number | null;
  limit: number;
};

type SearchResult = {
  id: number;
  name: string;
  category: string;
  price_cents: number;
  score: number;
  keyword_rank: number | null;
  semantic_rank: number | null;
};

// "cheap iphone" -> "cheap or iphone". Any word may match; products that
// match more of them still rank higher.
function toKeywordQuery(searchText: string): string {
  return searchText.split(/\s+/).filter(Boolean).join(" or ");
}

const HYBRID_SEARCH_SQL = `
  WITH keyword_matches AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY keyword_score DESC, id) AS keyword_rank
    FROM (
      SELECT id, ts_rank_cd(search_vector, query) AS keyword_score
      FROM products, websearch_to_tsquery('english', $1) AS query
      WHERE search_vector @@ query
        AND ($3::text IS NULL OR category = $3::text)
        AND ($4::integer IS NULL OR price_cents <= $4::integer)
      ORDER BY keyword_score DESC, id
      LIMIT ${CANDIDATES_PER_METHOD}
    ) AS best_keyword_matches
  ),
  semantic_matches AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY distance) AS semantic_rank
    FROM (
      SELECT id, embedding <=> $2::vector AS distance
      FROM products
      WHERE embedding IS NOT NULL
        AND ($3::text IS NULL OR category = $3::text)
        AND ($4::integer IS NULL OR price_cents <= $4::integer)
      ORDER BY embedding <=> $2::vector
      LIMIT ${CANDIDATES_PER_METHOD}
    ) AS nearest_products
  )
  SELECT
    products.id,
    products.name,
    products.category,
    products.price_cents,
    (
      COALESCE(1.0 / (${RRF_K} + keyword_rank), 0) +
      COALESCE(1.0 / (${RRF_K} + semantic_rank), 0)
    )::float8 AS score,
    keyword_rank::integer,
    semantic_rank::integer
  FROM keyword_matches
  FULL OUTER JOIN semantic_matches USING (id)
  JOIN products USING (id)
  ORDER BY score DESC, products.id
  LIMIT $5
`;

async function hybridSearch(searchRequest: SearchRequest): Promise<SearchResult[]> {
  const [queryEmbedding] = await embed([searchRequest.searchText]);

  const { rows } = await pool.query<SearchResult>(HYBRID_SEARCH_SQL, [
    toKeywordQuery(searchRequest.searchText),
    JSON.stringify(queryEmbedding),
    searchRequest.category,
    searchRequest.maxPriceCents,
    searchRequest.limit,
  ]);

  return rows;
}

// Print the keyword list, the vector list and the fused list side by side.
async function compare(searchText: string): Promise<void> {
  const results = await hybridSearch({
    searchText,
    category: null,
    maxPriceCents: null,
    limit: CANDIDATES_PER_METHOD * 2,
  });

  const keywordList = results
    .filter((result) => result.keyword_rank !== null)
    .sort((left, right) => (left.keyword_rank ?? 0) - (right.keyword_rank ?? 0))
    .map((result) => ({ rank: result.keyword_rank, name: result.name }));

  const semanticList = results
    .filter((result) => result.semantic_rank !== null)
    .sort((left, right) => (left.semantic_rank ?? 0) - (right.semantic_rank ?? 0))
    .map((result) => ({ rank: result.semantic_rank, name: result.name }));

  const fusedList = results.map((result) => ({
    score: result.score.toFixed(5),
    keyword: result.keyword_rank ?? "-",
    vector: result.semantic_rank ?? "-",
    name: result.name,
  }));

  console.log(`\nKeyword search for "${searchText}":`);
  console.table(keywordList);
  console.log("\nVector search:");
  console.table(semanticList);
  console.log("\nHybrid (reciprocal rank fusion):");
  console.table(fusedList);
}

// ---------------------------------------------------------------------------
// HTTP API
// ---------------------------------------------------------------------------

function parseSearchRequest(url: URL): SearchRequest | { error: string } {
  const searchText = url.searchParams.get("q")?.trim() ?? "";
  if (searchText === "") {
    return { error: "Missing ?q= search text" };
  }

  const maxPrice = url.searchParams.get("maxPrice");
  const maxPriceCents = maxPrice === null ? null : Math.round(Number(maxPrice) * 100);
  if (maxPriceCents !== null && !Number.isFinite(maxPriceCents)) {
    return { error: "maxPrice must be a number" };
  }

  const requestedLimit = Number(url.searchParams.get("limit") ?? 10);
  const limit =
    Number.isInteger(requestedLimit) && requestedLimit > 0
      ? Math.min(requestedLimit, MAX_LIMIT)
      : 10;

  return {
    searchText,
    category: url.searchParams.get("category"),
    maxPriceCents,
    limit,
  };
}

function serve(): void {
  const server = http.createServer(async (request, response) => {
    const url = new URL(request.url ?? "/", `http://${request.headers.host ?? "localhost"}`);

    const sendJson = (status: number, body: unknown): void => {
      response.writeHead(status, { "content-type": "application/json" });
      response.end(JSON.stringify(body, null, 2));
    };

    if (request.method !== "GET" || url.pathname !== "/search") {
      sendJson(404, { error: "Not found" });
      return;
    }

    const searchRequest = parseSearchRequest(url);
    if ("error" in searchRequest) {
      sendJson(400, searchRequest);
      return;
    }

    try {
      const startedAt = performance.now();
      const results = await hybridSearch(searchRequest);
      sendJson(200, {
        query: searchRequest.searchText,
        took_ms: Math.round(performance.now() - startedAt),
        results,
      });
    } catch (error) {
      console.error(error);
      sendJson(500, { error: "Search failed" });
    }
  });

  server.listen(PORT, () => {
    console.log(`Search API on http://localhost:${PORT}/search?q=cheap+iphone`);
  });
}

// ---------------------------------------------------------------------------
// Command line
// ---------------------------------------------------------------------------

async function main(): Promise<void> {
  const [command, argument] = process.argv.slice(2);

  switch (command) {
    case "setup":
      await setup();
      break;
    case "embed":
      await syncEmbeddings();
      break;
    case "compare":
      if (!argument) {
        console.error('Usage: node --env-file=.env server.ts compare "cheap iphone"');
        process.exit(1);
      }
      await compare(argument);
      break;
    case "serve":
      serve();
      return; // keep the pool open for the server
    default:
      console.error("Usage: node --env-file=.env server.ts setup | embed | compare <text> | serve");
      process.exit(1);
  }

  await pool.end();
}

main().catch((error) => {
  console.error(error);
  process.exit(1);
});

Running it

There are four commands. The first two set up the data. The third is for looking at the results. The fourth starts the server.

$ node --env-file=.env server.ts setup
Schema ready. 10 products upserted.

$ node --env-file=.env server.ts embed
Embedded 10 products.

$ node --env-file=.env server.ts embed
All embeddings are up to date.

The second embed did nothing because all the hashes matched. If you change a description in SEED_PRODUCTS and run setup and embed again, only that one product gets embedded again.

compare runs the hybrid search and prints the three lists. It is the easiest way to see what the combining step does:

$ node --env-file=.env server.ts compare "cheap iphone"

Keyword search for "cheap iphone":
┌─────────┬──────┬───────────────────────────────────┐
│ (index) │ rank │ name                              │
├─────────┼──────┼───────────────────────────────────┤
│ 0       │ 1    │ 'iPhone 15 Silicone Case'         │
│ 1       │ 2    │ 'iPhone SE (3rd generation) 64GB' │
│ 2       │ 3    │ 'iPhone 15 Pro 128GB'             │
│ 3       │ 4    │ 'iPhone 13 128GB (Refurbished)'   │
│ 4       │ 5    │ 'USB-C to Lightning Cable, 1m'    │
│ 5       │ 6    │ 'Apple Watch Series 9'            │
└─────────┴──────┴───────────────────────────────────┘

Vector search:
┌─────────┬──────┬───────────────────────────────────┐
│ (index) │ rank │ name                              │
├─────────┼──────┼───────────────────────────────────┤
│ 0       │ 1    │ 'iPhone SE (3rd generation) 64GB' │
│ 1       │ 2    │ 'iPhone 13 128GB (Refurbished)'   │
│ 2       │ 3    │ 'Google Pixel 8a'                 │
│ 3       │ 4    │ 'Motorola Moto G Power'           │
│ 4       │ 5    │ 'iPhone 15 Pro 128GB'             │
│ 5       │ 6    │ 'iPhone 15 Silicone Case'         │
│ 6       │ 7    │ 'USB-C to Lightning Cable, 1m'    │
│ 7       │ 8    │ 'Samsung Galaxy S24'              │
│ 8       │ 9    │ 'Apple Watch Series 9'            │
│ 9       │ 10   │ 'Fairphone 5'                     │
└─────────┴──────┴───────────────────────────────────┘

Hybrid (reciprocal rank fusion):
┌─────────┬───────────┬─────────┬────────┬───────────────────────────────────┐
│ (index) │ score     │ keyword │ vector │ name                              │
├─────────┼───────────┼─────────┼────────┼───────────────────────────────────┤
│ 0       │ '0.03252' │ 2       │ 1      │ 'iPhone SE (3rd generation) 64GB' │
│ 1       │ '0.03175' │ 4       │ 2      │ 'iPhone 13 128GB (Refurbished)'   │
│ 2       │ '0.03154' │ 1       │ 6      │ 'iPhone 15 Silicone Case'         │
│ 3       │ '0.03126' │ 3       │ 5      │ 'iPhone 15 Pro 128GB'             │
│ 4       │ '0.03031' │ 5       │ 7      │ 'USB-C to Lightning Cable, 1m'    │
│ 5       │ '0.02964' │ 6       │ 9      │ 'Apple Watch Series 9'            │
│ 6       │ '0.01587' │ '-'     │ 3      │ 'Google Pixel 8a'                 │
│ 7       │ '0.01563' │ '-'     │ 4      │ 'Motorola Moto G Power'           │
│ 8       │ '0.01471' │ '-'     │ 8      │ 'Samsung Galaxy S24'              │
│ 9       │ '0.01429' │ '-'     │ 10     │ 'Fairphone 5'                     │
└─────────┴───────────┴─────────┴────────┴───────────────────────────────────┘

Then the server:

$ node --env-file=.env server.ts serve
Search API on http://localhost:3000/search?q=cheap+iphone

curl "localhost:3000/search?q=cheap+iphone&limit=3"

{
  "query": "cheap iphone",
  "took_ms": 214,
  "results": [
    {
      "id": 3,
      "name": "iPhone SE (3rd generation) 64GB",
      "category": "phones",
      "price_cents": 42900,
      "score": 0.032522,
      "keyword_rank": 2,
      "semantic_rank": 1
    },
    {
      "id": 2,
      "name": "iPhone 13 128GB (Refurbished)",
      "category": "phones",
      "price_cents": 44900,
      "score": 0.031754,
      "keyword_rank": 4,
      "semantic_rank": 2
    },
    {
      "id": 8,
      "name": "iPhone 15 Silicone Case",
      "category": "accessories",
      "price_cents": 4900,
      "score": 0.031545,
      "keyword_rank": 1,
      "semantic_rank": 6
    }
  ]
}

The case is still third. Both searches think it is relevant, and it is. It is cheap and it is for an iPhone. But it is not a phone, and nothing in the query says “phone”. A filter is the right tool for that:

curl "localhost:3000/search?q=cheap+iphone&category=phones&maxPrice=500"

iPhone SE (3rd generation) 64GB     keyword 1   vector 1
iPhone 13 128GB (Refurbished)       keyword 2   vector 2
Google Pixel 8a                     keyword -   vector 3
Motorola Moto G Power               keyword -   vector 4

Two iPhones, then two cheap Android phones that only the vector search found. In a real shop I would let the user pick the category from a list instead of guessing it from the text.

I keep keyword_rank and semantic_rank in the API response on purpose. When a result looks wrong, they show which of the two searches put it there.

What’s going on

main looks at the first argument. setup and embed run and then close the pool. serve returns without closing it, because the server needs it.

setup runs the schema and upserts the products. Both can be run again and again, so it also works as a migration script.

syncEmbeddings is the hash comparison from earlier. It fetches only the stale rows, embeds them in batches of 100, and writes back each vector together with its hash. The hash is computed before the embedding call, not after. That’s on purpose. If someone edits a product while the job is running, the saved hash is the old one. The next run sees that it doesn’t match and embeds the product again.

hybridSearch embeds the search text and runs the big query. toKeywordQuery adds the or between the words.

compare calls hybridSearch with a big limit and sorts the same rows three ways. The keyword list is the rows that have a keyword_rank, sorted by it. Same for the vector list. There is no separate keyword-only or vector-only query in the file. The combined query already has both ranks.

serve reads the query string and calls hybridSearch. sendJson is a small helper so all the responses look the same.

Before using this for real

The file above works. Here is what changes when the table has more than ten rows and more than one person is searching.

Indexes

The GIN index on search_vector is the one you’ll need first. Without it, @@ has to check every row. With it, a keyword search on a million products takes a few milliseconds. GIN is a bit slower to update than GiST but faster to read, which fits a product catalogue.

The HNSW index on embedding is for the vector search. It is approximate. It finds the nearest vectors most of the time, without comparing the query to every row. Under ten or twenty thousand products you don’t need it. Postgres will scan the table anyway, and that is exact and fast enough. Above that, it is the difference between milliseconds and seconds.

Two things to know about it. Building it on a big table takes time and needs a higher maintenance_work_mem. So when you load a lot of data, insert first and create the index after. And the operator class has to match the operator. vector_cosine_ops goes with <=>. If they don’t match, Postgres ignores the index without telling you.

The index on category becomes useful as soon as you filter. That brings me to a problem.

Filters and the HNSW index

An approximate index doesn’t know about your WHERE clause. It finds the 20 nearest vectors first. Then Postgres applies category = 'phones'. If 15 of the 20 were accessories, you get 5 results instead of 20. pgvector 0.8 added a setting for this:

SET hnsw.iterative_scan = relaxed_order;

With it on, the index keeps going until the filtered LIMIT is reached. Another option: if you always filter on one column with few values, make a partial index per value (WHERE category = 'phones'). Either way, test your filtered queries with EXPLAIN ANALYZE and look at the row counts. This is where hybrid search can quietly return fewer results than it should.

Use the same model everywhere

Every vector in the table and every search vector must come from the same embedding model. Mixing them doesn’t give an error. It just gives distances that mean nothing. Keep the model name in one place, like the constant in this file. If you change the model, you have to change vector(1536) to the new size, clear every embedding_hash so the sync job embeds everything again, and rebuild the HNSW index. Treat it as a migration.

Keeping embeddings up to date

Products change all the time. Prices, descriptions, new products. With the hash approach, none of that touches the embedding code directly. The write path updates the row and is done. The sync job runs every few minutes from a cron job or a worker and picks up what changed. In the meantime, a new product can already be found by keyword search, because search_vector is filled in on insert. That is a nice property of hybrid search. A product without an embedding is still a product. Only one half of the search can see it.

Thousands, then millions

Up to a few hundred thousand products, this query with both indexes is fine on a normal Postgres server. Above that, these things help, roughly in this order. Measure and adjust CANDIDATES_PER_METHOD (20 to 100 is common). Store the vectors as halfvec to halve the memory the HNSW index needs. Run searches on a read replica so they don’t compete with writes. Partition the table by store or region if searches are always for one of them. At tens of millions of products you might look at a dedicated search engine. Below that, I wouldn’t.

Latency

The embedding API call takes the most time. Postgres answers the hybrid query in a few milliseconds when indexed. The call to OpenRouter for the search vector takes 100 to 300 milliseconds. Two easy improvements: cache search embeddings, because people type the same things (a Map in memory or Redis, keyed on the lowercased text), and time the two parts separately so you know which one got slower.

Filtering by price, category, store

Any column can be a filter. It goes into both CTEs before the LIMIT, in the same $n IS NULL OR column = $n form as category and price_cents here. A store_id for a shop with several stores is the common one. If every query filters on it, index it. And think about partial HNSW indexes or partitioning by store instead of one big index with a filter on top.

Where an LLM fits, and where it doesn’t

There is no language model in the request path. The database returns products, sorted, and those are the search results. A shop doesn’t need a paragraph about the iPhone SE. It needs the iPhone SE at the top of the list. Sending every search through an LLM would add a second or two and a few cents to every search, and the answer would be a bit different every time.

An LLM is useful around the search, not in the middle of it. It can turn “iphone under 500 euros” into q=iphone&maxPrice=500 before the search runs. It can re-rank the top 20 for a hard query, if you can afford the time. It can answer “which of these has the best camera?” after the results are on the page. That is the RAG setup from the last post, with hybrid search underneath instead of vector search only.

Where to go from here

• Weight the two sides. Multiply the keyword part by, say, 1.5 for a parts catalogue where exact terms matter more, or the vector part for products with vague descriptions. Same query, one more number.
• Run EXPLAIN ANALYZE on the combined query once you have real data and check which indexes get used.
• Add a language column and pick the text search config per row if your catalogue isn’t all in English. Stemming is per language.
• Log searches where the keyword side returns nothing. They are either typos, which pg_trgm can help with, or words your descriptions don’t use yet.
• The Postgres full-text search chapter and the pgvector README cover everything I skipped.

That’s it. One table, one query, one file.