How to Use pgvector to Search Documents by Meaning in PostgreSQL

A traditional database search works well when users know the exact words they want to find. For example, a query that searches for documents containing the phrase “database connection timeout” can find matching text. However, it may miss a document that describes the same problem as “the application cannot connect to the database because the request takes too long.”

The wording is different, but the meaning is similar.

This is where semantic search becomes useful. Instead of relying only on matching words, semantic search represents text as numerical vectors called embeddings. Text with similar meanings can have vectors that are close together, allowing an application to retrieve relevant documents even when they do not contain the exact search terms.

PostgreSQL can support this approach through pgvector, an extension that adds vector data types, similarity operations, and vector indexes to the database.

For .NET developers, backend engineers, and database administrators, pgvector provides a way to add semantic search without introducing a separate vector database for every application. Existing PostgreSQL tables, access controls, transactions, and operational practices can remain part of the solution.

This article explains how pgvector works, how to store document embeddings, how to search for relevant content, and what to consider before using semantic search in a production application.

What Is pgvector?

pgvector is a PostgreSQL extension for storing and searching vector data. A vector is an ordered collection of numerical values.

An embedding model converts text into a vector that represents certain semantic characteristics of the text. The resulting vector might contain hundreds or thousands of dimensions, depending on the model.

For example, a document about resetting a forgotten password and a document about recovering account access may produce vectors that are relatively close to each other. A document about optimizing a PostgreSQL index may produce a less similar vector.

The embedding model determines how text is represented. pgvector stores the resulting vectors and provides operations to compare them.

A typical semantic-search workflow looks like this:

Collect documents from application data, uploaded files, knowledge bases, or support articles.
Split long documents into smaller, searchable sections.
Generate an embedding for each section using an embedding model.
Store the text, metadata, and embedding in PostgreSQL.
Convert a user's search question into an embedding using the same compatible model.
Compare the query embedding with stored document embeddings.
Return the most relevant sections to the application.

The important distinction is that pgvector does not generate embeddings by itself. An embedding model performs that task. pgvector handles storage, similarity calculations, and indexed retrieval.

Enable pgvector in PostgreSQL

Before creating vector columns, install a pgvector version compatible with your PostgreSQL installation. The extension must be available to the PostgreSQL server, not merely installed in your application project.

Once the extension is installed and enabled for the database, run:

CREATE EXTENSION IF NOT EXISTS vector;

This makes the vector data type and pgvector operations available in the current database.

You can confirm that PostgreSQL recognizes the extension with:

SELECT
    extname,
    extversion
FROM pg_extension
WHERE extname = 'vector';

If the extension is unavailable, the installation or hosting environment may not include it. In managed database services, check which extensions are supported and whether additional configuration is required.

Do not assume that installing a client-side package automatically installs the PostgreSQL extension.

Design a Table for Semantic Search

A useful document table should contain more than an embedding. Store the original text, an identifier, and metadata that the application needs for filtering and access control.

The following example uses a three-dimensional vector to keep the SQL easy to read. Real embedding models commonly produce vectors with many more dimensions.

CREATE TABLE document_chunks
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document_id BIGINT NOT NULL,
    chunk_text TEXT NOT NULL,
    category TEXT,
    embedding VECTOR(3) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The vector dimension must match the embedding model's output dimension. If a model produces vectors with 1,536 values, for example, the column must be configured for that dimension when using a fixed-dimension vector column.

The dimension is not a setting to choose arbitrarily. The model determines it.

The document_id identifies the original document, while each row represents a searchable chunk. This structure allows one document to contain multiple sections, each with its own embedding.

Insert a sample document

You can insert a small example manually to understand the data structure:

INSERT INTO document_chunks
    (document_id, chunk_text, category, embedding)
VALUES
    (
        101,
        'PostgreSQL uses indexes to improve data retrieval.',
        'Database',
        '[0.12, 0.85, 0.34]'
    ),
    (
        102,
        'Connection pooling reduces the overhead of opening database connections.',
        'Backend',
        '[0.18, 0.78, 0.29]'
    ),
    (
        103,
        'CSS controls the presentation and layout of web pages.',
        'Frontend',
        '[0.91, 0.10, 0.15]'
    );

These vectors are illustrative values, not actual embeddings generated from the text. They demonstrate how PostgreSQL stores vector data, but they should not be used to evaluate real semantic-search quality.

In a real application, generate embeddings from the text using your chosen model and store the returned numerical values.

Understand Vector Similarity

Semantic search depends on how the application measures the distance or similarity between vectors.

pgvector supports several comparison operators. The appropriate choice depends on the embedding model and the similarity metric it is designed to use.

Operator	Measurement	Typical use
`<->`	Euclidean (L2) distance	Models and workflows designed for L2 distance
`<=>`	Cosine distance	Comparing vector direction when cosine similarity is appropriate
`<#>`	Negative inner product	Workflows that rank using inner product

For cosine distance, a smaller value indicates greater similarity. Cosine similarity itself is commonly expressed as one minus cosine distance.

The negative inner product operator may initially seem confusing because its result is negative. Sorting by this operator in ascending order gives the highest inner-product matches first.

Do not select a metric solely because it is popular. Follow the embedding model's recommendations and validate retrieval quality against representative search questions.

Perform a Semantic Search Query

Once the table contains real embeddings, the application can convert a search question into a vector and pass that vector to PostgreSQL.

For example, imagine that a user asks:

“What helps an application avoid repeatedly opening database connections?”

An embedding model converts that question into a vector. The application then sends the vector to PostgreSQL.

A cosine-distance search looks like this:

SELECT
    id,
    document_id,
    chunk_text,
    category,
    embedding <=> '[0.16, 0.80, 0.30]' AS distance
FROM document_chunks
ORDER BY embedding <=> '[0.16, 0.80, 0.30]'
LIMIT 5;

The query vector above is illustrative. A real application must replace it with the embedding generated for the user's question.

PostgreSQL compares the query vector with each stored vector, orders the results by cosine distance, and returns the five closest matches.

Add metadata filters

Many applications need to restrict results to a particular category or document type.

For example:

SELECT
    id,
    document_id,
    chunk_text,
    embedding <=> '[0.16, 0.80, 0.30]' AS distance
FROM document_chunks
WHERE category = 'Backend'
ORDER BY embedding <=> '[0.16, 0.80, 0.30]'
LIMIT 5;

This query searches only rows in the Backend category.

The metadata filter is important for both relevance and access control. If documents belong to different customers or users, enforce the appropriate tenant or permission restrictions in the database query. Semantic similarity must never override document authorization.

Generate Embeddings Outside PostgreSQL

An embedding model may run through a hosted API, a local model, or an internal machine-learning service. Your application is responsible for sending the text to that model and receiving the vector.

A practical ingestion process should:

Normalize text where appropriate.
Split long documents into manageable chunks.
Generate one embedding per chunk.
Validate the returned vector dimension.
Store the text and vector together.
Record enough model information to support future updates.

Choose a sensible chunk size

A full book or large technical document may contain too much information for one embedding to represent every topic accurately. Splitting the document into smaller sections often improves retrieval.

For example, a technical document might be divided into sections covering installation, configuration, troubleshooting, and deployment.

If the chunks are too large, a search may return a section containing several unrelated topics. If they are too small, important context may be lost.

A small amount of overlap between neighboring chunks can help preserve context across section boundaries. The best chunk size depends on the model, document structure, and types of questions users ask.

Test different strategies against real search examples rather than choosing a chunk size based only on a fixed rule.

Improve Search Performance with a Vector Index

A basic similarity query can compare the query vector with every stored vector. This approach is straightforward and can work well for small datasets.

As the number of document chunks grows, scanning all vectors may become expensive. pgvector supports approximate nearest-neighbor indexes that can reduce the amount of work required to retrieve similar vectors.

Two common index types are HNSW and IVFFlat.

Index	How it works	Considerations
HNSW	Builds a graph that helps navigate toward nearby vectors	Often offers a useful balance of query speed and recall, but can require substantial memory and index-building time
IVFFlat	Groups vectors into lists and searches selected lists	Requires careful tuning of list counts and probes; index quality depends on the data and configuration

The best index depends on dataset size, query volume, memory, write activity, and acceptable recall.

Create an HNSW index

For cosine-distance searches, an HNSW index can be created as follows:

CREATE INDEX document_chunks_embedding_hnsw
ON document_chunks
USING hnsw (embedding vector_cosine_ops);

The operator class must match the distance operator used by the query. For cosine distance, use vector_cosine_ops.

The query can then use the same ordering pattern:

SELECT
    id,
    document_id,
    chunk_text,
    embedding <=> '[0.16, 0.80, 0.30]' AS distance
FROM document_chunks
ORDER BY embedding <=> '[0.16, 0.80, 0.30]'
LIMIT 10;

PostgreSQL's query planner decides whether to use the index. Creating an index does not guarantee that every query will use it.

For very small tables, a sequential scan may be faster. As the dataset grows, the index may become more beneficial.

Understand the recall trade-off

Approximate indexes trade some retrieval accuracy for speed. Depending on the index and its settings, the returned results may not contain every exact nearest neighbor.

For HNSW, search parameters can affect the balance between latency and recall. For IVFFlat, the number of lists and probes influences how much of the index is examined.

Measure the results against exact searches on a representative sample. An index that is fast but repeatedly misses relevant documents may not be suitable for the application.

Filter Results Without Losing Relevant Documents

Metadata filtering introduces an additional challenge.

Suppose a database contains documents from many customers, and the application needs to search only one customer's records. A query might filter by tenant identifier and order results by vector distance.

With approximate indexes, filtering and candidate selection can interact in ways that leave fewer matching results than expected. This behavior depends on the pgvector version, index type, query plan, and filtering strategy.

For production systems, test the exact query pattern used by the application. Consider appropriate relational indexes for common metadata filters and evaluate whether the vector index returns enough authorized candidates.

Partitioning, iterative index scans where supported, or alternative query strategies may be useful for particular workloads. The right approach should be based on measurements rather than assumed index behavior.

Measure and Troubleshoot Search Performance

When semantic search becomes slow, investigate both PostgreSQL and the embedding workflow.

Inspect the query plan

Use EXPLAIN to understand how PostgreSQL plans the query:

EXPLAIN
SELECT
    id,
    chunk_text
FROM document_chunks
ORDER BY embedding <=> '[0.16, 0.80, 0.30]'
LIMIT 10;

This displays the planned execution steps without executing the query.

For a query that is safe to run under production conditions, EXPLAIN ANALYZE provides actual execution measurements:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    chunk_text
FROM document_chunks
ORDER BY embedding <=> '[0.16, 0.80, 0.30]'
LIMIT 10;

EXPLAIN ANALYZE executes the query, so use it carefully with expensive statements or queries that have side effects. For read-only retrieval queries, it is often useful for diagnosing execution time and buffer activity.

Look for sequential scans on large datasets, unexpected sorts, and whether the expected index is being used.

Separate embedding latency from database latency

A semantic-search request may involve multiple steps:

Receive the user's question.
Generate the query embedding.
Execute the PostgreSQL search.
Apply permissions and metadata filters.
Format or rerank the results.
Return the response.

If the embedding service takes 700 milliseconds and the database query takes 20 milliseconds, changing the PostgreSQL index will have little effect on total response time.

Measure each step separately. This helps identify whether the bottleneck is model inference, network communication, SQL execution, or application processing.

Keep embeddings consistent

If you change embedding models, the new model may produce vectors with a different dimension or a different representation of meaning.

Do not blindly compare vectors produced by incompatible models. Even if two models produce vectors with the same number of dimensions, their representations are not necessarily comparable.

A model migration may require regenerating embeddings, maintaining separate columns or tables during the transition, and switching queries only after the new vectors are ready.

Common Mistakes to Avoid

Using random or placeholder vectors

A vector column can accept valid numerical values without those values representing the stored text. Such data is useful for testing SQL syntax but cannot produce meaningful semantic search.

Generate embeddings from the actual document content before evaluating search relevance.

Choosing an index before measuring the workload

HNSW and IVFFlat have different memory, build-time, and retrieval characteristics. Start with a baseline query and representative data, then compare index options using latency and recall measurements.

Ignoring document permissions

A highly relevant document may still be unauthorized for the current user. Apply tenant and permission filters consistently, and consider PostgreSQL row-level security where appropriate.

Treating semantic search as an exact replacement for keyword search

Semantic search can find related concepts, but it may perform poorly when users need an exact identifier, error code, product number, or phrase.

Many applications benefit from combining semantic search with PostgreSQL full-text search, exact filters, or a reranking step. The best approach depends on the types of queries the application must support.

Forgetting to update embeddings

When document content changes, the stored embedding can become outdated. Update or regenerate the embedding whenever the indexed content changes, and consider using a background worker for large ingestion jobs.

Best Practices for Production Applications

Start with a clear retrieval requirement. Collect representative questions and define what a good result looks like before choosing an embedding model or index.

Store document identifiers, chunk text, metadata, and embeddings in a structure that supports the application's filtering requirements. Keep access-control decisions separate from similarity ranking.

Use the same compatible embedding model for stored documents and search queries. Version the embedding pipeline so that model changes and reprocessing jobs can be managed safely.

Measure search latency, retrieval quality, index size, database resource use, and ingestion throughput. Test under realistic concurrency rather than relying only on a few manual queries.

Finally, treat semantic search as a retrieval system, not as a guarantee that every result is correct. If the application passes retrieved text to a language model, validate the content and ensure that retrieved documents cannot override system instructions or authorization rules.

Advantages and Disadvantages of pgvector

Advantages

Keeps document metadata and embeddings in PostgreSQL.
Supports similarity search alongside ordinary relational queries.
Allows vector retrieval to work with SQL filters and existing database access controls.
Offers exact search and approximate index options.
Can reduce the need to operate a separate vector database for suitable workloads.

Disadvantages

Embeddings must be generated by an external or separately hosted model.
Approximate indexes require tuning and can reduce recall.
Large vector datasets consume storage and memory.
Model changes may require expensive embedding regeneration.
Semantic retrieval alone may not handle exact-match requirements well.

Summary

pgvector enables PostgreSQL to store embeddings and retrieve documents based on semantic similarity. An application generates embeddings, stores them alongside document chunks, converts each search question into a compatible vector, and uses PostgreSQL distance operators to find relevant content.

For small datasets, a straightforward similarity query may be enough. For larger collections, HNSW or IVFFlat indexes can improve retrieval performance, provided that the chosen index and its settings are tested against real workloads.

Successful semantic search requires more than creating a vector column. Choose an appropriate embedding model, design useful document chunks, enforce access controls, measure query performance, and validate retrieval quality. With these pieces in place, pgvector can provide a practical foundation for document search, knowledge-base retrieval, and retrieval-augmented generation using PostgreSQL.