As soon as you start turning text into vectors, whether for a search feature today or a RAG (Retrieval-Augmented Generation) pipeline you build later, you end up with a pile of embeddings that need a home. So which Python vector database do you reach for: ChromaDB, Pinecone, or pgvector? That single choice shapes your cost, your scale, and how much infrastructure you babysit. Think of it like picking where to keep your photos. A folder on your laptop is free and instant but fills up. A cloud service holds millions and syncs everywhere, for a monthly bill.

And if you already pay for storage somewhere, the cheapest move is to use what you have. Vector databases split the same three ways. ChromaDB runs on your laptop. Pinecone runs in the cloud at billion-vector scale. pgvector bolts vector search onto the PostgreSQL you already run. This post helps you decide in about 30 seconds, then shows the code for each.

Approximate nearest neighbor search is a fundamental operation behind modern search, recommendations, and retrieval systems.

the premise behind every vector database

Last Updated: July 2026 | Tested on: Python 3.14.6, chromadb 1.5.9 | Difficulty: Intermediate | Reading Time: 15 minutes

Quick refresher before the comparison. A vector database stores high-dimensional embeddings and finds the most similar ones fast. Once you turn text, images, or audio into embedding vectors, the whole game becomes “find the closest matches to this one,” and that is exactly the job a vector database is built for. It is the muscle behind semantic search, recommendations, and the retrieval step in every RAG pipeline.

We focus on the three you will actually choose between in 2026: ChromaDB, Pinecone, and pgvector. You will see how similarity search works under the hood, a decision table you can screenshot, and runnable code for each option. By the end you will know which vector database fits your project and how to wire it into your Python app. (Weaviate and Qdrant are solid too, and we point to them where they fit.)

Vector Database Architecture

Tip: click the diagram to open it full screen, then use the zoom and pan controls for a closer look.

Here is the shape of the whole thing. Text or images go through an embedding model and come out as high-dimensional vectors. Those vectors land in an index: HNSW (Hierarchical Navigable Small World), IVF (Inverted File), or flat. At query time, your search text becomes a vector too, and the database measures similarity (cosine or dot product) between it and everything stored, then hands back the closest matches. The index is the one engineering decision that really matters.

Think of it like the index at the back of a textbook: instead of reading every page to find a topic, you jump straight to roughly the right place. HNSW gives fast approximate search that is good enough for almost every app. Flat (brute-force) search checks every vector, so it guarantees perfect recall but slows down as your data grows. Every vector database you will compare below, ChromaDB, Pinecone, pgvector, and friends like Weaviate and Qdrant, is just a different packaging of this same picture.

The Decision Table

ChromaDB: Local-First Vector Database

This is the one to start with. Think of ChromaDB like a notebook you keep on your own desk: open it, jot things down, and everything is right there with nothing to plug in. ChromaDB is a pip install away, stores everything in a local folder, and even brings its own embedding model, so there is no API (Application Programming Interface) key and no server. Say a developer named Prathamesh spins up a working semantic search in a handful of lines below. Everything here ran on Python 3.14.6 with chromadb 1.5.9.

📄 chromadb_demo.py: zero-config vector database

import chromadb

# Prathamesh sets up a local, persistent ChromaDB
client = chromadb.PersistentClient(path="./chroma_db")
collection = client.get_or_create_collection(
    name="python_docs",
    metadata={"hnsw:space": "cosine"},  # Use cosine similarity
)

# Add documents (ChromaDB auto-embeds with its default model)
collection.add(
    documents=[
        "Python's GIL prevents true multi-threading for CPU-bound tasks.",
        "asyncio enables concurrent I/O operations using an event loop.",
        "Multiprocessing bypasses the GIL by spawning separate processes.",
        "Free-threading in Python 3.14.6 allows disabling the GIL entirely.",
    ],
    ids=["doc1", "doc2", "doc3", "doc4"],
    metadatas=[
        {"topic": "concurrency", "post": 88},
        {"topic": "concurrency", "post": 90},
        {"topic": "concurrency", "post": 89},
        {"topic": "concurrency", "post": 88},
    ],
)

# Query with natural language
results = collection.query(
    query_texts=["How do I run Python code in parallel?"],
    n_results=3,
)

print("ChromaDB Query Results:")
for doc, meta, dist in zip(results["documents"][0], results["metadatas"][0], results["distances"][0]):
    print(f"  [{1 - dist:.3f}] (Post {meta['post']}) {doc}")

print(f"\nTotal documents: {collection.count()}")

▶ Output

ChromaDB Query Results:
  [0.517] (Post 88) Python's GIL prevents true multi-threading for CPU-bound tasks.
  [0.404] (Post 89) Multiprocessing bypasses the GIL by spawning separate processes.
  [0.399] (Post 88) Free-threading in Python 3.14.6 allows disabling the GIL entirely.

Total documents: 4

What happened here: Notice we never called an embedding API. ChromaDB downloads a small sentence-transformers model (all-MiniLM-L6-v2) the first time you add documents, then embeds everything locally. That first run pulls about 80 MB, so it takes a minute; every run after that is instant. The query “How do I run Python code in parallel?” never shares a single keyword with the stored docs, yet semantic search still surfaces the GIL (Global Interpreter Lock), multiprocessing, and free-threading notes, because their meaning is close.

One thing that surprises people: the scores sit around 0.4 to 0.5, not 0.9. That is normal for this small model on short text. The number is a cosine similarity, not a relevance percentage, so what matters is the ranking (highest first), not whether it clears some “80%” bar. Swap in a bigger embedding model and the same documents score higher, but the order barely changes. Your own scores may differ by a hair across machines and model versions, so compare positions, not exact decimals.

Pinecone: Managed Cloud at Scale

When local storage stops being enough, Pinecone takes over the part you do not want to run yourself: sharding, replication, and uptime for billions of vectors. Think of it like renting a warehouse instead of stacking boxes in your garage: someone else handles the space, the security, and the forklifts, and you pay rent every month. The trade is that you hand it real text via an embedding API (here OpenAI) and you pay a bill. Notice the modern SDK (Software Development Kit) is the pinecone package, not the old pinecone-client (renamed in 2024). Here a developer named Viraj sets up a production index.

📄 pinecone_demo.py: production vector search at billion scale

from pinecone import Pinecone, ServerlessSpec
from openai import OpenAI

# Viraj sets up Pinecone for production
pc = Pinecone(api_key="YOUR_PINECONE_API_KEY")

# Create an index
pc.create_index(
    name="python-docs",
    dimension=1536,  # OpenAI text-embedding-3-small dimension
    metric="cosine",
    spec=ServerlessSpec(cloud="aws", region="us-east-1"),
)

index = pc.Index("python-docs")
openai_client = OpenAI()

# Embed and upsert
def embed(text):
    response = openai_client.embeddings.create(
        model="text-embedding-3-small", input=text
    )
    return response.data[0].embedding

docs = [
    {"id": "d1", "text": "Python decorators wrap functions to extend behavior."},
    {"id": "d2", "text": "Context managers handle resource cleanup with the with statement."},
    {"id": "d3", "text": "Generators yield values lazily to save memory."},
]

vectors = [(d["id"], embed(d["text"]), {"text": d["text"]}) for d in docs]
index.upsert(vectors=vectors)

# Query
query_embedding = embed("How do I add behavior to functions without modifying them?")
results = index.query(vector=query_embedding, top_k=2, include_metadata=True)

print("Pinecone Query Results:")
for match in results["matches"]:
    print(f"  [{match['score']:.3f}] {match['metadata']['text']}")

▶ Output (illustrative: needs a Pinecone API key and an OpenAI key, both billed, so we do not run it here)

Pinecone Query Results:
  [0.91] Python decorators wrap functions to extend behavior.
  [0.73] Context managers handle resource cleanup with the with statement.

What happened here: The query asks how to add behavior to a function without editing it, and the decorators doc comes back on top, exactly the kind of meaning-based match you want. The shape is the same as the ChromaDB example, with two differences. First, Pinecone does not embed for you, so you call an embedding model (OpenAI text-embedding-3-small at 1536 dimensions) and the index dimension must match that number or the upsert is rejected.

Second, this runs against a hosted service, so the code needs a Pinecone API key plus an OpenAI key and both cost money. We did not execute this block (the scores above are illustrative, your real run will differ), but the code is the current pinecone SDK syntax and is ready to run once you drop in your keys.

pgvector: Vectors in PostgreSQL

Already running PostgreSQL? Then you may not need a separate vector database at all. Think of pgvector like adding a new drawer to a filing cabinet you already own, rather than buying a whole new cabinet. The pgvector extension adds a vector column type plus similarity operators, so your embeddings live right next to your normal rows and you filter them with plain SQL (Structured Query Language). The big win is the <=> operator (cosine distance), which lets one query mix a WHERE clause and a similarity sort.

Two practical notes baked into the code below: import psycopg2.extras for the JSONB helper, and call register_vector from the pgvector package so psycopg2 knows how to send a Python list as a real vector. Say an engineer named Aditi wires it into an existing database.

📄 pgvector_demo.py: vector search in your existing Postgres

import psycopg2
import psycopg2.extras
from pgvector.psycopg2 import register_vector
from openai import OpenAI

# Aditi adds vector search to existing PostgreSQL
conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
cur = conn.cursor()

# Enable pgvector extension, then teach psycopg2 the vector type
cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
conn.commit()
register_vector(conn)  # now a Python list maps to a pgvector column

cur.execute("""
    CREATE TABLE IF NOT EXISTS documents (
        id SERIAL PRIMARY KEY,
        content TEXT NOT NULL,
        embedding vector(1536),
        metadata JSONB
    );
""")

# Create HNSW index for fast search
cur.execute("""
    CREATE INDEX IF NOT EXISTS docs_embedding_idx
    ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
""")

openai_client = OpenAI()

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

# Insert documents
docs = [
    ("Python lists are ordered mutable sequences.", {"topic": "data_structures"}),
    ("Dictionaries provide O(1) key-value lookup.", {"topic": "data_structures"}),
    ("Sets guarantee uniqueness and fast membership testing.", {"topic": "data_structures"}),
]

for content, meta in docs:
    emb = embed(content)
    cur.execute(
        "INSERT INTO documents (content, embedding, metadata) VALUES (%s, %s, %s)",
        (content, emb, psycopg2.extras.Json(meta)),
    )
conn.commit()

# Query with SQL + vector similarity
query_emb = embed("Which data structure is fastest for lookups?")
cur.execute("""
    SELECT content, 1 - (embedding <=> %s::vector) AS similarity
    FROM documents
    ORDER BY embedding <=> %s::vector
    LIMIT 3;
""", (query_emb, query_emb))

print("pgvector Query Results:")
for content, sim in cur.fetchall():
    print(f"  [{sim:.3f}] {content}")

conn.close()

▶ Output (illustrative: needs a running PostgreSQL with pgvector and an OpenAI key, so we do not run it here)

pgvector Query Results:
  [0.89] Dictionaries provide O(1) key-value lookup.
  [0.76] Sets guarantee uniqueness and fast membership testing.
  [0.70] Python lists are ordered mutable sequences.

What happened here: The query “Which data structure is fastest for lookups?” ranks the dictionary doc first, which is the correct semantic match (dicts give O(1) lookup). The interesting part is the SQL. The <=> operator measures cosine distance, so 1 - (embedding <=> query) gives a similarity you can read, and ORDER BY embedding <=> query sorts by closeness. Because it is just Postgres, you could bolt a WHERE metadata->>'topic' = 'data_structures' onto the same query and combine a normal filter with vector search in one round trip.

This block needs a live PostgreSQL with the pgvector extension plus an OpenAI key, so we did not run it and the scores above are illustrative (your real numbers will differ). The code itself is correct: it registers the vector type before inserting, which is the step people most often forget.

Common Mistakes

Practice Exercises

1. Exercise 1: Take the ChromaDB demo, add five of your own sentences, and query it. Print the top three matches with their scores and check whether the ranking matches your gut feeling.
2. Exercise 2: Add a where={"topic": "concurrency"} filter to the ChromaDB query so it only searches docs tagged with that topic. Notice how the result set shrinks before similarity even runs.
3. Exercise 3: Load 10,000 short strings into ChromaDB, then time a single query with time.perf_counter(). Re-run after raising the HNSW ef search value and compare speed against result quality.

More in this series:

• LLM-as-a-Judge: Online Evals, CI Gates, and Canary Sets
• Agentic RAG in Python: Hybrid Search, Reranking, and GraphRAG
• RAG Project: Build, Evaluate, and Ship a Retrieval App

Frequently Asked Questions

Interview Questions on Python Vector Databases

How interviewers actually probe this topic: real scenarios, with answers you can say out loud.

Q: What is a vector database and why can’t a normal relational database do the same job?

A vector database stores high-dimensional embeddings and finds the most similar ones fast using approximate nearest neighbor search. A normal relational database is built for exact matches and range filters on scalar columns, so it has no efficient way to answer “find the 10 rows closest in meaning to this vector.” Extensions like pgvector close that gap by adding a vector column type and distance operators, but a plain table with a B-tree index cannot rank by cosine or dot-product similarity at scale.

Q: What is the difference between HNSW and flat (brute-force) indexing?

Flat search compares the query vector against every stored vector, so it guarantees perfect recall but gets slower linearly as the dataset grows. HNSW (Hierarchical Navigable Small World) builds a layered graph that lets the search hop close to the answer without checking everything, trading a small amount of recall for a large speed gain. Use flat for small datasets where exact results matter, and HNSW for almost everything in production.

Q: Why do the similarity scores in a ChromaDB query come back around 0.4 to 0.5 instead of 0.9?

Those numbers are cosine similarities, not relevance percentages, and the score range depends on the embedding model and the text length. The small default model (all-MiniLM-L6-v2) on short sentences naturally lands in that middle band. What matters is the ranking order, not whether a result clears some imagined 80 percent bar, so you compare positions rather than exact decimals.

Q: When would you pick pgvector over a dedicated vector database like Pinecone?

Pick pgvector when you already run PostgreSQL and your scale is in the millions of vectors, not billions. Keeping embeddings in the same database means one system to back up, one place to join vectors with your normal rows, and one query that mixes a WHERE filter with a similarity sort. Reach for Pinecone when you need managed sharding, replication, and uptime at a scale where running your own database becomes the harder problem.

Q: You upsert vectors into Pinecone and every call fails with a dimension mismatch error. What do you check first?

Check that the index dimension matches the embedding model’s output exactly. If the index was created with dimension 1536 for text-embedding-3-small but your code now sends vectors from text-embedding-3-large (3072 dimensions), every upsert is rejected. The fix is to align the two: either recreate the index at the correct dimension or switch back to the model the index was built for. A dimension is a hard contract, not something the database will pad or truncate for you.

Q: Your RAG app returns confident but irrelevant documents even though vector search “works.” How do you debug retrieval quality?

First confirm the query and the stored documents are embedded with the same model, since mixing models produces vectors that are not comparable. Then check whether you need metadata filtering: pure similarity can surface the wrong document when many chunks look alike, so add filters on date, category, or access scope to narrow the candidate set before ranking. Finally inspect your chunking, because chunks that are too large blur the meaning and chunks that are too small lose context, and both hurt the match quality more than the choice of database does.

Q: Why is registering the vector type important when using pgvector with psycopg2?

Calling register_vector(conn) from the pgvector package teaches psycopg2 how to convert a Python list into a real pgvector value when it sends the query. Without it, the driver does not know the vector column type, so inserts and similarity queries fail or send the data in the wrong format. It is the single step people most often forget, and it must run after the extension is enabled on that connection.

What’s Next?

You learned how similarity search actually works, then how to wire it into Python three ways. You can now pick a Python vector database with confidence: ChromaDB to start, Pinecone to scale, pgvector when Postgres is already in the stack. That covers the storage layer for RAG. Next, in the MLOps (Machine Learning Operations) tutorial, we move from building to operating: experiment tracking, model versioning, and the habits that keep ML systems reliable once real users depend on them. For the full roadmap from Python basics to production AI, head back to the Python + AI/ML tutorial series home.

Reference: the complete, always-current details live in the official Python documentation.

Related Posts

Previous: RAG Tutorial in Python: LlamaIndex & Retrieval-Augmented Generation

Next: Agentic RAG in Python: Hybrid Search, Reranking, and GraphRAG

Series Home: Python + AI/ML Tutorial Series