pgvector for Beginners: Add Semantic Search to PostgreSQL

#database #postgres #pgvector #vectorsearch

You already have PostgreSQL, backups, and a team that knows how to operate them. Then a new feature asks for semantic search, and suddenly the architecture diagram grows a separate vector database. For many applications, that extra system is unnecessary. pgvector adds vector storage and similarity search to PostgreSQL itself.

That means embeddings can live beside the documents, users, and permissions they belong to — with normal transactions, joins, and backups.

What an embedding does

An embedding model turns text, an image, or another input into a list of numbers. Similar inputs tend to produce vectors that are close together in the model's mathematical space.

A keyword search for “database keeps running out of connections” might not match a paragraph about “connection pool exhaustion.” Semantic search can, because it compares meaning represented by embeddings rather than exact words.

The model matters. Store the model name with your data, use the same model for documents and queries, and expect to regenerate embeddings when changing models.

A tiny pgvector example

Use a PostgreSQL distribution with pgvector installed, or try the project's Docker image. Enable the extension once per database:

CREATE EXTENSION IF NOT EXISTS vector;

This table uses three dimensions so the example stays readable. Real models usually produce hundreds or thousands; the declared size must match your model.

CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text NOT NULL,
    embedding vector(3) NOT NULL
);

INSERT INTO documents (content, embedding) VALUES
    ('Postgres connection pool exhausted', '[0.9,0.1,0.0]'),
    ('Configure a reverse proxy', '[0.0,0.8,0.2]'),
    ('Tune PostgreSQL connections', '[0.8,0.2,0.1]');

Find the nearest rows using cosine distance:

SELECT content,
       1 - (embedding <=> '[0.85,0.15,0.05]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.85,0.15,0.05]'::vector
LIMIT 3;

The <=> operator returns cosine distance, so 1 - distance expresses similarity. pgvector also supports L2 distance and inner product; use the metric recommended for your embedding model.

In application code, pass the query embedding as a parameter. Do not build SQL by concatenating user input.

Add an index when exact search becomes slow

Without a vector index, PostgreSQL performs exact nearest-neighbor search. That gives perfect recall and can be completely reasonable for a small table.

As the data grows, an HNSW index can trade a little recall for faster approximate search:

CREATE INDEX documents_embedding_idx
ON documents
USING hnsw (embedding vector_cosine_ops);

The operator class must match the distance operator in your query. Check the plan and timing rather than assuming the index helps:

EXPLAIN (ANALYZE, BUFFERS)
SELECT content
FROM documents
ORDER BY embedding <=> '[0.85,0.15,0.05]'::vector
LIMIT 5;

Small tables may still prefer a sequential scan. On large production tables, build indexes concurrently and test recall against exact results. Our guide to debugging PostgreSQL queries with EXPLAIN explains how to read the plan.

Keep keywords in the picture

Semantic search is not automatically better at everything. Exact error codes, product names, and rare identifiers often work better with keyword search. PostgreSQL full-text search can cover that side, and you can combine keyword and vector rankings for hybrid search.

You also retain normal relational filters. A search can stay inside a tenant, workspace, or permission boundary without synchronizing access rules into another database. Design and test those filters carefully: approximate indexes and heavy filtering can interact in surprising ways.

When pgvector is a good fit

It is a strong default when PostgreSQL is already your primary database, your vector workload fits comfortably on it, and operational simplicity matters. It is especially useful for document search, recommendations, deduplication, and retrieval for AI features.

A dedicated vector engine may be justified by extreme scale, specialized indexing, or a workload your benchmarks show PostgreSQL cannot handle. Make that decision with real data, not because every AI demo includes another service.

Start with exact search, measure, and add HNSW when needed. You might discover that the simplest AI infrastructure improvement is using the database you already trust.

DEV Community