pgvector: Add Semantic Search to PostgreSQL in 30 Minutes

You don't need Pinecone or Weaviate. If you're already on PostgreSQL, pgvector gives you vector search without adding another service to maintain.

Setup

-- Install extension (requires PostgreSQL 14+)
CREATE EXTENSION IF NOT EXISTS vector;

-- Create a table with an embedding column
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536),  -- OpenAI text-embedding-3-small dimensions
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

Generating and Storing Embeddings

from openai import OpenAI
import psycopg2
import json

client = OpenAI()

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

def store_document(conn, content: str, metadata: dict = {}):
    embedding = embed(content)
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO documents (content, embedding, metadata) VALUES (%s, %s, %s)",
            (content, embedding, json.dumps(metadata))
        )
    conn.commit()

# Store documents
conn = psycopg2.connect("postgresql://localhost/mydb")
store_document(conn, "How to set up pgvector", {"source": "docs"})
store_document(conn, "Vector similarity search tutorial", {"source": "blog"})

Semantic Search

def search(conn, query: str, limit: int = 10) -> list[dict]:
    query_embedding = embed(query)

    with conn.cursor() as cur:
        cur.execute("""
            SELECT id, content, metadata,
                   1 - (embedding <=> %s::vector) AS similarity
            FROM documents
            ORDER BY embedding <=> %s::vector
            LIMIT %s
        """, (query_embedding, query_embedding, limit))

        return [
            {"id": row[0], "content": row[1], "metadata": row[2], "similarity": row[3]}
            for row in cur.fetchall()
        ]

results = search(conn, "how do I add vector search to postgres?")
for r in results:
    print(f"{r['similarity']:.3f}: {r['content']}")

The <=> operator is cosine distance. Use <-> for L2 (Euclidean) distance, or <#> for negative inner product.

Adding an Index for Performance

Without an index, pgvector does an exact nearest-neighbor scan (slow at scale). Add an IVFFlat or HNSW index:

-- IVFFlat: faster to build, slightly less accurate
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- HNSW: slower to build, faster queries, more accurate (recommended)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- After creating IVFFlat index, set probes at query time
SET ivfflat.probes = 10;  -- Higher = more accurate, slower

For HNSW, set hnsw.ef_search for the accuracy/speed tradeoff:

SET hnsw.ef_search = 40;  -- Default is 40, increase for better recall

Hybrid Search: Vector + Keyword

Combine semantic similarity with keyword matching for better results:

SELECT
    id,
    content,
    1 - (embedding <=> $1::vector) AS semantic_score,
    ts_rank(to_tsvector('english', content), plainto_tsquery($2)) AS keyword_score,
    (0.7 * (1 - (embedding <=> $1::vector))) +
    (0.3 * ts_rank(to_tsvector('english', content), plainto_tsquery($2))) AS combined_score
FROM documents
WHERE
    to_tsvector('english', content) @@ plainto_tsquery($2)
    OR (1 - (embedding <=> $1::vector)) > 0.7
ORDER BY combined_score DESC
LIMIT 10;

Filtering Before Vector Search

Always filter first, then do vector search -- it's much faster:

-- Bad: vector search over all documents, then filter
SELECT * FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;
-- (where you wanted only source='docs')

-- Good: filter to subset first
SELECT * FROM documents
WHERE metadata->>'source' = 'docs'
ORDER BY embedding <=> $1::vector
LIMIT 10;

Make sure you have a regular index on metadata->>'source' so the filter is fast.

Performance at Scale

At 1M+ documents:

• Use HNSW index (better recall vs IVFFlat at scale)
• Consider pgvector with pgvectorscale for distributed setups
• Partition the table and create per-partition indexes
• Reduce embedding dimensions if possible (text-embedding-3-small supports up to 1536, but 512 often works fine)

pgvector handles 10M+ rows on a single PostgreSQL instance for most use cases. You'll need a dedicated vector DB only at 100M+ scale with very high query rates.