vector-database

# Vector Database Skill ## Iron Law **Pin dimensions at model selection.** The embedding dimension must match the model exactly and is set once — changing models requires a full re-embedding migration. Store `embedding_model varchar(100)` alongside every vector column so the model is always auditable. --- ## Embedding Model Selection Choose model before writing any schema. Dimension determines index shape. | Model | Dims | Notes | |-------|------|-------| | `text-embedding-3-small` (OpenAI) | 1536 | Best cost/quality for most cases | | `text-embedding-3-large` (OpenAI) | 3072 | Max OpenAI quality, 2× cost | | `embed-english-v3.0` (Cohere) | 1024 | Native Weaviate integration | | `voyage-3-large` (Voyage AI) | 1024 | Top retrieval benchmarks | | `nomic-embed-text` (local) | 768 | Free, on-prem, no API call | **Rule:** Once a dimension is written into schema, it cannot change without `/migrate-embedding-model`. --- ## pgvector ### When to Use - Data lives in PostgreSQL (tickets, vendors, invoices, tenant records) - You need vector search + relational JOINs in a single query - Filtering on structured columns (city, category, rating) alongside semantic search ### Vector Column Declaration ```sql -- ALWAYS enable extension in migration up CREATE EXTENSION IF NOT EXISTS vector; -- Column: dimension must match model exactly ALTER TABLE vendors ADD COLUMN embedding vector(1536); ALTER TABLE vendors ADD COLUMN embedding_model varchar(100) DEFAULT 'text-embedding-3-small'; ``` ### Distance Operators | Operator | Distance Type | Use When | |----------|--------------|----------| | `<->` | L2 (Euclidean) | Default; requires L2-normalized embeddings | | `<#>` | Negative inner product | Dot-product similarity (fast for normalized vecs) | | `<=>` | Cosine | Cosine similarity; works unnormalized | **Critical:** The operator used in queries MUST match the operator used in `CREATE INDEX`. Mismatch = full table scan silently. ### Index Selection | | HNSW | IVFFlat | |--|------|---------| | **Build speed** | Slow, high memory | Fast, needs existing data | | **Query recall** | Higher (~0.99) | Tunable via `probes` | | **Use when** | >100K rows, production | Dev / budget-constrained / <100K rows | | **Update behavior** | Good — no rebuild needed | Degrades; needs ANALYZE after bulk inserts | ```sql -- HNSW (production default) CREATE INDEX ON vendors USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- IVFFlat (dev/budget) CREATE INDEX ON vendors USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); -- After bulk insert: ANALYZE vendors; ``` ### Example Query Pattern (vendor matching) ```sql SELECT v.id, v.name, v.city, v.rating, 1 - (v.embedding <=> $1::vector) AS similarity FROM vendors v WHERE v.city = $2 AND v.category = $3 AND v.rating >= $4 AND v.embedding IS NOT NULL ORDER BY v.embedding <=> $1::vector LIMIT 10; ``` For full migration template → see `references/pgvector-migration-template.md` --- ## Weaviate ### When to Use - Data lives outside PostgreSQL (scraped text, PDFs, docs, external reviews) - You need native hybrid search (BM25 + vector) with fusion control - Multi-tenancy (one collection shared across tenants) - Phase 2+ RAG against GCS / external document stores ### Collection Schema Essentials ```python import weaviate import weaviate.classes as wvc client = weaviate.connect_to_weaviate_cloud( cluster_url=os.environ["WEAVIATE_URL"], auth_credentials=wvc.init.Auth.api_key(os.environ["WEAVIATE_API_KEY"]), ) # ALWAYS set multi_tenancy at creation — cannot change later client.collections.create( name="VendorReview", vectorizer_config=wvc.config.Configure.Vectorizer.text2vec_openai( model="text-embedding-3-small", ), vector_index_config=wvc.config.Configure.VectorIndex.hnsw( distance_metric=wvc.config.VectorDistances.COSINE, ), multi_tenancy_config=wvc.config.Configure.multi_tenancy(enabled=True), properties=[ wvc.config.Property(name="text", data_type=wvc.config.DataType.TEXT), wvc.config.Property(name="source", data_type=wvc.config.DataType.TEXT), wvc.config.Property(name="vendor_id", data_type=wvc.config.DataType.TEXT), ], ) ``` ### Query Patterns ```python collection = client.collections.get("VendorReview") # nearText (semantic) result = collection.query.near_text(query="plumber emergency repair", limit=5) # Hybrid search (BM25 + vector) # alpha=1.0 = pure vector | alpha=0.0 = pure BM25 result = collection.query.hybrid( query="24-hour plumber", alpha=0.75, fusion_type=wvc.query.HybridFusion.RELATIVE_SCORE, limit=10, ) ``` **Fusion algorithms:** - `RANKED_FUSION` — rank-based merge; stable, good default - `RELATIVE_SCORE_FUSION` — score-normalized merge; better when score magnitude matters For named vectors and multi-tenant patterns → see `references/weaviate-collection-patterns.md` --- ## Two-Tier Architecture Decision When building a system with both structured and unstructured data: | Tier | Store | Use For | |------|-------|---------| | 1 | pgvector (in Cloud SQL) | Data with a DB row — vendors, tickets, invoices, tenant records | | 2 | Weaviate Serverless | Data outside DB — scraped reviews, PDFs in GCS, external docs | **Rule:** Keep data in the tier where it originates. Don't sync Postgres rows to Weaviate at MVP — async sync lag introduces consistency bugs. Move to Weaviate only when matching against external documents. --- ## Slash Commands - `/design-vector-schema` — pgvector migration SQL from model + row count + metric - `/design-weaviate-collection` — Weaviate Python collection from vectorizer + schema - `/scaffold-rag-pipeline` — Full RAG pipeline: chunk → embed → store → retrieve → rerank - `/migrate-embedding-model` — Re-embedding migration plan + reversible SQL - `/tune-vector-index` — HNSW vs IVFFlat recommendation + tuned params ## Reference Files | File | Content | When to Use | |------|---------|-------------| | `references/pgvector-migration-template.md` | Full migration SQL with up/down, index, metadata column | pgvector schema design | | `references/weaviate-collection-patterns.md` | Named vectors, multi-tenancy, advanced queries | Weaviate collection design | | `references/rag-pipeline-patterns.md` | Chunking strategies, retrieval, reranking, LangChain wiring | Building RAG pipelines | | `references/embedding-migration-guide.md` | Step-by-step model switching procedure | Re-embedding migrations | | `references/vector-index-tuning-playbook.md` | Quantization strategies, HNSW benchmarking, memory estimation, Qdrant config | Index tuning and performance optimization | | `references/ann-vs-knn.md` | KNN vs ANN tradeoffs, HNSW/IVFFlat tuning, recall measurement, high-risk scoped verification pattern | Choosing and tuning retrieval index strategy | | `references/rag-ingest-checklist.md` | Production ingestion checklist: embeddings, chunking, null guards, multi-tenancy | Safe data ingestion into vector stores | | `references/tier-1-tier-2-decision.md` | Decision framework: pgvector (hot path, SQL joins) vs Weaviate (cold path, pure semantic) | Choosing the right vector store for a use case | ## Documentation Sources Before generating code, consult these sources for current APIs: | Source | Tool | Purpose | |--------|------|---------| | pgvector | `Context7` MCP (`pgvector` library) | Vector operators, index syntax, distance functions | | Weaviate Python v4 | `weaviate-docs` MCP | Collection API, query patterns, named vectors | | OpenAI Embeddings | `Context7` MCP (`openai` library) | text-embedding-3-small/large, dimensions, pricing | | Cohere Embed | `Context7` MCP (`cohere` library) | embed-english-v3.0, batch embedding | ## Post-Code Review After writing vector database code, dispatch: - `pgvector-schema-reviewer` — operator/index alignment, dimension match, null guards, reversible migrations - `weaviate-schema-reviewer` — v4 client API, multi-tenancy declaration, distance metric - `rag-pipeline-reviewer` — model pinning, batch embedding, silent retrieval failure risks - `postgresql-database-reviewer` — for the broader migration (indexes, constraints, performance)