pgvector Tutorial: Vector Search Inside PostgreSQL

Most vector database tutorials tell you to spin up an entirely new database just for embeddings. If you already run PostgreSQL in production, there is a much simpler path. This pgvector tutorial shows you how to add vector similarity search directly to the database you already have, with no new infrastructure, no new connection string, and no data sync between systems. pgvector brings semantic search inside PostgreSQL where it belongs. 

TL;DR Summary

• pgvector is a free, open-source PostgreSQL extension that adds vector similarity search directly to your existing database.
• If you already use PostgreSQL, pgvector means you do not need a separate vector database at all.
• Install it with a single Docker image, enable with CREATE EXTENSION vector, and you are ready.
• pgvector supports cosine distance (<=>), L2 distance (<->), and inner product (<#>) with HNSW and IVFFlat indexing.
• It is used in RAG pipelines, semantic search, and recommendation systems at Supabase, Azure, and AWS RDS.

What is pgvector?

pgvector is an open-source PostgreSQL extension that adds a vector data type and similarity search operators to standard PostgreSQL. It enables the storage, querying, and indexing of high-dimensional vectors directly in your existing database.

The big advantage over dedicated vector databases is that your vectors and your application data live in the same table. You can join them, filter with WHERE clauses, and keep them in sync inside a single transaction with no separate write path.

pgvector is one of the most practical ways to add AI-powered search to an existing product without adding new infrastructure. HCL  GUVI’s AI Software Development Course is IITM Pravartak certified and covers the hands-on AI engineering skills that make integrating tools like pgvector into real applications straightforward.

pgvector Tutorial: Installation

The fastest way to get PostgreSQL with pgvector is to use the official Docker image, which ships with the extension pre-installed:

docker run –name pgvector-db -e POSTGRES_PASSWORD=secret -p 5432:5432 -d pgvector/pgvector:pg17

This pulls a PostgreSQL 17 image with pgvector v0.8.3 already compiled and ready. No build step required.

Install the Python packages you need:

pip install psycopg pgvector openai

Enabling pgvector in Your Database

Connect to your database and run:

Sql

CREATE EXTENSION IF NOT EXISTS vector;

That is the entire installation step at the database level. From this point, you can use the vector type in any table definition.

Creating a Table with a Vector Column

Sql

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536)
);

The number inside vector(1536) must match the dimensions of your embedding model. For OpenAI’s text-embedding-3-small, that is 1536. For sentence-transformers MiniLM, it is 384. Choose your embedding model before writing any schema, because the vector dimension is baked into the column definition and changing it later means re-embedding your entire dataset.

Inserting Vectors with Python

Python

import psycopg
from pgvector.psycopg import register_vector
from openai import OpenAI

openai_client = OpenAI()
conn = psycopg.connect("postgresql://postgres:secret@localhost:5432/postgres")
register_vector(conn)

def get_embedding(text):
    return openai_client.embeddings.create(
        model="text-embedding-3-small", input=text
    ).data[0].embedding

with conn.cursor() as cur:
    content = "pgvector adds vector search to PostgreSQL"
    embedding = get_embedding(content)
    cur.execute(
        "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
        (content, embedding)
    )
conn.commit()

Calling register_vector(conn) tells psycopg how to handle the pgvector type automatically. Without it, you get type errors on insert.

Running a Similarity Search

The <=> operator calculates cosine distance. Ordering by it ascending returns the most similar documents first.

Sql

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

The <=> operator returns cosine distance where 0 is identical and 2 is opposite, so 1 – distance gives you cosine similarity as a score between 0 and 1.

Pass your query embedding as the parameter using the same Python setup as above.

Adding an HNSW Index for Performance

Without an index, every similarity query performs a full table scan comparing the query vector against every row. That is fine at ten thousand rows. At hundreds of thousands, it gets slow.

HNSW builds a multi-layer graph where each vector is connected to its neighbors, giving much better query performance than IVFFlat in terms of the speed-recall tradeoff.

Create it with:

Sql

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Ensure the operator class in your index matches the operator in your queries, because a mismatch produces no error, only a silent regression to full-table scans. If you query with <=>, index with vector_cosine_ops. If you query with <->, use vector_l2_ops.

When to Use pgvector vs a Dedicated Vector Database

Common Mistakes to Avoid

• Forgetting to call register_vector() in Python. Without this call, psycopg does not know how to serialise Python lists into the pgvector type and raises a type error on every insert. Call it once immediately after opening your connection.
• Indexing before you have enough data. The IVFFlat index requires a training step and was likely created with too little data for the number of lists. Drop the index until the table has more data. For HNSW, you can create the index on an empty table, but it performs best once there are at least a few thousand rows.
• Mismatching distance operators and index operator classes. Using <=> in queries but vector_l2_ops in the index causes PostgreSQL to silently fall back to a full table scan. Always match the operator class in your CREATE INDEX to the distance operator in your queries.

Conclusion

This pgvector tutorial covered the complete beginner path: running PostgreSQL with pgvector in Docker, enabling the extension, creating a table with a vector column, inserting embeddings from Python, running cosine similarity search, and adding an HNSW index for production performance. If you already use PostgreSQL, pgvector is the fastest, lowest-overhead way to add semantic search to your application in 2026.

FAQs

1. What is pgvector?

pgvector is an open-source PostgreSQL extension that adds a vector data type and similarity search operators, enabling semantic search and RAG directly inside your existing PostgreSQL database.

2. Is pgvector free? 

Yes. pgvector is open-source under the PostgreSQL License. It is also available pre-installed on managed services including AWS RDS, Azure Database for PostgreSQL, and Supabase.

3. What distance operators does pgvector support? 

pgvector supports cosine distance (<=>), L2 Euclidean distance (<->), and negative inner product (<#>). Cosine distance is the most common choice for text embeddings generated by LLMs.

4. What is the difference between HNSW and IVFFlat in pgvector?

HNSW builds a graph-based index with better query performance and no training step but uses more memory. IVFFlat partitions vectors into clusters and requires training data before the index is created. HNSW is the recommended default for most use cases.

5. Should I use pgvector or a dedicated vector database like Qdrant? 

If you already run PostgreSQL and have fewer than 10 million vectors, pgvector gives you semantic search with no new infrastructure. For very large datasets or use cases requiring sub-millisecond latency at massive scale, a dedicated vector database like Qdrant or Weaviate may perform better.