Skip to content
← All posts
5 min read Dawid Skłodowski

Semantic search in Postgres with pgvector

Keyword search matches words; semantic search matches meaning. A deep-dive on embeddings and vector similarity, and how pgvector lets you do semantic search in the Postgres you already run — no separate vector database required.

The full-text search posts were all about matching words — stems, trigrams, ranking by term overlap. That has a hard ceiling: it cannot match meaning. A search for “laptop won’t turn on” should surface a document titled “notebook fails to boot”, but they share no words, so keyword search misses it entirely. Semantic search closes that gap by matching on meaning rather than wording, and the technology that makes it work — embeddings and vector similarity — is now available inside Postgres via the pgvector extension. Here is how it works and how to build it.

Embeddings: turning meaning into vectors

The key idea is the embedding: a model (typically an LLM embedding model you call via an API) turns a piece of text into a vector — a list of numbers, often hundreds or more — that represents its meaning in a high-dimensional space. The crucial property is that texts with similar meaning produce vectors that are close together, even if they share no words. “Laptop won’t turn on” and “notebook fails to boot” land near each other; “best pasta recipe” lands far away.

# get an embedding for a piece of text (via an embedding API)
embedding = EmbeddingClient.embed("notebook fails to boot")
# => [0.012, -0.045, 0.231, ...]  a vector of, say, 1536 numbers

Search then becomes a geometry problem: embed the user’s query, and find the stored documents whose vectors are closest to it. Closeness in this space means similarity in meaning, so the nearest vectors are the most semantically relevant results — regardless of shared keywords.

pgvector: vectors as a Postgres column

The pgvector extension adds a vector column type and the distance operators to compare them, so your embeddings live as a column on your existing tables — no separate vector database to run:

CREATE EXTENSION vector;
ALTER TABLE documents ADD COLUMN embedding vector(1536);

-- find the 10 documents most similar in meaning to a query vector
-- <=> is cosine distance: smaller = more similar
SELECT id, title
FROM documents
ORDER BY embedding <=> '[0.012, -0.045, ...]'
LIMIT 10;

The <=> operator computes distance between vectors (cosine distance is the usual choice for text embeddings; pgvector also offers L2 and inner product). Order by it ascending and you get the most semantically similar documents first. That is the whole query — find the nearest vectors — and it returns results that keyword search could never find because it is matching meaning, not words.

Building it: store embeddings, search by embedding

The end-to-end shape in a Rails app:

# 1. When a document is created/updated, generate and store its embedding (in a background job)
class EmbedDocumentJob < ApplicationJob
  def perform(doc_id)
    doc = Document.find(doc_id)
    doc.update!(embedding: EmbeddingClient.embed(doc.searchable_text))
  end
end

# 2. To search, embed the query and find nearest neighbours
def semantic_search(query)
  query_vector = EmbeddingClient.embed(query)
  Document.order(Arel.sql("embedding <=> '#{query_vector}'")).limit(10)
end

Two engineering notes carrying over from the LLM-API post: generating embeddings is an API call, so do it in a background job, not in the request; and embeddings cost (per token) and the same input gives a stable embedding, so you only re-embed when the text changes. The embeddings, once stored, are searched entirely within Postgres — fast and free per query.

Indexing for scale

A brute-force nearest-neighbour search compares the query against every row, which is fine for thousands of documents but slow for millions. pgvector provides approximate-nearest-neighbour indexes (IVFFlat, with HNSW emerging) that make vector search fast at scale, at a small cost to recall:

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

This is the same indexing principle as the GIN indexes for full-text search — without it you scan everything; with it you jump to the likely neighbours. For a large corpus, an ANN index is what makes semantic search performant.

Semantic vs keyword: use both

Semantic search is powerful but it is not a replacement for keyword search — they have complementary strengths, and the best results often combine them:

  • Semantic search excels at meaning, synonyms, and fuzzy intent — it finds the right document even when the wording is completely different. But it can be too fuzzy: it may miss an exact keyword match a user expects, or rank a vaguely-related result above an exact one.
  • Keyword search (FTS) excels at exact terms, names, codes, and precision — when a user searches a product SKU or a specific phrase, they want that exact match, which semantic similarity might rank below something “meaningfully similar”.

The strongest approach is often hybrid search: combine the semantic (vector) and keyword (FTS) results, blending their rankings, to get both meaning and precision. And the fact that both live in Postgres — FTS via tsvector, semantic via pgvector — makes hybrid search a single-database affair, which is exactly the “Postgres for everything” advantage applied to the AI era. You do not need a separate vector database alongside a separate search engine; one Postgres holds your data, your keyword index, and your vector index together.

Verdict

Semantic search matches meaning where keyword search only matches words — embedding text into vectors so that similar meanings sit close together, then finding the nearest vectors to a query. The significant thing in 2023 is that you do not need a dedicated vector database to do it: pgvector brings vector columns, distance operators, and ANN indexing into the Postgres you already run, so your embeddings live beside your data and search happens in one place. Generate embeddings in a background job (it’s an API call with a per-token cost), index with IVFFlat for scale, and — crucially — treat semantic search as a complement to keyword search, not a replacement, combining them into hybrid search for both meaning and precision. The headline is the same theme these posts keep returning to, now extended to AI: before reaching for a specialised datastore, check what Postgres can do — and with pgvector, it can now do semantic search too.

#postgresql #ai #llm #search