What Is pgvector and What Can You Do With It?

AI applications often represent text, images, and other data as embeddings: arrays of numbers that capture useful patterns or semantic relationships. A vector search finds the stored vectors that are closest to a query vector.
These numeric representations work because the training process of modern models arranges similar concepts near each other in high‑dimensional space, so distance becomes a proxy for meaning.

pgvector is an open-source PostgreSQL extension that adds a vector type and similarity operators. It lets a team keep embeddings beside relational data instead of introducing a separate vector database.

Why Keep Vectors in PostgreSQL?

A separate vector service can be a good choice, but it also adds another system to operate. Maintaining an extra service means provisioning additional hardware, monitoring another process, and handling data synchronization between two stores. pgvector is attractive when the application already depends on PostgreSQL and needs joins, filters, transactions, and vector search in the same database.

It provides:

• familiar SQL and PostgreSQL tooling;
• transactions that can cover ordinary columns and vectors;
• normal backup, replication, and access-control mechanisms; and
• a simpler deployment for moderate workloads.

The trade‑off is that PostgreSQL is not automatically the best choice for every very large or highly specialised vector workload. In extreme‑scale scenarios the single‑node architecture of PostgreSQL can become a bottleneck, whereas systems built expressly for vector search distribute the index across many machines.

A Minimal Example

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

SELECT title, content,
       1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

The <=> operator measures cosine distance. Cosine distance evaluates the angle between two vectors, ignoring their magnitude, which is why it is popular for embeddings that are L2‑normalized. pgvector also supports Euclidean distance (<->) and negative inner product (<#>). The dimension must match the embedding model used by the application; a mismatch would cause a runtime error because the underlying distance formulas assume equal length.

Exact and Approximate Search

For smaller tables, an exact ORDER BY ... LIMIT query is often the simplest option. It guarantees that the returned rows are the true nearest neighbours, but it scans the entire table, which grows costly as rows increase. As the table grows, approximate‑nearest‑neighbour (ANN) indexes can reduce query work at the cost of some recall or index resources. Recall drops when the index skips a few potential neighbours, yet the speed gain can be decisive for interactive applications.

IVFFlat

IVFFlat groups vectors into lists. A query probes only selected lists, so lists and probes affect the speed/recall trade‑off. More lists create finer partitions, while a higher probe count examines more partitions, improving recall but slowing the query.

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

SET ivfflat.probes = 10;

The index is normally built after representative data has been loaded, and its settings should be tuned with the real workload.

HNSW

HNSW builds a multilayer proximity graph. Each layer connects vectors that are close in the underlying space, allowing the search to “jump” quickly toward the target region. It often offers a useful speed/recall trade‑off, but index construction and memory use can be significant because the graph stores multiple neighbor links per vector.

CREATE INDEX documents_embedding_hnsw
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 100;

Vector retrieval throughput and recall depend strictly on hardware memory bandwidth, vector dimensionality, filtering predicate selectivity, and index construction hyperparameters. On an instance with 32 GB RAM, pgvector's HNSW index achieves sub-10 ms query latency at 95% recall for 1,000-dimensional embeddings (using ef_search = 100), provided index pages fit within PostgreSQL shared_buffers and the operating system page cache.

Common Applications

Semantic Search

Keyword search looks for terms; vector search can find related wording. A production search usually combines vector similarity with metadata filters and, often, full‑text search. The filter narrows the candidate set (e.g., by tenant or date), while the vector score re-ranks the remaining rows by semantic relevance.

SELECT title, content
FROM documents
WHERE organisation_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

Retrieval‑Augmented Generation

A RAG application embeds a question, retrieves relevant rows, and supplies those rows to a language model. Retrieval quality still depends on chunking, metadata, the embedding model, and evaluation. Good chunking ensures that each stored text fragment is a self‑contained idea, which makes the similarity comparison more meaningful.

from openai import OpenAI

client = OpenAI()
query_vector = client.embeddings.create(
    model="text-embedding-3-small",
    input="What was decided at the last meeting?",
).data[0].embedding

cursor.execute("""
    SELECT content
    FROM documents
    ORDER BY embedding <=> %s::vector
    LIMIT 5
""", (query_vector,))

Recommendations and Image Similarity

User, product, or image embeddings can support nearest‑neighbour recommendations. The application still needs business rules, permissions, and quality checks; similarity alone is not a complete recommendation system.

Performance and Operations

• Measure scale rather than relying on slogans. Millions of vectors fit comfortably in a single PostgreSQL instance with proper vacuuming, but beyond that the index size and query latency start to climb sharply, prompting either partitioning or migration to a dedicated service.
• Plan memory for ANN indexes. HNSW indexes can consume substantial memory because each vector stores several neighbor pointers; on a 64 GB machine reserve at most 30 GB for the index to leave headroom for the operating system and other workloads.
• Use hybrid retrieval when appropriate. PostgreSQL full‑text search can quickly prune the candidate set, after which pgvector refines the ranking; alternatively, merge two ranked lists with reciprocal rank fusion, which often yields higher overall relevance than either method alone.
• Treat filters as part of the design. Adding tenant or permission filters changes the planner’s cost estimates and can prevent the use of an ANN index if the filter excludes most of the indexed lists.

Getting Started

Many managed PostgreSQL providers offer pgvector, but availability and supported versions vary. On a self‑hosted installation, enable the extension after installing the matching package:

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

Generate vectors with a hosted or local embedding model, store the model name and dimension with your application configuration, and validate retrieval quality with representative queries.

When Is pgvector a Good Fit?

pgvector is a sensible starting point when:

• PostgreSQL is already the system of record;
• vector queries need relational joins or permissions;
• the team wants one operational platform; and
• the workload is within the tested capacity of the chosen instance.

A dedicated vector system may be more suitable when the workload requires specialised distribution, extremely high throughput, or a scale that PostgreSQL cannot meet economically.

The Bottom Line

pgvector does not make PostgreSQL a universal replacement for vector databases. It does make PostgreSQL a practical home for embeddings in many applications. Start with a simple exact query, add an index only after measuring, and choose the architecture that fits the data, latency, and operational requirements.