PostgreSQL + pgvector
Today’s milestone
By the end of today, you should be able to:
- Understand what
pgvectoradds to PostgreSQL. - Enable the extension.
- Store embeddings in a
vector(n)column. - Query nearest matches using cosine distance.
- Use pgvector with SQLAlchemy.
- Replace yesterday’s in-memory search with database-backed search.
Do not study HNSW, IVFFlat, ANN tuning, chunking, or RAG today.
1. Why pgvector?
Yesterday, your flow was:
Feedback
↓
Embedding
↓
Python list
↓
Stored in memory
That works for learning, but not for a real application.
You need persistence:
Feedback
↓
Embedding
↓
PostgreSQL
↓
Similarity Search
pgvector is a PostgreSQL extension that adds vector types and vector similarity operators directly inside Postgres. It supports exact and approximate nearest-neighbor search and multiple distance metrics.
For your project this is attractive because you can keep:
feedback text
sentiment
category
priority
embedding
inside the same database instead of introducing a separate vector database immediately.
2. Enable pgvector
Once pgvector is installed on your PostgreSQL server, enable it in the database:
CREATE EXTENSION IF NOT EXISTS vector;
You only need to create the extension once per database.
Check:
SELECT extname
FROM pg_extension
WHERE extname = 'vector';
Expected:
vector
3. Create a table with an embedding
Suppose your embedding model generates 768-dimensional vectors.
You can create:
CREATE TABLE feedback (
id BIGSERIAL PRIMARY KEY,
feedback_text TEXT NOT NULL,
category VARCHAR(50),
sentiment VARCHAR(20),
priority VARCHAR(20),
embedding VECTOR(768),
created_at TIMESTAMPTZ DEFAULT NOW()
);
Important:
VECTOR(768)
means every stored embedding must have dimension 768.
If your model produces another dimension, use that dimension instead.
Do not randomly choose this number.
4. Store an embedding
Conceptually:
INSERT INTO feedback (
feedback_text,
embedding
)
VALUES (
'The delivery was late',
'[0.12, -0.43, 0.88]'
);
For learning, a 3-dimensional example is easier:
CREATE TABLE feedback_demo (
id BIGSERIAL PRIMARY KEY,
feedback_text TEXT NOT NULL,
embedding VECTOR(3)
);
Insert:
INSERT INTO feedback_demo (
feedback_text,
embedding
)
VALUES
(
'Delivery was late',
'[1, 0, 0]'
),
(
'Payment failed',
'[0, 1, 0]'
),
(
'Support did not respond',
'[0, 0, 1]'
);
5. Similarity operators in pgvector
pgvector provides different operators for different distance metrics.
The most important ones for now are:
<-> L2 / Euclidean distance
<#> negative inner product
<=> cosine distance
For cosine-based semantic search, use:
<=>
The official pgvector docs distinguish cosine distance from L2 distance this way.
6. Run your first cosine search
Suppose your query embedding is:
[0.9, 0.1, 0]
Run:
SELECT
id,
feedback_text,
embedding <=> '[0.9, 0.1, 0]' AS cosine_distance
FROM feedback_demo
ORDER BY embedding <=> '[0.9, 0.1, 0]'
LIMIT 3;
Important:
cosine distance:
smaller = closer
That is different from cosine similarity:
cosine similarity:
larger = more similar
7. Convert cosine distance to similarity
If you want a more intuitive score:
SELECT
id,
feedback_text,
1 - (
embedding <=> '[0.9, 0.1, 0]'
) AS similarity
FROM feedback_demo
ORDER BY embedding <=> '[0.9, 0.1, 0]'
LIMIT 3;
Conceptually:
cosine_distance = 0.05
becomes:
similarity = 0.95
That is often easier to expose in your API.
8. Add a similarity threshold
Yesterday you learned why top-k alone can be dangerous.
Your database may always return something even when everything is irrelevant.
So you can filter:
SELECT
id,
feedback_text,
1 - (
embedding <=> '[0.9, 0.1, 0]'
) AS similarity
FROM feedback_demo
WHERE
1 - (
embedding <=> '[0.9, 0.1, 0]'
) >= 0.7
ORDER BY embedding <=> '[0.9, 0.1, 0]'
LIMIT 3;
But remember:
0.7is only an example.
You should determine the threshold based on evaluation data.
9. SQLAlchemy integration
Install the Python pgvector package:
uv add pgvector
The official Python package supports SQLAlchemy and provides a VECTOR type plus distance helpers.
Model:
from pgvector.sqlalchemy import VECTOR
from sqlalchemy import BigInteger, String, Text
from sqlalchemy.orm import Mapped, mapped_column
from app.db.base import Base
class Feedback(Base):
__tablename__ = "feedback"
id: Mapped[int] = mapped_column(
BigInteger,
primary_key=True,
)
feedback_text: Mapped[str] = mapped_column(
Text,
nullable=False,
)
category: Mapped[str | None] = mapped_column(
String(50),
)
sentiment: Mapped[str | None] = mapped_column(
String(20),
)
embedding: Mapped[list[float] | None] = mapped_column(
VECTOR(768)
)
Replace 768 with the dimension of your actual embedding model.
10. Insert feedback using SQLAlchemy
Your service flow becomes:
Feedback text
↓
EmbeddingProvider
↓
embedding: list[float]
↓
Feedback model
↓
PostgreSQL
Example:
async def create_feedback(
session: AsyncSession,
embedding_provider: EmbeddingProvider,
text: str,
) -> Feedback:
embedding = await embedding_provider.embed_text(
text
)
feedback = Feedback(
feedback_text=text,
embedding=embedding,
)
session.add(feedback)
await session.commit()
await session.refresh(feedback)
return feedback
Notice something important:
Your embedding provider remains separate from PostgreSQL.
That gives you:
EmbeddingProvider
↓
Service
↓
Database
instead of coupling your database directly to an AI SDK.
11. Query nearest feedback with SQLAlchemy
The pgvector SQLAlchemy integration provides helpers such as cosine_distance().
Example:
from sqlalchemy import select
async def search_feedback(
session: AsyncSession,
query_embedding: list[float],
top_k: int = 3,
) -> list[Feedback]:
statement = (
select(Feedback)
.where(Feedback.embedding.is_not(None))
.order_by(
Feedback.embedding.cosine_distance(
query_embedding
)
)
.limit(top_k)
)
result = await session.execute(statement)
return list(
result.scalars().all()
)
This replaces yesterday’s Python loop:
for record in records:
score = cosine_similarity(...)
with:
PostgreSQL
↓
Vector comparison
↓
ORDER BY distance
↓
LIMIT top_k
12. Return similarity score too
You will often want both:
feedback
similarity
Example:
from sqlalchemy import select
async def search_feedback(
session: AsyncSession,
query_embedding: list[float],
top_k: int = 3,
):
distance = Feedback.embedding.cosine_distance(
query_embedding
)
statement = (
select(
Feedback,
distance.label("distance"),
)
.where(
Feedback.embedding.is_not(None)
)
.order_by(distance)
.limit(top_k)
)
result = await session.execute(statement)
return [
{
"feedback": feedback,
"similarity": 1 - distance_value,
}
for feedback, distance_value in result.all()
]
Now your API could eventually return:
[
{
"feedback": "Delivery was late",
"similarity": 0.94
},
{
"feedback": "Shipping took too long",
"similarity": 0.89
}
]
13. Update your semantic search service
Yesterday:
Query
↓
Embed
↓
Python loop
↓
cosine_similarity()
Today:
Query
↓
EmbeddingProvider
↓
Query vector
↓
PostgreSQL + pgvector
↓
ORDER BY cosine distance
↓
Top K
Your service can look like:
class SemanticSearchService:
def __init__(
self,
embedding_provider: EmbeddingProvider,
) -> None:
self.embedding_provider = embedding_provider
async def search(
self,
session: AsyncSession,
query: str,
top_k: int = 3,
):
query_embedding = (
await self.embedding_provider.embed_text(
query
)
)
distance = Feedback.embedding.cosine_distance(
query_embedding
)
statement = (
select(
Feedback,
distance.label("distance"),
)
.where(
Feedback.embedding.is_not(None)
)
.order_by(distance)
.limit(top_k)
)
result = await session.execute(statement)
return [
{
"id": feedback.id,
"text": feedback.feedback_text,
"similarity": 1 - distance_value,
}
for feedback, distance_value in result.all()
]
14. Your API flow
Do not overcomplicate the endpoint.
Conceptually:
GET /feedback/search?q=delivery+delay
Route:
@router.get("/feedback/search")
async def search_feedback(
query: str,
service: SemanticSearchServiceDependency,
session: DatabaseSession,
):
return await service.search(
session=session,
query=query,
top_k=3,
)
Keep responsibilities clear:
Route
↓
Service
↓
Embedding Provider
↓
PostgreSQL / pgvector
15. Why PostgreSQL + pgvector instead of a vector DB?
For your current project, pgvector is a very reasonable first choice because PostgreSQL already gives you:
- Transactions
- Relational data
- Metadata
- Filtering
- JOINs
- Backups
- Existing operational experience
and pgvector adds vector search to that same system. The pgvector project explicitly positions itself around storing vectors alongside normal Postgres data while retaining standard Postgres capabilities such as ACID transactions and joins.
Example:
WHERE category = 'DELIVERY'
combined with:
ORDER BY embedding <=> :query_embedding
That becomes powerful later.
16. One important production concern: embedding dimension
Suppose your database is:
VECTOR(768)
but your provider starts returning:
1536 dimensions
Insert will fail.
This is why your application should know:
model name
model version
dimension
Changing embedding models is effectively a data migration.
17. Another production concern: NULL embeddings
Sometimes embedding generation may fail.
Do not blindly insert fake vectors like:
[0.0] * 768
Better:
embedding = NULL
and retry embedding later.
Then your search excludes:
Feedback.embedding.is_not(None)
Zero vectors can also cause issues for certain similarity calculations, so representing “not embedded yet” as NULL is cleaner.
18. Tests for today
You do not need extensive integration testing.
Write 3 useful tests.
Test 1 — insert embedding
Verify that a feedback record with an embedding can be persisted.
Test 2 — semantic ordering
Insert:
Delivery complaint
Payment complaint
Support complaint
Search:
shipping issue
Verify the delivery-related record ranks first.
Test 3 — top-k
Search with:
top_k=2
Verify:
len(results) == 2
19. Interview preparation
What is pgvector?
pgvector is a PostgreSQL extension that adds vector data types and similarity-search operations, allowing embeddings to be stored and queried alongside relational data.
Why use pgvector instead of a dedicated vector database?
If the application already uses PostgreSQL and the vector workload is moderate, pgvector reduces infrastructure complexity and allows vector search to be combined with SQL filters, joins, transactions, and existing relational data.
What does VECTOR(768) mean?
The column stores vectors with exactly 768 dimensions. The dimension must match the embedding model output.
What does <=> mean?
In pgvector,
<=>is the cosine-distance operator. Lower distance means closer vectors.
Cosine distance versus cosine similarity?
Cosine similarity increases as vectors become more similar, while cosine distance decreases. A common conversion is
1 - cosine_distance.
Why shouldn't I calculate similarity in Python?
For small datasets you can.
But production-wise:
Database → loads only top results
is far better than:
Load 1 million vectors into Python
→ loop
→ calculate similarity
→ sort
The database is the right place for retrieval.
What happens when the embedding model changes?
I usually need to regenerate existing vectors because the new model may have a different semantic space or vector dimension.