Supabase’s pgvector extension lets you do AI-powered semantic search directly in your PostgreSQL database, turning text into high-dimensional vectors and finding the most similar ones.
Let’s see it in action. Imagine you have a table of blog posts, each with a title, content, and an embedding column (a vector type).
CREATE TABLE blog_posts (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
embedding vector(1536) -- Assuming OpenAI's text-embedding-ada-002
);
Now, you want to find blog posts semantically similar to "What are the best practices for cloud security?". First, you’d generate an embedding for this query using your chosen embedding model (e.g., OpenAI’s API). Let’s say this query embedding is [0.001, 0.002, ..., 0.005].
To find similar posts, you’d run a query like this:
SELECT
id,
title,
content,
embedding <-> '[0.001, 0.002, ..., 0.005]' AS distance
FROM
blog_posts
ORDER BY
distance
LIMIT 5;
The <-> operator calculates the L2 (Euclidean) distance between the query vector and the embedding vectors in your table. Smaller distances mean greater similarity. This query directly leverages PostgreSQL and pgvector to perform the similarity search.
This whole process is about transforming unstructured text into a numerical representation that captures its meaning. The "embedding" is this numerical representation, a point in a high-dimensional space. When you search for similar items, you’re essentially finding the points in that space that are closest to your query point. The magic is that this "closeness" in the vector space corresponds to semantic similarity in the original text.
The pgvector extension indexes these vectors using an Approximate Nearest Neighbor (ANN) algorithm, specifically Hierarchical Navigable Small Worlds (HNSW). This is crucial because calculating exact distances to every vector in a large dataset would be prohibitively slow. HNSW builds a multi-layer graph structure that allows it to quickly find approximate nearest neighbors, trading a tiny bit of accuracy for massive speed gains. When you create the index:
CREATE INDEX ON blog_posts USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
(Note: ivfflat is another index type, HNSW is often preferred for its speed and quality trade-off, but ivfflat is also common and effective).
The vector_l2_ops specifies the distance metric to use for indexing and querying. For HNSW, it would look like this:
CREATE INDEX ON blog_posts USING hnsw (embedding vector_l2_ops);
The lists parameter in ivfflat (or ef_construction and M for HNSW) controls the trade-off between index build time, index size, and search speed/accuracy. Higher values generally mean better accuracy and slower searches/builds.
The core problem pgvector solves is the scalability of semantic search. Before extensions like this, you’d typically offload vector search to a specialized vector database. pgvector brings this capability directly into your existing relational database, allowing you to join vector search results with your other relational data in a single query. This drastically simplifies your architecture and reduces data duplication.
What most people don’t realize is how sensitive the quality of your semantic search is to the dimensionality of your embeddings and the specific distance metric used. While L2 distance is common and works well, cosine similarity (which measures the angle between vectors, not their magnitude) can sometimes yield better results for certain text tasks, especially when vector magnitudes might not directly correlate with semantic meaning. pgvector supports vector_cosine_ops for this:
CREATE INDEX ON blog_posts USING hnsw (embedding vector_cosine_ops);
And querying with cosine distance:
SELECT
id,
title,
embedding <=> '[0.001, 0.002, ..., 0.005]' AS distance
FROM
blog_posts
ORDER BY
distance
LIMIT 5;
The <=> operator is overloaded by pgvector to correctly compute cosine distance when vector_cosine_ops is used in the index.
The next step is often optimizing your vector generation process and exploring hybrid search strategies that combine keyword matching with semantic similarity.