#pgvector

pgvector is a PostgreSQL extension: add a vector column to a database you already run, and search with SQL. You do not operate a second vector product. Extension repo: pgvector/pgvector. Postgres itself (install, SQL, indexes) is the PostgreSQL course on this site.

Teams with users and articles already in PG should start here. For a laptop-only RAG spike, use Chroma first.

#Enable the extension

Your install must ship pgvector (distro package, Postgres.app, or the pgvector/pgvector Docker image). Then:

CREATE EXTENSION IF NOT EXISTS vector;

Without this, the type and <=> do not exist. If you lack permission, ask an admin to run it once on the target database.

#Minimal table and cosine query

Dimensionality must match the embedding model: 1536 for default text-embedding-3-small, often 1024 for bge-m3. Do not change the type later without re-embedding the column.

CREATE TABLE docs (
  id        bigserial PRIMARY KEY,
  content   text NOT NULL,
  source    text,
  embedding vector(1536) NOT NULL
);

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

-- Smaller cosine distance = closer; 1 - distance is similarity
SELECT id, content,
       1 - (embedding <=> '[0.01, 0.02]'::vector) AS similarity
FROM docs
ORDER BY embedding <=> '[0.01, 0.02]'::vector
LIMIT 5;

A real query passes 1536 dimensions; the two-d literal is syntax only. Common operators:

Text embeddings usually use cosine. Skip HNSW on tiny tables; add it around tens of thousands of rows.

#Python: psycopg + pgvector

pip install "psycopg[binary]" pgvector

pip install "psycopg[binary]" pgvector

import psycopg
from pgvector.psycopg import register_vector

DSN = "postgresql://postgres:secret@localhost:5432/rag"
conn = psycopg.connect(DSN)
register_vector(conn)
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")

conn.execute(
    """
    CREATE TABLE IF NOT EXISTS docs (
      id bigserial PRIMARY KEY,
      content text NOT NULL,
      source text,
      embedding vector(1536) NOT NULL
    )
    """
)

vec = [0.0] * 1536  # replace with embed_openai / embed_ollama
vec[0] = 0.1
conn.execute(
    "INSERT INTO docs (content, source, embedding) VALUES (%s, %s, %s)",
    ("Refunds must be requested within 7 days of delivery.", "refund.md", vec),
)
conn.commit()

q = vec  # the question must be embedded too
rows = conn.execute(
    """
    SELECT content, 1 - (embedding <=> %s) AS similarity
    FROM docs
    ORDER BY embedding <=> %s
    LIMIT 5
    """,
    (q, q),
).fetchall()
print(rows)

register_vector(conn) lets Python lists / NumPy arrays bind to vector. Skip it and inserts fail with a type error.

On an existing business table, add a column instead of a new database:

ALTER TABLE articles ADD COLUMN embedding vector(1536);
-- After backfill:
-- CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);

Backfill: SELECT id, body FROM articles WHERE embedding IS NULL, embed in batches, UPDATE. Full walkthrough: Practical Examples example 2.

#How this fits RAG

One SQL statement can apply WHERE org_id = 42 AND published and ORDER BY embedding <=> :q. That is pgvector’s edge over a pure vector DB: tenancy and time filters live next to similarity. The prompt still needs “answer only from content.”

#Next steps

• Qdrant
• PostgreSQL tutorial
• Practical Examples example 2