Every time someone starts building a RAG app, they hit the same mental checkpoint: "Okay, I need a vector database — guess I’m signing up for Pinecone." Then they spend a week evaluating Pinecone vs Weaviate vs Qdrant vs Chroma, pick one, bolt it into the stack, and now they’re operating two databases instead of one, paying for two services, and debugging two infrastructure layers whenever something breaks at 2 AM.
Here’s the thing: if you’re already running PostgreSQL — and you almost certainly are — you already have a vector database. You just haven’t turned it on yet.
This article covers everything you need to go from a blank Postgres instance to a working RAG pipeline using pgvector and pg_vectorize. We’ll talk about real index tuning, the gotchas that will bite you in production, and when it actually makes sense to graduate to a dedicated vector store.
Why Another Specialized Database Is Probably Wrong for You
Dedicated vector databases are excellent — for specific workloads. If you’re indexing a billion documents, running hybrid search at scale, or need multi-tenancy with strict isolation per customer, sure, Qdrant or Weaviate have earned their place.
But the majority of RAG applications are not billion-document problems. They’re "index our 20,000 product docs and answer customer questions" problems. For those, dragging in a whole new database engine means new connection pooling, new backup strategy, new monitoring, new auth, and new failure modes — all for a dataset that fits in a single Postgres table.
pgvector is a Postgres extension that adds a vector column type and distance operators. It runs inside your existing Postgres process. Your existing backups cover it. Your existing connection pool handles it. Your existing ORM can query it. That’s the pitch, and it’s a strong one.
The Stack
Here’s what we’re working with:
- pgvector — the low-level extension: vector storage, indexes, distance functions
- pg_vectorize — a higher-level extension from Tembo that handles embedding generation, automatic background sync, and a cleaner API for search
You can use pgvector alone and call an embedding API yourself. That’s perfectly valid and gives you maximum control. pg_vectorize wraps that pattern into Postgres functions, which is convenient if you want the database to manage embeddings automatically. We’ll cover both approaches.
Setting Up with Docker Compose
The fastest way to get going is the Tembo image, which ships with both extensions pre-installed.
# docker-compose.yml
services:
postgres:
image: quay.io/tembo/pg17-pgvectorize:latest
environment:
POSTGRES_USER: rag
POSTGRES_PASSWORD: changeme
POSTGRES_DB: ragdb
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data
# pgvectorize uses pg_cron for background jobs, which needs this
command: >
postgres
-c shared_preload_libraries='pg_vectorize,pg_cron'
-c cron.database_name=ragdb
volumes:
pgdata:
If you’re using a plain Postgres 15/16/17 image and want to install pgvector yourself:
# On the host, after the container is running
apt-get install -y postgresql-17-pgvector
Or build a custom image:
FROM postgres:17
RUN apt-get update && apt-get install -y \
postgresql-17-pgvector \
&& rm -rf /var/lib/apt/lists/*
Connect to the database and enable the extensions:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS vectorscale; -- optional, adds DiskANN index
CREATE EXTENSION IF NOT EXISTS pg_vectorize CASCADE; -- installs pg_cron etc.
pgvector Basics: The Low-Level API
Before pg_vectorize abstracts things away, understand what you’re sitting on top of.
Storing Vectors
CREATE TABLE documents (
id bigserial PRIMARY KEY,
content text NOT NULL,
metadata jsonb,
embedding vector(1536) -- dimension must match your model
);
The dimension is fixed at table creation and must match your embedding model exactly. OpenAI text-embedding-3-small outputs 1536 dimensions. text-embedding-3-large outputs 3072. nomic-embed-text (a solid open-source choice) outputs 768. Mix these up and you’ll get an error, or worse, silent garbage results.
Inserting Embeddings
From Python, using the psycopg + openai combination:
import openai
import psycopg
import json
client = openai.OpenAI() # or point to your local Ollama/LiteLLM endpoint
def get_embedding(text: str, model="text-embedding-3-small") -> list[float]:
response = client.embeddings.create(input=text, model=model)
return response.data[0].embedding
conn = psycopg.connect("postgresql://rag:changeme@localhost/ragdb")
docs = [
{"content": "pgvector adds vector similarity search to PostgreSQL.", "meta": {"source": "docs"}},
{"content": "HNSW indexes trade memory for query speed.", "meta": {"source": "docs"}},
{"content": "IVFFlat is cheaper to build but slower to query.", "meta": {"source": "docs"}},
]
with conn.cursor() as cur:
for doc in docs:
emb = get_embedding(doc["content"])
cur.execute(
"INSERT INTO documents (content, metadata, embedding) VALUES (%s, %s, %s)",
(doc["content"], json.dumps(doc["meta"]), emb)
)
conn.commit()
Querying: Similarity Search
pgvector exposes three distance operators:
| Operator | Distance | Use when |
|---|---|---|
<-> |
L2 (Euclidean) | General-purpose, unnormalized vectors |
<=> |
Cosine | Text embeddings — most common choice |
<#> |
Inner product (negated) | Pre-normalized vectors, fastest |
-- Find the 5 most semantically similar documents to a query vector
SELECT
id,
content,
1 - (embedding <=> '[0.021, -0.045, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.021, -0.045, ...]'::vector
LIMIT 5;
In practice you’d pass the query embedding as a parameter from your application layer, not hardcode it.
Indexing: This Is Where People Get It Wrong
Out of the box, pgvector does exact nearest-neighbor search — a full sequential scan. On 10,000 rows that’s fine. On 500,000 rows you’ll notice. On 5 million rows your queries will be timing out.
You have two index types. Pick one, understand the tradeoffs.
IVFFlat
Divides the vector space into lists (Voronoi cells), searches only the probes closest cells at query time.
-- Build after you have data — IVFFlat quality depends on existing vectors
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- At query time, tune the probe count per session or globally
SET ivfflat.probes = 10;
The rule of thumb: lists = rows / 1000 for up to 1M rows, sqrt(rows) beyond that. More probes = better recall but slower queries. The default is 1 probe, which is basically useless — always set this.
HNSW
Hierarchical Navigable Small World. Builds a multi-layer proximity graph. Much faster queries, much better recall, but uses more memory and takes longer to build.
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- Tune search quality at query time
SET hnsw.ef_search = 100;
m is the number of connections per node (higher = better recall, more memory). ef_construction controls build quality. ef_search controls query-time accuracy vs speed.
For production RAG workloads, use HNSW. The memory overhead is acceptable and the query latency difference is significant at scale. IVFFlat makes more sense when you’re inserting vectors constantly and can’t afford the index rebuild cost.
Gotcha: HNSW index builds are memory-hungry. If you’re building an index on millions of vectors and your Postgres is running in a 2GB container, the build will be slow or OOM. Set maintenance_work_mem = '2GB' (or more) for the session running the CREATE INDEX command.
pg_vectorize: The Batteries-Included Layer
pg_vectorize wraps the embedding + indexing + sync lifecycle into Postgres functions. It handles calling the embedding API, storing results, and keeping them updated as your source data changes.
Configure the Embedding Model
-- Using OpenAI
ALTER SYSTEM SET vectorize.openai_key = 'sk-...';
SELECT pg_reload_conf();
-- Or using a local Ollama endpoint
ALTER SYSTEM SET vectorize.ollama_service_url = 'http://host.docker.internal:11434';
SELECT pg_reload_conf();
Create a Vectorize Job
-- Suppose you have an existing table with text content
CREATE TABLE kb_articles (
id bigserial PRIMARY KEY,
title text,
body text,
updated_at timestamptz DEFAULT now()
);
-- Register it with pg_vectorize
SELECT vectorize.table(
job_name => 'kb_embeddings',
"table" => 'kb_articles',
primary_key => 'id',
columns => ARRAY['title', 'body'], -- concatenates these for embedding
transformer => 'openai/text-embedding-3-small',
-- or: 'ollama/nomic-embed-text'
schedule => '* * * * *' -- pg_cron expression, runs every minute
);
pg_vectorize creates a shadow table to store embeddings and wires up a pg_cron job that picks up new or updated rows. You don’t touch the embedding storage directly.
Search with pg_vectorize
SELECT * FROM vectorize.search(
job_name => 'kb_embeddings',
query => 'how do I reset my password',
return_columns => ARRAY['title', 'body'],
num_results => 5
);
This call embeds your query using the same model as the job, runs the similarity search, and returns results with a similarity_score column. All the plumbing is hidden.
Building a Minimal RAG Pipeline
Here’s a complete, minimal Python example that ties everything together — retrieval from Postgres, generation with the Claude API (or any OpenAI-compatible endpoint).
import psycopg
import openai
DB_URL = "postgresql://rag:changeme@localhost/ragdb"
EMBED_MODEL = "text-embedding-3-small"
CHAT_MODEL = "claude-sonnet-4-6" # or gpt-4o, or point to claude-adapter
embed_client = openai.OpenAI()
chat_client = openai.OpenAI(
base_url="https://api.anthropic.com/v1", # adjust as needed
api_key="your-key"
)
def retrieve(query: str, top_k: int = 5) -> list[dict]:
q_emb = embed_client.embeddings.create(
input=query,
model=EMBED_MODEL
).data[0].embedding
with psycopg.connect(DB_URL) as conn:
rows = conn.execute(
"""
SELECT content, metadata,
1 - (embedding <=> %s::vector) AS score
FROM documents
ORDER BY embedding <=> %s::vector
LIMIT %s
""",
(q_emb, q_emb, top_k)
).fetchall()
return [{"content": r[0], "metadata": r[1], "score": r[2]} for r in rows]
def generate(query: str, context_docs: list[dict]) -> str:
context = "\n\n".join(d["content"] for d in context_docs)
messages = [
{"role": "system", "content": "Answer based only on the provided context. If the context doesn't contain the answer, say so."},
{"role": "user", "content": f"Context:\n{context}\n\nQuestion: {query}"}
]
resp = chat_client.chat.completions.create(
model=CHAT_MODEL,
messages=messages,
max_tokens=1024
)
return resp.choices[0].message.content
def rag(query: str) -> str:
docs = retrieve(query)
return generate(query, docs)
if __name__ == "__main__":
answer = rag("What index type should I use for high-throughput vector search?")
print(answer)
This is the full loop. Retrieval is a single parameterized SQL query. Generation is a single API call. No vector database SDK, no extra service to deploy.
Gotchas You Will Hit
Dimension mismatch at insert time. If you create a vector(1536) column and then switch to a model that outputs 768 dimensions, inserts will fail. There’s no implicit casting. You need to ALTER TABLE ... ALTER COLUMN embedding TYPE vector(768) and re-embed everything. Plan your model choice upfront.
Cosine similarity requires normalized vectors for <#>. The inner product operator <#> is fastest but assumes unit vectors. If you use it on unnormalized embeddings, your results will be wrong and you won’t get an error — just garbage rankings. Either normalize vectors in your application (numpy.linalg.norm) or stick to <=>.
HNSW doesn’t support partial indexes. If you try CREATE INDEX ... USING hnsw ... WHERE some_condition, Postgres will refuse. You’ll need to denormalize the filter condition or use a different strategy for filtered search.
Filtered search kills index performance. This is the classic vector database problem. If you do WHERE tenant_id = 5 ORDER BY embedding <=> query LIMIT 10, Postgres has two bad choices: scan the index (fast but might not return enough rows after filtering) or scan the table (slow). pgvector 0.7+ added better support for index-assisted filtered search, but you should still test your actual query patterns against real data volumes before going to production.
Cold index builds on large tables block production. Use CREATE INDEX CONCURRENTLY — pgvector supports it. Index builds without CONCURRENTLY take an AccessShareLock on the table, which blocks writes.
Embedding API latency in the write path. If you’re calling OpenAI synchronously on every INSERT, your write throughput is capped at ~50 rps (OpenAI’s typical p95 latency is 150-300ms per request). Batch your embedding calls, or use pg_vectorize’s async job approach so writes are decoupled from embedding generation.
Tuning work_mem for Vector Operations
Sorting a large number of vectors in memory for the final distance computation benefits from generous work_mem. A reasonable starting point for a dedicated RAG workload:
-- In postgresql.conf or per-session
SET work_mem = '256MB';
SET maintenance_work_mem = '2GB'; -- for index builds
Don’t set these globally high if you’re running mixed workloads — work_mem is per sort operation per connection, and it multiplies fast.
When pgvector Is Not Enough
Be honest about this. pgvector is excellent up to low tens of millions of vectors on reasonable hardware. Beyond that, or when you need:
- Multi-tenant isolation with per-tenant indexes — pg_vectorize doesn’t have a first-class answer here yet
- Real-time updates at very high ingestion rates — the HNSW index update cost adds up
- Hybrid sparse+dense search (BM25 + vector) — pgvector doesn’t do sparse vectors; you’d need
pg_search(also from Tembo) or a separate system
…then a dedicated store like Qdrant (self-hosted, excellent Rust implementation) or Weaviate starts earning its complexity cost.
But be skeptical of the dedicated database pitch until you’ve actually hit these limits. Most production RAG systems never do.
The Production Checklist
Before you call it production-ready:
- HNSW index created,
ef_searchtuned via benchmark against your actual query distribution -
maintenance_work_memset high enough for index rebuilds - Embeddings generated in batches, not synchronously per row
-
CREATE INDEX CONCURRENTLY— never blocking index builds on live tables - Backup strategy covers the
pgvectorizeshadow tables, not just your main tables - Monitoring on index build time and query p99 latency — vector search query plans can change dramatically as data volume grows
- Tested filtered search performance under your actual filter cardinality
The Bottom Line
pgvector removes the "I need a vector database" excuse for reaching for a new service. The extension is mature, actively maintained, and the HNSW implementation is competitive with dedicated stores on datasets that fit the 1-50M vector range.
pg_vectorize is a smart convenience layer when you want the database to own the embedding lifecycle — it handles the operational burden of keeping embeddings in sync as source data changes, which is genuinely annoying to build yourself.
Start here. Add a dedicated vector store only when you have specific evidence that pgvector is the bottleneck, not because Pinecone has a nicer landing page.