Vector Databases

pgvector, Qdrant, and Weaviate — indexing, metadata, and filtering.

7 min readAI for SRE WorkflowsMaster

What Is a Vector Database?

A vector database stores embedding vectors and supports fast similarity search. Unlike a regular database that finds rows by exact match, a vector database finds the most similar vectors using approximate nearest neighbor (ANN) algorithms.

Vector Database Options

Database	Type	Best For	Ops Overhead
pgvector	PostgreSQL extension	Teams already using Postgres	Low
Qdrant	Standalone service	High-performance search	Medium
Weaviate	Standalone service	Hybrid search built-in	Medium
Pinecone	Managed service	No ops overhead	None
Chroma	Embedded library	Local dev and testing	None

pgvector with PostgreSQL

pgvector adds vector operations to PostgreSQL. If your team already runs Postgres, this is the lowest-friction option. No new infrastructure to manage.

Setting up pgvector


-- Enable the extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create a table for document chunks
CREATE TABLE doc_chunks (
    id SERIAL PRIMARY KEY,
    text TEXT NOT NULL,
    embedding VECTOR(1536),  -- OpenAI text-embedding-3-small dimension
    source VARCHAR(255),
    service VARCHAR(100),
    doc_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Create an HNSW index for fast similarity search
CREATE INDEX ON doc_chunks
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

HNSW (Hierarchical Navigable Small World) is the recommended index type for most use cases. It provides fast approximate search with good recall.

Searching with pgvector


-- Find the most similar chunks to a query embedding
SELECT text, source, service,
       1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM doc_chunks
WHERE service = 'redis'
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

The <=> operator computes cosine distance. Subtracting from 1 gives cosine similarity. The WHERE clause filters by metadata before the vector search.

Metadata Filtering

Metadata filtering restricts the search to a subset of documents before similarity search runs. This is critical for operational RAG — you only want to retrieve documents relevant to the service or incident type being asked about.

Filtered retrieval with pgvector


import psycopg2
import openai

def retrieve_relevant_chunks(query, service=None, doc_type=None, top_k=5):
    # Get query embedding
    emb = openai.Embedding.create(
        model="text-embedding-3-small",
        input=query
    )["data"][0]["embedding"]

    conn = psycopg2.connect("dbname=ops_rag")
    cur = conn.cursor()

    # Build query with optional metadata filters
    sql = """
        SELECT text, source,
               1 - (embedding <=> %s::vector) AS similarity
        FROM doc_chunks
        WHERE 1=1
    """
    params = [str(emb)]

    if service:
        sql += " AND service = %s"
        params.append(service)
    if doc_type:
        sql += " AND doc_type = %s"
        params.append(doc_type)

    sql += " ORDER BY embedding <=> %s::vector LIMIT %s"
    params.extend([str(emb), top_k])

    cur.execute(sql, params)
    return cur.fetchall()

Always filter by metadata when possible. Reducing the search space improves both speed and accuracy.

Indexing Strategies

The choice of index affects search speed, accuracy, and memory usage. For most operational use cases, HNSW provides the best balance.

Index	Speed	Accuracy	Memory	Build Time
HNSW	Fast	High	Higher	Slow
IVF	Fast	Medium	Lower	Fast
Flat (exact)	Slow	Perfect	Lowest	None

Start with HNSW

For datasets under 10 million vectors, HNSW with m=16 and ef_construction=64 is a reliable default. Tune ef_search to balance speed and recall based on your latency requirements.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.

Stage 7 · Master

RAG Over Your Ops Knowledge