Qdrant & PostgreSQL pgvector

1. Specialized Engine vs. Unified Relational Database

When engineering production AI applications, engineering teams face a fundamental architectural fork:

2. PostgreSQL pgvector: Schema, Distance Operators & Indexing

With pgvector, your vectors live in the same row as your relational application data. Enable the extension and create a table with a typed vector column:

-- Step 1: Enable the pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Step 2: Create a knowledge base table with 1536-dimensional vector column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    organization_id UUID NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    metadata JSONB,
    embedding vector(1536),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

pgvector Distance Operators:

Building HNSW and IVFFlat Indexes in Postgres:

-- Option A: HNSW Index (Fastest query time, higher RAM usage)
CREATE INDEX ON documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Option B: IVFFlat Index (Lower memory, requires population before building)
-- Tip: Set lists = num_rows / 1000 for up to 1M rows
CREATE INDEX ON documents 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

Combining Vector Search with Relational SQL JOINs:

-- Find top 5 relevant documents strictly belonging to the active organization
SELECT 
    d.id,
    d.title,
    1 - (d.embedding <=> $1) AS cosine_similarity
FROM documents d
JOIN organizations org ON d.organization_id = org.id
WHERE org.is_active = TRUE
  AND org.plan_tier = 'enterprise'
ORDER BY d.embedding <=> $1
LIMIT 5;

3. Qdrant: High-Speed Rust Vector Search & Payload Filtering

Standard HNSW graphs suffer when combined with restrictive metadata filters: if you search a graph and filter afterwards (post-filtering), you might eliminate 95% of the results.Qdrant solves this with Filterable HNSW, traversing the graph while dynamically evaluating payload constraints at every node hop.

from qdrant_client import QdrantClient
from qdrant_client.http import models

# Initialize in-memory or connect to local Docker: QdrantClient("http://localhost:6333")
client = QdrantClient(":memory:")

COLLECTION_NAME = "enterprise_support"

# Create collection with Cosine distance
client.create_collection(
    collection_name=COLLECTION_NAME,
    vectors_config=models.VectorParams(
        size=384,
        distance=models.Distance.COSINE
    )
)

# Upload Points (Vectors + Metadata Payloads)
client.upload_points(
    collection_name=COLLECTION_NAME,
    points=[
        models.PointStruct(
            id=1,
            vector=[0.05] * 384,
            payload={"category": "billing", "region": "us-east", "status": "resolved"}
        ),
        models.PointStruct(
            id=2,
            vector=[0.82] * 384,
            payload={"category": "technical", "region": "eu-central", "status": "open"}
        )
    ]
)

# Query with Payload Filter
search_result = client.query_points(
    collection_name=COLLECTION_NAME,
    query=[0.06] * 384,
    limit=2,
    query_filter=models.Filter(
        must=[
            models.FieldCondition(
                key="category",
                match=models.MatchValue(value="billing")
            )
        ]
    )
)

print("Qdrant Top Matches:", [hit.id for hit in search_result.points])

4. Architectural Decision Guide: When to Pick Which?