Skip to content

Semantic search with pgvector: find what the shopper meant

From the work/ 17 min read

Store product embeddings in Postgres with pgvector, rank by cosine distance through an HNSW index, and fuse keyword and meaning with RRF. Tested on PGlite.

  • AI
  • Databases
In this article (9)
  1. Why keyword search fails
  2. What an embedding is
  3. Storing embeddings with pgvector
  4. The semantic search query
  5. Hybrid search: keywords plus meaning
  6. Cost, and when to re-embed
  7. Common mistakes
  8. Frequently asked questions
  9. Checklist

Semantic search finds products by what the shopper meant, not by the exact words they typed. You turn every product and every query into an embedding, a list of numbers, store it in Postgres with pgvector and sort by distance: “something warm for the snow” finds the down jacket even though its listing never says “snow.”

Below are the table, the HNSW index, the similarity query, a hybrid search that blends keywords with meaning, and the script that generates the embeddings, with the real output of every query. We tested it on PGlite 0.5.10 (PostgreSQL 18.6 compiled to WebAssembly) and its pgvector 0.8.7 extension. The call to the embeddings provider is replaced with toy vectors: the SQL is real; the quality of a real model is not something we measured.

Why keyword search fails

Most store search boxes compare words. In Postgres that is full-text search: each product is stored as a tsvector, its words reduced to their stems, and whatever the shopper types becomes a tsquery. It works when the shopper types the product's name. It fails when they type what they need:

  • They describe the use, not the product. Nobody titles a jacket “something warm for the snow.”
  • They use a different word. Sneakers, trainers and tennis shoes can mean the same product, and your catalog uses only one of them.
  • They type sentences. With websearch_to_tsquery, unquoted words are joined with &: every one of them has to appear in the product.

This is the keyword search from our tests, run against a made-up catalog of ten products:

export async function textSearch(sql, q, limit = 5) {
  return sql`
    SELECT handle, title, round(ts_rank_cd(search, query)::numeric, 3) AS rank
    FROM products, websearch_to_tsquery('english', ${q}) query
    WHERE search @@ query
    ORDER BY ts_rank_cd(search, query) DESC
    LIMIT ${limit}`;
}
  1. websearch_to_tsquery('english', …) turns the shopper's text into a query using English rules. According to the PostgreSQL text search docs, it understands quotes, OR and a leading dash, and it never raises syntax errors, so raw user input is safe to pass in.
  2. search @@ query keeps the products whose tsvector matches. search is a column of the table shown further down.
  3. ts_rank_cd sorts by relevance; the docs call it cover density ranking, which also weighs how close the matching words are to each other.

Here is what came back for “something warm for the snow.” “for” and “the” are English stop words and get dropped; three stems remain and no product has all three:

SELECT websearch_to_tsquery('english', 'something warm for the snow');
-- 'someth' & 'warm' & 'snow'

-- textSearch('something warm for the snow')
 handle | title | rank
--------+-------+------
(0 rows)
  1. The query became 'someth' & 'warm' & 'snow': all three stems must be in the same product.
  2. Zero rows. The shopper sees “no results” on a store that does sell jackets, beanies and thermal socks.

What an embedding is

Anthropic's embeddings docs define them as numerical representations of text that let you measure semantic similarity. In practice, an embedding model takes a piece of text and returns a vector: a fixed-length list of numbers, 1024 of them for example. Texts with similar meaning land close together in that space even when they share no words, so search comes down to finding the products closest to the query's vector.

a meaning space: similar things sit close togetherdown jacketwool beaniethermal socksfleece hoodierunning shoesrain bootslight rain shellsteel thermosfrench presscotton tee“something warmfor the snow”search returns what sits closest to its vectorillustrative: real models use hundreds or thousands of dimensions, not 2

Scroll the figure sideways to see all of it

Three facts from that page shape the design:

  • Anthropic does not offer its own embedding model. The page says so in those words, uses Voyage AI as its example and recommends assessing several vendors. This article says “your provider”: the SQL does not change.
  • The model sets the dimension. The models listed there return 1024 numbers by default, and several also offer 256, 512 or 2048. Your Postgres column is declared with that exact dimension.
  • The provider may ask whether a text is a document or a query. In the page's example the catalog is sent with input_type="document" and the search with input_type="query". If your provider asks for it, use one function for each.

Our tests call no provider at all. The embed(texts) function is a stub: a hand-made dictionary that maps about forty words onto 8 concepts (cold, rain, footwear, sport, coffee, hot weather, clothing and kitchen) and returns the vector normalized to length 1. It is deterministic and repeatable, but it is not a model: it only knows the words on its list. It tests the SQL, not search quality.

Storing embeddings with pgvector

pgvector is a Postgres extension that adds a vector type, distance operators and nearest-neighbor indexes. Before you create the table, check that your server has it with SELECT * FROM pg_available_extensions WHERE name = 'vector'. This is schema.sql, the one from the tests:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE products (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  handle        text NOT NULL UNIQUE,
  title         text NOT NULL,
  description   text NOT NULL DEFAULT '',
  search        tsvector GENERATED ALWAYS AS
                  (to_tsvector('english', title || ' ' || description)) STORED,
  content_hash  text GENERATED ALWAYS AS (md5(title || ' ' || description)) STORED,
  embedding     vector(1024),
  embedded_hash text,
  embedded_with text
);

CREATE INDEX products_search_idx ON products USING gin (search);
CREATE INDEX products_embedding_idx ON products USING hnsw (embedding vector_cosine_ops);
  1. CREATE EXTENSION turns pgvector on in the database. PGlite does not bundle it: it ships as a separate package, @electric-sql/pglite-pgvector, according to its extensions page.
  2. search is a generated column holding the text ready for search. The PostgreSQL docs on text search tables and indexes use a stored generated column so it keeps itself up to date. The hybrid search uses it.
  3. content_hash is the md5 of the text that becomes the vector; when someone edits the title or description, it changes on its own.
  4. embedding vector(1024) stores the vector, with your model's dimension. pgvector rejects any other size: our tests use vector(8), and a 3-number vector failed with expected 8 dimensions, not 3.
  5. embedded_hash and embedded_with record which text and which model produced the stored vector. When they no longer match content_hash and the current model, the vector is stale.
  6. The GIN index speeds up keyword search; the HNSW index speeds up vector search by cosine distance (vector_cosine_ops).

The pgvector README says four things about HNSW worth knowing before you create one:

  • With no index, pgvector runs an exact search with perfect recall (it finds every true neighbor). With HNSW or IVFFlat the search is approximate: it trades some recall for speed, and the README warns you will see different results after adding the index.
  • HNSW has a better speed-recall tradeoff than IVFFlat, but builds more slowly and uses more memory. Unlike IVFFlat, it can be created on an empty table.
  • It indexes vector columns up to 2,000 dimensions and halfvec up to 4,000.
  • At query time it keeps a candidate list of 40 by default (hnsw.ef_search). If you are missing results, raise it inside a transaction with SET LOCAL hnsw.ef_search = 100.

These are the README's distance operators you will use with text:

OperatorDistanceIndex operator classWhen
<=>Cosinevector_cosine_opsThe general choice, and the one we use.
<#>Negative inner productvector_ip_opsWhen your provider returns vectors of length 1: the README recommends it for performance.
<->Euclidean (L2)vector_l2_opsWhen your model's docs call for it.

<#> returns the inner product negated because Postgres only scans indexes in ascending order. And the query's operator must match the index's class: an index built with vector_cosine_ops only speeds up ORDER BY embedding <=> ….

Each product's vector is generated by index-products.mjs, on Postgres.js, the same driver as the Postgres job queue with SKIP LOCKED:

// Embeds every product whose text or model changed since its last embedding.
// embed(texts) calls your provider and returns one vector per text, in order.
export async function indexProducts(sql, embed, { model, dimensions, batchSize = 64 }) {
  let total = 0;
  for (;;) {
    const rows = await sql`
      SELECT id, title || ' ' || description AS text, content_hash
      FROM products
      WHERE embedded_hash IS DISTINCT FROM content_hash
         OR embedded_with IS DISTINCT FROM ${model}
      ORDER BY id
      LIMIT ${batchSize}`;
    if (rows.length === 0) return total;

    const vectors = await embed(rows.map((r) => r.text));
    if (vectors.length !== rows.length) {
      throw new Error(`expected ${rows.length} vectors, got ${vectors.length}`);
    }
    for (const v of vectors) {
      if (v.length !== dimensions || !v.every(Number.isFinite)) {
        throw new Error(`expected ${dimensions} finite numbers per vector`);
      }
    }

    await sql.begin(async (tx) => {
      for (const [i, row] of rows.entries()) {
        await tx`
          UPDATE products
          SET embedding = ${JSON.stringify(vectors[i])}::vector,
              embedded_hash = ${row.content_hash}, embedded_with = ${model}
          WHERE id = ${row.id}`;
      }
    });
    total += rows.length;
  }
}
  1. It picks up to 64 products whose embedded_hash differs from content_hash or whose vector came from another model. IS DISTINCT FROM treats NULL as a value, so brand-new products are included.
  2. It sends the texts to embed, the function that calls your provider, as one batch.
  3. It validates the answer before writing: one vector per product, the exact dimension, finite numbers only. If the provider sends back something wrong, it throws and leaves the table alone; two tests check this.
  4. It stores each vector as the text [0.1,0.2,…] cast with ::vector, together with the hash of the text that was sent and the model name, in a single transaction.
  5. It loops until nothing is pending and returns how many products it processed: 10 on the first run, 0 on the second.

The detail that matters is step 4: it stores the hash of the text that went to the provider, not the row's hash at write time. If someone edits the product while the provider is answering, the stored hash no longer matches and the next loop redoes it. We tested that: writing embedded_hash = content_hash in the UPDATE makes the race test fail, and the product keeps a stale vector marked as current.

two paths: index in the background, search in the requeston save · in the backgroundproduct editednew content_hashjob queueone job per changeembed(texts)provider, batchedUPDATE productsembedding + hashon search · inside the requestsearch box“boots”embed([q])1 callORDER BY <=>HNSW indextop 5 products+ text via RRFthe provider is called when indexing and on every searchunchanged text: content_hash = embedded_hash, nothing is sent again

Scroll the figure sideways to see all of it

The semantic search query

To search, you embed the shopper's query with the same model and ask for the closest products:

export async function semanticSearch(sql, embed, q, limit = 5) {
  const [vector] = await embed([q]);
  const v = JSON.stringify(vector);
  return sql`
    SELECT handle, title, round((1 - (embedding <=> ${v}::vector))::numeric, 3) AS similarity
    FROM products
    WHERE embedding IS NOT NULL
    ORDER BY embedding <=> ${v}::vector
    LIMIT ${limit}`;
}
  1. embed([q]) creates the query's vector: one provider call per search.
  2. embedding <=> … is the cosine distance. ORDER BY that distance with a LIMIT is the query shape the HNSW index speeds up.
  3. 1 - distance is the cosine similarity, as the README shows; we print it with three decimals.
  4. WHERE embedding IS NOT NULL leaves out products that have no vector yet.

With the test catalog and the toy vectors, this came back:

-- semanticSearch('something warm for the snow')
 handle        | title            | similarity
---------------+------------------+------------
 down-jacket   | Down jacket      | 0.920
 wool-beanie   | Wool beanie      | 0.901
 thermal-socks | Thermal socks    | 0.780
 fleece-hoodie | Fleece hoodie    | 0.707
 rain-shell    | Light rain shell | 0.303
(5 rows)

-- semanticSearch('boots')
 handle        | title         | similarity
---------------+---------------+------------
 running-shoes | Running shoes | 0.539
 thermal-socks | Thermal socks | 0.520
 rain-boots    | Rain boots    | 0.489
 cotton-tee    | Cotton tee    | 0.000
 steel-thermos | Steel thermos | 0.000
(5 rows)
  1. The query that returned zero rows as keywords now brings back the jacket, the beanie and the thermal socks first.
  2. For “boots,” the product actually called that came third, behind the running shoes. That comes from how we built the toy vectors, but it shows a real risk: similarity measures resemblance, not an exact match.
  3. The last two rows have a similarity of 0.000 and still show up. Vector search never says “no results”: it always returns the closest rows, however far away they are.

With ten rows, the planner prefers a full table scan. To check that the query can use the index we ran it with SET LOCAL enable_seqscan = off, and EXPLAIN showed Index Scan using products_embedding_idx with Order By: (embedding <=> …).

Hybrid search: keywords plus meaning

The pgvector README suggests using it together with Postgres full-text search and combining the results with Reciprocal Rank Fusion (RRF) or a cross-encoder. RRF is the simpler one: each list sorts by its own criterion, and each product earns 1 / (k + position) for every list it appears in. We use k = 60, the value in the pgvector-python RRF example:

export async function hybridSearch(sql, embed, q, limit = 5) {
  const [vector] = await embed([q]);
  const v = JSON.stringify(vector);
  return sql`
    WITH semantic AS (
      SELECT id, rank() OVER (ORDER BY embedding <=> ${v}::vector) AS rank
      FROM products
      WHERE embedding IS NOT NULL
      ORDER BY embedding <=> ${v}::vector
      LIMIT 20
    ),
    keyword AS (
      SELECT id, rank() OVER (ORDER BY ts_rank_cd(search, query) DESC) AS rank
      FROM products, websearch_to_tsquery('english', ${q}) query
      WHERE search @@ query
      ORDER BY ts_rank_cd(search, query) DESC
      LIMIT 20
    )
    SELECT p.handle, semantic.rank AS by_meaning, keyword.rank AS by_text,
           round(COALESCE(1.0 / (60 + semantic.rank), 0)
               + COALESCE(1.0 / (60 + keyword.rank), 0), 4) AS score
    FROM semantic
    FULL OUTER JOIN keyword ON keyword.id = semantic.id
    JOIN products p ON p.id = COALESCE(semantic.id, keyword.id)
    ORDER BY score DESC, p.id
    LIMIT ${limit}`;
}
  1. The semantic CTE takes the 20 products closest in meaning and numbers their positions with rank().
  2. The keyword CTE takes the top 20 by text, with the same query as the first section.
  3. FULL OUTER JOIN merges both lists: a product can be in one, the other or both.
  4. Each product adds 1 / (60 + position) per list, and COALESCE(…, 0) puts zero where it is missing. Whatever ranks well in both lists wins.
  5. The final order is by score, with the id as a stable tiebreaker.
-- hybridSearch('boots')
 handle        | by_meaning | by_text | score
---------------+------------+---------+--------
 rain-boots    | 3          | 1       | 0.0323
 running-shoes | 1          |         | 0.0164
 thermal-socks | 2          |         | 0.0161
 down-jacket   | 4          |         | 0.0156
 wool-beanie   | 4          |         | 0.0156
(5 rows)
  1. The rain boots move to first place: 1/63 for meaning plus 1/61 for text gives 0.0323, nearly double the running shoes, which only appear in one list.
  2. For “something warm for the snow,” the keyword list is empty and the hybrid result keeps the semantic order: jacket, beanie, socks.
  3. The jacket and the beanie tie at position 4 with zero similarity: they show up because the semantic list always fills its slots. In your store, show fewer results or cut at a minimum similarity you calibrate against real searches.
text, meaning or both: what each search findsquerytext (GIN)meaning (HNSW)hybrid (RRF)“…for the snow”0 rowsjacket, beanie…jacket, beanie…“boots”rain-bootsshoes 1st, boots 3rdrain-boots 1stsynonymsif you list themyes, model-dependentyesexact SKU or nameyesnot guaranteedyesno match0 rowsalways N rowsalways N rowscost per searchSQL onlySQL + 1 callSQL + 1 callhybrid keeps what each one findsrows 1–2: measured on PGlite with toy vectors; the rest follows from design

Scroll the figure sideways to see all of it

Keywords cover what meaning does not guarantee: an exact name, a SKU, a brand or a size. Meaning covers what keywords cannot reach: synonyms, sentences and needs. And because RRF only uses positions, you do not have to tune weights between scores that live on different scales.

Cost, and when to re-embed

We do not quote prices: they change and depend on the provider (for Voyage AI, Anthropic's docs point to MongoDB's). What you can work out from your own data:

ItemFormulaIllustrative example
Index the catalogproducts × tokens per product × price per token5,000 × 150 = 750,000 tokens, once
Every searchsearches × tokens per search × price per token100,000 × 8 = 800,000 tokens a month
Re-embededited products × tokens per product × price per token200 × 150 = 30,000 tokens
Storage4 × dimensions + 8 bytes per vector, per the README4,104 bytes at 1024 dimensions; 5,000 products ≈ 20.5 MB, plus the index

The token counts in the table are an illustrative example, not a measurement. To get yours, the HTTP example in Anthropic's docs shows the response carrying usage in usage.total_tokens: log it on your first indexing run and work out the average per product. The size we did measure: pg_column_size of a vector(1024) returned 4104 bytes on PGlite.

When to re-embed:

  • When the text you embed changes. Here that is the title and description. If you add the product type or tags to the text, add them to content_hash too.
  • When you switch models or dimensions. A vector from one model is not comparable with one from another: the query and the products must come from the same model. Changing model in the script re-embeds the whole catalog; the test confirms it with all ten products.
  • Not when price or stock changes. Those do not belong in the vector: filter them with WHERE.

And do not do it inside the request of whoever is editing the product. When the update webhook arrives, verify the Shopify webhook HMAC, enqueue a job and let a worker call indexProducts: the Postgres job queue retries it if the provider fails. Since the script only processes what changed, a run with no changes never calls the provider.

Common mistakes

  • Mixing models. Searching with a new model's vector over products embedded by the old one compares vectors from different spaces; if the dimension changed too, it fails (in our test: different vector dimensions 2 and 3). Re-embed everything before you switch the query.
  • Picking a dimension the index cannot take. If your model returns 2048 numbers, an HNSW index on vector will not index them: the limit is 2,000. Use halfvec, which goes to 4,000, or request a smaller dimension.
  • Filtering without knowing when the filter runs. With approximate indexes, the WHERE clause is applied after the index scan. The README's example: if a condition matches 10% of rows, with HNSW and hnsw.ef_search at 40, you get only 4 rows on average. Since pgvector 0.8.0 there are iterative index scans: SET hnsw.iterative_scan = strict_order.
  • Treating similarity as relevance. The query always returns the full LIMIT, even when the last rows have nothing to do with the search. Cut off the distant ones.
  • Embedding inside the request. Indexing in the save request makes the admin wait and loses the work if the provider fails. Do it in the background.
  • Ignoring the input type. If your provider separates documents and queries, always say which is which. Anthropic's embeddings docs, in the Voyage AI part, say not to omit input_type because setting it can improve retrieval quality.

Frequently asked questions

Can I generate the embeddings with Claude?

No. Anthropic's docs say Anthropic does not offer its own embedding model, use Voyage AI as an example provider and recommend assessing several. The vector comes from an embedding model; the SQL in this article works with whichever one you pick.

Do I need a separate vector database?

If you already run Postgres, pgvector keeps the vectors in the same table as the product, with regular transactions, joins and filters. The README gives the limit of a non-partitioned Postgres table: 32 TB by default. Our rule: start in your own Postgres and move only if you measure a problem you cannot solve there.

How many dimensions should I pick?

Whatever your model returns. If it offers several, like 256, 512, 1024 or 2048 for the models listed in Anthropic's docs, fewer dimensions take less space (4 × dimensions + 8 bytes) and fit an HNSW index on vector up to 2,000. Compare quality on your real searches before you shrink them.

What happens if the embeddings provider goes down?

The semantic half needs the query's vector, so with no provider there is no similarity. Our recommendation: catch the error and answer with the keyword half alone, which depends on nothing else. That fallback is not in the tested code.

Checklist

  • pgvector is available on your Postgres (pg_available_extensions) and enabled with CREATE EXTENSION.
  • The vector(n) column has your model's exact dimension, at most 2,000 if you index it with HNSW as vector.
  • The table has a generated content_hash, plus embedded_hash and embedded_with.
  • The HNSW index uses the operator class of your query: vector_cosine_ops with <=>.
  • indexProducts checks count, dimension and numbers before it writes.
  • Vectors are regenerated in the background, from a queue, when the text or the model changes.
  • Search blends keywords and meaning with RRF and cuts off distant results.
  • You tested with your store's real searches before swapping the search box.

Start with hybrid search and a list of real searches that return nothing today: it is the fastest way to see whether embeddings help your catalog. To re-embed in the background, set up the Postgres job queue with SKIP LOCKED, and if changes arrive by webhook, verify their HMAC before you enqueue them.

The same table can power a RAG chatbot that answers from your documents.

Saw this on Instagram? Comment EMBEDDINGS on the Reel and I'll send you the guide.

Written by

Luis Morales

Luis Morales

Frontend and Shopify developer, EcomLabs

@luismoralesdev on Instagram

Published November 11, 2026 · updated November 18, 2026

← Blog