Four store types and when to use each
- Relational (Postgres, MySQL) — rows/tables, fixed schema, ACID transactions, joins, SQL. The default for structured, integrity-critical data: users, subscriptions, submissions, scores. Postgres is the pragmatic default — and via pgvector it doubles as a vector store.
- Document (MongoDB) — schema-flexible JSON documents. Good for heterogeneous, nested, evolving data (variable-shape logs). Weaker cross-document joins/transactions.
- Vector (pgvector, Pinecone, Weaviate, Qdrant) — stores embeddings and does approximate nearest-neighbor (ANN) similarity search — the retrieval backbone of RAG. Index: HNSW (best recall/latency, more memory) or IVFFlat. Metric: usually cosine.
- Key-value (Redis) — O(1) get/set, in-memory speed. Caching, sessions, rate-limit counters, ephemeral agent state, pub/sub.
Storing conversation, session & agent state
Messages/threads live in a relational or document store: one row per message with conversation_id, role, content, created_at, token counts — reconstruct context by querying the thread. Keep live working state in Redis (fast, TTL'd) but persist durable checkpoints to Postgres so an agent can resume after a crash. Long-term memory / RAG: embed content into a vector store and retrieve top-k at inference.
Caching — a big cost/latency lever
- Standard cache: Redis in front of the DB for hot reads; set TTLs, pick an eviction policy (LRU), and guard against cache stampede (many misses at once) with locks or request coalescing.
- Semantic caching: cache LLM responses keyed by embedding similarity of the prompt, so a near-duplicate question hits the cache — a big cost saver, but tune the threshold or you'll return a wrong-but-similar answer.
- Prompt caching (provider-side, distinct) caches large repeated prompt prefixes to cut input-token cost/latency. Don't confuse it with app-level semantic caching.