pgvector: Vector Search in PostgreSQL

What This Article Covers

• What pgvector is and why it’s a PostgreSQL extension for vector search.
• How to install pgvector and use the vector data type.
• Which index types exist and when to choose IVFFlat or HNSW.
• How to store and query vectors in SQL.
• How pgvector compares to Chroma and Qdrant.

Introduction

If you’re already running PostgreSQL in your project, you don’t need a separate vector database. pgvector is a PostgreSQL extension that brings vector search directly to your existing database. You store embeddings alongside your regular table data and query them with SQL. This saves infrastructure and significantly simplifies your stack. This article shows you how to install pgvector, store vectors, and perform similarity searches.

Why Do You Need pgvector?

Many projects already use PostgreSQL for user data, documents, or configurations. When you want to add RAG, you face a choice: install an additional vector database like Chroma or Qdrant, or integrate vector search into PostgreSQL. pgvector makes the latter possible. The biggest advantage is keeping vectors and regular data in the same database. You can write join queries that combine vector results with metadata from other tables. That’s often cumbersome with dedicated vector databases.

pgvector works best when your vector collection ranges from thousands to a few million entries. For extremely large datasets or specialized requirements like GPU acceleration, dedicated solutions often serve you better.

pgvector at a Glance

pgvector adds a new data type called vector to PostgreSQL. You define columns with this type, store embeddings as number lists, and use special operators for similarity search. The main operators are:

• <-> for L2 distance (Euclidean distance)
• <#> for inner product
• <=> for cosine similarity

Beyond that, pgvector offers two index types to speed up search with large datasets: IVFFlat and HNSW. Both approximate nearest neighbor (ANN) methods reduce search time by comparing only promising candidates instead of every vector.

Who This Article Is For

This article is for developers and administrators who already use PostgreSQL and want to add RAG capabilities. You should have basic SQL knowledge and understand what embeddings are. If you’re completely new to vector databases, first read the overview article on vector databases and the guide on embedding models.

Key Terms

Term	Meaning
vector	pgvector data type for embeddings, e.g. vector(768)
Embedding	Numeric vector that encodes the meaning of text
Cosine Similarity	Similarity measure based on the angle between two vectors
L2 Distance	Euclidean distance between two vectors
IVFFlat	Index type that partitions space into clusters (Inverted File)
HNSW	Index type with hierarchical graph (Hierarchical Navigable Small World)
ANN	Approximate nearest neighbor, approximative search for speed
Extension	PostgreSQL extension that adds new data types and functions

Installation

pgvector with Docker

The easiest approach is a Docker image that includes pgvector. For more on Docker, see the article Docker Basics.

docker run -d \
  --name pgvector-db \
  -e POSTGRES_PASSWORD=deinpasswort \
  -e POSTGRES_DB=vektoren \
  -p 5432:5432 \
  pgvector/pgvector:pg16

pgvector on an Existing PostgreSQL

If you already have PostgreSQL installed, compile pgvector from source:

git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
make install

Then enable the extension in PostgreSQL:

CREATE EXTENSION IF NOT EXISTS vector;

Getting Started

Create a Table with a Vector Column

CREATE TABLE dokumente (
    id SERIAL PRIMARY KEY,
    titel TEXT,
    inhalt TEXT,
    quelle TEXT,
    embedding VECTOR(768)
);

The number in VECTOR(768) must match your embedding model’s dimensions. If you use multilingual-e5-large, that’s 1024 dimensions.

Insert Vectors

INSERT INTO dokumente (titel, inhalt, quelle, embedding)
VALUES (
    'Was ist lokale KI',
    'Lokale KI läuft auf dem eigenen Rechner.',
    'blog',
    '[0.1, 0.2, 0.3, 0.4]'
);

In practice, you generate embeddings with Python and then insert them. The vector list is passed as text in square brackets.

Similarity Search

SELECT titel, quelle, embedding <=> '[0.1, 0.2, 0.3, 0.4]' AS distanz
FROM dokumente
ORDER BY embedding <=> '[0.1, 0.2, 0.3, 0.4]'
LIMIT 5;

The operator <=> calculates cosine similarity. The smaller the value, the more similar the vectors are. You sort in ascending order and take the top results.

Index Types: IVFFlat vs. HNSW

Without an index, PostgreSQL scans every vector sequentially. That becomes slow with hundreds of thousands of entries. An index speeds up search dramatically.

IVFFlat Index

IVFFlat partitions vector space into clusters. During search, only the cluster matching the query vector is scanned.

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

The lists parameter determines the number of clusters. A good rule of thumb is rows / 1000. You should rebuild the index after inserting data so cluster centers are chosen well.

HNSW Index

HNSW builds a hierarchical graph. It’s often faster and more accurate than IVFFlat on queries, but needs more memory and longer build time.

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

The parameter m controls graph connections, ef_construction controls build quality. For search, you can adjust ef_search:

SET hnsw.ef_search = 100;

Which Index Should You Choose?

HNSW is the better choice in most cases. It offers higher accuracy with comparable speed. IVFFlat is simpler to understand and uses less memory. If you’re starting fresh, use HNSW. If you have an existing IVFFlat index and are happy with it, stick with it.

Using pgvector with Python

import psycopg2
from pgvector.psycopg2 import register_vector

conn = psycopg2.connect("dbname=vektoren user=postgres password=deinpasswort")
register_vector(conn)

cur = conn.cursor()

cur.execute("CREATE EXTENSION IF NOT EXISTS vector")

cur.execute("""
    CREATE TABLE IF NOT EXISTS dokumente (
        id SERIAL PRIMARY KEY,
        titel TEXT,
        embedding VECTOR(768)
    )
""")

# Embedding from your model
embedding = [0.1] * 768

cur.execute(
    "INSERT INTO dokumente (titel, embedding) VALUES (%s, %s)",
    ("Lokale KI", embedding)
)

cur.execute("""
    SELECT titel, embedding <=> %s AS distanz
    FROM dokumente
    ORDER BY embedding <=> %s
    LIMIT 5
""", (embedding, embedding))

results = cur.fetchall()
for row in results:
    print(row)

conn.commit()
cur.close()
conn.close()

Comparison with Chroma and Qdrant

Property	pgvector	Chroma	Qdrant
Type	PostgreSQL extension	Standalone vector database	Standalone vector database
Setup	Simple if PostgreSQL exists	Very simple via pip	Simple via Docker
SQL support	Full	No	No
Joins with table data	Yes	No	No
Scalability	Medium	Small to medium	High
Filtering	Full (SQL WHERE)	Basic	Advanced (Payload)
Index types	IVFFlat, HNSW	HNSW	HNSW
GPU support	No	No	No
Memory footprint	Medium	Low	Medium

pgvector shines when you’re already running PostgreSQL and need to combine vectors with relational data. For pure vector search at scale, Chroma and Qdrant are often better choices. Learn more about combining different search approaches in the article on Hybrid Search.

Common Pitfalls

• Wrong vector dimension: Your column definition VECTOR(768) must match your embedding model’s dimension exactly. A 1024-dimensional model won’t work in a 768-dimensional column.
• No index for large datasets: Without IVFFlat or HNSW, queries slow down dramatically once you reach tens of thousands of rows. Build the index before moving to production.
• Creating IVFFlat index before inserting data: IVFFlat computes cluster centers during index creation. If the table is empty, the clusters will be suboptimal. Insert data first, then create the index.
• Wrong operator class: Each distance metric requires its own operator class, like vector_cosine_ops for Cosine Similarity. Using the wrong class causes errors or incorrect results.
• Insufficient work_mem: HNSW builds require working memory. For large tables, temporarily increase work_mem to around 1GB.
• Forgotten extension: After a PostgreSQL update or restore, you must run CREATE EXTENSION vector again, or vector functions won’t be available.
• Embeddings stored as text: Always store vectors as the vector type, never as text or JSON. Only then will similarity operators and indexes work correctly.
• Skipped vacuum maintenance: Like any PostgreSQL table, regular VACUUM and ANALYZE runs matter. They help the query optimizer generate efficient plans.

Hardware, Costs, and Security

pgvector runs on any machine that supports PostgreSQL. Small projects can run on a regular desktop or a modest VPS. With millions of vectors, allocate sufficient RAM since HNSW indexes are kept in memory. An SSD is recommended, especially for index building.

Costs: pgvector is open source and free. You only pay for the hardware or server running PostgreSQL.

Security: Since pgvector integrates into PostgreSQL, standard security practices apply. Use strong passwords, restrict network access, and enable SSL for connections. If running pgvector in Docker, don’t expose the port publicly. See Docker Fundamentals for secure deployment patterns.

Further Reading

• pgvector GitHub Repository
• pgvector Documentation
• Vector Database Overview
• RAG Fundamentals
• Local RAG
• Embedding Models
• Setting Up Chroma
• Setting Up Qdrant
• Hybrid Search
• Docker Fundamentals

FAQ

Do I need a separate vector database if I use pgvector?

No. pgvector extends PostgreSQL with vector search capabilities. If you already have PostgreSQL, no additional database is necessary.

Which PostgreSQL versions are supported?

pgvector supports PostgreSQL 13 and newer. Currently versions 13 through 17 are officially supported.

How many vectors can pgvector handle?

Up to several million vectors work well with an HNSW index. Beyond that, memory becomes the limiting factor.

Should I use IVFFlat or HNSW?

HNSW is the better choice in most cases, offering higher accuracy at comparable speed. IVFFlat is simpler and more memory efficient.

Can I use pgvector with Python?

Yes. Official bindings exist for psycopg2, psycopg3, SQLAlchemy, and asyncpg. The library is called pgvector-python.

Does pgvector work with Docker?

Yes. An official Docker image is available at pgvector/pgvector. You can also build your own image installing pgvector on a PostgreSQL base image.

What distance metrics does pgvector support?

L2 Distance (Euclidean), Inner Product, and Cosine Similarity. Each metric has its own operator and index operator class.

Can I link vectors with regular table data?

Yes, and that’s one of pgvector’s biggest advantages. You can write JOIN queries that combine vector results with data from other tables.

How much memory does an HNSW index need?

It depends on vector dimension and entry count. With 768 dimensions and 1 million vectors, the index can consume several gigabytes.

Is pgvector free?

Yes, pgvector is open source under the PostgreSQL license and costs nothing. You only pay for hardware.

Can I combine pgvector with Ollama?

Yes. Ollama provides the language model, pgvector stores the embeddings. Your Python script connects both through the database connection.

Do I need to rebuild the index after large data changes?

HNSW indexes update automatically. After major changes, a rebuild can improve quality. IVFFlat indexes should be rebuilt after significant modifications.

Sources

• pgvector GitHub Repository
• pgvector Python Bindings
• PostgreSQL Documentation
• HNSW Paper by Malkov and Yashunin
• IVFFlat Explanation in pgvector README