Understanding pgvector Cosine Distance Queries

Introduction

pgvector is a PostgreSQL extension that adds a native vector data type and similarity search operations, enabling Postgres to function as a vector database. Instead of managing a separate vector database service (Pinecone, Qdrant, Milvus), teams can store embeddings alongside their relational data in the same PostgreSQL instance they already operate. This simplifies architecture, eliminates the operational overhead of another database, and enables hybrid searches that combine vector similarity with SQL filters in a single query.

flowchart TD
    A["1. Create table + HNSW cosine index"] --> B["2. Generate embeddings, store as vector rows"]
    B --> C["3. Query: ORDER BY embedding <=> query_vector LIMIT k"]
    C --> D["4. Tune ef_search for recall vs. speed"]

Setup and Schema

-- Install the extension (requires pgvector compiled and installed)
CREATE EXTENSION IF NOT EXISTS vector;

-- Create table with vector column
CREATE TABLE document_chunks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL,
  chunk_index INT NOT NULL,
  content TEXT NOT NULL,
  category TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  embedding vector(1536) NOT NULL  -- OpenAI text-embedding-3-small dimensions
);

-- Create HNSW index for fast approximate nearest neighbor search
-- Optimized for cosine distance (inner product on normalized vectors)
CREATE INDEX ON document_chunks 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- m: max connections per node (higher = better recall, more memory)
-- ef_construction: search width during index build (higher = better recall, slower build)

Generating and Storing Embeddings

import openai
import psycopg2
import json

openai_client = openai.OpenAI()

def embed(text: str) -> list[float]:
    response = openai_client.embeddings.create(
        input=text,
        model="text-embedding-3-small"
    )
    return response.data[0].embedding  # 1536-dimensional vector

def store_chunk(conn, document_id: str, chunk_index: int, content: str, category: str):
    embedding = embed(content)
    
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO document_chunks 
              (document_id, chunk_index, content, category, embedding)
            VALUES (%s, %s, %s, %s, %s::vector)
        """, (document_id, chunk_index, content, category, json.dumps(embedding)))
    conn.commit()

Similarity Queries

-- Basic similarity search: top 5 most similar chunks
-- <=> is cosine distance operator (0 = identical, 2 = opposite)
SELECT 
  id,
  content,
  1 - (embedding <=> '[0.015, -0.024, ..., 0.082]') AS cosine_similarity
FROM document_chunks
ORDER BY embedding <=> '[0.015, -0.024, ..., 0.082]'
LIMIT 5;

-- Hybrid search: combine vector similarity with metadata filter
-- This is pgvector's killer feature vs. dedicated vector DBs
SELECT 
  id,
  content,
  category,
  1 - (embedding <=> %s) AS similarity
FROM document_chunks
WHERE category = 'security'          -- SQL filter applied BEFORE vector search
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY embedding <=> %s
LIMIT 10;

-- Distance operators:
-- <->   L2 (Euclidean) distance — use vector_l2_ops index
-- <#>   Inner product (negative) — use vector_ip_ops index  
-- <=>   Cosine distance — use vector_cosine_ops index

Python Integration

def semantic_search(query: str, category_filter: str = None, top_k: int = 5) -> list[dict]:
    query_embedding = embed(query)
    embedding_str = json.dumps(query_embedding)
    
    with conn.cursor() as cur:
        if category_filter:
            cur.execute("""
                SELECT id, content, category,
                       1 - (embedding <=> %s::vector) AS similarity
                FROM document_chunks
                WHERE category = %s
                ORDER BY embedding <=> %s::vector
                LIMIT %s
            """, (embedding_str, category_filter, embedding_str, top_k))
        else:
            cur.execute("""
                SELECT id, content, category,
                       1 - (embedding <=> %s::vector) AS similarity
                FROM document_chunks
                ORDER BY embedding <=> %s::vector
                LIMIT %s
            """, (embedding_str, embedding_str, top_k))
        
        return [
            {"id": str(row[0]), "content": row[1], "category": row[2], "similarity": float(row[3])}
            for row in cur.fetchall()
        ]

# Usage
results = semantic_search("How does zero-trust authentication work?", category_filter="security")

Tuning ef_search for Recall vs. Speed

-- Increase ef_search at query time for better recall (at cost of speed)
SET hnsw.ef_search = 100;  -- Default: 40. Higher = better recall, slower

SELECT content, 1 - (embedding <=> %s) as sim
FROM document_chunks
ORDER BY embedding <=> %s
LIMIT 5;

Key Takeaways

• pgvector adds a native vector data type and three distance operators (<->, <#>, <=>) to PostgreSQL — no separate vector database required.
• The HNSW index with vector_cosine_ops enables sub-millisecond approximate nearest neighbor search across millions of vectors.
• Hybrid search (vector similarity + SQL WHERE clauses) is pgvector’s primary advantage — combine semantic search with metadata filters in one query.
• ef_search controls the recall/speed tradeoff at query time — increase it for high-precision use cases, leave at default for high-throughput.
• Use 1 - (embedding <=> query_vector) to convert cosine distance (0=identical) to cosine similarity (1=identical) for more intuitive scoring.