Python & Data Science
LLMs & GenAI Under review

Vector Databases Compared: When You Actually Need One

Last time, Rae learned what embeddings are—how they turn text into coordinates that capture meaning, and why they beat keyword search for her support bot’s help docs. She saw that cosine similarity could match a customer’s question to the right help article even when no words overlapped. But she was left with a practical problem: with thousands of help-doc chunks to search, how does she store and query all these vectors fast?

Say Rae is building a library for her company’s product documentation. In a traditional library, books are filed by title or author. You want “The Great Gatsby,” you go to the ‘F’ section for Fitzgerald. That’s how SQL databases work—exact matches or specific ranges.

But what if a customer walks in and says, “I want a book that feels like a lonely rainy afternoon in Paris”? A standard index can’t help you there. You need to understand the meaning behind the request. This is the world of embeddings and vector databases—and it’s the next problem Rae needs to solve for her thousands of embedded help-doc chunks.

1. The Problem: Why Your Regular Database Feels Slow

Embeddings turn text or images into long lists of numbers — vectors. To find the most similar item, you calculate the distance between your search vector and every vector in the database.

Standard SQL databases aren’t built for this. A B-Tree index handles price < 20 fine, but it has no idea what to do with a 1536-dimensional vector. Without a specialized index, the database falls back to a Full Table Scan.

Here’s a naive search on just 10,000 items in standard Python.

import numpy as np
import time

# Create 10,000 random vectors (1536 dimensions, like OpenAI embeddings)
data = np.random.rand(10000, 1536).astype('float32')
query_vector = np.random.rand(1536).astype('float32')

def naive_search(query, library):
    # Calculate cosine similarity manually for every item
    # This is an O(n) operation
    similarities = np.dot(library, query) / (np.linalg.norm(library, axis=1) * np.linalg.norm(query))
    return np.argsort(similarities)[-5:]

start = time.time()
results = naive_search(query_vector, data)
end = time.time()

print(f"Search took: {(end - start) * 1000:.2f} ms")
This block generates synthetic data to measure the cost of brute-force vector search. `np.random.rand(10000, 1536)` creates a matrix of 10,000 rows, each with 1,536 dimensions (matching OpenAI's embedding size). The `naive_search` function computes cosine similarity by dividing the dot product of every row against the query by the product of their L2 norms—`np.linalg.norm(library, axis=1)` normalizes each row. `np.argsort` returns the indices that would sort the similarities array, and `[-5:]` grabs the top 5 most similar. The `time.time()` bracketing measures the real-world wall-clock cost.

On my machine, this runs in about 40-60ms — not bad for 10,000 items. But scale to 1 million and you’re looking at 4 to 6 seconds. Have 1,000 users searching at once? Your server melts. The math behind vector search is simple; the scale is what hurts.

2. What Vector Databases Actually Do

A vector database isn’t just a storage bucket. It’s a specialized engine that does three things:

  1. Storage: Stores dense numeric arrays efficiently.
  2. Indexing: Uses “Approximate Nearest Neighbor” (ANN) algorithms like HNSW (Hierarchical Navigable Small World). Think of a subway map that lets you skip stops to get close to your destination fast.
  3. Metadata Filtering: Lets you say, “Find me similar images, but ONLY from the ‘Nature’ category.”

Here’s how you’d query a vector database versus a regular database with the pgvector extension.

# Hypothetical comparison of API complexity

# 1. pgvector (SQL approach)
# "SELECT * FROM items ORDER BY embedding <=> '[0.1, 0.2...]' LIMIT 5 WHERE category = 'books'"

# 2. Qdrant (Vector DB approach)
# client.search(
#     collection_name="my_items",
#     query_vector=[0.1, 0.2, ...],
#     query_filter=Filter(must=[FieldCondition(key="category", match=MatchValue(value="books"))]),
#     limit=5
# )
This is a conceptual side-by-side, not runnable code. The pgvector approach uses standard SQL with the `<=>` operator for cosine distance—familiar to anyone who has written a `SELECT` query. The Qdrant approach uses a Python client with a `Filter` object containing `FieldCondition` matchers. Both achieve the same goal—similarity search combined with metadata filtering—but the SQL path lives inside your existing Postgres instance while the Qdrant path requires a separate service.

Vector databases handle the “Where” clause and similarity search at the same time. Standard databases often struggle to optimize both together.

3. The Trade-Offs: Speed vs. Simplicity vs. Cost

You have three main paths. The numbers tell the story.

FeatureIn-Memory (FAISS)PostgreSQL (pgvector)Vector DB (Qdrant/Pinecone)
Latency< 1ms100-500ms10-50ms
ComplexityLow (just a file)Medium (existing DB)High (new service)
ScaleTiny (RAM limited)Medium (Millions)Massive (Billions)

Here’s a quick benchmark simulation comparing all three.

# Simulating the latency trade-offs
results = {
    "FAISS (In-Memory)": "0.8ms - Blazing fast, but loses data if script crashes",
    "pgvector (Postgres)": "120ms - Convenient, uses your existing database",
    "Qdrant (Dedicated)": "15ms - Optimized for high-speed production use"
}

for system, stat in results.items():
    print(f"{system}: {stat}")
This block simulates the latency trade-offs by printing representative numbers for three storage tiers. The dictionary maps each system name to a string describing its typical latency and trade-off. The `for` loop iterates over each key-value pair and prints them, making the comparison readable at a glance.

4. When a Vector DB Is Worth It

Don’t buy a semi-truck to move a single box. With fewer than 100,000 documents, you probably don’t need a specialized vector DB.

It’s worth it if:

  • You need sub-100ms latency: When your app feels laggy, Postgres might be the bottleneck.
  • High query volume: Thousands of people searching at once.
  • Complex metadata: You need to filter by 10 different attributes while searching by similarity.

5. A Real Example: Building a Semantic Search Feature

Say we’re building search for a recipe app.

Stage 1: The Prototype (FAISS) You start with 5,000 recipes. FAISS is free and lives right in your Python script. It’s fast. But adding a recipe means rebuilding the whole index.

Stage 2: Growing Up (pgvector) You reach 50,000 recipes and want a real database. You add pgvector to your Postgres instance. Now you have backups and transactions. Push past 100,000, though, and your CPU spikes during searches.

Stage 3: The Big Leagues (Qdrant/Pinecone) You’re at 1 million recipes and 100,000 users. Time for a dedicated vector DB.

# Example of how a recommendation recommendation changes based on scale
def recommend_storage(doc_count, queries_per_sec):
    if doc_count < 10000 and queries_per_sec < 5:
        return "Use FAISS or simple Numpy"
    elif doc_count < 100000:
        return "Use pgvector (Postgres)"
    else:
        return "Use a dedicated Vector DB (Qdrant/Weaviate/Pinecone)"

print(f"Recommendation for 500k docs: {recommend_storage(500000, 50)}")
This function takes two arguments—document count and queries per second—and returns a storage recommendation based on simple thresholds. Under 10,000 docs with low traffic, it suggests FAISS or Numpy. Under 100,000 docs, it suggests pgvector. Above that, a dedicated vector DB. The `print` call demonstrates the function with 500k docs and 50 queries/sec, which falls into the dedicated-DB tier.

6. Vector DB Landscape: Which One?

  • Pinecone: The “Easy Button.” It’s fully managed, but costs can climb quickly.
  • Qdrant: Fast and open-source. A solid pick if you want to self-host.
  • Weaviate: Built on GraphQL, and a good fit for complex data structures.
  • pgvector: The natural choice if you’re already on Postgres and not yet at massive scale.

Which vector storage should Rae reach for as a small startup?

FAISS (in-memory, local file) — Use this for prototyping and local development. If Rae is testing her retrieval pipeline on her laptop with a few hundred chunks, FAISS is perfect: zero infrastructure, sub-millisecond lookups, no dependencies beyond pip install faiss-cpu. But it lives in RAM—if her script crashes, the index is gone. No metadata filtering, no persistence, no concurrency.

pgvector (Postgres extension) — The sweet spot for most early-stage startups, including Rae right now. If she already has a Postgres database (and most startups do), pgvector adds vector similarity search without standing up a new service. She gets transactions, backups, joins with her existing tables, and the familiar SQL interface. The trade-off: at around 100k–500k vectors with heavy query load, searches slow to 100–500ms because Postgres wasn’t built for high-dimensional ANN workloads.

Qdrant / Pinecone / Weaviate (dedicated vector DB) — Reach for these when pgvector starts buckling under load—typically when you cross ~500k vectors, need sub-50ms latency at high concurrency, or want built-in features like hybrid search and sharding. Pinecone is fully managed (no DevOps) but gets expensive fast. Qdrant is open-source and self-hostable, which fits Rae’s budget but requires someone to babysit the infrastructure. Weaviate offers GraphQL-based queries for complex schemas.

Rae’s current recommendation: Start with pgvector. She already runs Postgres for her app’s user data, so adding a vector column costs zero new infrastructure. If her bot’s traffic spikes and pgvector can’t keep up, she can migrate to Qdrant (self-hosted, free) without changing her embedding pipeline—just the storage layer.

When to avoid a dedicated vector DB entirely:

  • If you have fewer than 100k vectors and modest query volume—pgvector is simpler and cheaper.
  • If you don’t have a DevOps person—managing a separate database service is a hidden cost.
  • If you need ACID transactions across your vector and relational data—splitting them into two stores introduces the sync problems described in Section 7.

7. The Honest Gotchas

Vector databases aren’t magic. Two tradeoffs to keep in mind:

  1. Embedding Drift: Switch embedding models — from OpenAI to Cohere, for instance — and you have to recalculate every vector in your database. That’s serious rework.
  2. Metadata Sync: Update a user’s name in Postgres, and you need to update it in your vector DB too. Skip that, and your filters will be wrong.
# The Sync Problem
postgres_user = {"id": 1, "status": "inactive"}
vector_db_user = {"id": 1, "status": "active"} # Oops! Out of sync.

if postgres_user['status'] != vector_db_user['status']:
    print("Warning: Your Vector DB is returning 'inactive' users because of a sync lag!")
This block illustrates a real-world data consistency issue when using two separate stores. Two dictionaries represent the same user (id 1) but disagree on `status`—Postgres says "inactive" while the vector DB still has "active." The `if` check compares the two and prints a warning, simulating what happens when Postgres is updated but the separate vector database hasn't synced yet: search results would include users who should have been filtered out.

8. Decision Framework: Should You Use One?

Ask yourself three questions:

  1. Do I have more than 100,000 items?
  2. Is my search taking longer than 200ms?
  3. Do I have a DevOps person who can manage another database?

If all three answers are no, stick with Postgres or simple in-memory search.

9. What’s Next: Hybrid Search and Beyond

Vector search is great for “vibes” but struggles with specific words. Search for “iPhone 15” and a vector search might return “Samsung Galaxy” because they’re semantically similar.

Next up: Hybrid Search. We’ll combine the “meaning” of vector search with the “exactness” of keyword search.

Summary Checklist:

  • Use FAISS for local scripts and small prototypes.
  • Use pgvector for most early-stage startups.
  • Use Qdrant/Pinecone when you hit millions of rows or need extreme speed.

Rae has made her call: pgvector. She already runs Postgres for her app’s user data, so adding a vector column costs zero new infrastructure. With storage sorted, she’s ready to build the real thing — not just retrieval, but a complete RAG pipeline that chunks her product manual, embeds each chunk, retrieves the right ones when a customer asks a question, and feeds them to an LLM to generate an answer. That’s what she builds next.

Check Your Understanding

Questions below move from simple recall up to open-ended design, roughly following Bloom’s Taxonomy.

Remember What are the three things a vector database does, according to the article’s Section 2?

Understand In your own words, explain why a standard B-Tree index (great for price < 20) can’t help with a similarity search over 1536-dimensional vectors.

Apply Using the article’s recommend_storage function, what would it return for a startup with 75,000 documents and 3 queries per second?

Analyze The article’s “Sync Problem” example shows Postgres and the Vector DB disagreeing on a user’s status. Walk through why this kind of drift is a structural risk specifically for the “two separate stores” architecture (Postgres + Vector DB) that wouldn’t exist if you used pgvector inside your single Postgres database instead.

Evaluate The article’s “Embedding Drift” gotcha says switching AI models means recalculating every vector in the database. Critique the decision framework’s three yes/no questions (item count, latency, DevOps capacity) for not mentioning this cost at all: should “how often do we expect to change embedding models” be a fourth question in that framework, and why might a team’s answer to it change their storage choice even if they answered “no” to all three listed questions?

Create Design a phased scaling plan (following the article’s recipe-app example) for a new product: a customer support ticket search feature starting at 2,000 tickets and expected to grow to 2 million over 18 months. Using the article’s three-stage pattern (FAISS → pgvector → dedicated Vector DB), specify roughly when you’d migrate at each stage and what signal (from the article’s gotchas or decision framework) would trigger each migration.

References & Further reading

Apply What You Learned is for Supporter and Insider subscribers.

Subscribe to unlock the exercises on this post.

See plans

Related articles

Looking for something else?

Search every article by title, summary or topic.