Contents
If you already have PostgreSQL running your application, adding a separate vector database just for embeddings feels like exactly the kind of infrastructure sprawl you were trying to avoid. pgvector exists to solve that specific problem — it adds vector search directly to Postgres so you can store embeddings alongside your regular data without maintaining another service.
I started using pgvector specifically because I was tired of managing a separate vector store for a project that already had Postgres handling everything else. The "one database to rule them all" approach简化s development and operations in ways that justify any performance tradeoffs for most projects. Ever wished you could just add vector search to your existing database without rewriting your data layer? That's pgvector.
By the end of this tutorial, you'll have pgvector running, embeddings stored, and similarity queries working — all without leaving PostgreSQL. IMO, this is the most practical vector search option for teams already invested in the Postgres ecosystem :)
Figure 1: pgvector adds vector search directly to your PostgreSQL database
Why pgvector Exists
Here's the situation many teams face: you have PostgreSQL running your application, handling user data, transactions, and everything else. Then you build a RAG system and suddenly need Pinecone or Qdrant for vector search. Now you're maintaining two databases, syncing data between them, and paying for two sets of infrastructure.
pgvector eliminates that split. It's a PostgreSQL extension that adds a vector data type and similarity search operators, letting you store and query embeddings alongside your regular relational data. One database, one connection pool, one backup strategy.
Installation
Installation depends on your setup, but it's straightforward everywhere:
Local PostgreSQL
# macOS
brew install pgvector
# Ubuntu/Debian
sudo apt install postgresql-16-pgvector
# Then enable in your database
psql -c "CREATE EXTENSION vector;"Managed Services
Most managed Postgres services include pgvector:
- Supabase — enabled by default
- Neon — available as extension
- AWS RDS — install via
CREATE EXTENSION vector; - Google Cloud SQL — available on recent versions
- Azure Database for PostgreSQL — supported extension
Docker
FROM pgvector/pgvector:pg16That's it — the Docker image comes with pgvector pre-installed.
Creating Your First Vector Table
Let's create a table with a vector column and populate it:
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding vector(1536), -- OpenAI embedding dimension
metadata JSONB
);
-- Insert some documents with embeddings
INSERT INTO documents (content, embedding, metadata) VALUES
('PostgreSQL is a powerful open-source database', '[0.1, 0.2, ...]', '{"source": "docs"}'),
('pgvector adds vector search to Postgres', '[0.3, 0.4, ...]', '{"source": "tutorial"}');The vector(1536) declaration creates a column that stores 1536-dimensional vectors — matching OpenAI's text-embedding-3-small output. Adjust the dimension to match your embedding model.
Generating Embeddings
You need to generate embeddings before storing them. Here's a Python example:
import psycopg2
from openai import OpenAI
client = OpenAI()
conn = psycopg2.connect("dbname=mydb")
cur = conn.cursor()
def get_embedding(text):
response = client.embeddings.create(
model="text-embedding-3-small",
input=text
)
return response.data[0].embedding
# Store a document with its embedding
text = "pgvector enables vector search in PostgreSQL"
embedding = get_embedding(text)
cur.execute(
"INSERT INTO documents (content, embedding) VALUES (%s, %s)",
(text, str(embedding))
)
conn.commit()Running Similarity Search
Here's where pgvector earns its keep — similarity queries look natural in SQL:
-- Find the 5 most similar documents to a query vector
SELECT content,
1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;The <=> operator computes cosine distance. Lower distance means more similar. The 1 - distance gives you a similarity score where higher is better.
Distance Metrics
pgvector supports three distance metrics:
-- Cosine distance (most common for embeddings)
ORDER BY embedding <=> query_vector
-- L2 (Euclidean) distance
ORDER BY embedding <-> query_vector
-- Inner product (negative for ranking)
ORDER BY embedding <#> query_vectorCosine distance is the standard for text embeddings. Use L2 for image embeddings in some cases.
Adding Indexes for Performance
Without an index, pgvector does sequential scan — fine for thousands of vectors, slow for millions. Add an index for production:
HNSW Index (Recommended)
-- Create HNSW index for better query performance
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);m— connections per node (higher = better recall, more memory)ef_construction— build-time accuracy (higher = slower build, better index)
IVFFlat Index (Faster Build)
-- Create IVFFlat index for faster builds
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);lists— number of clusters (roughly sqrt(num_rows) is a good starting point)
When to use which:
- HNSW: better query performance, use for production workloads
- IVFFlat: faster to build, use for prototyping or frequently updated data
Filtering with Metadata
One of pgvector's biggest advantages is combining vector search with SQL filtering:
-- Search only within a specific category
SELECT content,
1 - (embedding <=> $1) AS similarity
FROM documents
WHERE metadata->>'category' = 'tutorial'
ORDER BY embedding <=> $1
LIMIT 5;
-- Search with multiple filters
SELECT content,
1 - (embedding <=> $1) AS similarity
FROM documents
WHERE metadata->>'category' = 'tutorial'
AND created_at > '2026-01-01'
AND metadata->>'language' = 'en'
ORDER BY embedding <=> $1
LIMIT 5;This is something dedicated vector databases often struggle with — complex relational filtering alongside vector search. Postgres does it natively.
Using pgvector with LangChain
LangChain integrates directly with pgvector:
from langchain_postgres import PGVector
from langchain_openai import OpenAIEmbeddings
# Connection string
CONNECTION_STRING = "postgresql+psycopg://user:pass@localhost/mydb"
# Create vector store
vector_store = PGVector(
connection_string=CONNECTION_STRING,
embedding_function=OpenAIEmbeddings(model="text-embedding-3-small"),
collection_name="documents"
)
# Add documents
vector_store.add_texts(
texts=["Document content here"],
metadatas=[{"source": "tutorial"}]
)
# Search
results = vector_store.similarity_search("vector search", k=5)Hybrid Search with pgvector
Combine vector search with full-text search using Postgres's built-in capabilities:
-- Hybrid search: vector similarity + text search
SELECT content,
1 - (embedding <=> $1) AS vector_score,
ts_rank(to_tsvector('english', content), plainto_tsquery('english', 'postgresql database')) AS text_score,
(1 - (embedding <=> $1)) * 0.7 +
ts_rank(to_tsvector('english', content), plainto_tsquery('english', 'postgresql database')) * 0.3 AS combined_score
FROM documents
ORDER BY combined_score DESC
LIMIT 5;This gives you hybrid search without any external dependencies — just Postgres doing what it does best.
Common Mistakes with pgvector
- Forgetting to create an index. Sequential scan works for small datasets but becomes unusable at scale. Always add an HNSW or IVFFlat index for production.
- Wrong dimension size. Make sure your vector column dimension matches your embedding model output exactly.
- Not vacuuming. Like all Postgres tables, vector tables need regular vacuuming to reclaim space from updates and deletes.
- Using pgvector for billions of vectors. At extreme scale, dedicated vector databases may offer better performance. pgvector shines at small to medium scale.
When to Use pgvector vs Dedicated Databases
| Use pgvector when | Use dedicated DB when |
|-------------------|----------------------|
| Already using PostgreSQL | Need billions of vectors |
| Small to medium dataset | Require specialized vector operations |
| Want one database for everything | Need vector-specific features |
| Complex relational queries needed | Maximum throughput required |
| Team knows Postgres well | Budget allows separate infrastructure |
Frequently Asked Questions
What is pgvector?
pgvector is a PostgreSQL extension that adds vector storage and similarity search capabilities. It lets you store embeddings directly in Postgres and run approximate nearest neighbor queries without a separate vector database.
Is pgvector as fast as dedicated vector databases?
For small to medium datasets (under 10 million vectors), pgvector performs comparably to dedicated solutions. For massive scale, dedicated databases like Qdrant or Pinecone may offer better performance, but pgvector eliminates infrastructure complexity.
How do I install pgvector?
Install via your package manager (brew install pgvector on Mac, apt install postgresql-16-pgvector on Linux). Then run CREATE EXTENSION vector; in your database. Many managed Postgres services like Supabase and Neon include it by default.
What indexes does pgvector support?
pgvector supports IVFFlat and HNSW indexes. IVFFlat is faster to build but slightly less accurate. HNSW provides better query performance and accuracy but uses more memory and takes longer to build.
Can pgvector replace Pinecone or Qdrant?
For many use cases, yes. If you already use PostgreSQL and your dataset is under 10 million vectors, pgvector eliminates the need for a separate vector database. You keep one database for everything.
How do I use pgvector with LangChain?
Use PGVector from langchain_postgres. It integrates directly with LangChain's retrieval pipeline, supporting document storage, similarity search, and metadata filtering all within PostgreSQL.
Wrapping This Up
pgvector brings vector search to PostgreSQL without requiring a separate database. For teams already running Postgres, it's the lowest-friction path to adding RAG capabilities — one database, one query language, one infrastructure to maintain.
Start with HNSW indexes for production, use SQL filtering for complex queries, and integrate with LangChain for RAG pipelines. FYI, pgvector is the "just works" option for most teams — it's not the fastest vector database at massive scale, but it eliminates an entire category of infrastructure complexity that dedicated solutions require :)
Now go check if your managed Postgres service supports pgvector. If it does, you're one CREATE EXTENSION away from vector search.