Polyglot persistence: the right datastore for each job (and keeping them in sync)
No single database is best at everything, so serious systems use several — search, vector, graph, relational, object storage — each for what it's good at. The hard part isn't picking them; it's keeping them consistent.
A document-heavy AI product ends up storing the same information several ways, because no one database is good at all of them. That's polyglot persistence: use the store that fits each access pattern, and accept that the real engineering is keeping them in sync.
Choosing the store per access pattern
- A search engine (OpenSearch/Elasticsearch) for lexical, keyword, and phrase queries.
- A vector database for semantic similarity search (see the vector-search posts).
- A graph database for relationships and multi-hop traversal.
- Postgres (or similar) for transactional, relational, per-tenant metadata — the source of truth.
- Object storage (S3) for the raw artifacts: PDFs, XML, large files, cheap and durable.
The hard part: cross-store consistency
The moment the same fact lives in two stores, you have a consistency problem. The naive approach — dual writes, where the app writes to Postgres and then to the search index — fails silently: the second write can error after the first succeeded, and now the stores disagree with no transaction to roll back. That's the dual-write problem, and it's the source of most 'the search results are stale' bugs.
CDC and the outbox pattern
The robust fix is to make one store the source of truth and derive the others from its change stream. Change Data Capture (CDC) tails the database's write log and emits an event per change, which downstream consumers apply to the search index, the vector store, and the graph. The outbox pattern gets the same guarantee without a log tailer: within the same database transaction that writes the data, you write an event to an outbox table, and a relay reliably publishes it — so the event is emitted if and only if the data was committed. Either way you accept eventual consistency (the derived stores lag by a moment) in exchange for never losing or duplicating an update.
MongoDB vs Postgres for tenant metadata
For the source-of-truth metadata store, the usual question is document (MongoDB) vs relational (Postgres). Postgres wins when you need transactions, joins, constraints, and strong per-tenant isolation with row-level security — which is most SaaS metadata. MongoDB wins for flexible, evolving document shapes without migrations. For multi-tenant systems with real integrity and isolation requirements, Postgres is usually the safer default; reach for MongoDB when the schema genuinely won't hold still.
Polyglot persistence isn't about collecting databases — it's about admitting no single store is best at everything, then doing the unglamorous work of keeping them honest with each other.