L50 · Vector Databases — pgvector, Pinecone & Milvus | Bangla DBMS

1. Why a Whole New Kind of Database?

Every database you have met so far — relational, document, key-value, graph — answers one fundamental question: "find rows where some column equals (or compares to) some value." That works beautifully for usernames, prices and dates. It collapses, however, the moment you ask the kind of question every modern AI app needs to answer:

"Find me the documents that mean roughly the same thing as this question."

ChatGPT, Perplexity, Gemini, এমনকি আপনার অফিসের internal AI assistant — সবাইকেই একটি কাজ করতে হয়: user-এর প্রশ্নের অর্থগত (semantic) অর্থে সবচেয়ে কাছাকাছি document খুঁজে বের করা। এটি SQL-এর WHERE name = 'Arif'-এর মতো exact match নয়। এই কাজের জন্য দরকার পড়ে একটি বিশেষ ধরনের database — Vector Database, যেখানে query হয় "অর্থে কাছাকাছি" (nearest in meaning), exact-equal নয়।

In this module you will learn what an embedding is, how distance between two embeddings encodes "similarity of meaning", which algorithms (HNSW, IVF, ScaNN) make billion-vector search fast, and how to build a real RAG (Retrieval-Augmented Generation) pipeline on top of Postgres+pgvector — the same architecture used by Notion AI, Stripe Docs, and most ChatGPT-style products today.

2. What Is an Embedding?

An embedding is a list of numbers — typically 384, 768, 1 536 or 3 072 of them — that represents the meaning of a piece of text, image, or audio. A model (e.g. OpenAI's text-embedding-3-small, Cohere's embed-v3, or open-source BGE-large) reads an input and emits a fixed-length vector. Inputs that mean similar things produce vectors that point in similar directions in this high-dimensional space.

Embedding = একটি text/image-এর "অর্থের" সংখ্যাগত প্রতিচ্ছবি — একটি দীর্ঘ vector (যেমন ৩৮৪ বা ১৫৩৬ সংখ্যা)। একটি neural model (OpenAI, Cohere, BGE ইত্যাদি) input পড়ে সেই vector তৈরি করে। দুটি বাক্যের অর্থ যত কাছাকাছি, তাদের vector-ও তত কাছাকাছি দিকে নির্দেশ করে। অর্থাৎ — অর্থ = জ্যামিতি।

embed.py

# Generate an embedding with OpenAI's text-embedding-3-small (1536-dim)
from openai import OpenAI
client = OpenAI()

def embed(text: str) -> list[float]:
    resp = client.embeddings.create(
        model="text-embedding-3-small",
        input=text,
    )
    return resp.data[0].embedding   # length = 1536

v1 = embed("How do I reset my bKash PIN?")
v2 = embed("My bKash password forgot — way to recover?")
v3 = embed("Best biryani in Dhanmondi")

# v1 and v2 will be very close. v3 will be far away.

Practical note You almost never compute embeddings by hand. You call an API (OpenAI, Cohere, Voyage, Jina) or run an open-source model (BGE, GTE, Nomic) once per document and store the resulting vector in your database. That stored vector is what you query against later.

3. Distance Metrics — How Do We Measure "Similar"?

Once each piece of text is a point in 1 536-dimensional space, "similar in meaning" becomes "close in space". There are three ways to measure that closeness:

Metric	Formula	Best for	pgvector op
Cosine	1 − (a·b)/(‖a‖‖b‖)	Text embeddings — cares only about direction, not length. Almost always the right default.	`<=>`
Dot product	−(a·b)	When vectors are already normalised (most modern models output unit-length). Fastest.	`<#>`
L2 / Euclidean	‖a − b‖	Image features, geo data, anything where magnitude matters.	`<->`

সহজ নিয়ম: text বা LLM embedding-এর জন্য সাধারণত cosine distance ব্যবহার করুন। যদি model-টি ইতিমধ্যেই unit-length vector দেয় (যেমন OpenAI ও বেশিরভাগ আধুনিক model), তাহলে dot product বেছে নিতে পারেন — ফলাফল একই, কিন্তু গণনা সামান্য দ্রুত। L2 distance বেশি ব্যবহৃত হয় image বা সংখ্যাগত feature-এ যেখানে মান (magnitude)-ও গুরুত্বপূর্ণ।

Figure 50.1 — Cosine distance = angle between vectors. Smaller angle ⇒ closer in meaning.

4. Approximate Nearest Neighbour (ANN) — Speed vs Recall

With ten thousand vectors, brute-force comparison (compare query to every stored vector) is fine. With ten million it crawls. With a billion it is impossible. So vector databases use approximate nearest-neighbour indexes that return almost the right top-k in milliseconds, accepting a small recall loss in exchange for huge speedups.

Algorithm	Idea	Strength	Used by
HNSW (Hierarchical Navigable Small World)	A multi-layer graph where each node points to nearby nodes. Search hops greedily through ever-finer layers.	Very high recall, fast queries, easy to update.	pgvector, Qdrant, Milvus, Weaviate, Pinecone
IVF (Inverted File)	Cluster vectors with k-means; at query time only search the nearest few clusters.	Memory-friendly, great at billion-scale, simpler maths.	Faiss, Milvus, pgvector (`ivfflat`)
ScaNN (Google)	IVF + asymmetric quantization tuned with anisotropic loss.	State-of-the-art on Google-scale benchmarks.	Vertex AI Matching Engine
PQ (Product Quantization)	Compress each vector to a few bytes by splitting into sub-vectors and codebook-encoding each.	10–50× memory reduction with small accuracy loss.	Faiss, Milvus, Pinecone

মূল trade-off: exact search সবচেয়ে নিখুঁত কিন্তু ধীর। ANN প্রায়-নিখুঁত (recall ≈ 95–99%) কিন্তু ১০০০ গুণ পর্যন্ত দ্রুত। বাস্তব production-এ recall একটু কম হলেও latency কম হওয়া বেশি জরুরি — তাই আজকের প্রায় সব vector DB default হিসেবে HNSW বা IVF ব্যবহার করে।

Recall vs latency knob HNSW exposes parameters like ef_search: higher ⇒ better recall, slower query. IVF exposes nprobe (how many clusters to search). Tune them based on your "how-many-misses-per-100-queries can my product tolerate?" budget.

5. The Vector-DB Landscape

There are now dozens of vector stores. They split roughly into three camps:

🐘 Inside an existing DB

pgvector — Postgres extension. Mix vectors with normal SQL columns & joins.
Atlas Vector Search — vectors inside MongoDB documents.
SingleStoreDB, Elasticsearch 8+, Redis Stack.
Best when you already use that DB and want one less moving part.

☁️ Managed pure-vector

Pinecone — fully managed, used by ChatGPT plugins, Notion AI.
Weaviate Cloud, Qdrant Cloud, Vespa Cloud.
Highest performance, simplest scaling — at the cost of vendor lock-in & price.

🔓 Open-source self-hosted

Milvus — billion-scale, distributed.
Qdrant — Rust, fast, Docker-friendly.
Weaviate — built-in modules & hybrid search.
Chroma — embedded-style, perfect for prototypes.

কোনটি বেছে নেবেন? ছোট-মাঝারি প্রজেক্ট (≤ ১০ লাখ vector) এবং ইতিমধ্যে Postgres ব্যবহার করছেন — সরাসরি pgvector use করুন, আলাদা service লাগবে না। কোটি-কোটি vector এবং no-ops দরকার হলে Pinecone। নিজে control রাখতে চাইলে এবং বড় scale-এ — Milvus বা Qdrant। দ্রুত prototype-এর জন্য Chroma।

6. Hands-On — pgvector in Postgres

Postgres + the pgvector extension is the most popular starting point in 2025–26. Below is the full flow, in real Postgres syntax:

pgvector_setup.sql

-- 1. Enable the extension (one-time)
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Schema: a documents table that stores both raw text and its 1536-dim embedding
CREATE TABLE docs (
    id        BIGSERIAL PRIMARY KEY,
    title     TEXT,
    body      TEXT,
    embedding VECTOR(1536)        -- OpenAI text-embedding-3-small dimension
);

-- 3. Insert (in real life, the vector comes from your embedding API)
INSERT INTO docs (title, body, embedding) VALUES
 ('bKash PIN reset guide',    'Steps to reset your bKash PIN ...', '[0.013, -0.041, ...]'),
 ('Nagad password recovery',   'How to recover Nagad password ...', '[0.018, -0.039, ...]'),
 ('Best biryani in Dhanmondi', 'Top 5 biryani spots ...',         '[0.420,  0.090, ...]');

-- 4. Build an HNSW index on the embedding column for fast ANN search
CREATE INDEX docs_embedding_hnsw
    ON docs USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

Now the actual semantic query — "find the 3 docs most similar to the user's question":

pgvector_query.sql

-- :q_emb is the embedding of the user's question, computed in your app
SELECT id, title,
       embedding <=> :q_emb AS distance     -- <=> is cosine distance
FROM   docs
ORDER BY embedding <=> :q_emb              -- nearest first
LIMIT  3;

<=> হলো pgvector-এর cosine distance operator। ORDER BY embedding <=> :q লেখার সাথে সাথে Postgres index-টি কাজে লাগায় — ফলে কোটি row-এর মধ্য থেকেও top-3 আসে millisecond-এ। একই query-তে আপনি SQL WHERE clause যোগ করেও metadata-filter চালাতে পারেন (যেমন WHERE lang='bn' AND created_at > '2025-01-01') — যেটি pgvector-এর সবচেয়ে বড় শক্তি: একই database-এ relational + vector।

The pattern is identical in SQLite-style playgrounds — except SQLite stores the vector as JSON or BLOB and computes distance in user code. Below is a tiny demo that simulates cosine ranking in pure SQL using a pre-computed dot product, so you can run it right here.

mini_vector_demo.sql

-- Pretend each doc is a unit-length 3-D vector (x, y, z).
-- Query vector q ≈ (0.90, 0.32, 0.28)  ~ "I forgot my mobile-banking password".
-- Cosine similarity = q·d  (already unit-length). Distance = 1 − similarity.
SELECT id, title,
       ROUND(1.0 - (x*0.90 + y*0.32 + z*0.28), 4) AS cosine_distance
FROM     docs
ORDER BY cosine_distance ASC
LIMIT    3;

7. RAG — Retrieval-Augmented Generation

Vector search alone is just smart "find similar documents". RAG is the architecture that wires it into a Large Language Model so the model can answer questions about your private data — your company wiki, your customer's order history, your textbooks — without ever fine-tuning the model.

Figure 50.2 — End-to-end Retrieval-Augmented Generation pipeline.

RAG-এর চারটি ধাপ: (১) document-গুলোকে ছোট ছোট chunk-এ ভাঙা ও প্রতিটি chunk-এর embedding নেওয়া। (২) সেই vector-গুলোকে DB-তে save করা। (৩) user-এর প্রশ্ন এলে সেটিরও embedding নিয়ে DB-তে top-k সবচেয়ে কাছাকাছি chunk খোঁজা। (৪) সেই chunk-গুলোকে LLM-এর prompt-এ যোগ করে দেওয়া, যাতে LLM আপনার নিজস্ব data-র উপর ভিত্তি করে উত্তর দেয় — hallucination অনেক কমে যায়।

rag.py

# Minimal RAG: 30 lines, no framework.
import psycopg
from openai import OpenAI

oai = OpenAI()
db  = psycopg.connect("postgresql://localhost/ragdemo")

def embed(text):
    return oai.embeddings.create(
        model="text-embedding-3-small", input=text
    ).data[0].embedding

def retrieve(question, k=4):
    qv = embed(question)
    with db.cursor() as cur:
        cur.execute(
            "SELECT title, body FROM docs "
            "ORDER BY embedding <=> %s::vector LIMIT %s",
            (qv, k),
        )
        return cur.fetchall()

def answer(question):
    chunks  = retrieve(question)
    context = "\n\n".join(f"### {t}\n{b}" for t,b in chunks)
    prompt  = f"""You are a support agent. Use ONLY the context below.
If the answer isn't there, say so.

Context:
{context}

Question: {question}"""
    return oai.chat.completions.create(
        model="gpt-4o-mini",
        messages=[{"role": "user", "content": prompt}],
    ).choices[0].message.content

print(answer("How do I reset my bKash PIN?"))

8. Hybrid Search & Real-World Pitfalls

Pure vector search misses exact matches. Search for "iPhone 16 Pro Max 256GB" and a vector index may happily return "iPhone 15 Pro" because they "feel similar". Production systems therefore combine BM25 (classical keyword search, the algorithm Elasticsearch is famous for) with vector search and merge the two ranked lists — hybrid search.

hybrid.sql

-- Reciprocal Rank Fusion of BM25 (full-text) + cosine (vector)
WITH bm25 AS (
  SELECT id, ts_rank(tsv, plainto_tsquery(:q)) AS r
  FROM   docs WHERE tsv @@ plainto_tsquery(:q) LIMIT 50
),
ann AS (
  SELECT id, 1 - (embedding <=> :qv) AS r
  FROM   docs ORDER BY embedding <=> :qv LIMIT 50
)
SELECT id, SUM(1.0/(60 + rank)) AS rrf_score
FROM   (
   SELECT id, ROW_NUMBER() OVER (ORDER BY r DESC) AS rank FROM bm25
   UNION ALL
   SELECT id, ROW_NUMBER() OVER (ORDER BY r DESC) FROM ann
) u
GROUP BY id ORDER BY rrf_score DESC LIMIT 5;

⚠️ Common pitfalls

Bad chunking — chunks too small lose context; too big dilute meaning. Aim for 200–400 tokens with 10–20% overlap.
Embedding drift — if you ever swap the embedding model, you must re-embed every document.
Dimension mismatch — query and stored vectors must share the same dimension; runtime errors otherwise.
No metadata filter — top-k by similarity often crosses tenants/languages. Always filter first.
Stale data — when a source doc updates, you must re-index its chunks. Plan a CDC pipeline.

✅ Production checklist

Store the source chunk_id & offsets so you can cite back to original text.
Pick HNSW for < 10 M vectors, IVF+PQ for billions.
Combine BM25 + vector — hybrid almost always beats either alone.
Monitor recall with a held-out eval set; alert on regressions.
Cache embeddings — they cost money each time you re-compute.

Vector DB যদিও জাদুকরী মনে হয়, বাস্তবে এটি অন্য সব database-এর মতোই engineering discipline দাবি করে — ভালো chunking strategy, একই embedding model দীর্ঘ সময় ব্যবহার, metadata filter, এবং hybrid search। এই চারটি অভ্যাস আপনার RAG system-কে "demo-quality" থেকে "production-quality"-তে নিয়ে যাবে।

9. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Embedding	A fixed-length vector that represents the meaning of an input.	একটি input-এর অর্থের সংখ্যাগত প্রতিচ্ছবি — fixed length vector।
ANN	Approximate Nearest Neighbour — fast, slightly inexact similarity search.	প্রায়-নিকটতম প্রতিবেশী খোঁজার দ্রুত algorithm।
HNSW	Multi-layer graph index, default in pgvector & Qdrant.	বহুস্তর graph-ভিত্তিক index।
IVF	Cluster-based index — cheaper memory, billion-scale.	Cluster-নির্ভর index, বিলিয়ন স্কেলে কাজ করে।
Cosine distance	Angular distance between two vectors; ignores magnitude.	দুটি vector-এর মধ্যকার কোণ-ভিত্তিক দূরত্ব।
RAG	Retrieval-Augmented Generation — feed retrieved context into an LLM.	Retrieve করা context LLM-কে দিয়ে গ্রাউন্ডেড উত্তর তৈরি করা।
Hybrid search	Combination of keyword (BM25) and vector ranking.	Keyword + vector — দুই পদ্ধতি একসাথে ব্যবহার।

10. Practice Problems

Try each problem yourself first; click Show Answer only after attempting.

প্রতিটি প্রশ্ন আগে নিজে চেষ্টা করুন। কোডের answer সরাসরি ব্রাউজারেই run করতে পারবেন।

In one sentence, define an embedding.

এক বাক্যে embedding-এর সংজ্ঞা দিন।

✨ Show Answer

Answer: An embedding is a fixed-length numeric vector produced by a model that places semantically similar inputs close together in a high-dimensional space.

Embedding হলো একটি model দ্বারা তৈরি fixed-length সংখ্যাগত vector — যেটি অর্থ-সদৃশ input-গুলোকে high-dimensional space-এ পাশাপাশি বসায়।
Why is cosine distance preferred over L2 for text embeddings?

Text embedding-এর জন্য L2-র চেয়ে cosine কেন বেছে নেওয়া হয়?

✨ Show Answer

Answer: Text embedding models encode meaning in the direction of the vector, not its magnitude. Two paraphrases may have slightly different lengths but point the same way; cosine ignores length and compares only direction, so it captures meaning-similarity better than L2.

Text embedding-এ অর্থ থাকে vector-এর "দিকে" — দৈর্ঘ্যে নয়। তাই দিকনির্দেশ-ভিত্তিক cosine distance বেশি সঠিক।

Write the Postgres SQL to create a products table that stores a 768-dimensional embedding plus an HNSW cosine index.

৭৬৮-মাত্রার embedding রাখার জন্য একটি products table এবং HNSW cosine index লিখুন।

✨ Show Answer

ans3.sql

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    description TEXT,
    embedding   VECTOR(768)
);

CREATE INDEX products_emb_hnsw
    ON products USING hnsw (embedding vector_cosine_ops);

Explain the speed-vs-recall trade-off in one paragraph.

Speed vs recall trade-off এক অনুচ্ছেদে বুঝিয়ে বলুন।

✨ Show Answer

Answer: Exact nearest-neighbour search compares the query to every stored vector — perfect recall but linear time. ANN indexes (HNSW, IVF) skip most of the data using clever structures, returning the top-k in logarithmic or sub-linear time but occasionally missing a true neighbour. Tuning knobs like ef_search or nprobe let you trade extra latency for higher recall, or vice versa.

নিখুঁত search সব vector-এর সাথে তুলনা করে — সঠিক কিন্তু ধীর। ANN অনেক vector এড়িয়ে যায় — দ্রুত কিন্তু মাঝে মাঝে কাছের প্রতিবেশী মিস করে। parameter (ef_search/nprobe) tune করে recall ও latency balance করা হয়।
Run the cosine demo from §6 and report which 3 documents are returned.

§৬-এর demo চালিয়ে শীর্ষ ৩টি document-এর নাম লিখুন।
✨ Show Answer
ans5.sql
```
SELECT id, title,
       ROUND(1.0 - (x*0.90 + y*0.32 + z*0.28), 4) AS dist
FROM     docs
ORDER BY dist ASC
LIMIT    3;
```
Top 3 — bKash PIN reset, Nagad password recovery, Rocket account block (all mobile-banking themed, as expected).
List four steps of a RAG pipeline in order.

RAG pipeline-এর চারটি ধাপ ক্রমানুসারে লিখুন।

✨ Show Answer

Answer: (1) Chunk & embed source documents → store vectors. (2) At query time, embed the user's question. (3) Retrieve the top-k nearest chunks from the vector DB. (4) Insert those chunks as context into an LLM prompt and let the LLM answer.

(১) Document chunk + embed করে DB-তে রাখা। (২) প্রশ্নকেও embed করা। (৩) DB-তে top-k নিকটতম chunk বের করা। (৪) সেগুলোকে LLM-এর prompt-এ দিয়ে উত্তর তৈরি করা।
Why is hybrid (BM25 + vector) search often better than vector-only?

Hybrid search কেন শুধুমাত্র vector search-এর চেয়ে ভালো ফল দেয়?

✨ Show Answer

Answer: Vector search captures meaning but can miss exact matches like product codes, model numbers and rare names; BM25 catches exact tokens but misses paraphrases. Combining both via Reciprocal Rank Fusion gets the best of both worlds — semantic similarity and literal precision.

Vector search অর্থ ধরে কিন্তু exact code/model number মিস করে; BM25 exact word ধরে কিন্তু paraphrase ধরে না। দুটি একসাথে ব্যবহার করলে দুই দিক থেকেই accuracy বাড়ে।
A teammate suggests storing all customers' chat history embeddings together. Why is metadata filtering essential here?

সব customer-এর chat embedding একই table-এ রাখা হলে metadata filter কেন আবশ্যক?

✨ Show Answer

Answer: Without a WHERE customer_id = ? filter, a query for customer A might surface chunks belonging to customer B simply because the meanings are similar — a serious privacy and security violation. Always combine vector similarity with hard metadata filters (tenant, language, date, ACL) before ranking.

Metadata filter ছাড়া এক customer-এর প্রশ্ন অন্য customer-এর chat টেনে আনতে পারে — এটি গুরুতর privacy/security সমস্যা। তাই tenant id, language ইত্যাদি filter আগে প্রয়োগ করতে হয়।
You change your embedding model from text-embedding-3-small (1536-dim) to BGE-large (1024-dim). What must you do?

Embedding model বদলালে কী করতে হবে?

✨ Show Answer

Answer: Re-embed every document and rebuild the index. The dimension is different (1536 → 1024), and even at the same dimension different models live in incompatible vector spaces — a query embedded by model B will not match documents embedded by model A. Plan a blue-green migration: build the new index in parallel, dual-write, then switch reads.

প্রতিটি document নতুন model দিয়ে আবার embed করতে হবে এবং index পুনর্নির্মাণ করতে হবে। দুটি model-এর vector space আলাদা, dimension আলাদা — পুরোনো vector নতুন query-র সাথে মেলে না।
Decide: a 30-employee Bangladeshi startup wants a chatbot over its 2 000-page company handbook. Which vector store and why?

৩০ জনের startup, ২০০০ পৃষ্ঠার handbook — কোন vector store ব্যবহার করবেন এবং কেন?

✨ Show Answer

Answer: pgvector. The data set is small (a few thousand chunks, well under any scale where dedicated vector DBs shine), the team almost certainly already runs Postgres for its app database, and pgvector lets them combine vectors with normal SQL filters (department, document version, ACL) in one query — no extra service, no extra bill, and one familiar backup & monitoring story.

pgvector — ছোট data, ইতিমধ্যে Postgres চালু থাকার সম্ভাবনা বেশি, এবং SQL filter-এর সাথে vector search একই query-তে চালানো যায়। আলাদা service ও খরচ লাগবে না।

1. Why a Whole New Kind of Database?

2. What Is an Embedding?

3. Distance Metrics — How Do We Measure "Similar"?

4. Approximate Nearest Neighbour (ANN) — Speed vs Recall

5. The Vector-DB Landscape

🐘 Inside an existing DB

☁️ Managed pure-vector

🔓 Open-source self-hosted

6. Hands-On — pgvector in Postgres

7. RAG — Retrieval-Augmented Generation

8. Hybrid Search & Real-World Pitfalls

⚠️ Common pitfalls

✅ Production checklist

9. Glossary (শব্দকোষ)

10. Practice Problems

Summary — Module 50