pgvector: embeddings en PostgreSQL

pgvector: Vector Search in PostgreSQL

Enabling the pgvector Extension

-- Run this once per database to enable the extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Verify it's installed
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';
-- Returns: vector | 0.7.0 (or similar)

Creating a Table with a Vector Column

CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    content     TEXT NOT NULL,
    category    TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    embedding   vector(1536)   -- must match your model dimension
);

-- Index on category for fast metadata filtering
CREATE INDEX ON documents (category);

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'documents';

Inserting Embeddings from Python

import psycopg2
from pgvector.psycopg2 import register_vector
from openai import OpenAI

conn = psycopg2.connect('postgresql://user:pass@localhost/mydb')
register_vector(conn)
cur = conn.cursor()

client = OpenAI()

document = 'pgvector adds vector search to PostgreSQL.'
resp = client.embeddings.create(model='text-embedding-3-small', input=document)
embedding = resp.data[0].embedding

cur.execute(
    'INSERT INTO documents (content, category, embedding) VALUES (%s, %s, %s)',
    (document, 'database', embedding)
)
conn.commit()
print('Inserted document with embedding')

Cosine Similarity with the <=> Operator

-- Find top 5 documents most similar to a query embedding
-- Replace '[0.1, 0.2, ...]' with the actual query vector
SELECT
    id,
    content,
    category,
    1 - (embedding <=> '[0.1, 0.2, 0.3]'::vector) AS cosine_similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector
LIMIT 5;

Combining Vector Search with SQL Filters

-- Semantic search filtered to the 'database' category
-- and only recent documents
SELECT
    id,
    content,
    1 - (embedding <=> %s::vector) AS similarity
FROM documents
WHERE
    category = 'database'
    AND created_at > NOW() - INTERVAL '30 days'
ORDER BY embedding <=> %s::vector
LIMIT 5;

-- In Python with psycopg2:
# cur.execute(sql, (query_embedding, query_embedding))

Creating an HNSW Index for Speed

-- Create an HNSW index for cosine distance queries
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- At query time, control recall vs speed:
SET hnsw.ef_search = 40;  -- higher = better recall, slower

-- Verify the index is being used:
EXPLAIN SELECT * FROM documents
ORDER BY embedding <=> '[0.1]'::vector(1)
LIMIT 5;

IVFFlat Index: Alternative to HNSW

-- IVFFlat index: good for large, infrequently updated corpora
-- lists ≈ sqrt(n) where n is the number of vectors
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- At query time, probes controls recall vs speed:
SET ivfflat.probes = 10;  -- check 10 of 100 clusters

Full Python Query Example

import psycopg2
from pgvector.psycopg2 import register_vector
from openai import OpenAI

conn = psycopg2.connect('postgresql://user:pass@localhost/mydb')
register_vector(conn)
client = OpenAI()

def semantic_search(query, category=None, top_k=5):
    resp = client.embeddings.create(model='text-embedding-3-small', input=query)
    q_vec = resp.data[0].embedding

    sql = '''
        SELECT content,
               1 - (embedding <=> %s::vector) AS similarity
        FROM documents
        {where}
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    '''
    where = 'WHERE category = %s' if category else ''
    params = [q_vec, q_vec, top_k] if not category else [q_vec, category, q_vec, top_k]
    cur = conn.cursor()
    cur.execute(sql.format(where=where), params)
    return [{'text': r[0], 'score': r[1]} for r in cur.fetchall()]

pgvector vs Pinecone Trade-offs

Using pgvector with Supabase

from supabase import create_client
import os

url = os.environ['SUPABASE_URL']
key = os.environ['SUPABASE_SERVICE_KEY']
supabase = create_client(url, key)

# Insert a document with embedding
supabase.table('documents').insert({
    'content': 'Supabase hosts PostgreSQL with pgvector.',
    'embedding': embedding   # list of 1536 floats
}).execute()

# Vector similarity search via RPC (Supabase edge function)
# result = supabase.rpc('match_documents', {'query_embedding': q_vec, 'match_count': 5}).execute()

Quick Check

Lesson Recap