Choosing a vector store

The algorithm and the product are separate decisions. HNSW can live in Postgres, in OpenSearch, or in a dedicated vector database. Pick the store from scale, filters, update rate and who wants to operate it — not from a logo.

The menu

Decision criteria in practice: scale and RAM, filter complexity, update and delete rate, ops ownership, hybrid search, and cost model. Managed vector DBs price on storage plus QPS. Disk-first designs change the economics for huge cold corpora.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id         bigserial PRIMARY KEY,
  tenant_id  text NOT NULL,
  doc_id     text NOT NULL,
  content    text NOT NULL,
  embedding  vector(1024) NOT NULL
);

CREATE INDEX ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

That sketch keeps vectors next to tenant_id. Set ef_search per session when you tune recall. If BM25+vector in one query path matters on day one, prefer OpenSearch, Elastic, Weaviate, Qdrant, or an app-side fusion. See advanced RAG.

Operations that decide the move

Build time. HNSW is CPU-heavy. Millions of vectors can take minutes to hours. Bulk-load, then build. Avoid building interactively on every small upsert if the engine supports deferred optimization.

Updates and deletes. Append-mostly corpora are friendliest. High churn (per-user memory rewritten constantly) fragments the graph. Deletes may be soft-deleted until compaction — filter deleted=false if tombstones linger. A model upgrade is a full re-embed: treat it as a new index generation.

Observability. Track recall@k offline, query latency, filter empty-result rate, index RAM, upsert lag and delete visibility. A silent efSearch default that is too low looks like "RAG got worse" after a corpus grows. Dimension or metric mismatches fail loudly in good systems and corrupt quietly in bad glue code.

Common mistakes

• Buying a dedicated store for the first 80k chunks. Postgres will do.
• Forgetting tombstones. Soft-deleted chunks still get retrieved.
• In-place mutation when the embedding model changes. Build a new generation.

Check yourself

1. When does pgvector usually stop being enough? When RAM, rebuild time, or filter/hybrid needs hurt — not at the first demo.
2. You upgrade the embedding model. Do you UPDATE the old rows? No. Re-embed into a new index generation. The spaces are different.

Key takeaways

• Start with pgvector when Postgres is enough.
• Move to a dedicated store for scale, hybrid search or ops reasons.
• Size memory from first principles: 10M × 1024 × 4 bytes ≈ 40 GB raw.
• Watch upsert lag, tombstones and recall after growth.

Further learning

pgvectorOpen-source vector similarity search for Postgres. Contribute to pgvector/pgvector development by creating an account on GitHub.GitHub

Pinecone Learning CenterPinecone is the trusted AI knowledge company. Its AI knowledge platform—Database, Nexus, and Marketplace—powers accurate, fast, cost-effective AI for 10,000+ customers and 1M developers worldwide.pinecone.io