pgvector — Vector Similarity Search Inside PostgreSQL

What it is

pgvector is a PostgreSQL extension that adds a native vector data type, HNSW and IVFFlat indexes, and distance operators for similarity search. It lets you run semantic search, RAG retrieval, and recommendation workloads inside the same database as your application data.

The extension targets teams that already use PostgreSQL and want to add vector search without deploying a separate vector database. It keeps your operational complexity low by reusing your existing database infrastructure.

The project is actively maintained and suitable for both individual developers and teams looking to integrate it into their existing toolchain. Documentation and community support are available for onboarding.

How it saves time or tokens

pgvector eliminates the need to sync data between PostgreSQL and a dedicated vector store. Embeddings live in the same table as your application data, enabling SQL JOINs between vectors and structured data. Transactional consistency means your vector index stays in sync with your data automatically. One backup, one monitoring stack, one connection pool.

How to use

1. Install the pgvector extension: CREATE EXTENSION vector;
2. Add a vector column to your table: ALTER TABLE items ADD COLUMN embedding vector(1536);
3. Insert embeddings generated by your embedding model (OpenAI, Cohere, local models).
4. Create an index and query by similarity using distance operators.

Example

-- Enable the extension
CREATE EXTENSION vector;

-- Create a table with embeddings
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding vector(1536)
);

-- Create an HNSW index for fast approximate search
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Find the 5 most similar documents
SELECT id, title, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 5;

Related on TokRepo

• AI Tools for RAG — RAG frameworks and retrieval tools that work with pgvector.
• AI Tools for Database — Database tools and extensions for AI workloads.

Common pitfalls

• Not creating an index before querying. Without an HNSW or IVFFlat index, pgvector does exact (brute-force) search, which is slow for tables with more than 10,000 rows.
• Using the wrong distance operator. <=> is cosine distance, <-> is L2 distance, <#> is inner product. Match the operator to how your embeddings were trained.
• Storing embeddings with the wrong dimension. The vector column dimension must match your embedding model's output dimension exactly. A mismatch causes insertion errors.
• Not reading the changelog before upgrading. Breaking changes between versions can cause unexpected failures in production. Pin your version and review release notes.