pgvector: Embeddings Inside Postgres

~14 min · extensions, vector, ai

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Vector similarity, where your data already lives

The pgvector extension adds a vector data type and similarity operators (<-> L2, <#> inner product, <=> cosine) to PostgreSQL. ANN indexes (HNSW, IVFFlat) make millions-of-vectors search practical without a separate vector database.

The shape of an AI feature with pgvector

Generate an embedding for each row (OpenAI, Cohere, local model — your choice).
Store it in a vector(N) column on the same row.
Add an HNSW index for fast approximate nearest neighbour.
Query: "find the 5 rows whose embedding is closest to this query embedding."

When it's the right answer

Up to roughly 10 million vectors with a single Postgres instance, pgvector is operationally simpler than a dedicated vector DB and gives you the joy of querying embeddings alongside the relational data. Past that scale, dedicated vector stores (Pinecone, Weaviate, Qdrant) start to win on absolute throughput.

Code

Install + create a vector column·sql

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE documents
ADD COLUMN embedding vector(1536);  -- 1536 = OpenAI text-embedding-3-small

-- HNSW index for fast cosine-distance search
CREATE INDEX documents_embedding_hnsw
ON documents USING hnsw (embedding vector_cosine_ops);

Insert + query·sql

-- Application generates the embedding and inserts as a literal
INSERT INTO documents (title, body, embedding)
VALUES ('Pippa Quest', '...', '[0.012, -0.041, ...]');

-- Find the 5 most similar documents to a query embedding
SELECT id, title,
       1 - (embedding <=> '[0.012, -0.041, ...]'::vector) AS similarity
FROM   documents
ORDER  BY embedding <=> '[0.012, -0.041, ...]'::vector
LIMIT  5;

Hybrid search: filter + similarity·sql

-- Find similar documents, but only ones with the right tag
SELECT id, title
FROM   documents
WHERE  tags @> ARRAY['python']
ORDER  BY embedding <=> :query_vector
LIMIT  10;

External links

Exercise

Install pgvector on a sandbox Postgres. Add a vector(384) column to a table. Generate embeddings (with any local model — sentence-transformers/all-MiniLM-L6-v2 is 384-dim). INSERT 100 rows. Add an HNSW index. Run a similarity query.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousExtensions: PostgreSQL's Real Superpower Next →Why PostgreSQL Wins by Being Good Enough at Everything

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.