Vector databases explained: patterns, pitfalls and fixes

Introduction

Embeddings have become central to modern AI applications—from semantic search to recommendation engines to RAG pipelines—but storing millions of high-dimensional vectors and querying them in milliseconds is a fundamentally different problem than traditional databases. Vector databases explained: patterns, pitfalls and fixes shows how to navigate the landscape of embedding storage, search algorithms, and production deployment at scale.

When you move beyond prototype notebooks, you face real questions: Do you build on Postgres with an extension, or commit to a specialist system? How do you filter results by metadata without sacrificing speed? What indexing strategy keeps latency below 50ms when you have 100 million embeddings? This guide walks through the architecture patterns, common pitfalls we've seen in production, and the fixes that actually work.

Approximate Nearest Neighbor Indexes Like HNSW

Exact nearest neighbor search in high dimensions is computationally expensive. A brute-force scan of 10 million 1,536-dimensional vectors to find the closest 10 neighbors will take seconds—unacceptable for a user-facing API. That's where approximate nearest neighbor (ANN) algorithms become critical.

HNSW (Hierarchical Navigable Small World) is the most widely deployed ANN algorithm in production systems. It builds a multi-layer graph where each vector is a node, edges connect nearby vectors, and search navigates from a random entry point down through layers, greedily moving toward the query vector. The clever part: you don't need to visit every node. You jump through a small set of candidates, cutting query cost from O(n) to roughly O(log n) with a controlled accuracy trade-off.

HNSW trades space for speed. Building the graph requires memory overhead—typically 1.5x to 2x the raw vector data—but query latency drops dramatically. With tuning, you can search 10 million vectors in 5–20ms on commodity hardware.

Other ANN algorithms exist: LSH (Locality-Sensitive Hashing), IVF (Inverted File), and learned indices from Google and others. But HNSW strikes the best balance of speed, memory footprint, and build time for most production workloads. Many vector databases use it under the hood or offer it as an option.

Here's how you'd typically interact with an HNSW implementation via a Python client:

import hnswlib
import numpy as np

# Create index: 10-dimensional vectors, up to 100k vectors
index = hnswlib.Index(space='cosine', dim=10)
index.init_index(max_elements=100000, ef_construction=200, M=16)

# Add vectors with IDs
vectors = np.random.rand(1000, 10).astype('float32')
ids = np.arange(1000)
index.add_items(vectors, ids)

# Query: find 10 nearest neighbors
query_vector = np.random.rand(1, 10).astype('float32')
labels, distances = index.knn_query(query_vector, k=10)

print(f"Nearest neighbor ID: {labels[0][0]}, distance: {distances[0][0]}")

The ef_construction parameter controls index quality: higher values (200–500) build a more refined graph, improving query accuracy but slowing insertion. The M parameter sets the maximum number of connections per node; 16 is typical. Tuning these for your latency and accuracy budget is crucial in production.

pgvector Adds Vectors to Postgres

Many teams already run Postgres for operational data. Adding a vector extension avoids a separate database and simplifies architecture. pgvector is a battle-tested Postgres extension that lets you store embeddings in a native data type and query them with similarity operators.

Set up is straightforward: install the extension, create a vector column, and build an index. Postgres handles durability, transactions, and backup as usual.

import psycopg2
from pgvector.psycopg2 import register_vector

conn = psycopg2.connect("dbname=myapp user=postgres")
register_vector(conn)

cursor = conn.cursor()

# Create table with vector column
cursor.execute("""
  CREATE TABLE IF NOT EXISTS documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536),
    created_at TIMESTAMP DEFAULT NOW()
  );
""")

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

conn.commit()

# Insert a document with its embedding
embedding = [0.1, 0.2, 0.3, ...]  # 1536 dims
cursor.execute(
    "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
    ("The quick brown fox", embedding)
)

# Find similar documents
query_embedding = [0.15, 0.22, 0.31, ...]
cursor.execute("""
  SELECT id, content, embedding <-> %s AS distance
  FROM documents
  ORDER BY distance
  LIMIT 10;
""", (query_embedding,))

results = cursor.fetchall()
cursor.close()
conn.close()

pgvector supports multiple distance metrics: <-> (cosine), <=> (L2), and <#> (negative dot product). It integrates with Postgres transactions, so you can wrap embeddings and metadata updates in the same ACID transaction—a huge win for consistency.

The downside: performance degrades at scale. Beyond 100 million vectors on a single instance, query latency creeps up and index build times become painful. Postgres is also not optimized for the hardware patterns that pure vector stores are. If you're doing only occasional lookups on moderate-sized datasets (a few million vectors), pgvector is a pragmatic choice. If you're building a semantic search engine with 500 million documents, you'll likely outgrow it.

Managed Options: Pinecone, Weaviate, Qdrant

Specialist vector databases handle the scaling concerns for you. The three most mature managed options are Pinecone, Weaviate, and Qdrant. Each makes different trade-offs.

Pinecone is a fully managed cloud service. You provision an index, send embeddings via REST or gRPC, and Pinecone handles sharding, replication, and scaling. The appeal is simplicity: no ops overhead, automatic failover, and pay-as-you-go pricing. The cost is vendor lock-in and less control over tuning parameters. It works well for small-to-medium teams on a tight engineering budget.

Weaviate is open-source and can run self-hosted or managed. It stores vectors alongside rich metadata in a graph-like structure, making filtered queries natural. Weaviate also has built-in integration with language models for on-the-fly vectorization. If you need flexibility and fine-grained control, self-hosting Weaviate on Kubernetes gives you that.

Qdrant is also open-source, strongly focused on performance. It supports payload filtering (metadata), multi-vector search, and custom similarity functions. The Qdrant team has published detailed performance benchmarks, which is reassuring. Hosting options range from managed cloud to Docker to Kubernetes.

Here's a minimal example with Pinecone:

from pinecone import Pinecone

pc = Pinecone(api_key="your-api-key")
index = pc.Index("my-index")

# Upsert vectors with metadata
index.upsert(vectors=[
    {
        "id": "doc-1",
        "values": [0.1, 0.2, 0.3, ...],
        "metadata": {"source": "blog", "author": "alice"}
    },
    {
        "id": "doc-2",
        "values": [0.15, 0.22, 0.31, ...],
        "metadata": {"source": "paper", "author": "bob"}
    }
])

# Query with metadata filter
results = index.query(
    vector=[0.12, 0.21, 0.32, ...],
    top_k=10,
    filter={"source": {"$eq": "blog"}}
)

for match in results["matches"]:
    print(f"ID: {match['id']}, Score: {match['score']}")

Choosing between these comes down to your scale, budget, and operational comfort. Teams with Kubernetes expertise and strict data residency requirements often pick self-hosted Weaviate or Qdrant. Startups and growing companies lean toward Pinecone's simplicity. Neither choice is wrong; understand your constraints.

Filter by Metadata Alongside Similarity

A vector database that only does pure similarity search is limited. In practice, you almost always want to combine semantic relevance with filtering: "Find the 10 most similar documents where category = 'ML' and created_after = '2024-01-01'."

This is where many teams stumble. A naive approach—retrieve all k-nearest neighbors, then filter in application code—is wasteful. If you ask for the top 100 neighbors but only 5 pass your filter, you've wasted 95 lookups.

Modern vector databases handle this efficiently by pushing the filter down into the index traversal. When searching an HNSW graph, you only consider nodes whose metadata meets the filter criteria. This keeps the result set tight and latency low.

Here's an example with Qdrant, which has first-class filter support:

from qdrant_client import QdrantClient
from qdrant_client.models import Filter, FieldCondition, MatchValue

client = QdrantClient(url="http://localhost:6333")

# Upsert points (vectors + payload/metadata)
client.upsert(
    collection_name="documents",
    points=[
        {
            "id": 1,
            "vector": [0.1, 0.2, 0.3, ...],
            "payload": {
                "title": "AI Safety",
                "category": "ml",
                "published": "2024-01-15"
            }
        },
        {
            "id": 2,
            "vector": [0.12, 0.22, 0.31, ...],
            "payload": {
                "title": "Web Dev",
                "category": "backend",
                "published": "2024-02-01"
            }
        }
    ]
)

# Search with metadata filter
results = client.search(
    collection_name="documents",
    query_vector=[0.11, 0.21, 0.32, ...],
    query_filter=Filter(
        must=[
            FieldCondition(
                key="category",
                match=MatchValue(value="ml")
            )
        ]
    ),
    limit=10
)

for hit in results:
    print(f"ID: {hit.id}, Score: {hit.score}, Title: {hit.payload['title']}")

The filter is evaluated during traversal, not after. This design pattern scales to millions of metadata conditions without query explosion. Some databases (like Pinecone) use range filters; others (like Qdrant) support richer boolean expressions. Understand your tool's filter syntax and test performance with your typical filter patterns in staging before production.

Common Mistakes

Mistake 1: Not tuning index parameters for your latency budget. HNSW's ef_construction and M parameters have direct trade-offs with build time and query latency. We've seen teams use defaults and wonder why they don't meet SLAs. Benchmark on a production-scale sample dataset before deploying.

Mistake 2: Mixing vector search with complex joins in a traditional database. If you're doing a similarity search in one system and then joining against users, orders, or product tables in another, you'll create latency nightmares. Store denormalized metadata in the vector database itself, or accept a two-stage lookup (vector search, then enrich) with careful caching.

Mistake 3: Underestimating the cost of re-indexing. Rebuilding an HNSW index on 100 million vectors can take hours. If your embedding model changes, you can't simply re-embed and update in place—you need a re-index, which is a production event. Plan for this; maybe keep a read replica or a shadow index during the migration.

Mistake 4: Ignoring cold-start vector quality. If your embeddings come from a fine-tuned model or from a closed-source API, small changes in the model or API version can degrade similarity scores. Monitor embedding quality; store the model version or embedding timestamp with each vector so you can detect drift.

Mistake 5: Overloading metadata filters without testing cardinality. A filter like "user_id = 42" might match only 1% of vectors, or it might match 50%. If it matches 50% and you're filtering millions, the index advantage shrinks. Profile your typical filters; if you have a "hot" filter that's too broad, consider denormalizing or pre-computing candidate sets.

Key Takeaways

• HNSW is the standard ANN algorithm: it trades space for speed, cutting query cost to roughly O(log n) on disk or memory lookups.
• pgvector is a pragmatic choice if you already run Postgres and have fewer than 100 million vectors; it avoids a new system but has scaling limits.
• Specialist vector databases (Pinecone, Weaviate, Qdrant) scale beyond those limits and add managed ops; choose based on your team's expertise and budget.
• Metadata filtering must be pushed down into index traversal, not done in application code, to avoid wasteful lookups and latency spikes.
• Tune index parameters (ef_construction, M) to match your latency SLA, and test on production-scale data before going live.
• Plan for embedding model changes and re-indexing overhead; it's not a one-time setup.
• Monitor embedding quality and store provenance (model version, timestamp) to catch drift early.