Vector Databases Explained: Choosing, Configuring, and Querying Pinecone, Weaviate, and pgvector for LLM Applications

Introduction

Imagine you've built a customer support chatbot for a SaaS company. You have 50,000 support articles, product docs, and past ticket resolutions. A user asks: "How do I migrate my billing settings after upgrading my plan?" A keyword search returns articles about billing and articles about migrations — most of them irrelevant. But what you actually need is a search engine that understands meaning, not just words.

That's precisely what a vector database does. Rather than matching on keywords, it finds documents that are semantically similar to the query — documents that mean the same thing, even if they use completely different words. This capability is the backbone of modern LLM applications: retrieval-augmented generation (RAG) systems, semantic search, recommendation engines, memory for AI agents, and more.

By the end of this lesson, you'll be able to choose the right vector database for your project, configure it correctly, load embeddings into it, and query it in Python. We'll work hands-on with all three of the most popular options: Pinecone (managed cloud), Weaviate (open-source/managed hybrid), and pgvector (a PostgreSQL extension). You'll understand not just how to use them but why each design decision matters.

What you'll learn:

• What a vector database is and how it differs from a traditional database
• How embeddings are stored, indexed, and queried using approximate nearest neighbor search
• How to set up and query Pinecone, Weaviate, and pgvector with real Python code
• How to choose among the three for different production scenarios
• Common configuration pitfalls that kill performance or accuracy

---

Prerequisites

Before diving in, you should have:

• Basic Python skills (functions, dictionaries, pip install)
• A conceptual understanding of what embeddings are — if you need a refresher, read Embeddings Explained: What They Are, How They Work, and How to Generate Them with the OpenAI and Sentence Transformers APIs before continuing
• Familiarity with the OpenAI API is helpful; see Using the OpenAI API with Python if you haven't used it before

---

What Is a Vector Database, Really?

Let's build intuition from the ground up. An embedding is a list of floating-point numbers — a vector — that represents the meaning of a piece of text in a high-dimensional space. Two pieces of text that mean similar things will have vectors that point in similar directions.

A vector database is optimized for one primary operation: given a query vector, find the stored vectors that are most similar to it. This is called nearest neighbor search.

The naive approach — computing the distance from the query vector to every single stored vector — works fine at small scale. With 1,000 documents, it's fast. With 10 million documents, it's prohibitively slow. Vector databases solve this with Approximate Nearest Neighbor (ANN) indexes — data structures that trade a tiny bit of accuracy for enormous speed gains. The most common algorithm used is HNSW (Hierarchical Navigable Small World), which organizes vectors into a layered graph structure that you can traverse efficiently during search.

Here's what this means practically: a well-configured vector database can search 100 million vectors in under 100 milliseconds, returning the top 10 most semantically similar results with 95%+ recall (meaning it finds the true nearest neighbors almost every time).

Key insight: A vector database is not just a database that stores vectors. It's a system with a specialized index that makes ANN search fast at scale. Storing vectors in a regular SQL column and doing ORDER BY cosine_distance() works fine for thousands of records but collapses at millions.

Three dimensions determine which vector database is right for you:

1. Scale and throughput — how many vectors, how many queries per second?
2. Operational overhead — do you want to manage infrastructure, or pay for managed service?
3. Filtering and hybrid search — do you need to combine vector similarity with structured metadata filters?

Let's look at each option with those dimensions in mind.

---

Option 1: Pinecone — Managed, Serverless Vector Search

Pinecone is a fully managed, purpose-built vector database. You don't install anything. You create an account, create an index, and start inserting and querying vectors via a REST API or Python SDK. The infrastructure, replication, scaling, and index management are entirely invisible to you.

Setting Up Pinecone

Install the SDK and create an index:

pip install pinecone-client openai

import pinecone
import openai
import os

# Initialize Pinecone
pinecone.init(
    api_key=os.environ["PINECONE_API_KEY"],
    environment="us-east1-gcp"  # found in your Pinecone console
)

# Create an index — do this once
# dimension must match your embedding model's output size
# OpenAI text-embedding-3-small outputs 1536 dimensions
pinecone.create_index(
    name="support-docs",
    dimension=1536,
    metric="cosine"  # cosine similarity is standard for text
)

index = pinecone.Index("support-docs")

Inserting Vectors

Vectors in Pinecone are stored as tuples of (id, vector, metadata). The metadata is structured JSON that you can filter on during queries.

def embed_text(text: str) -> list[float]:
    """Generate an embedding using OpenAI's API."""
    client = openai.OpenAI(api_key=os.environ["OPENAI_API_KEY"])
    response = client.embeddings.create(
        input=text,
        model="text-embedding-3-small"
    )
    return response.data[0].embedding

# Sample support articles
articles = [
    {
        "id": "doc-001",
        "text": "To migrate your billing settings after a plan upgrade, navigate to Account > Billing > Transfer Settings.",
        "category": "billing",
        "product_version": "v3"
    },
    {
        "id": "doc-002",
        "text": "API rate limits are enforced per workspace. Upgrading your plan increases the limit from 1000 to 5000 requests per minute.",
        "category": "api",
        "product_version": "v3"
    },
]

# Build the upsert payload
vectors_to_upsert = []
for article in articles:
    embedding = embed_text(article["text"])
    vectors_to_upsert.append((
        article["id"],
        embedding,
        {"category": article["category"], "version": article["product_version"]}
    ))

# Upsert in batches (Pinecone recommends batches of 100)
index.upsert(vectors=vectors_to_upsert)

Querying with Metadata Filters

def search_support_docs(query: str, category: str = None, top_k: int = 5):
    query_embedding = embed_text(query)
    
    # Optional metadata filter
    filter_dict = {}
    if category:
        filter_dict["category"] = {"$eq": category}
    
    results = index.query(
        vector=query_embedding,
        top_k=top_k,
        include_metadata=True,
        filter=filter_dict if filter_dict else None
    )
    
    return results["matches"]

# Usage
matches = search_support_docs(
    query="How do I change billing after upgrading?",
    category="billing"
)
for match in matches:
    print(f"Score: {match['score']:.4f} | ID: {match['id']}")
    print(f"Metadata: {match['metadata']}\n")

Warning: Pinecone's metadata filters use their own query syntax, not SQL. Read the filter documentation carefully — the $eq, $in, and $gte operators behave like MongoDB-style filters, not Python boolean expressions. Filtering on fields that aren't explicitly indexed as metadata is a common source of bugs.

Pinecone is the right choice when you want to ship fast, don't have a dedicated infrastructure team, and are building at serious scale (millions of vectors, high QPS). Its free tier is genuinely usable for prototypes.

---

Option 2: Weaviate — Open Source with Hybrid Search

Weaviate is an open-source vector database you can run locally via Docker, deploy on Kubernetes, or use through Weaviate Cloud Services (WCS). Its killer feature is hybrid search — the ability to combine vector similarity search with BM25 keyword search in a single query. This is enormously useful when semantic search alone produces noisy results.

Running Weaviate Locally

# Start Weaviate with Docker
docker run -d \
  -p 8080:8080 \
  -e QUERY_DEFAULTS_LIMIT=20 \
  -e AUTHENTICATION_ANONYMOUS_ACCESS_ENABLED=true \
  -e DEFAULT_VECTORIZER_MODULE=none \
  -e ENABLE_MODULES="" \
  cr.weaviate.io/semitechnologies/weaviate:1.24.0

pip install weaviate-client openai

Defining a Schema and Inserting Data

Weaviate uses a schema that defines object classes (similar to table definitions). Each class specifies which properties exist and whether they're vectorizable.

import weaviate
import openai
import os

client = weaviate.Client("http://localhost:8080")

# Define the schema for support articles
schema = {
    "class": "SupportArticle",
    "description": "Customer support documentation",
    "vectorizer": "none",  # we'll supply our own vectors
    "properties": [
        {
            "name": "content",
            "dataType": ["text"],
            "description": "Full article text"
        },
        {
            "name": "category",
            "dataType": ["text"],
            "description": "Article category"
        },
        {
            "name": "articleId",
            "dataType": ["text"],
            "description": "Unique article identifier"
        }
    ]
}

# Create the class (only run once)
client.schema.create_class(schema)

openai_client = openai.OpenAI(api_key=os.environ["OPENAI_API_KEY"])

def embed_text(text: str) -> list[float]:
    response = openai_client.embeddings.create(
        input=text,
        model="text-embedding-3-small"
    )
    return response.data[0].embedding

articles = [
    {
        "articleId": "doc-001",
        "content": "To migrate your billing settings after a plan upgrade, navigate to Account > Billing > Transfer Settings.",
        "category": "billing"
    },
    {
        "articleId": "doc-002",
        "content": "API rate limits are enforced per workspace. Upgrading your plan increases the limit from 1000 to 5000 requests per minute.",
        "category": "api"
    },
]

# Insert objects with pre-computed vectors
with client.batch as batch:
    batch.batch_size = 50
    for article in articles:
        vector = embed_text(article["content"])
        batch.add_data_object(
            data_object={
                "content": article["content"],
                "category": article["category"],
                "articleId": article["articleId"]
            },
            class_name="SupportArticle",
            vector=vector
        )

Querying with Hybrid Search

This is where Weaviate shines. The hybrid query type lets you blend vector similarity with BM25 keyword relevance:

def hybrid_search(query: str, alpha: float = 0.75, limit: int = 5):
    """
    alpha=1.0 means pure vector search
    alpha=0.0 means pure BM25 keyword search
    alpha=0.75 weights heavily toward semantic similarity
    """
    query_vector = embed_text(query)
    
    result = (
        client.query
        .get("SupportArticle", ["content", "category", "articleId"])
        .with_hybrid(
            query=query,
            alpha=alpha,
            vector=query_vector
        )
        .with_limit(limit)
        .with_additional(["score", "explainScore"])
        .do()
    )
    
    return result["data"]["Get"]["SupportArticle"]

results = hybrid_search("billing migration plan upgrade")
for r in results:
    print(f"Score: {r['_additional']['score']}")
    print(f"Content: {r['content'][:100]}...")
    print()

Tip: Start with alpha=0.75 for most RAG use cases and tune it empirically using a test set of queries you care about. If users tend to search with exact product names or version numbers, lean closer to alpha=0.5 to give keyword matching more weight.

Weaviate is the right choice when you need hybrid search, want self-hosted control over your data (common in regulated industries), or want to add graph-like relationships between objects. It's also a natural fit if you're building the kind of knowledge graph-augmented RAG system that benefits from linked object structures.

---

Option 3: pgvector — Vector Search Inside PostgreSQL

pgvector is a PostgreSQL extension that adds a native vector column type and ANN index support. If your application already runs on Postgres, this means you can add semantic search without adding a new piece of infrastructure. You get the full power of SQL — JOINs, transactions, complex WHERE clauses, and access control — combined with vector similarity search.

Installing and Enabling pgvector

-- In psql, once the extension is installed on your Postgres server
CREATE EXTENSION IF NOT EXISTS vector;

You can run this locally with Docker:

docker run -d \
  -e POSTGRES_PASSWORD=password \
  -p 5432:5432 \
  ankane/pgvector

Creating a Table with a Vector Column

CREATE TABLE support_articles (
    id          SERIAL PRIMARY KEY,
    article_id  TEXT UNIQUE NOT NULL,
    content     TEXT NOT NULL,
    category    TEXT,
    version     TEXT,
    embedding   vector(1536)  -- dimension matches your embedding model
);

-- Create an HNSW index for fast ANN search
-- This replaces the older IVFFlat index for most use cases
CREATE INDEX ON support_articles
USING hnsw (embedding vector_cosine_ops);

Inserting and Querying from Python

import psycopg2
import openai
import os
import json

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="postgres",
    user="postgres",
    password="password"
)
cur = conn.cursor()

openai_client = openai.OpenAI(api_key=os.environ["OPENAI_API_KEY"])

def embed_text(text: str) -> list[float]:
    response = openai_client.embeddings.create(
        input=text,
        model="text-embedding-3-small"
    )
    return response.data[0].embedding

# Insert an article
def insert_article(article_id: str, content: str, category: str, version: str):
    embedding = embed_text(content)
    cur.execute(
        """
        INSERT INTO support_articles (article_id, content, category, version, embedding)
        VALUES (%s, %s, %s, %s, %s)
        ON CONFLICT (article_id) DO UPDATE
            SET content = EXCLUDED.content,
                embedding = EXCLUDED.embedding
        """,
        (article_id, content, category, version, str(embedding))
    )
    conn.commit()

insert_article(
    "doc-001",
    "To migrate your billing settings after a plan upgrade, navigate to Account > Billing > Transfer Settings.",
    "billing",
    "v3"
)

# Semantic similarity search with SQL filter
def semantic_search(query: str, category: str = None, limit: int = 5):
    query_embedding = embed_text(query)
    
    if category:
        cur.execute(
            """
            SELECT article_id, content, category,
                   1 - (embedding <=> %s::vector) AS similarity
            FROM support_articles
            WHERE category = %s
            ORDER BY embedding <=> %s::vector
            LIMIT %s
            """,
            (str(query_embedding), category, str(query_embedding), limit)
        )
    else:
        cur.execute(
            """
            SELECT article_id, content, category,
                   1 - (embedding <=> %s::vector) AS similarity
            FROM support_articles
            ORDER BY embedding <=> %s::vector
            LIMIT %s
            """,
            (str(query_embedding), str(query_embedding), limit)
        )
    
    return cur.fetchall()

results = semantic_search("changing billing after plan change", category="billing")
for article_id, content, category, similarity in results:
    print(f"[{similarity:.4f}] {article_id}: {content[:80]}...")

The <=> operator in pgvector computes cosine distance. Use <-> for L2 (Euclidean) distance and <#> for inner product. For text embeddings, cosine distance is almost always the right choice.

Note: pgvector's HNSW index doesn't search rows that the query planner decides to skip via WHERE clauses. This is actually an advantage: you get vector search within a filtered result set, which can be more accurate than Pinecone's post-filter approach on small filtered sets. However, for very large filtered sets, the index may not be fully utilized — benchmark your specific query patterns.

pgvector is the right choice when you already use PostgreSQL, when your team knows SQL deeply, or when you need strong consistency guarantees and complex relational queries alongside vector search. It's also the most cost-effective option since you're adding capabilities to infrastructure you're already paying for.

---

Choosing the Right Tool: A Decision Framework

Here's how to think through the choice:

Factor	Pinecone	Weaviate	pgvector
Operational complexity	Very low (managed)	Medium (self-host or managed)	Low (if you already run Postgres)
Scale ceiling	Billions of vectors	Hundreds of millions	Tens of millions (per instance)
Hybrid search	Limited	Excellent (native BM25+vector)	Possible with extensions
SQL/relational joins	No	No	Yes, full SQL
Cost model	Pay per vector + queries	Infrastructure cost	Postgres cost
Data sovereignty	Cloud only	Self-hostable	Full control

For a startup shipping a RAG prototype fast: Pinecone. For a regulated enterprise that can't send data to a third-party cloud: self-hosted Weaviate. For a team that already runs Postgres and wants to avoid new infrastructure: pgvector.

Key insight: The best vector database is almost always the one that fits your existing stack, not the one with the best benchmark numbers. A pgvector setup that your team can operate confidently will outperform a cutting-edge managed service that nobody understands.

If you're building a full RAG pipeline, this choice is just one piece. You'll also need to think carefully about chunking strategies for RAG since the way you split documents dramatically affects retrieval quality, and how you build a reranking layer to improve precision after the initial retrieval.

---

Hands-On Exercise

Build a minimal end-to-end semantic search system using pgvector (since it requires no external accounts). You'll embed a small dataset of job postings and search them by skill.

Setup: Make sure you have Docker installed. Run the pgvector Docker command from the pgvector section above.

Your task:

1. Create the support_articles table (or a job_postings table with columns: id, title, description, tech_stack, embedding vector(1536)).

2. Write a Python function that takes a list of job posting dictionaries and inserts them with embeddings. Use text-embedding-3-small from OpenAI (or all-MiniLM-L6-v2 from sentence-transformers if you want a free local option — note it produces 384-dimensional vectors, so set vector(384) instead).

3. Insert at least 5 realistic job postings with varied descriptions (data engineer, ML engineer, backend engineer, etc.).

4. Write a search function and test these queries:

   • "experience with distributed data pipelines"
   • "building REST APIs in Python"
   • "training neural networks on large datasets"

5. Check whether the top result makes intuitive sense. If it doesn't, try re-reading the embeddings explanation article to understand why certain texts are or aren't semantically close.

Stretch goal: Add a tech_stack filter to your query function and verify that filtering by "python" correctly narrows results.

---

Common Mistakes & Troubleshooting

Dimension mismatch errors. This is the most common beginner mistake. If your index was created for 1536 dimensions and you later switch to a different embedding model (say, text-embedding-3-large which produces 3072 dimensions), every insert and query will fail. Always create your index with the correct dimension before generating any embeddings, and treat the model-index pair as a coupled unit.

Not batching inserts. Inserting vectors one at a time is slow across all three databases. Pinecone recommends batches of 100. Weaviate's batch client handles this automatically. For pgvector, use executemany() or COPY for bulk loads. A 10,000-document load that takes 45 minutes single-threaded might take 3 minutes with proper batching.

Forgetting to create the ANN index in pgvector. If you insert vectors into a pgvector table without running CREATE INDEX ... USING hnsw, every query performs a full sequential scan. Your first few queries will feel fine, then they'll slow to a crawl as data grows. Always create the index, and create it after bulk-inserting your initial data (it's faster to index existing data than to maintain the index during bulk insert).

Querying with unfiltered metadata in Pinecone. Pinecone's metadata filtering doesn't pre-filter; it post-filters after ANN retrieval. If your filter eliminates most results (e.g., filtering to 0.1% of vectors), you may get fewer results than top_k even when relevant documents exist. Use namespaces to partition data when you have large, distinct corpora that will always be queried separately.

Using cosine similarity when your embeddings aren't normalized. Most embedding APIs return normalized vectors, making cosine similarity and dot product equivalent. But some local models (especially fine-tuned ones) may not normalize their output. If your similarity scores look wrong, check whether your vectors are unit-normalized (sum(x**2 for x in vec) ≈ 1.0).

Warning: Never store embeddings generated by one model in an index and then query with embeddings from a different model. The vector spaces are incompatible — the results will be meaningless. If you change embedding models, you must re-embed your entire corpus and rebuild your index.

Skipping metadata entirely. Vector databases let you store rich metadata alongside vectors. If you don't store at least source document IDs, you'll have no way to return citations to the user or debug retrieval failures. For RAG applications, also store the original text chunk so you don't need a second database lookup to fetch the passage. This directly supports building a citation and source attribution system for your RAG responses.

---

Summary & Next Steps

You've covered the full picture: what vector databases actually do (ANN search over high-dimensional embeddings), the architectural tradeoffs between Pinecone, Weaviate, and pgvector, and working Python code for all three. You can now make an informed choice for your project and implement a production-ready vector store.

The key decisions to revisit as your project evolves:

• Scale: If you expect to grow past tens of millions of vectors, plan your database choice before you have to migrate.
• Hybrid search needs: If pure semantic search produces poor results on your domain (common with technical content containing exact product names or version numbers), Weaviate's hybrid search is worth the operational overhead.
• Filtering selectivity: If you need heavy metadata filtering, test it under realistic conditions early — filtering behavior differs significantly across the three systems.

To go deeper, your natural next steps in the Building with LLMs learning path are:

• Retrieval-Augmented Generation Explained: How RAG Works and When to Use It — take the vector retrieval skills you built here and integrate them into a complete RAG pipeline
• Building a Document Q&A System with Embeddings: A Complete Beginner's Guide — a full end-to-end project that ties embeddings, vector search, and LLM generation together
• Implementing Structured Agent Memory — learn how vector databases serve as the long-term semantic memory layer for production AI agents

Vector databases are the connective tissue of the modern LLM stack. Once you're comfortable with the concepts here, you'll find them everywhere.