Vector Databases skill for Codex

Done well means: the store was chosen from measured requirements (vector count, dimensions, filter shape, tenancy, ops budget), collections carry the embedding model name and version, filters are indexed, recall is measured against exact search, and a re-embed can happen without downtime.

Decide before you install anything

Gather these numbers first; ask the user for any you cannot derive from the repo:

Input	Why it matters
Vector count now and in 12 months	Memory and tier sizing
Dimensions and dtype (float32, float16, int8, binary)	count * dims * bytes is the raw floor
Filter shape (tenant, ACL, date, category)	Filtered ANN is where stores differ most
Tenancy model (one tenant per customer?)	Namespaces, partitions or payload filter
Write pattern (bulk nightly vs constant upserts)	Index rebuild cost, consistency
Hosting constraint (managed only, self-host, already run Postgres)	Ops budget
Need lexical/hybrid search in the same engine	Narrows the field

Raw memory estimate: vectors * dims * 4 bytes for float32, then roughly 1.5x for HNSW graph and overhead. 10M x 1536 floats is about 61 GB raw, so quantization or disk-backed indexes stop being optional at that size.

Choice table

Situation	Default	Reason
Already run Postgres, < ~10-20M vectors, relational filters and joins	pgvector ($pgvector-search)	One system, transactions, SQL filters, existing backups
Self-hosted, heavy filtering, hybrid dense+sparse, quantization	Qdrant	Payload indexes, server-side fusion, scalar/binary quantization, aliases
Fully managed, no ops team, serverless pricing	Pinecone (serverless)	Namespaces, no capacity planning; pay per read/write units
Want built-in BM25 hybrid, modules for vectorizing, GraphQL/gRPC API	Weaviate	Native hybrid query with alpha, multi-tenancy per collection
Prototype, notebook, local dev, tests	Chroma (embedded/persistent)	Zero setup; plan the production store separately
Billions of vectors, GPU indexes, dedicated platform team	Milvus / Zilliz	Built for that scale; heavier to run

Default answer when nothing forces a choice: pgvector if Postgres is already in the stack, otherwise Qdrant. Do not add a second database for 200k vectors.

Workflow

- [ ] 1. Read AGENTS.md, docker-compose, IaC and existing clients to see what is already deployed
- [ ] 2. Fill the requirements table; estimate memory with and without quantization
- [ ] 3. Design the collection: vector config, payload schema, indexed filter fields, tenancy
- [ ] 4. Write an ingest path that is idempotent (deterministic IDs) and batched
- [ ] 5. Build a recall harness: ANN top-k vs exact top-k on 200 sampled queries
- [ ] 6. Load-test filtered queries at expected QPS; record p50/p95
- [ ] 7. Document backup/snapshot and re-embed procedures before go-live

Collection design rules

• One embedding model per collection (or per named vector). Store embed_model and embed_version in collection metadata or a config table; never mix vectors from two models in one index, the distances are meaningless across models.
• Distance metric matches the model. Most text embedding models (OpenAI, Cohere, BGE) expect cosine; with unit-normalised vectors dot product is equivalent and cheaper.
• Deterministic IDs: uuid5(namespace, f"{doc_id}:{chunk_no}") so re-ingest upserts instead of duplicating. Qdrant requires UUID or unsigned int IDs.
• Payload holds what you filter and cite: tenant_id, doc_id, source_url, acl, updated_at, chunk_no. Keep full text in the payload only if you need it at query time; otherwise fetch from the primary DB by doc_id.
• Index every filter field. Unindexed payload filters force scans and wreck latency.

Qdrant example

from qdrant_client import QdrantClient, models

client = QdrantClient(url="http://localhost:6333")
client.create_collection(
    collection_name="kb_v2",
    vectors_config={"dense": models.VectorParams(size=1536, distance=models.Distance.COSINE, on_disk=True)},
    sparse_vectors_config={"sparse": models.SparseVectorParams(modifier=models.Modifier.IDF)},
    quantization_config=models.ScalarQuantization(
        scalar=models.ScalarQuantizationConfig(type=models.ScalarType.INT8, always_ram=True)
    ),
)
client.create_payload_index("kb_v2", field_name="tenant_id",
                            field_schema=models.KeywordIndexParams(type="keyword", is_tenant=True))
client.create_payload_index("kb_v2", field_name="updated_at", field_schema="datetime")
client.update_collection_aliases(change_aliases_operations=[
    models.CreateAliasOperation(create_alias=models.CreateAlias(collection_name="kb_v2", alias_name="kb"))
])

hits = client.query_points(
    collection_name="kb",  # always query the alias
    query=query_vec, using="dense", limit=10,
    query_filter=models.Filter(must=[
        models.FieldCondition(key="tenant_id", match=models.MatchValue(value=tenant)),
    ]),
    search_params=models.SearchParams(hnsw_ef=128, quantization=models.QuantizationSearchParams(rescore=True)),
).points

Pinecone example

from pinecone import Pinecone, ServerlessSpec

pc = Pinecone()  # PINECONE_API_KEY; needs network
if not pc.has_index("kb-v2"):
    pc.create_index("kb-v2", dimension=1536, metric="cosine",
                    spec=ServerlessSpec(cloud="aws", region="us-east-1"))
index = pc.Index("kb-v2")
index.upsert(vectors=[{"id": cid, "values": vec, "metadata": meta}], namespace=tenant)
res = index.query(vector=q, top_k=10, namespace=tenant, include_metadata=True,
                  filter={"doc_type": {"$in": ["guide", "faq"]}})

Dimension and metric are fixed at index creation. Namespaces are the tenant boundary; a query never crosses namespaces, which is exactly what you want for isolation.

Multi-tenancy

Tenants	Pattern
Few, large, strict isolation	Separate collections or indexes
Many small (thousands+)	Single collection with an indexed tenant_id (Qdrant is_tenant), Pinecone namespaces, Weaviate multi-tenancy
Mixed	Large tenants get their own collection; the long tail shares one

The tenant filter is a security control. Enforce it in one server-side function that every query goes through, and write a test that a query for tenant A never returns tenant B data.

Quantization and memory

Mode	Memory vs float32	Recall impact	Use
float16 / halfvec	50%	Negligible	Almost always safe
Scalar int8	~25%	Small; rescoring recovers most	Default at > 5M vectors
Binary (1 bit)	~3%	Large unless rescored with originals	High-dim models (>= 1024) with oversampling + rescore
Product quantization	Configurable	Model-dependent	Very large corpora; measure carefully

Always keep original vectors (on disk) for rescoring and future index rebuilds.

Re-embedding without downtime

Embedding model upgrades are the most common painful migration. Plan for it on day one:

1. Create a new collection kb_v3 with the new dimensions and metric.
2. Backfill from the source of truth (not from the old vector store), batched and resumable.
3. Dual-write new and updated documents to both collections during the backfill.
4. Run the recall harness and the retrieval eval set against kb_v3.
5. Flip the alias (Qdrant aliases; Pinecone: switch the index name in config; pgvector: new column or table, then swap in a transaction).
6. Keep kb_v2 for a rollback window, then delete it only after the user confirms.

Query embeddings must come from the same model as the collection. Ship the alias flip and the query-side model change together, behind one config value.

Operations

• Backups: Qdrant snapshots (POST /collections/{name}/snapshots), Weaviate backup modules, Postgres base backups for pgvector, Pinecone collections or backups depending on plan. Restore one into a scratch environment before trusting it.
• Deletes: deleting a document must delete all of its chunks (filter doc_id = X). Test it; orphaned chunks keep surfacing deleted content.
• Monitoring: p95 query latency split by filtered/unfiltered, segment or index count, memory, indexing queue length, and recall from the periodic harness.
• Destructive calls (delete_collection, delete_index, dropping tables) require explicit user confirmation naming the collection and environment.

Gotchas

• Chroma’s defaults (L2 distance in older versions, embedded SQLite) are fine for prototypes; set the space explicitly to cosine and do not ship the embedded mode behind a multi-worker server.
• Highly selective filters on HNSW can return fewer than k results or degrade recall. Use payload indexes, filter-aware search (Qdrant, Weaviate), partitions per tenant, or raise ef.
• Upsert batches that are too large time out; 100-500 vectors per request is a safe start.
• Cosine scores are not probabilities and shift between embedding models; never hard-code a similarity threshold without re-validating it after a model change.
• Managed-service network calls need network access; in a Codex sandbox use a local Docker instance (docker run -p 6333:6333 qdrant/qdrant) for tests when network is off.

Verification

# Recall@k of ANN vs exact search on sampled queries
def recall_at_k(ann_ids: list[list[str]], exact_ids: list[list[str]], k: int = 10) -> float:
    return sum(len(set(a[:k]) & set(e[:k])) / k for a, e in zip(ann_ids, exact_ids)) / len(ann_ids)

• Exact search: Qdrant SearchParams(exact=True), pgvector with index scans disabled, or brute-force NumPy over a sample. Target recall@10 >= 0.95 for RAG unless the user accepts less.
• Tenant isolation test passes; delete-document test leaves zero chunks.
• Load test at expected QPS with real filters meets the latency target.

Report template

**Choice:** Qdrant 1.x self-hosted (reasons: 18M vectors, tenant filter + date filter, hybrid)
**Sizing:** 18M x 1024 float32 = 74 GB raw; int8 scalar in RAM 18 GB, originals on disk
**Collection:** kb_v3 (alias kb), cosine, payload indexes tenant_id (is_tenant), updated_at
**Recall@10 vs exact:** 0.97 at hnsw_ef=128, rescore on; p95 38 ms filtered at 200 QPS
**Runbooks written:** snapshot/restore, re-embed via alias flip, document delete