pgvector: PostgreSQL Vector Extension for AI Applications

Installation & Schema Setup

Enable the extension, create a table with a vector column, and set up HNSW indexing.

-- Enable pgvector extension (requires superuser or rds_superuser on RDS)
CREATE EXTENSION IF NOT EXISTS vector;

-- Create a documents table with an embedding column
-- dimension must match your embedding model (OpenAI 3-small = 1536)
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    content     TEXT NOT NULL,
    category    VARCHAR(64),
    user_id     BIGINT REFERENCES users(id),
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    embedding   vector(1536)    -- pgvector column type
);

-- Create HNSW index for fast approximate nearest neighbor search
-- HNSW is recommended over IVFFlat for most use cases (better recall, faster queries)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- For L2 distance use: vector_l2_ops
-- For inner product use: vector_ip_ops

-- (Alternative) IVFFlat index — faster to build, lower memory, less accurate
-- lists = sqrt(row_count) is a good starting point
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

Insert Embeddings & Run Similarity Search

Store OpenAI embeddings and query with cosine distance, combining with SQL filters.

import openai
import psycopg2
import json

oa = openai.OpenAI()
conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
cur = conn.cursor()

# Generate and store an embedding
def embed(text: str) -> list[float]:
    return oa.embeddings.create(
        model="text-embedding-3-small",
        input=text
    ).data[0].embedding

docs = [
    ("RAG combines retrieval with LLM generation", "ai"),
    ("pgvector adds vector search to PostgreSQL",  "database"),
    ("HNSW is the recommended ANN index type",     "database"),
]

for content, category in docs:
    emb = embed(content)
    cur.execute(
        "INSERT INTO documents (content, category, embedding) VALUES (%s, %s, %s)",
        (content, category, json.dumps(emb))   # psycopg2 auto-converts list to vector
    )
conn.commit()

# Similarity search with cosine distance (<=>)
query_emb = embed("how does retrieval augmented generation work")

cur.execute("""
    SELECT content, category,
           embedding <=> %s::vector AS distance
    FROM documents
    WHERE category = 'ai'           -- SQL filter (pre-filter with HNSW iterative scan)
    ORDER BY distance               -- nearest first
    LIMIT 5
""", (json.dumps(query_emb),))

for row in cur.fetchall():
    print(f"dist={row[2]:.4f} | {row[0]}")

Advanced: SQL JOINs, CTEs & Row-Level Security

pgvector's biggest advantage — combine vector similarity with full SQL capabilities.

-- JOIN vector search with user data (pgvector's killer feature)
SELECT
    d.content,
    u.name AS author,
    d.created_at,
    d.embedding <=> query.vec AS similarity_distance
FROM
    documents d
    JOIN users u ON d.user_id = u.id,
    (SELECT %s::vector AS vec) query   -- parameterized query vector
WHERE
    d.created_at > NOW() - INTERVAL '30 days'
    AND d.category IN ('ai', 'database')
ORDER BY
    similarity_distance
LIMIT 10;

-- Row-Level Security for multi-tenant vector search
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
    USING (user_id = current_setting('app.user_id')::bigint);

-- Now every query automatically filters to current user's vectors
-- Perfect for SaaS applications with per-user document stores

-- Tune HNSW search precision vs. speed at query time
SET hnsw.ef_search = 100;   -- default 40; higher = better recall, slower

-- Check index usage
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM documents
ORDER BY embedding <=> '[...]'::vector LIMIT 5;

pgvector vs Dedicated Vector Databases