Semantic Search for eCommerce with Embeddings and pgvector

A Shopify merchant I work with sells outdoor gear. Their search logs were full of queries like "jacket for rainy hikes" and "warm socks for winter running" that returned nothing, while the products those shoppers wanted sat two clicks away. Keyword search does not understand intent. Embeddings do. This is the search layer I built for them, on PostgreSQL, with numbers.

Why embeddings alone are not enough either

Embeddings map text to points in a high-dimensional space where similar meanings sit close together. "Sneakers" and "trainers" land near each other; "waterproof jacket" and "rain shell" too. What embeddings are bad at is the precise, literal stuff: a SKU like TX-4471-BLK, a brand name, an exact size. Those tokens are rare and the model has no semantic hook for them. So the working design is hybrid: run a vector query and a keyword query, merge the rankings, and let each cover the other's blind spot. I use the same fusion approach in my RAG pipeline; it is the same problem wearing a different hat.

Schema

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE products (
  id            BIGINT PRIMARY KEY,           -- WooCommerce/Shopify product ID
  sku           TEXT,
  title         TEXT NOT NULL,
  brand         TEXT,
  category_path TEXT,                          -- "Clothing > Jackets > Waterproof"
  attributes    JSONB NOT NULL DEFAULT '{}',   -- {"colour":"black","size":["S","M"],"gender":"unisex"}
  description   TEXT,
  price_cents   INT NOT NULL,
  in_stock      BOOLEAN NOT NULL DEFAULT true,
  embed_text    TEXT NOT NULL,                 -- what we embedded (for debugging + hashing)
  embed_hash    TEXT NOT NULL,
  embedding     VECTOR(1536) NOT NULL,
  tsv           TSVECTOR GENERATED ALWAYS AS (
                  setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
                  setweight(to_tsvector('simple',  coalesce(sku,'') || ' ' || coalesce(brand,'')), 'A') ||
                  setweight(to_tsvector('english', coalesce(category_path,'')), 'B') ||
                  setweight(to_tsvector('english', coalesce(description,'')), 'C')
                ) STORED,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX products_embedding_idx ON products USING hnsw (embedding vector_cosine_ops);
CREATE INDEX products_tsv_idx       ON products USING gin (tsv);
CREATE INDEX products_stock_idx     ON products (in_stock) WHERE in_stock;

Two details are doing real work. The setweight calls make a title match count more than a description match in keyword ranking. The 'simple' dictionary for SKU and brand prevents English stemming from mangling TX-4471-BLK into nonsense.

What to embed

Do not embed the raw description with its HTML, size charts and shipping boilerplate; it dilutes the vector. Build a compact, structured text that reads like how a shopper would describe the product:

// lib/embed-text.ts
export function buildEmbedText(p: RawProduct): string {
  const attrs = Object.entries(p.attributes)
    .map(([k, v]) => `${k}: ${Array.isArray(v) ? v.join(', ') : v}`)
    .join('; ');
  const desc = stripHtml(p.description).replace(/\s+/g, ' ').slice(0, 600);
  return [
    `Product: ${p.title}`,
    p.brand ? `Brand: ${p.brand}` : '',
    `Category: ${p.category_path}`,
    attrs ? `Attributes: ${attrs}` : '',
    `Description: ${desc}`,
  ].filter(Boolean).join('\n');
}

That format lifted relevance noticeably on the outdoor catalogue because "Category: Clothing > Jackets > Waterproof" is a far stronger signal for "jacket for rainy hikes" than a 2,000-word marketing description.

Syncing from WooCommerce and Shopify

Both platforms fire webhooks on product create/update/delete. The handler queues a job (never embed inside the webhook request; Shopify times out at 5 seconds), and the job hashes the embed text so unchanged products cost nothing.

// workers/upsert-product.ts   (BullMQ job; see my event-driven architecture post)
import OpenAI from 'openai';
import { createHash } from 'node:crypto';
import { sql } from '@/lib/db';
import { buildEmbedText } from '@/lib/embed-text';

const openai = new OpenAI();

export async function upsertProduct(p: RawProduct) {
  const embedText = buildEmbedText(p);
  const hash = createHash('sha256').update(embedText).digest('hex');

  const [existing] = await sql`SELECT embed_hash FROM products WHERE id = ${p.id}`;
  let embedding: number[] | null = null;

  if (!existing || existing.embed_hash !== hash) {
    const { data } = await openai.embeddings.create({ model: 'text-embedding-3-small', input: embedText });
    embedding = data[0].embedding;
  }

  await sql`
    INSERT INTO products (id, sku, title, brand, category_path, attributes, description, price_cents, in_stock, embed_text, embed_hash, embedding)
    VALUES (${p.id}, ${p.sku}, ${p.title}, ${p.brand}, ${p.category_path}, ${JSON.stringify(p.attributes)}::jsonb,
            ${p.description}, ${p.price_cents}, ${p.in_stock}, ${embedText}, ${hash},
            ${embedding ? JSON.stringify(embedding) : sql`(SELECT embedding FROM products WHERE id = ${p.id})`}::vector)
    ON CONFLICT (id) DO UPDATE SET
      sku = EXCLUDED.sku, title = EXCLUDED.title, brand = EXCLUDED.brand, category_path = EXCLUDED.category_path,
      attributes = EXCLUDED.attributes, description = EXCLUDED.description, price_cents = EXCLUDED.price_cents,
      in_stock = EXCLUDED.in_stock, embed_text = EXCLUDED.embed_text, embed_hash = EXCLUDED.embed_hash,
      embedding = EXCLUDED.embedding, updated_at = now()`;
}

For WooCommerce the webhook topic is product.updated; for Shopify it is products/update. Price and stock changes update the row but skip the embedding call because they are not part of embed_text. The queue mechanics (retries, dead letters, idempotency) are covered in Event-Driven Architecture with Redis and BullMQ.

The hybrid query with filters

Filters (in stock, price range, category) must apply inside both candidate searches, before fusion. Filtering afterwards means your top-20 vector hits might all be out of stock and you return nothing.

-- $1 query embedding · $2 query text · $3 max price cents · $4 category prefix or NULL
WITH filtered AS (
  SELECT * FROM products
  WHERE in_stock
    AND price_cents <= $3
    AND ($4::text IS NULL OR category_path LIKE $4 || '%')
),
dense AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rnk
  FROM filtered ORDER BY embedding <=> $1 LIMIT 40
),
sparse AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv, q, 32) DESC) AS rnk
  FROM filtered, websearch_to_tsquery('english', $2) q
  WHERE tsv @@ q LIMIT 40
),
fused AS (
  SELECT id, SUM(1.0 / (60 + rnk)) AS score
  FROM (SELECT * FROM dense UNION ALL SELECT * FROM sparse) u
  GROUP BY id
)
SELECT p.id, p.sku, p.title, p.price_cents, p.category_path, f.score
FROM fused f JOIN products p ON p.id = f.id
ORDER BY f.score DESC
LIMIT 24;

Latency on a 48,000-product catalogue on a 2 vCPU Postgres instance: p50 11 ms, p95 19 ms for the SQL, plus roughly 60 ms to embed the query. The query embedding is cached in Redis keyed by the normalised query string, so repeat searches skip it; that pattern is in my Redis caching guide.

Wiring it into the storefront

The storefront calls a tiny Next.js route (/api/search?q=&max=&cat=) that embeds the query, runs the SQL and returns JSON. On Shopify this is a Hydrogen or headless setup (I discuss when that is worth it in Headless Shopify: Is It Worth It?); on a Liquid theme it is a predictive-search replacement that calls the endpoint via fetch. On WooCommerce, a small plugin hooks pre_get_posts for s= queries, fetches ranked IDs from the endpoint, and passes them as post__in with orderby=post__in so the rest of the theme works unchanged.

<?php
add_action('pre_get_posts', function (WP_Query $q): void {
    if (is_admin() || !$q->is_main_query() || !$q->is_search()) return;

    $ids = umm_semantic_search_ids(get_search_query(), 48);   // cached HTTP call to the endpoint
    if (!$ids) return;                                          // fall back to native search

    $q->set('s', '');
    $q->set('post_type', 'product');
    $q->set('post__in', $ids);
    $q->set('orderby', 'post__in');
});

Results on the outdoor store

The cost line surprises people. Embedding 48,000 products once was about $1.20; ongoing updates are a rounding error. The expensive part of "AI search" is never the AI; it is getting the data model, the filters and the sync right.

If you run a WooCommerce or Shopify store with a big catalogue and a search box that shoppers have learned to ignore, this is one of the highest-ROI projects I know of. Have a look at my services or get in touch if you want it built.