You're Already on Postgres — Add Vector Search in Five SQL Lines With pgvector

Chris Harper

2 min read

Sep 3, 2026 · 12:09 UTC

Tutorial

RAG

Vectors

pgvector turns your existing PostgreSQL database into a vector store with one extension — no separate service, no new infra. Here's the minimal path from embeddings to similarity search for RAG.

What you'll be able to do after this:

Add a vector column to any Postgres table and store embeddings alongside relational data
Run cosine similarity search in SQL with two lines
Add an HNSW index so queries stay fast at 100k+ rows

Enable the extension

CREATE EXTENSION IF NOT EXISTS vector;

On Supabase: one toggle in Database → Extensions. On AWS RDS (PostgreSQL 15+) or Azure Database for PostgreSQL: enable from the extensions list. For a local Postgres install: apt install postgresql-16-pgvector, then run the SQL above.

Store embeddings

CREATE TABLE documents (
  id        SERIAL PRIMARY KEY,
  content   TEXT,
  embedding VECTOR(1536)  -- match your model's output dimension
);

From Python, generate embeddings with sentence-transformers or any embedding endpoint and insert with psycopg2:

import psycopg2
cur.execute(
    "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
    (chunk_text, embedding_list)
)

Query by similarity

SELECT content, 1 - (embedding <=> %s::vector) AS score
FROM documents
ORDER BY embedding <=> %s::vector
LIMIT 5;

<=> is cosine distance; <-> is L2 (Euclidean); <#> is negative inner product. For normalized embeddings, all three produce the same ranking — cosine (<=>) is the safe default.

Add an index before production

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Without an index, pgvector does a full table scan — fine under ~10k rows, noticeable past that. HNSW keeps query time in low milliseconds at 100k+ rows.

When to use something else

pgvector uses Postgres CPU and I/O budget. If you're storing millions of vectors with heavy concurrent load, a dedicated vector database (Qdrant, Chroma, Pinecone) offloads the index pressure and keeps your Postgres instance uncontested. For a typical RAG app under ~500k documents, staying in Postgres means one fewer service to run, one fewer connection to manage, and free joins against your existing tables.

The best starting resource: pgvector: Embeddings and vector similarity — Supabase Docs covers setup, LangChain integration, and metadata filtering. For a hands-on video: PGVector: Turn PostgreSQL into Vector Database (Python Tutorial) — YouTube.