Introduction
As AI moves from experimentation into real products, one challenge appears over and over again: memory. Large language models (LLMs) are incredibly capable, but they can’t store long-term knowledge about users or applications out-of-the-box. They respond only to what they see in the prompt and once the prompt ends, the memory disappears.
This is where vector databases and especially PgVector step in.
PgVector is a PostgreSQL extension that adds first-class vector similarity search to a database you probably already use. With its rise in popularity especially in production AI systems it has become one of the simplest and most powerful ways to build AI memory.
This post is a deep dive into PgVector, how it works, why it matters, and how to implement it properly for real LLM-powered features.
What Is PgVector?
PgVector is an open-source PostgreSQL extension that adds support for storing and querying vector data types. These vectors represent high‑dimensional numerical representations embeddings generated from AI models.
Examples:
- A sentence embedding from OpenAI might be a vector of 1,536 floating‑point numbers.
- An image embedding from CLIP might be 512 or 768 numbers.
- A user profile embedding might be custom‑generated from your own model.
PgVector lets you:
- Store these vectors
- Index them efficiently
- Query them using similarity search (cosine, inner product, Euclidean)
This enables your LLM applications to:
- Retrieve knowledge
- Add persistent memory
- Reduce hallucinations
- Add personalization or context
- Build recommendation engines
And all of that without adding a new complex piece of infrastructure because it works inside PostgreSQL.
How PgVector Works
At its core, PgVector introduces a new column type:
vector(1536)
You decide the dimension based on your embedding model. PgVector then stores the vector and allows efficient search using:
- Cosine distance (1 – cosine similarity)
- Inner product
- Euclidean (L2)
Similarity Search
Similarity search means: given an embedding vector, find the stored vectors that are closest to it.
This is crucial for LLM memory.
Instead of asking the model to “remember” everything or hallucinating answers, we retrieve the most relevant facts, messages, documents, or prior interactions before the LLM generates a response.
Indexing
PgVector supports two main index types:
- IVFFlat (fast, approximate search – great for production)
- HNSW (hierarchical – even faster for large datasets)
Example index creation:
CREATE INDEX ON memories USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
Using PgVector With Embeddings
Step 1: Generate Embeddings
You generate embeddings from any model:
- OpenAI Embeddings
- Azure
- HuggingFace models
- Cohere
- Llama.cpp
- Custom fine‑tuned transformers
Example (OpenAI):
POST https://api.openai.com/v1/embeddings{
“model”: “text-embedding-3-large”,
“input”: “Hello world”
}
This returns a vector like:
[0.0213, -0.0045, 0.9983, …]
Step 2: Store Embeddings in PostgreSQL
A table for memory might look like:
CREATE TABLE memory (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
embedding vector(1536),
metadata JSONB,
created_at TIMESTAMP DEFAULT NOW()
);
Insert data:
INSERT INTO memory (content, embedding)
VALUES (
‘User likes Japanese and Mexican cuisine’,
‘[0.234, -0.998, …]’
);
Step 3: Query Similar Records
SELECT content, (embedding <=> ‘[0.23, -0.99, …]’) AS distance
FROM memory
ORDER BY embedding <=> ‘[0.23, -0.99, …]’
LIMIT 5;
This returns the top 5 most relevant memory snippets and those will be added to the prompt context.
Storing Values for AI Memory
What You Store Depends on Your Application
You can store:
- Chat history messages
- User preferences
- Past actions
- Product details
- Documents
- Errors and solutions
- Knowledge base articles
- User profiles
Recommended Structure
A flexible structure:
{
“type”: “preference”,
“user_id”: 42,
“source”: “chat”,
“topic”: “food”,
“tags”: [“japanese”, “mexican”]
}
This gives you the ability to:
- Filter search by metadata
- Separate memories per user
- Restrict context retrieval by type
Temporal Decay (Optional)
You can implement ranking adjustments:
- Recent memories score higher
- Irrelevant memories score lower
- Outdated memories auto‑expire
This creates human‑like memory behavior.
Reducing Hallucinations With PgVector
LLMs hallucinate when they lack context.
Most hallucinations are caused by missing information, not by model failure.
PgVector solves this by ensuring the model always receives:
- The top relevant facts
- Accurate summaries
- Verified data
Retrieval-Augmented Generation (RAG)
You transform a prompt from:
Without RAG:
“Tell me about Ivan’s garden in Canada.”
With RAG:
“Tell me about Ivan’s garden in Canada. Here are relevant facts from memory: The garden is 20m². – Located in Canada. – Used for planting vegetables.”
The model no longer needs to guess.
Why This Reduces Hallucination
Because the model:
- Is not guessing user data
- Only completes based on retrieved facts
- Gets guardrails through data-driven knowledge
- Becomes deterministic
PgVector acts like a mental database for the AI.
Adding PgVector to a Production App
Here’s the blueprint.
1. Install the extension
CREATE EXTENSION IF NOT EXISTS vector;2. Create your memory table
Use the structure that fits your domain.
3. Create an index
CREATE INDEX memory_embedding_idx
ON memory USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);4. Create a Memory Service
Your backend service should:
- Accept content
- Generate embeddings
- Store them with metadata
And another service should:
- Take an embedding
- Query top-N matches
- Return the context
5. Use RAG in your LLM pipeline
Every LLM call becomes:
- Embed the question
- Retrieve relevant memory
- Construct prompt
- Call the LLM
- Store new memories (if needed)
6. Add Guardrails
Production memory systems need:
- Permission control (per user)
- Expiration rules
- Filters (e.g., exclude private data)
- Maximum memory size
7. Add Analytics
Track:
- Hit rate (how often memory is used)
- Relevance quality
- Retrieval time
Common Pitfalls and How to Avoid Them
❌ Storing whole conversation transcripts
This leads to massive token usage. Instead, store summaries.
❌ Retrieving too many memories
Keep context small. 3–10 items is ideal.
❌ Wrong distance metric
Most embedding models work best with cosine similarity.
❌ Using RAG without metadata filters
You don’t want another user’s memory leaking into the context.
❌ No indexing
Without IVFFlat/HNSW, retrieval becomes extremely slow.
When Should You Use PgVector?
Use it if you:
- Already use PostgreSQL
- Want simple deployment
- Want memory that scales to millions of rows
- Need reliability and ACID guarantees
- Want to avoid new infrastructure like Pinecone, Weaviate, or Milvus
Do NOT use it if you:
- Need billion‑scale vector search
- Require ultra‑low latency for real‑time gaming or streaming
- Need dynamic sharding across many nodes
But for 95% of AI apps, PgVector is perfect.
Conclusion
PgVector is the bridge between normal production data and the emerging world of AI memory. For developers building real applications chatbots, agents, assistants, search engines, personalization engines it offers the most convenient and stable foundation.
You get:
- Easy deployment
- Reliable storage
- Fast similarity search
- A complete memory layer for AI
This turns your LLM features from fragile experiments into solid, predictable production systems.
If you’re building AI products in 2025, PgVector isn’t “nice to have” it’s a core architectural component.