Vector Search and Semantic Product Recommendations with PostgreSQL pgvector

Why Keyword Search Fails in Real E-Commerce

When I was working on an early e-commerce backend, we ran straight into an annoying problem. Customers kept complaining that our search bar was broken. Someone would type "running shoes for monsoon rains", and our database returned zero products. Our warehouse had over two hundred pairs of waterproof athletic shoes, but because the vendor listed them as "water-resistant trail sneakers", standard SQL ILIKE '%running shoes%' missed every single one.

Traditional search relies on keyword matching. If the customer does not use the exact words written in your product title, they see an empty page and leave your site. That kills sales.

A lot of online tutorials tell you to sign up for hosted vector database services that cost $70 to $100 every month. If you are an Indian developer building a startup or a student building a portfolio project, paying $70 a month in foreign currency is crazy. You do not need a separate database. You can run vector search directly inside your existing PostgreSQL database using the free open-source extension called pgvector for ₹0 extra cost.

How Vector Search Works in Plain English

Instead of matching exact letters, vector search converts words and descriptions into lists of numbers called embeddings. Items with similar meanings end up close to each other in mathematical space.

A production semantic search pipeline works in two simple stages:

[1. Ingestion Pipeline - Background Task]
New Product Added -> Create 1536-number Embedding -> Store in PostgreSQL 'vector' column
                                                                |
                                                                v
                                                          [HNSW Index]

[2. Search Pipeline - Real-Time API]
Customer Query -> Generate Query Vector -> Run Cosine Distance Query (<=>) -> Return Top Matches

Because the meaning is encoded in the numbers, searching for "warm jacket for winter trips" will automatically find "thermal insulated fleece coat" even though the words are completely different.

Setting Up pgvector in PostgreSQL 16

Setting up pgvector takes less than two minutes. Connect to your PostgreSQL database and run the following SQL statements:

-- Step 1: Enable the pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Step 2: Create products table with a 1536-dimension vector column
-- This matches models like OpenAI text-embedding-3-small
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  sku VARCHAR(64) UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  in_stock BOOLEAN DEFAULT TRUE,
  embedding vector(1536) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Step 3: Create an HNSW index for sub-millisecond search
CREATE INDEX idx_products_embedding_hnsw 
ON products 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Why You Should Choose HNSW Over IVFFlat

When you start reading pgvector docs, you will see two index algorithms: IVFFlat and HNSW. Here is the direct trade-off so you do not waste hours guessing:

Always use HNSW for e-commerce. You do not want to run periodic maintenance scripts just to keep your search index fresh after vendors add new inventory.

Building the Semantic Search Service in TypeScript

Here is a clean Node.js service using the standard pg package and an embedding API to run searches:

// src/services/product-search.service.ts
import { Pool } from 'pg';
import OpenAI from 'openai';

const db = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
});

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});

export interface ProductMatch {
  id: number;
  sku: string;
  title: string;
  price: number;
  matchScore: number;
}

export async function findSemanticProducts(userQuery: string, limit = 8): Promise<ProductMatch[]> {
  // 1. Convert user search query into 1536-dimensional embedding
  const response = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: userQuery,
  });

  const queryVector = response.data[0].embedding;

  // 2. Query PostgreSQL using the Cosine Distance operator (<=>)
  // Cosine distance ranges from 0 (identical) to 2 (opposite)
  // Similarity score = 1 - distance
  const query = `
    SELECT 
      id, sku, title, price,
      ROUND((1 - (embedding <=> $1::vector))::numeric, 4) AS match_score
    FROM products
    WHERE in_stock = TRUE
    ORDER BY embedding <=> $1::vector ASC
    LIMIT $2;
  `;

  const { rows } = await db.query(query, [JSON.stringify(queryVector), limit]);

  return rows.map((row) => ({
    id: Number(row.id),
    sku: row.sku,
    title: row.title,
    price: Number(row.price),
    matchScore: Number(row.match_score),
  }));
}

The Catch: Why Pure Vector Search Fails on SKUs

Vector search is amazing for natural language, but it has one big weakness: exact model names and serial codes. If a customer types "SKU-992-RED" or "iPhone 15 Pro 256GB", vector distance might return an iPhone 14 case or general red items because the mathematical distance between short numbers is noisy.

The industry solution is Hybrid Search using Reciprocal Rank Fusion (RRF). You run both a standard full-text search and a vector search in parallel, then blend their ranks:

-- Hybrid Search Query: Blending Full-Text Search and Vector Distance
WITH keyword_matches AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(to_tsvector('english', title), query) DESC) AS rank
  FROM products, plainto_tsquery('english', 'Air Max 90') query
  WHERE to_tsvector('english', title) @@ query
  LIMIT 40
),
vector_matches AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> '[0.014, -0.038, ...]'::vector ASC) AS rank
  FROM products
  WHERE in_stock = TRUE
  LIMIT 40
)
SELECT 
  p.id, p.title, p.price,
  COALESCE(1.0 / (60 + k.rank), 0.0) + COALESCE(1.0 / (60 + v.rank), 0.0) AS final_score
FROM products p
LEFT JOIN keyword_matches k ON p.id = k.id
LEFT JOIN vector_matches v ON p.id = v.id
WHERE k.id IS NOT NULL OR v.id IS NOT NULL
ORDER BY final_score DESC
LIMIT 10;

With this hybrid query, exact SKUs hit the top instantly, and natural descriptions like "cushioned running shoes for long distance" still surface the best semantic matches.

Real Hardware Numbers: RAM and Cost

Let's talk about real production numbers so you do not overpay for cloud servers:

• Memory per Row: A 1536-dimension float vector uses approximately 6KB of storage. That means 10,000 products take only 60MB of RAM. A store with 100,000 products needs about 600MB of RAM for the vectors plus 800MB for the HNSW index in PostgreSQL memory.
• Server Costs: A 50,000-item catalog runs comfortably on a standard 4GB or 8GB RAM server. You can even host this on Oracle Cloud's free 24GB ARM instance for ₹0 cloud expense. Check out our guide on Free Developer Resources to see how to claim free cloud servers.
• Embedding Generation Cost: Using models like text-embedding-3-small costs about $0.02 per 1 million tokens. Embedding an entire 30,000-product catalog costs less than ₹100 in total.

You can test and validate your JSON catalog payloads with our free JSON Formatter while building your ingest pipelines.

Do not waste money on overhyped SaaS vector databases before your store makes its first rupee. Spin up PostgreSQL, install pgvector, build a clean hybrid search query, and test it with real customer phrases. Build it tonight.