Implementing Production Semantic Search with OpenAI Embeddings and PostgreSQL pgvector (2026)

The Modern Retrieval Paradigm: Relational Data Meets Vector Embeddings

In early generative AI architectures, engineering teams frequently adopted dedicated, standalone vector databases (such as Pinecone, Qdrant, or Milvus) alongside their existing relational stores. However, in production, this dual-database topology introduces severe operational liabilities: distributed transaction desynchronization, eventual consistency lag, duplicate authorization checks, and spiraling SaaS infrastructure costs.

With the maturation of PostgreSQL 16+ and the pgvector 0.7+ extension, modern software architectures can store relational business entities, transactional ledgers, user access control lists (ACLs), and high-dimensional vector embeddings within a single unified, ACID-compliant database cluster.

1. Database Schema & HNSW Indexing Strategy in pgvector

To implement high-throughput search, we install the vector extension and define table schemas equipped with both vector embeddings and traditional PostgreSQL full-text search columns.

Production PostgreSQL SQL DDL

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

-- Table storing documents, lexical search vectors, and semantic embeddings
CREATE TABLE enterprise_knowledge_base (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(64) NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    
    -- Lexical search tsvector column
    tsv_content TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(content, '')), 'B')
    ) STORED,

    -- Vector embedding (1536 dimensions for text-embedding-3-small)
    embedding VECTOR(1536),

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 1. Create Generalized Inverted Index (GIN) for lexical text search
CREATE INDEX idx_knowledge_tsv ON enterprise_knowledge_base USING GIN(tsv_content);

-- 2. Create Hierarchical Navigable Small World (HNSW) index for vector cosine distance
-- m = 16 (bidirectional links per node), ef_construction = 128 (construction search depth)
SET maintenance_work_mem = '2GB';
CREATE INDEX idx_knowledge_hnsw_cosine ON enterprise_knowledge_base 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

2. Vector Distance Metrics: Choosing the Right Operator

pgvector supports three primary distance calculation operators:

• <=> Cosine Distance: Measures the angular difference between vectors, ignoring magnitude. This is the correct metric for normalized embeddings such as OpenAI's models (distance = 1 - cosine_similarity).
• <-> L2 / Euclidean Distance: Measures direct straight-line geometric distance. Ideal for spatial coordinates and computer vision feature spaces.
• <#> Negative Inner Product: Maximizes dot-product multiplication. Fastest computational operator when embeddings are pre-normalized to unit length.

3. End-to-End TypeScript Implementation: Ingestion & Hybrid RRF Search

The following production TypeScript service generates embeddings via the OpenAI API, executes parallel semantic and lexical searches in PostgreSQL, and merges rankings using Reciprocal Rank Fusion (RRF).

import OpenAI from "openai";
import { Pool } from "pg";

const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export interface SearchResult {
  id: string;
  title: string;
  content: string;
  score: number;
}

/**
 * Generates normalized 1536-dimensional embedding using text-embedding-3-small
 */
export async function generateEmbedding(text: string): Promise {
  const cleanInput = text.replace(/\n/g, " ").trim();
  const response = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: cleanInput,
    encoding_format: "float",
  });
  return response.data[0].embedding;
}

/**
 * Executes hybrid search merging BM25 and Vector Cosine similarity via Reciprocal Rank Fusion
 */
export async function hybridSearch(query: string, tenantId: string, limit: number = 10): Promise {
  const queryEmbedding = await generateEmbedding(query);
  const vectorString = `[${queryEmbedding.join(",")}]`;

  // SQL query executing Reciprocal Rank Fusion (k = 60)
  const sql = `
    WITH semantic_search AS (
        SELECT id, title, content,
               ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) as rank
        FROM enterprise_knowledge_base
        WHERE tenant_id = $2
        ORDER BY embedding <=> $1::vector
        LIMIT 40
    ),
    lexical_search AS (
        SELECT id, title, content,
               ROW_NUMBER() OVER (ORDER BY ts_rank_cd(tsv_content, plainto_tsquery('english', $3)) DESC) as rank
        FROM enterprise_knowledge_base
        WHERE tenant_id = $2 AND tsv_content @@ plainto_tsquery('english', $3)
        ORDER BY ts_rank_cd(tsv_content, plainto_tsquery('english', $3)) DESC
        LIMIT 40
    )
    SELECT
        COALESCE(s.id, l.id) as id,
        COALESCE(s.title, l.title) as title,
        COALESCE(s.content, l.content) as content,
        (
            COALESCE(1.0 / (60.0 + s.rank), 0.0) +
            COALESCE(1.0 / (60.0 + l.rank), 0.0)
        ) as rrf_score
    FROM semantic_search s
    FULL OUTER JOIN lexical_search l ON s.id = l.id
    ORDER BY rrf_score DESC
    LIMIT $4;
  `;

  const client = await pool.connect();
  try {
    // Tune HNSW search depth for this transaction (higher ef_search = higher recall)
    await client.query("SET LOCAL hnsw.ef_search = 64;");
    const result = await client.query(sql, [vectorString, tenantId, query, limit]);
    return result.rows.map(r => ({
      id: r.id,
      title: r.title,
      content: r.content,
      score: parseFloat(r.rrf_score),
    }));
  } finally {
    client.release();
  }
}

Benchmark Comparison: IVFFlat vs HNSW in Production

Performance Tuning & Memory Sizing Rules

1. Set maintenance_work_mem High During Index Creation: HNSW graph construction requires substantial memory. Set SET maintenance_work_mem = '4GB'; before executing CREATE INDEX to prevent temporary disk spills.
2. Tune hnsw.ef_search Dynamically: The default ef_search is 40. For higher recall in deep knowledge bases, increase to 64 or 100 at the session level.
3. Keep HNSW Graphs in Buffer Cache: For sub-10ms search responses, ensure the total size of your HNSW indexes fits entirely within PostgreSQL's shared_buffers and the operating system file system cache.

Enterprise Search Engineering with Betadrix

Modernizing search infrastructure from brittle keyword engines to high-performance semantic retrieval transforms customer conversion and internal employee productivity. At Betadrix, our AI and database architects design low-latency vector search architectures, hybrid RAG retrieval pipelines, and enterprise PostgreSQL databases. Discover our AI development services, consult our PostgreSQL development services, or custom software development offerings to scale your retrieval systems.